It might be a good idea to log a TAR re. this with Oracle.
: )
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin & Operations | Admin. et Exploit. des syst�mes
Technology Services | Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO | R�gion des Maritimes, MPO
E-Mail: [EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, September 11, 2002 4:38 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cannot see parallel hint in outline?
<< File: InterScan_Disclaimer.txt >>
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
