to?
[EMAIL PROTECTED] 7/9/2003 3:39:31 PM
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries are not that
complicated, but joins 10 - 12 tables. I compared the plans between RULE
hint and witout RULE hint, they are almost
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries are not that
complicated, but joins 10 - 12 tables. I compared the plans between RULE
hint and witout RULE hint, they are almost the same except join methods.
Also, I checked
what are your
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
set to?
[EMAIL PROTECTED] 7/9/2003 3:39:31 PM
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries are not that
complicated
what are your
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
set to?
[EMAIL PROTECTED] 7/9/2003 3:39:31 PM
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries are not that
complicated, but joins 10
optimizer_index_cost_adj
optimizer_max_permutations
set to?
[EMAIL PROTECTED] 7/9/2003 3:39:31 PM
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries are not that
complicated, but joins 10 - 12 tables. I compared the plans
: Wednesday, July 09, 2003 4:14 PM
what are your
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
set to?
[EMAIL PROTECTED] 7/9/2003 3:39:31 PM
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
set to?
[EMAIL PROTECTED] 7/9/2003 3:39:31 PM
Few sql queries run fast (less than a second) when I use RULE hint and
without RULE hint it takes 30 seconds. The queries are not that
complicated, but joins 10 - 12 tables
You also might try out first_rows hint instead of rule. Oracle keeps
threatening to take the rule hint away.
Jay
-Original Message-
Sent: Wednesday, July 09, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L
Hi!
It's generally good idea to review optimizer_index_caching
'L000%'
AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'PCW%'
)
/
As you can see, I've even tried Index Hints.
The RULE hint gives me the best performance and usage of
indexes:
Operation OptionsObject ID PID
Pos
. I ran full statistics on
all the tables immediately before executing the query. Using the CBO, it
never returns. I cancel the query, but it won't die until I kill the unix
process.
If I use the /*+ RULE */ hint, the query plan (also listed below) changes
dramatically and the query executes in 30
I prefer the analogy of asking if Einstein theory
of general relativity was correct before the technology
was available to prove it correct ;)
Two serious points though:
I didn't say untraceable I said 'a lot harder to decide why'.
Secondly - if you start with the premise that hints are
The cartesian merge join in the last
two plans is interesting feature. It's
the sort of thing you associate with
very small tables - not tables which
look as if they might be returning
large volumes of data.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Given the limited information I supplied, this
is a perfectly reasonable speculation. The
following three extracts from the 10053 trace
(under 8.1.7.4) may help to clarify my point,
though:
S_A_S set to 32K:
Sort width:2 Area size: 24576 Degree: 1
Blocks to Sort:
Hemant,
You are right - the execution plan does change. However it looks as though
the cost decreases each time you increase the sort_area_size. Presumably
as the sort area grows in size more options become feasible to Oracle so it
changes it's approach. This is an indication that the
' )
AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'F-%'
AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'L000%'
AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'PCW%'
)
/
As you can see, I've even tried Index Hints.
The RULE hint gives me the best performance and usage of
indexes
Coincidentally, one of the points I mentioned at the
Hotsos Symposium was the increasing the sort_area_size
could affect execution paths for the worse. (Even when
there is no risk of excess memory usage causing swapping).
I was going to post a simple example to demonstrate this -
and then cane
Did the increase in SORT_AREA_SIZE change the execution plan, regardless of
the use of hints (since certain hints can easily be ignored)? Larger
SORT_AREA_SIZE might encourage the CBO to choose a SORT-MERGE join, for
example...
- Original Message -
To: Multiple recipients of list
Absolutely identical plans, and traces -
the only change was that the cost of the
sort step went up. This was testing on a
very simple plan too, trying to trade between
sort (order by)
and an index driven order by - so nothing as
complex as messing with a merge join.
If you're interested in
Sounds more and more like a if a tree falls in the
forest and no-one is there kind of discussion..
We're getting to the stage where there is an
indeterminate number of untraceable but deliberate
reasons for the optimizer to 'avoid' a hint. Without
access to the source code, that's getting pretty
Have not had the chance to research this but when I read your message I saw
that the numbers might make sense if there is nothing else missing.
You are saying the cost / pass 18 when it was 1M.
Do not you think that '18' is the cost for one pass of memory sort in 1M of
memory?
If the answer is
Here's one bad SQL I am working on right now.
Even the execution plan changes with a different SORT_AREA_SIZE !
9.2.0.2 on Tru64 :
SQL alter session set sort_area_size=1048576;
Session altered.
SQL @expl_PRCD
14 rows deleted.
Explained.
SQL @explain
Enter value for statement: PRCD_H
23815K 826
Doesn't this suggest setting sort_area_size larger?
-Original Message-
From: Glenn Travis
Sent: Friday, February 14, 2003 11:04 AM
To: Multiple recipients of list ORACLE-L
Subject: Optimizer help, get query to run as good as with RULE hint
I have
I've gathered statistics many different ways to get queries against the
trading partner tables (i.e., HZ) to do something better to or equal a
rule hint, and in the end a sort area of 1M or less and leaving
hash_area_size unset (defaults to 2mb or less) works best. With
hash_area_size 2Mb, hash
executing the query. Using the CBO, it never returns. I
cancel the query, but it won't die until I kill the unix process.
If I use the /*+ RULE */ hint, the query plan (also listed below) changes dramatically
and the query executes in 30 seconds.
What could cause the optimizer to behave so
query to run as good as with RULE hint
I have a problem query which will not complete. This query
is part of a report run within Oracle Applications.
Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11
Listed below is the query and the explain plan. I ran full
statistics
25 matches
Mail list logo
