Hi
the following statement is taking 25% CPU to run. Although the statement completes in around 30 seconds, I have been advised that the CPU usage needs to be reduced as we can have over 4 instances of the same process running at any one time. Please can anyone make any suggestions to reduce the amount of CPU used?
The statement is:
INSERT /*+APPEND */ INTO LINE_CALL_OVER_20P
(ID,RUN_DATA,BILL_DATA,TEL_LINE_ID,CALL_DETS_TOTS,LINE_CALL_CHGS,LINE_TYPE,EXP_SEQ1,DESCR_CODE,AMT,DESCR_TEXT,DUR_MINS,DUR_SECS,TOTAL_AMT)
(SELECT 1, -- ID
U1.SITE_ID || U1.PROCESS_CTRL, -- RUN DATA
U1.ACCT_NUM || U1.STMT_CODE, -- BILL DATA
O1.SRV_ACCT_NUM||U1.CAT_CODE||U1.SRV_OCCUR, -- TEL LINE ID
U1.ACCT_NUM || U1.SEQ_1, -- CALL DETS TOTALS
1, -- LINE CALL CHGS
u1.line_type, -- LINE TYPE
u1.seq_1, -- SEQ 1
u1.descr_code, -- DESCR CODE
(CASE
WHEN REGEXP_LIKE(SUM(u1.usage_amt), '^[0-9]+$') AND REGEXP_LIKE(SUM(u11.usage_amt), '^[0-9]+$') THEN
SUM(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)-(4+1)))
- SUM(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)-(4+1)))
WHEN REGEXP_LIKE(SUM(u1.usage_amt), '^[0-9]+$') AND NOT REGEXP_LIKE(SUM(u11.usage_amt), '^[0-9]+$') THEN
SUM(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)-(4+1)))
- SUM('-'||TRANSLATE(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
WHEN NOT REGEXP_LIKE(SUM(u1.usage_amt), '^[0-9]+$') AND REGEXP_LIKE(SUM(u11.usage_amt), '^[0-9]+$') THEN
SUM('-'||TRANSLATE(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
- SUM(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)-(4+1)))
ELSE
SUM('-'||TRANSLATE(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
- SUM('-'||TRANSLATE(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
END),
h2.descr, -- descr_text
SUM(u2.USAGE_MINUTES),
SUM(u2.USAGE_SECONDS),
COUNT(u2.USAGE_AMT)
FROM stmtu1p u1,
stmtu1p u11,
stmtu1p u2,
stmto1p o1,
stmth2p h2
WHERE (u1.descr_code = h2.descr_code(+))
AND u1.acct_num = u11.acct_num
AND u1.stmt_code = u11.stmt_code
AND u1.srv_acct_num = u11.srv_acct_num
AND u1.srv_occur = u11.srv_occur
AND u1.acct_num = u2.acct_num
AND u1.stmt_code = u2.stmt_code
AND u1.srv_acct_num = u2.srv_acct_num
AND u1.srv_occur = u2.srv_occur
AND u1.acct_num = o1.acct_num
AND u1.srv_acct_num = o1.srv_acct_num
AND u1.stmt_code = o1.stmt_code
AND u1.srv_occur = o1.srv_occur
AND u1.line_type = 'OS'
AND u1.seq_1 = 330
AND u11.line_type = 'DZ'
AND u2.line_type = 'DU'
GROUP BY u1.site_id,
u1.process_ctrl,
u1.acct_num,
u1.stmt_code,
O1.SRV_ACCT_NUM,
o1.area_code,
o1.exch_id,
o1.phone_num,
u1.seq_1,
u1.line_type,
u1.descr_code,
u1.cat_code,
u1.srv_occur,
h2.descr)
The execution plan for the statement is:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3269235626
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 88 | 15312 | | 19465 (5)| 00:03:54 |
| 1 | LOAD AS SELECT | LINE_CALL_OVER_20P | | | | | |
| 2 | HASH GROUP BY | | 88 | 15312 | | 19465 (5)| 00:03:54 |
|* 3 | HASH JOIN OUTER | | 88 | 15312 | | 19464 (5)| 00:03:54 |
|* 4 | HASH JOIN | | 88 | 14344 | | 19382 (5)| 00:03:53 |
|* 5 | HASH JOIN | | 126 | 15246 | | 13050 (5)| 00:02:37 |
|* 6 | HASH JOIN | | 2390 | 207K| 2160K| 12816 (5)| 00:02:34 |
|* 7 | TABLE ACCESS FULL| STMTU1P | 47050 | 1608K| | 6262 (5)| 00:01:16 |
|* 8 | TABLE ACCESS FULL| STMTU1P | 57060 | 3009K| | 6265 (5)| 00:01:16 |
| 9 | TABLE ACCESS FULL | STMTO1P | 59033 | 1844K| | 232 (7)| 00:00:03 |
|* 10 | TABLE ACCESS FULL | STMTU1P | 790K| 31M| | 6309 (6)| 00:01:16 |
| 11 | TABLE ACCESS FULL | STMTH2P | 39680 | 426K| | 80 (7)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("U1"."DESCR_CODE"="H2"."DESCR_CODE"(+))
4 - access("U1"."ACCT_NUM"="U2"."ACCT_NUM" AND "U1"."STMT_CODE"="U2"."STMT_CODE" AND
"U1"."SRV_ACCT_NUM"="U2"."SRV_ACCT_NUM" AND "U1"."SRV_OCCUR"="U2"."SRV_OCCUR")
5 - access("U1"."ACCT_NUM"="O1"."ACCT_NUM" AND "U1"."SRV_ACCT_NUM"="O1"."SRV_ACCT_NUM" AND
"U1"."STMT_CODE"="O1"."STMT_CODE" AND "U1"."SRV_OCCUR"="O1"."SRV_OCCUR")
6 - access("U1"."ACCT_NUM"="U11"."ACCT_NUM" AND "U1"."STMT_CODE"="U11"."STMT_CODE" AND
"U1"."SRV_ACCT_NUM"="U11"."SRV_ACCT_NUM" AND "U1"."SRV_OCCUR"="U11"."SRV_OCCUR")
7 - filter("U11"."LINE_TYPE"='DZ')
8 - filter("U1"."LINE_TYPE"='OS' AND "U1"."SEQ_1"=330)
10 - filter("U2"."LINE_TYPE"='DU')
The tkprof file for the statement is:
TKPROF: Release 10.2.0.1.0 - Production on Sun Feb 17 11:29:08 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: /u01/app/oracle/admin/ORATEST/udump/oratest_ora_1396968.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace=true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 (PREPROC1)
********************************************************************************
INSERT /*+APPEND */ INTO LINE_CALL_OVER_20P
(ID,RUN_DATA,BILL_DATA,TEL_LINE_ID,CALL_DETS_TOTS,LINE_CALL_CHGS,LINE_TYPE,EXP_SEQ1,DESCR_CODE,AMT,DESCR_TEXT,DUR_MINS,DUR_SECS,TOTAL_AMT)
(SELECT 1, -- ID
U1.SITE_ID || U1.PROCESS_CTRL, -- RUN DATA
U1.ACCT_NUM || U1.STMT_CODE, -- BILL DATA
O1.SRV_ACCT_NUM||U1.CAT_CODE||U1.SRV_OCCUR, -- TEL LINE ID
U1.ACCT_NUM || U1.SEQ_1, -- CALL DETS TOTALS
1, -- LINE CALL CHGS
u1.line_type, -- LINE TYPE
u1.seq_1, -- SEQ 1
u1.descr_code, -- DESCR CODE
(CASE
WHEN REGEXP_LIKE(SUM(u1.usage_amt), '^[0-9]+$') AND REGEXP_LIKE(SUM(u11.usage_amt), '^[0-9]+$') THEN
SUM(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)-(4+1)))
- SUM(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)-(4+1)))
WHEN REGEXP_LIKE(SUM(u1.usage_amt), '^[0-9]+$') AND NOT REGEXP_LIKE(SUM(u11.usage_amt), '^[0-9]+$') THEN
SUM(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)-(4+1)))
- SUM('-'||TRANSLATE(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
WHEN NOT REGEXP_LIKE(SUM(u1.usage_amt), '^[0-9]+$') AND REGEXP_LIKE(SUM(u11.usage_amt), '^[0-9]+$') THEN
SUM('-'||TRANSLATE(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
- SUM(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)-(4+1)))
ELSE
SUM('-'||TRANSLATE(SUBSTR(u1.usage_amt,1,LENGTH(u1.usage_amt)- 4) ||'.'|| SUBSTR(u1.usage_amt,LENGTH(u1.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
- SUM('-'||TRANSLATE(SUBSTR(u11.usage_amt,1,LENGTH(u11.usage_amt)- 4) ||'.'|| SUBSTR(u11.usage_amt,LENGTH(u11.usage_amt)- (4 + 1)),'},J,K,L,M,N,O,P,Q,R','0,1,2,3,4,5,6,7,8,9'))
END),
h2.descr, -- descr_text
SUM(u2.USAGE_MINUTES),
SUM(u2.USAGE_SECONDS),
COUNT(u2.USAGE_AMT)
FROM stmtu1p u1,
stmtu1p u11,
stmtu1p u2,
stmto1p o1,
stmth2p h2
WHERE (u1.descr_code = h2.descr_code(+))
AND u1.acct_num = u11.acct_num
AND u1.stmt_code = u11.stmt_code
AND u1.srv_acct_num = u11.srv_acct_num
AND u1.srv_occur = u11.srv_occur
AND u1.acct_num = u2.acct_num
AND u1.stmt_code = u2.stmt_code
AND u1.srv_acct_num = u2.srv_acct_num
AND u1.srv_occur = u2.srv_occur
AND u1.acct_num = o1.acct_num
AND u1.srv_acct_num = o1.srv_acct_num
AND u1.stmt_code = o1.stmt_code
AND u1.srv_occur = o1.srv_occur
AND u1.line_type = 'OS'
AND u1.seq_1 = 330
AND u11.line_type = 'DZ'
AND u2.line_type = 'DU'
GROUP BY u1.site_id,
u1.process_ctrl,
u1.acct_num,
u1.stmt_code,
O1.SRV_ACCT_NUM,
o1.area_code,
o1.exch_id,
o1.phone_num,
u1.seq_1,
u1.line_type,
u1.descr_code,
u1.cat_code,
u1.srv_occur,
h2.descr)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 0 0 0
Execute 1 12.95 18.41 28872 94074 608 39018
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 12.98 18.46 28872 94074 608 39018
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 (PREPROC1)
Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=94126 pr=28872 pw=1727 time=18450373 us)
39018 HASH GROUP BY (cr=94061 pr=28872 pw=1245 time=17322258 us)
258728 HASH JOIN OUTER (cr=94061 pr=27627 pw=0 time=12275770 us)
258728 HASH JOIN (cr=93694 pr=27627 pw=0 time=12974313 us)
46380 HASH JOIN (cr=62830 pr=23324 pw=0 time=6837995 us)
46380 HASH JOIN (cr=61729 pr=23324 pw=0 time=9986105 us)
46381 TABLE ACCESS FULL STMTU1P (cr=30865 pr=17444 pw=0 time=4545326 us)
57603 TABLE ACCESS FULL STMTU1P (cr=30864 pr=5880 pw=0 time=2073762 us)
59033 TABLE ACCESS FULL STMTO1P (cr=1101 pr=0 pw=0 time=77 us)
789379 TABLE ACCESS FULL STMTU1P (cr=30864 pr=4303 pw=0 time=793734 us)
39954 TABLE ACCESS FULL STMTH2P (cr=367 pr=0 pw=0 time=105 us)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
1 LOAD AS SELECT OF 'LINE_CALL_OVER_20P'
39018 HASH (GROUP BY)
258728 HASH JOIN (OUTER)
258728 HASH JOIN
46380 HASH JOIN
46380 HASH JOIN
46381 TABLE ACCESS MODE: ANALYZED (FULL) OF 'STMTU1P'
(TABLE)
57603 TABLE ACCESS MODE: ANALYZED (FULL) OF 'STMTU1P'
(TABLE)
59033 TABLE ACCESS MODE: ANALYZED (FULL) OF 'STMTO1P'
(TABLE)
789379 TABLE ACCESS MODE: ANALYZED (FULL) OF 'STMTU1P' (TABLE)
39954 TABLE ACCESS MODE: ANALYZED (FULL) OF 'STMTH2P' (TABLE)
********************************************************************************
select file#
from
file$ where ts#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 1 0
Execute 4 0.01 0.00 0 0 0 0
Fetch 12 0.00 0.00 0 20 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.01 0.01 0 20 1 8
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL FILE$ (cr=5 pr=0 pw=0 time=67 us)
********************************************************************************
update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8
where
ts#=:1 and user#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 2 0
Execute 4 0.01 0.00 0 12 4 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.01 0 12 6 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE TSQ$ (cr=3 pr=0 pw=0 time=267 us)
1 TABLE ACCESS CLUSTER TSQ$ (cr=3 pr=0 pw=0 time=83 us)
1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=27 us)(object id 11)
********************************************************************************
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
DECODE(:17,0,NULL,:17),scanhint=:18
where
ts#=:1 and file#=:2 and block#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.01 0.00 0 20 4 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.01 0 20 4 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE SEG$ (cr=5 pr=0 pw=0 time=338 us)
1 TABLE ACCESS CLUSTER SEG$ (cr=5 pr=0 pw=0 time=162 us)
1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=24 us)(object id 9)
********************************************************************************
commit
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 35 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 35 0
Misses in library cache during parse: 0
Parsing user id: 85 (PREPROC1)
********************************************************************************
alter session set sql_trace=false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 (PREPROC1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.04 0 0 0 0
Execute 4 12.95 18.42 28872 94076 643 39018
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 12.98 18.47 28872 94076 643 39018
Misses in library cache during parse: 2
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 12 0.00 0.01 0 0 3 0
Execute 12 0.03 0.01 0 32 8 8
Fetch 12 0.00 0.00 0 20 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36 0.03 0.03 0 52 11 16
Misses in library cache during parse: 3
Misses in library cache during execute: 3
4 user SQL statements in session.
12 internal SQL statements in session.
16 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: /u01/app/oracle/admin/ORATEST/udump/oratest_ora_1396968.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
12 internal SQL statements in trace file.
16 SQL statements in trace file.
7 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
PREPROC1.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
232 lines in trace file.
50 elapsed seconds in trace file.
Many thanks