>> - Original Message -
>> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
>> Sent: Wednesday, July 09, 2003 4:14 PM
>>
>>
>> what are your
>> optimizer_index_caching
>> optimizer_index_cost_adj
>> optimi
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_cachin
ltiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: 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/200
TECTED]>
Sent: 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 R
ultiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Thursday, July 10, 2003 12:14 AM
>
>
> what are your
> optimizer_index_caching
> optimizer_index_cost_adj
> optimizer_max_permutations
> set to?
>
>
> >>> [EMAIL PROTECTED]
uly 10, 2003 12:14 AM
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
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 no
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
PRCD_INSTRUCTION_RUNSHEET.HIERARCHICAL = 'Y' )
> AND ( PRCD.PRCD_ACTIVE_FLAG = 'A' )
> AND ( PART.PART_ACTIVE_FLAG = 'A' )
> AND ( PART.OBSELETE_FLAG <> 'Y' )
> AND PRCD_INSTRUCTION_RUNSHEET.PRCD_ID NOT LIKE 'F-%'
>
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 seconds.
What
)
AND ( PART.PART_ACTIVE_FLAG = 'A' )
AND ( PART.OBSELETE_FLAG <> 'Y' )
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 &#x
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 optimisor
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:
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:
Co
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
ignora
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
Op
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
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
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
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 ORACLE-
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 a
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 < 2
.4, Apps 11.5.3, HP-UX 11.11
> >
> > Listed below is the query and the explain plan. 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
ject: Optimizer help, get 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 bel
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 beha
25 matches
Mail list logo
