Checking Explain Plan of Query Consuming High CPU...

Normally faced....this issue..

This is how we can proceed with this..issue to drill down.

1) using TOP command

PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND


3202 intf 1 10 0 36G 36G cpu 6:46 99.78% oracle
11999 oracle 1 10 0 36G 36G cpu 114:59 98.09% oracle
8944 oracle 11 20 0 38G 38G cpu 371:33 96.28% oracle
25210 oracle 1 59 0 36G 36G sleep 0:46 32.78% oracle
27249 intf 32 60 0 36G 36G cpu 35:04 19.30% oracle
26601 oracle 1 60 0 36G 36G sleep 0:14 17.44% oracle
23417 oracle 11 49 0 36G 36G sleep 0:17 15.69% oracle
26916 oracle 1 60 0 36G 36G sleep 0:08 12.22% oracle
27685 cftprd1 1 60 0 70M 8688K sleep 63:26 7.50% CFTMAIN
8128 oracle 1 39 0 36G 36G sleep 208:31 5.44% oracle
27500 cftadmin 1 59 0 1480K 1312K sleep 0:01 5.17% ksh
425 oracle 1 59 0 36G 36G sleep 0:00 2.50% oracle
427 oracle 1 59 0 36G 36G sleep 0:00 2.48% oracle
442 oracle 1 59 0 36G 36G sleep 0:00 2.46% oracle
444 oracle 1 53 0 36G 36G sleep 0:00 2.34% oracle

2) Lets get the SID of these..using the query
 
select s.sid, p.spid FROM v$session s, v$process p where s.paddr = p.addr and p.spid in (3202,11999,8944);

SID SPID
---------- ------------
364   11999
1041 8944
1087 3202
 
3) Lets get the Query ...
 
select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sid=1087;
 
ADDRESS HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
---------------- ---------- ------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
000000042F9C2090 872866612 0 1579831869
select DEPT_EANADRES , RETC_EANADRES , COLLODRG_EAN , MIN_AANT_TERUGLEVER_DAGEN
, DATUM , AANTAL from p1i005.intf_ff_s_ccpool order by dept_eanadres , retc_ean
adres , COLLODRG_EAN , DATUM


4) Lets Check the Explain Plan of this Query
 
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || '  ' || options operation, object_name
FROM (SELECT id, parent_id, operation, options, object_name FROM v$sql_plan WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id;


you have the explain plan here after putting few info

Enter value for address: 000000042F9C2090
old 6: WHERE address = '&address'
new 6: WHERE address = '000000042F9C2090'
Enter value for hash_value: 872866612
old 7: AND hash_value = &hash_value
new 7: AND hash_value = 872866612
Enter value for child_number: 0
old 8: AND child_number = &child_number
new 8: AND child_number = 0

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 VIEW
3 2 SORT UNIQUE
4 3 UNION-ALL
5 4 FILTER
6 5 HASH JOIN
7 6 TABLE ACCESS FULL L0SOORTEN_COLLODRAGER
8 6 HASH JOIN
9 8 HASH JOIN
10 9 MERGE JOIN CARTESIAN

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
11 10 TABLE ACCESS FULL L0RETOURENCENTRUM
12 10 BUFFER SORT
13 12 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
14 9 TABLE ACCESS FULL L0DEPT_CLLO_TEGOED_PER_RC
15 8 TABLE ACCESS FULL L0AFROEP_DEPOTS
16 5 FILTER
17 16 SORT GROUP BY NOSORT
18 17 INDEX RANGE SCAN DCTR_PK
19 5 TABLE ACCESS BY INDEX ROWID L0CLDRG_AFSPR_PER_DEPT
20 19 INDEX RANGE SCAN CAPD_PK
21 4 HASH JOIN

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
22 21 NESTED LOOPS
23 22 NESTED LOOPS
24 23 NESTED LOOPS
25 24 NESTED LOOPS
26 25 TABLE ACCESS FULL L0CLDRG_AFSPR_PER_DEPT
27 25 TABLE ACCESS BY INDEX ROW L0SOORTEN_COLLODRAGER
28 27 INDEX UNIQUE SCAN SRCD_PK
29 24 TABLE ACCESS BY INDEX ROWI L0AFROEP_DEPOTS
30 29 INDEX UNIQUE SCAN DEPT_PK
31 23 TABLE ACCESS BY INDEX ROWID L0DEPT_CLLO_TEGOED_PER_RC
32 31 INDEX RANGE SCAN DCTR_PK

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
33 32 FILTER
34 33 SORT GROUP BY NOSORT
35 34 INDEX RANGE SCAN DCTR_PK
36 22 TABLE ACCESS BY INDEX ROWID L0RETOURENCENTRUM
37 36 INDEX UNIQUE SCAN RETC_PK
38 21 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
39 4 FILTER
40 39 HASH JOIN
41 40 HASH JOIN
42 41 TABLE ACCESS FULL L0SOORTEN_COLLODRAGER
43 41 HASH JOIN

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
44 43 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI
45 43 TABLE ACCESS FULL L0DEPT_COLLO_TEGOED
46 40 TABLE ACCESS FULL L0AFROEP_DEPOTS
47 39 FILTER
48 47 SORT GROUP BY NOSORT
49 48 INDEX RANGE SCAN DCTG_PK
50 39 TABLE ACCESS BY INDEX ROWID L0CLDRG_AFSPR_PER_DEPT
51 50 INDEX RANGE SCAN CAPD_PK
52 4 HASH JOIN
53 52 TABLE ACCESS BY INDEX ROWID L0DEPT_COLLO_TEGOED
54 53 NESTED LOOPS

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
55 54 NESTED LOOPS
56 55 NESTED LOOPS
57 56 TABLE ACCESS FULL L0CLDRG_AFSPR_PER_DEPT
58 56 TABLE ACCESS BY INDEX ROW L0SOORTEN_COLLODRAGER
59 58 INDEX UNIQUE SCAN SRCD_PK
60 55 TABLE ACCESS BY INDEX ROWI L0AFROEP_DEPOTS
61 60 INDEX UNIQUE SCAN DEPT_PK
62 54 INDEX RANGE SCAN DCTG_PK
63 62 FILTER
64 63 SORT GROUP BY NOSORT
65 64 INDEX RANGE SCAN DCTG_PK

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
66 52 TABLE ACCESS FULL INTF_HLP_CCPOOL_COLLI


67 rows selected.
SQL>


We can move further from here...

Vijay Kumar