Printable View
I was told that when a particular query is run in production, it is erroring out without space in temp tablespace (temp tablespace already has 16 GB). I then had production data copied into a different envrionment and when I run the same query, I see that it is using up only 9MB of temp space (the blocks column in V$TEMPSEG_USAGE shows value 1152). Am I seeing anything wrong here ?
Thanks.
wy not trace the query in production, see what is actually happening
I have found the query whose Hash Join is asking for more than 16GB of temp tablespace. Here is the query in with the explain plan. The tables are all analyzed everyday and I am running Oracle 9.2.0.6 on Linux.
Code:SELECT COUNT(DISTINCT car.assessment_locator) AS assessments_mapped
FROM CLG_ASSESSMENT_RESULT car, CLG_ITEM_RESULT cir, CLG_ITEM ci, CLG_CORRELATION corr, CB_STANDARD cbs, CLG_ASSESSMENT_INSTANCE cai, CLG_USER u
WHERE u.org_unit_id = :1
AND u.user_id = cai.assignor_id
AND car.ASSESSMENT_locator = :2
AND car.completed_date >= :3
AND car.completed_date <= :4
AND cir.ASSESSMENT_RESULT_ID = car.ASSESSMENT_RESULT_ID
AND (ci.LOCATOR = corr.CONTENT_ID OR ci.ancestor_locator = corr.content_id)
AND corr.ASSET_TYPE_ID = 113
AND corr.cbs_id = cbs.CBS_ID
AND ci.LOCATOR = cir.item_locator
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=1 Bytes=208 )
1 0 SORT (GROUP BY)
2 1 HASH JOIN (Cost=120 Card=6846 Bytes=1423968)
3 2 MERGE JOIN (CARTESIAN) (Cost=107 Card=320 Bytes=64960)
4 3 NESTED LOOPS (Cost=65 Card=1 Bytes=193)
5 4 NESTED LOOPS (Cost=64 Card=1 Bytes=189)
6 5 NESTED LOOPS (Cost=15 Card=1 Bytes=154)
7 6 NESTED LOOPS (Cost=13 Card=1 Bytes=88)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ASSESSMENT_RESULT' (Cost=10 Card=1 Bytes=45)
9 8 BITMAP CONVERSION (TO ROWIDS)
10 9 BITMAP AND
11 10 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_COMPDATE' (NON-UNIQUE) (Cost=2 Card=184)
14 10 BITMAP CONVERSION (FROM ROWIDS)
15 14 INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_ASMNTLOC' (NON-UNIQUE) (Cost=4 Card=184)
16 7 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM_RESULT' (Cost=3 Card=12 Bytes=516)
17 16 INDEX (RANGE SCAN) OF 'PK_ITEM_RESULT' (UNIQUE) (Cost=2 Card=12)
18 6 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM' (Cost=2 Card=1 Bytes=66)
19 18 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card=1)
20 5 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_CORRELATION' (Cost=64 Card=1 Bytes=35)
21 20 BITMAP CONVERSION (TO ROWIDS)
22 21 BITMAP OR
23 22 BITMAP CONVERSION (FROM ROWIDS)
24 23 SORT (ORDER BY)
25 24 INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)
26 22 BITMAP CONVERSION (FROM ROWIDS)
27 26 SORT (ORDER BY)
28 27 INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)
29 4 INDEX (UNIQUE SCAN) OF 'PK_CB_STANDARD' (UNIQUE)
30 3 BUFFER (SORT) (Cost=107 Card=435 Bytes=4350)
31 30 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_USER' (Cost=42 Card=435 Bytes=4350)
32 31 INDEX (RANGE SCAN) OF 'IDX_USER_ORGUNITID' (NON-UNIQUE) (Cost=2 Card=435)
33 2 INDEX (FAST FULL SCAN) OF 'IDX_ASMNTINST_ASSIGNORID' (NON-UNIQUE) (Cost=9 Card=35353 Bytes=176765)
Is the explain plan the same in the "different" environment? Are the init parameters different, any hardware differences, etc?
The query and explain plan are from production.
I understand. But if you would like answers to your questions, then maybe you should answer the questions we ask you.
I am going to rephrase my original question in a new thread as it is confusing. So closing this one.
The cost and card values shown in the execution plan are not NOT very high. Hence, no need to fill out TEMP tablespace.
Are you sure that tables and indexes are analyzed with proper estimate?
Take a extended trace data for this query. It will show you the actual temp usage.
Tamil
Tamil,
I am doing compute stats, not estimate.
I say that this query is using 16GB of temp space by looking at V$TEMPSEG_USAGE and v$sql.
I do not have the extended trace data. Will work on getting that.
Thanks.