Re: Tuning Query help

astalavista wrote:
> 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)
>
> *** 2007-01-22 16:40:48.475
>
> *** SESSION ID:(37.3227) 2007-01-22 16:40:48.474
>
> 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
>

(SNIP)
> SINGLE TABLE ACCESS PATH
>
> TABLE: JOB_VNTXPO_UGA ORIG CDN: 10485875 ROUNDED CDN: 10485875 CMPTD CDN:
> 10485875

(SNIP)
> TABLE: JOB_VNTXPO_UGA ORIG CDN: 10485875 ROUNDED CDN: 10485875 CMPTD CDN:
> 10485875

(SNIP)
> TABLE: XPONENT_PDTMAR ORIG CDN: 781 ROUNDED CDN: 781 CMPTD CDN: 781
(SNIP)
> TABLE: XPONENT_MAR ORIG CDN: 7 ROUNDED CDN: 7 CMPTD CDN: 7
(SNIP)
> 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

(SNIP)
> Outer join cardinality: 14546125 = max ( outer (10485875) , (outer
> (10485875) * inner (781) * sel (1.7762e-03) ) [flag=16]

(SNIP)
> 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

(SNIP)
> 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

(SNIP)
> 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

Read through what is left of the 10053 trace file. You will find things such as "Total Temp space used: 2,803,868,000 (bytes)" and "Max Area size: 338,136". Your sort area size is about 1/3MB in size, yet Oracle is having to sort gigabytes worth of data - several times.
>From this observation, it appears that you are making heavy use of the
temp tablespace. You are joining a 10,485,875 row table to inself using an outer join.

See the suggestions provided in your other post: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e64da3e310d4207f/2901e78ed4c1da22

Others in the group might be able to provide a better answer, but it looks like an IO bottleneck around the temp tablespace.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jan 22 2007 - 19:41:48 CST