Further to this question, I found another interesting thing. The results between
ol$hints of outln and user_outlines are inconsistent after I swap the outline
names(bad sql and tuned name). User_outlines's name didn't reflect the swapping.
Is there something wrong?
Thanks
Chuan
-----Original Message-----
Sent: Wednesday, 11 September 2002 3:53 PM
To: Multiple recipients of list ORACLE-L
Hi, All,
I created a plan as follows:
create or replace outline hsubstr_vchfilename
on select /*+ full(test) parallel(test, 10) */ max(num_sequencel) + 1 from test
where substr(filename,1,3)='AAA';
And I got the following outlines:
OL_NAME HINT# CATEGORY HINT_TYPE
HINT_TEXT STAGE NODE# TABLE_NAME TABLE_TIN TABLE_POS
HSUBSTR_VCHFILENAME 1 DEFAULT 0 NO_EXPAND
3 1 0 0
HSUBSTR_VCHFILENAME 2 DEFAULT 0 ORDERED
3 1 0 0
HSUBSTR_VCHFILENAME 3 DEFAULT 0 NO_FACT(test)
3 1 test 1 0
HSUBSTR_VCHFILENAME 4 DEFAULT 0 FULL(test) 3
1 test 1 1
HSUBSTR_VCHFILENAME 5 DEFAULT 0 NOREWRITE
2 1 0 0
HSUBSTR_VCHFILENAME 6 DEFAULT 0 NOREWRITE
1 1 0 0
Why couldn't I see hint_text for parallel hints?
The actually execution plan output like follows:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62067 Card=1 Bytes=2
8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (FULL) OF 'test' (Cost=62067 Card=1
07970 Bytes=3023160)
Actually I want to see the following execution plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=62067 Card=1 Bytes=2
8)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q115000
3 2 PARTITION RANGE* (ALL) :Q115000
4 3 TABLE ACCESS* (FULL) OF 'TBL_RAWAMA' (Cost=62067 Car :Q115000
d=5397992 Bytes=151143776)
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(MAX(A1.C0))
FROM (SELECT /*+ NO_EXPAND ROWID(A2
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
What am I missing? or Oracle just do this way?
BTW, I set up:
alter session set query_rewrite_enabled=true;
alter session set use_stored_outlines=true;
alter session set cursor_sharing=force;
Any experience or idea?
TIA
Chuan
Unless otherwise stated, this e-mail does not represent the views of
TransACT Communications Pty Limited. This text and any attachments of
this e-mail are confidential and may be legally privileged. This email
is for the use of the intended recipient only. If you are not the intended
recipient do not take any action in relation to this email, other than to
notify TransACT Communications by replying to this e-mail and destroying
the original communication. Except as required by law, TransACT
Communications does not represent that this transmission is free of errors,
viruses or interference.
