Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Tuning Query help

Tuning Query help

From: astalavista <nobody_at_nowhere.com>
Date: Mon, 22 Jan 2007 22:01:38 +0100
Message-ID: <45b5262c$0$305$426a74cc@news.free.fr> 





Hi,



Can you help me with this query, below the 10053 trace:



Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options



JServer Release 9.2.0.4.0 - Production



ORACLE_HOME = /logi/ora/9204



System name: AIX



Node name: CBSU06



Release: 3



Version: 4



Machine: 00538D4A4C00



Instance name: P08



Redo thread mounted by this instance: 1



Oracle process number: 33



Unix process pid: 93962, image: oracle_at_CBSU06 (TNS V1-V3)




QUERY


SELECT a.mois,



DBMS_UTILITY.GET_HASH_VALUE(A.UGA,1,POWER(2,30)) AS UGA,


Sum(a.caxv)CAXV, Sum(a.unxv*c.coepdt)UNXV,Sum(a.pxv)PXV,Sum(a.caxh)CAXH, 
Sum(a.unxh*c.coepdt)UNXH, Sum(a.pxh)PXH,



Sum(DECODE(b.caxv,NULL,0,b.caxv))CAXV_A1, 
d.idmar,Sum(DECODE(b.unxv,NULL,0,b.unxv*c.coepdt))UNXV_A1 , 
Sum(DECODE(b.pxv,NULL,0,b.pxv))PXV_A1,


Sum(DECODE(b.caxh,NULL,0,b.caxh))CAXH_A1, 
Sum(DECODE(b.unxh,NULL,0,b.unxh*c.coepdt))UNXH_A1 , 
Sum(DECODE(b.pxh,NULL,0,b.pxh))PXH_A1





FROM zwxp.JOB_VNTXPO_UGA a, zwxp.JOB_VNTXPO_UGA b,



(SELECT CODPDT,COEPDT,IDMAR,AMM FROM XPONENT_PDTMAR)C,





(SELECT IDMAR FROM XPONENT_MAR)D



WHERE a.uga = b.uga(+)



AND a.codcip = b.codcip(+)



AND b.codcip = c.amm(+)



AND c.idmar = d.idmar(+)



AND b.mois(+) = ADD_MONTHS(a.mois,-12)



GROUP BY d.idmar,a.mois,a.UGA



Column: IDMAR Col#: 1 Table: XPONENT_MAR Alias: XPONENT_MAR



NDV: 7 NULLS: 0 DENS: 1.4286e-01 LO: 30 HI: 37



NO HISTOGRAM: #BKT: 1 #VAL: 2


Column: IDMAR Col#: 1 Table: XPONENT_MAR Alias: XPONENT_MAR



NDV: 7 NULLS: 0 DENS: 1.4286e-01 LO: 30 HI: 37



NO HISTOGRAM: #BKT: 1 #VAL: 2


Column: AMM Col#: 1 Table: XPONENT_PDTMAR Alias: XPONENT_PDTMAR



NDV: 540 NULLS: 0 DENS: 1.8519e-03



NO HISTOGRAM: #BKT: 1 #VAL: 2


Column: IDMAR Col#: 2 Table: XPONENT_PDTMAR Alias: XPONENT_PDTMAR



NDV: 7 NULLS: 0 DENS: 1.4286e-01 LO: 30 HI: 37



NO HISTOGRAM: #BKT: 1 #VAL: 2


Column: AMM Col#: 1 Table: XPONENT_PDTMAR Alias: XPONENT_PDTMAR



NDV: 540 NULLS: 0 DENS: 1.8519e-03



NO HISTOGRAM: #BKT: 1 #VAL: 2


Column: UGA Col#: 1 Table: JOB_VNTXPO_UGA Alias: B



NDV: 745 NULLS: 0 DENS: 1.3423e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: CODCIP Col#: 3 Table: JOB_VNTXPO_UGA Alias: B



NDV: 563 NULLS: 0 DENS: 1.7762e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: CODCIP Col#: 3 Table: JOB_VNTXPO_UGA Alias: B



NDV: 563 NULLS: 0 DENS: 1.7762e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: UGA Col#: 1 Table: JOB_VNTXPO_UGA Alias: A



NDV: 745 NULLS: 0 DENS: 1.3423e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: UGA Col#: 1 Table: JOB_VNTXPO_UGA Alias: A



NDV: 745 NULLS: 0 DENS: 1.3423e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: CODCIP Col#: 3 Table: JOB_VNTXPO_UGA Alias: A



NDV: 563 NULLS: 0 DENS: 1.7762e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: UGA Col#: 1 Table: JOB_VNTXPO_UGA Alias: A



NDV: 745 NULLS: 0 DENS: 1.3423e-03



HEIGHT BALANCED HISTOGRAM: #BKT: 75 #VAL: 76


Column: MOIS Col#: 2 Table: JOB_VNTXPO_UGA Alias: A



NDV: 25 NULLS: 0 DENS: 4.7683e-08



FREQUENCY HISTOGRAM: #BKT: 10485875 #VAL: 25







SINGLE TABLE ACCESS PATH


TABLE: JOB_VNTXPO_UGA ORIG CDN: 10485875 ROUNDED CDN: 10485875 CMPTD CDN: 

10485875



Access path: tsc Resc: 3776 Resp: 3776



Access path: index (no sta/stp keys)



Index: IDB_JOB_VNTXPO_UGA_MOIS



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 214


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: JOB_VNTXPO_UGA_CODCIP



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 24969


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: JOB_VNTXPO_UGA_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 22313


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: PK_VNTXPO_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 42114


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: IDB_JOB_VNTXPO_UGA_MOIS



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 214


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00






Cost: 98903 Cost_io: 98903 Cost_cpu: 0 Selectivity: 1



Not believed to be index-only.



BEST_CST: 3776.00 PATH: 2 Degree: 1






SINGLE TABLE ACCESS PATH


TABLE: JOB_VNTXPO_UGA ORIG CDN: 10485875 ROUNDED CDN: 10485875 CMPTD CDN: 

10485875



Access path: tsc Resc: 3776 Resp: 3776



Access path: index (no sta/stp keys)



Index: IDB_JOB_VNTXPO_UGA_MOIS



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 214


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: JOB_VNTXPO_UGA_CODCIP



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 24969


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: JOB_VNTXPO_UGA_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 22313


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: PK_VNTXPO_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 42114


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: IDB_JOB_VNTXPO_UGA_MOIS



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 214


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00






Cost: 98903 Cost_io: 98903 Cost_cpu: 0 Selectivity: 1



Not believed to be index-only.



BEST_CST: 3776.00 PATH: 2 Degree: 1






SINGLE TABLE ACCESS PATH


TABLE: XPONENT_PDTMAR ORIG CDN: 781 ROUNDED CDN: 781 CMPTD CDN: 781


Access path: tsc Resc: 2 Resp: 2



Access path: index (iff)



Index: XPONENT_PDTMAR_IDX1



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00



Access path: iff Resc: 2 Resp: 2



Access path: index (no sta/stp keys)



Index: XPONENT_PDTMAR_IDX1



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 5


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: XPONENT_PDTMAR_IDX1



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 5


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



Access path: index (no sta/stp keys)



Index: XPONENT_PDTMAR_IDX1



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 5


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



BEST_CST: 2.00 PATH: 23 Degree: 1






SINGLE TABLE ACCESS PATH


TABLE: XPONENT_MAR ORIG CDN: 7 ROUNDED CDN: 7 CMPTD CDN: 7


Access path: tsc Resc: 2 Resp: 2



Access path: index (iff)



Index: XPONENT_MAR_PK



TABLE: XPONENT_MAR


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00



Access path: iff Resc: 2 Resp: 2



Access path: index (no sta/stp keys)



Index: XPONENT_MAR_PK



TABLE: XPONENT_MAR


RSC_CPU: 0 RSC_IO: 1


IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00



BEST_CST: 1.00 PATH: 4 Degree: 1



Grouping column cardinality [ IDMAR] 7



Grouping column cardinality [ MOIS] 25



Grouping column cardinality [ UGA] 745






GENERAL PLANS







Join order[1]: JOB_VNTXPO_UGA [ A] JOB_VNTXPO_UGA [ B] XPONENT_PDTMAR 
[XPONENT_PDTMAR] XPONENT_MAR [XPONENT_MAR]


Now joining: JOB_VNTXPO_UGA [ B] *******



NL Join



Outer table: cost: 3776 cdn: 10485875 rcz: 37 resp: 3776



Inner table: JOB_VNTXPO_UGA



Access path: tsc Resc: 3776



Join: Resc: 39594667776 Resp: 39594667776



Access path: index (unique)



Index: PK_VNTXPO_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 9.5366e-08 TB_SEL: 9.5366e-08



Join: resc: 20975526 resp: 20975526



Access path: index (join index)



Index: JOB_VNTXPO_UGA_CODCIP



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 18670


IX_SEL: 0.0000e+00 TB_SEL: 1.7762e-03



Join: resc: 195771290026 resp: 195771290026



Access path: index (join index)



Index: JOB_VNTXPO_UGA_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 113


IX_SEL: 0.0000e+00 TB_SEL: 1.3423e-03



Join: resc: 1184907651 resp: 1184907651



Access path: index (eq-unique)



Index: PK_VNTXPO_UGA



TABLE: JOB_VNTXPO_UGA


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00



Join: resc: 20975526 resp: 20975526



Access path: and-equal



RSC_CPU 0 RSC_IO: 100


Join: resc: 1048591276 resp: 1048591276



Outer join cardinality: 10485875 = max ( outer (10485875) , (outer 


(10485875) * inner (10485875) * sel (9.5366e-08) ) [flag=16]




Using multi-column join key (card = 10485875 sel = 9.5366e-08)



Grouping column cardinality [ IDMAR] 7



Grouping column cardinality [ MOIS] 25



Grouping column cardinality [ UGA] 745



Best NL cost: 20975526 resp: 20975526



SM Join



Outer table:



resc: 3776 cdn: 10485875 rcz: 37 deg: 1 resp: 3776



Inner table: JOB_VNTXPO_UGA



resc: 3776 cdn: 10485875 rcz: 37 deg: 1 resp: 3776



using join:1 distribution:2 #groups:1



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 65473 Row size: 51 Rows: 10485875



Initial runs: 1582 Merge passes: 3 IO Cost / pass: 70510



Total IO sort cost: 138502



Total CPU sort cost: 0



Total Temp space used: 1263281000



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 65473 Row size: 51 Rows: 10485875



Initial runs: 1582 Merge passes: 3 IO Cost / pass: 70510



Total IO sort cost: 138502



Total CPU sort cost: 0



Total Temp space used: 1263281000



Merge join Cost: 284555 Resp: 284555



HA Join



Outer table:



resc: 3776 cdn: 10485875 rcz: 37 deg: 1 resp: 3776



Inner table: JOB_VNTXPO_UGA



resc: 3776 cdn: 10485875 rcz: 37 deg: 1 resp: 3776



using join:8 distribution:2 #groups:1



Hash join one ptn Resc: 5321694 Deg: 1



hash_area: 123 (max=123) buildfrag: 62721 probefrag: 62721 ppasses: 510



Hash join Resc: 5329246 Resp: 5329246



Join result: cost: 284555 cdn: 10485875 rcz: 74



Now joining: XPONENT_PDTMAR [XPONENT_PDTMAR] *******



NL Join



Outer table: cost: 284555 cdn: 10485875 rcz: 74 resp: 284555



Inner table: XPONENT_PDTMAR



Access path: tsc Resc: 2



Join: Resc: 21256305 Resp: 21256305



Access path: index (iff)



Index: XPONENT_PDTMAR_IDX1



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00



Inner table: XPONENT_PDTMAR



Access path: iff Resc: 2



Join: Resc: 21256305 Resp: 21256305



Access path: index (no sta/stp keys)



Index: XPONENT_PDTMAR_IDX1



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 5


IX_SEL: 1.0000e+00 TB_SEL: 1.7762e-03



Join: resc: 52713930 resp: 52713930



Access path: index (scan)



Index: XPONENT_PDTMAR_PK



TABLE: XPONENT_PDTMAR


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 1.8519e-03 TB_SEL: 1.8519e-03



Join: resc: 21256305 resp: 21256305



Outer join cardinality: 14546125 = max ( outer (10485875) , (outer 


(10485875) * inner (781) * sel (1.7762e-03) ) [flag=16]




Grouping column cardinality [ IDMAR] 7



Grouping column cardinality [ MOIS] 25



Grouping column cardinality [ UGA] 745



Best NL cost: 21256305 resp: 21256305



SM Join



Outer table:



resc: 284555 cdn: 10485875 rcz: 74 deg: 1 resp: 284555



Inner table: XPONENT_PDTMAR



resc: 2 cdn: 781 rcz: 11 deg: 1 resp: 2



using join:1 distribution:2 #groups:1



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 118108 Row size: 92 Rows: 10485875



Initial runs: 2854 Merge passes: 4 IO Cost / pass: 127194



Total IO sort cost: 313442



Total CPU sort cost: 0



Total Temp space used: 2021237000



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 3 Row size: 23 Rows: 781



Initial runs: 1 Merge passes: 1 IO Cost / pass: 4



Total IO sort cost: 4



Total CPU sort cost: 0



Total Temp space used: 0



Merge join Cost: 598002 Resp: 598002



HA Join



Outer table:



resc: 284555 cdn: 10485875 rcz: 74 deg: 1 resp: 284555



Inner table: XPONENT_PDTMAR



resc: 2 cdn: 781 rcz: 11 deg: 1 resp: 2



using join:8 distribution:2 #groups:1



Hash join one ptn Resc: 18688 Deg: 1



hash_area: 123 (max=123) buildfrag: 110082 probefrag: 3 ppasses: 895



Hash join Resc: 303245 Resp: 303245



Join result: cost: 303245 cdn: 14546125 rcz: 85



Now joining: XPONENT_MAR [XPONENT_MAR] *******



NL Join



Outer table: cost: 303245 cdn: 14546125 rcz: 85 resp: 303245



Inner table: XPONENT_MAR



Access path: tsc Resc: 2



Join: Resc: 29395495 Resp: 29395495



Access path: index (iff)



Index: XPONENT_MAR_PK



TABLE: XPONENT_MAR


RSC_CPU: 0 RSC_IO: 2


IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00



Inner table: XPONENT_MAR



Access path: iff Resc: 2



Join: Resc: 29395495 Resp: 29395495



Access path: index (unique)



Index: XPONENT_MAR_PK



TABLE: XPONENT_MAR


RSC_CPU: 0 RSC_IO: 0


IX_SEL: 1.4286e-01 TB_SEL: 1.4286e-01



Join: resc: 303245 resp: 303245



Access path: index (eq-unique)



Index: XPONENT_MAR_PK



TABLE: XPONENT_MAR


RSC_CPU: 0 RSC_IO: 0


IX_SEL: 0.0000e+00 TB_SEL: 0.0000e+00



Join: resc: 303245 resp: 303245



Outer join cardinality: 14546125 = max ( outer (14546125) , (outer 


(14546125) * inner (7) * sel (1.4286e-01) ) [flag=16]




Grouping column cardinality [ IDMAR] 7



Grouping column cardinality [ MOIS] 25



Grouping column cardinality [ UGA] 745



Best NL cost: 303245 resp: 303245



SM Join



Outer table:



resc: 303245 cdn: 14546125 rcz: 85 deg: 1 resp: 303245



Inner table: XPONENT_MAR



resc: 1 cdn: 7 rcz: 2 deg: 1 resp: 1



using join:1 distribution:2 #groups:1



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 185211 Row size: 104 Rows: 14546125



Initial runs: 4475 Merge passes: 4 IO Cost / pass: 199458



Total IO sort cost: 491522



Total CPU sort cost: 0



Total Temp space used: 2803868000



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 1 Row size: 13 Rows: 7



Initial runs: 1 Merge passes: 1 IO Cost / pass: 2



Total IO sort cost: 2



Total CPU sort cost: 0



Total Temp space used: 0



Merge join Cost: 794769 Resp: 794769



HA Join



Outer table:



resc: 303245 cdn: 14546125 rcz: 85 deg: 1 resp: 303245



Inner table: XPONENT_MAR



resc: 1 cdn: 7 rcz: 2 deg: 1 resp: 1



using join:8 distribution:2 #groups:1



Hash join one ptn Resc: 28776 Deg: 1



hash_area: 123 (max=123) buildfrag: 172239 probefrag: 1 ppasses: 1401



Hash join Resc: 332022 Resp: 332022



GROUP BY sort



GROUP BY cardinality: 65188, TABLE cardinality: 14546125



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 188772 Row size: 106 Rows: 14546125



Initial runs: 4562 Merge passes: 4 IO Cost / pass: 203293



Total IO sort cost: 407009



Total CPU sort cost: 0



Total Temp space used: 1408246000



Join result: cost: 710254 cdn: 14546125 rcz: 87



Best so far: TABLE#: 2 CST: 3776 CDN: 10485875 BYTES: 387977375



Best so far: TABLE#: 3 CST: 284555 CDN: 10485875 BYTES: 775954750



Best so far: TABLE#: 1 CST: 303245 CDN: 14546125 BYTES: 1236420625



Best so far: TABLE#: 0 CST: 710254 CDN: 14546125 BYTES: 1265512875



GROUP BY cardinality: 65188, TABLE cardinality: 14546125



SORT resource Sort statistics



Sort width: 13 Area size: 338136 Max Area size: 338136 Degree: 1



Blocks to Sort: 188772 Row size: 106 Rows: 14546125



Initial runs: 4562 Merge passes: 4 IO Cost / pass: 203293



Total IO sort cost: 407009



Total CPU sort cost: 0



Total Temp space used: 1408246000



Final:



CST: 710254 CDN: 14546125 RSC: 710254 RSP: 710254 BYTES: 1265512875


IO-RSC: 710254 IO-RSP: 710254 CPU-RSC: 0 CPU-RSP: 0

Received on Mon Jan 22 2007 - 15:01:38 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US