RE: Cannot see parallel hint in outline?

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=E8mes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique=20
Maritimes Region, DFO      | R=E9gion des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca

 -----Original Message-----

From: 	Chuan Zhang [mailto:Chuan.Zhang_at_transact.com.au]=20
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 >>=20 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?=20

Thanks

Chuan

-----Original Message-----
Sent: Wednesday, 11 September 2002 3:53 PM To: Multiple recipients of list ORACLE-L

=09
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=20
where substr(filename,1,3)=3D'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=3DCHOOSE (Cost=3D62067 Card=3D1 = Bytes=3D2

          8)

   1 0 SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)
   3    2       TABLE ACCESS (FULL) OF 'test' (Cost=3D62067 Card=3D1
          07970 Bytes=3D3023160)

Actually I want to see the following execution plan:=20

Execution Plan

   0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D62067 Card=3D1 = Bytes=3D2

          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=3D62067 = Car
:Q115000

          d=3D5397992 Bytes=3D151143776)

   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?=20

BTW, I set up:
alter session set query_rewrite_enabled=3Dtrue; alter session set use_stored_outlines=3Dtrue; Received on Wed Sep 11 2002 - 05:52:39 CDT