| sql consumes more cpu [message #338830] |
Wed, 06 August 2008 01:21  |
guyj
Messages: 31
Registered: September 2005
|
Member |
|
|
Hi Guys,
Need your expertise on tunning this high cpu consuming sql. Database is on 10.2.0.1.
Thanks.
-
Attachment: sql.JPG
(Size: 215.21KB, Downloaded 774 times)
|
|
|
|
|
|
|
|
| Re: sql consumes more cpu [message #338940 is a reply to message #338848] |
Wed, 06 August 2008 05:09  |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
What you expect from this Badly written SQL??
The Problem is:-
1) Use of fuction based elimination of rows after joining.
Do you have Function Based Indexes??I don't think so.
2) Getting MAX(LENGTH(AGL_LOC_PREFIX)) and used that in
elimination of rows.
3) No Clear information Provided.
Explain Plan
Table Structures
Count For Each Table.
Stats Available or Not
Regards,
Rajat
|
|
|
|
| Re: sql consumes more cpu [message #338958 is a reply to message #338940] |
Wed, 06 August 2008 05:48 |
guyj
Messages: 31
Registered: September 2005
|
Member |
|
|
Stats are up to date.
IS_M_AGREEMENT_LINE table contains 26450 records. others less than that.
no function based indexes.
Execution Plan
----------------------------------------------------------
Plan hash value: 3879578553
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 169 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 169 | 5 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 116 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 60 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | IS_R_ROUTE_DTL | 1 | 13 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | ROU_PK | 1 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | IS_M_AGREEMENT | 1 | 47 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | AGR_STY_FK_I | 1 | | 0 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 56 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | IS_M_AGR_ROUTE_RATE | 1 | 56 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IS_ARA_1 | 1 | | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | IS_M_AGREEMENT_LINE | 1 | 53 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | AGL_PK | 1 | | 0 (0)| 00:00:01 |
| 13 | SORT AGGREGATE | | 1 | 96 | | |
| 14 | NESTED LOOPS | | 1 | 96 | 5 (0)| 00:00:01 |
| 15 | MERGE JOIN CARTESIAN | | 1 | 60 | 4 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 34 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| IS_R_ROUTE_DTL | 1 | 13 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | ROU_PK | 1 | | 0 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID| IS_M_AGREEMENT | 1 | 21 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | AGR_STY_FK_I | 1 | | 0 (0)| 00:00:01 |
| 21 | BUFFER SORT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID| IS_M_AGR_ROUTE_RATE | 1 | 26 | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | IS_ARA_1 | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | IS_M_AGREEMENT_LINE | 1 | 36 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | AGL_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ROU_CODE"='b4')
6 - filter("AGR_IN_OUT_FLAG"='b1' AND ("AGR_VALID_STOP" IS NULL OR
"AGR_VALID_STOP">=TRUNC(SYSDATE@!)) AND "AGR_VALID_START"<=TRUNC(SYSDATE@!))
7 - access("AGR_STY_CODE"='b2')
9 - filter("ARA_VALID_STOP" IS NULL OR "ARA_VALID_STOP">=TRUNC(SYSDATE@!))
10 - access("ARA_FAC_CODE"='b7' AND "ARA_VALID_START"<=TRUNC(SYSDATE@!))
11 - filter("AGL_LOC_PREFIX"=SUBSTR('b6',TO_NUMBER('b5'),LENGTH("AGL_LOC_PREFIX")) AND
("AGL_VALID_STOP" IS NULL OR "AGL_VALID_STOP">=TRUNC(SYSDATE@!)) AND
"AGL_VALID_START"<=TRUNC(SYSDATE@!) AND DECODE("AGR_TMBAND_STATUS",'N','%','Y',"ARA_TBT_CODE")=DE
CODE("AGR_TMBAND_STATUS",'N','%','Y',"PK_IS_PRICING"."FN_GET_TBT_INSIDE_SQL"('b3',"AGL_MAJ_CODE")
) AND "AGL_RGP_CODE"="ROU_RGP_CODE" AND "AGL_AGR_SEQ"="AGR_SEQ" AND LENGTH("AGL_LOC_PREFIX")=
(SELECT MAX(LENGTH("AGL_LOC_PREFIX")) FROM "IS_M_AGREEMENT"
"IS_M_AGREEMENT","IS_M_AGREEMENT_LINE" "IS_M_AGREEMENT_LINE","IS_R_ROUTE_DTL"
"IS_R_ROUTE_DTL","IS_M_AGR_ROUTE_RATE" "IS_M_AGR_ROUTE_RATE" WHERE
"ARA_VALID_START"<=TRUNC(SYSDATE@!) AND "ARA_FAC_CODE"='b7' AND ("ARA_VALID_STOP" IS NULL OR
"ARA_VALID_STOP">=TRUNC(SYSDATE@!)) AND "ROU_CODE"='b4' AND "ARA_AGL_SEQ"="AGL_SEQ" AND
"AGL_LOC_PREFIX"=SUBSTR('b6',TO_NUMBER('b5'),LENGTH("AGL_LOC_PREFIX")) AND ("AGL_VALID_STOP" IS
NULL OR "AGL_VALID_STOP">=TRUNC(SYSDATE@!)) AND "AGL_VALID_START"<=TRUNC(SYSDATE@!) AND
DECODE("AGR_TMBAND_STATUS",'N','%','Y',"ARA_TBT_CODE")=DECODE("AGR_TMBAND_STATUS",'N','%','Y',"PK
_IS_PRICING"."FN_GET_TBT_INSIDE_SQL"('b3',"AGL_MAJ_CODE")) AND "AGL_RGP_CODE"="ROU_RGP_CODE" AND
"AGL_AGR_SEQ"="AGR_SEQ" AND "AGR_STY_CODE"='b2' AND "AGR_IN_OUT_FLAG"='b1' AND ("AGR_VALID_STOP"
IS NULL OR "AGR_VALID_STOP">=TRUNC(SYSDATE@!)) AND "AGR_VALID_START"<=TRUNC(SYSDATE@!)))
12 - access("ARA_AGL_SEQ"="AGL_SEQ")
18 - access("ROU_CODE"='b4')
19 - filter("AGR_IN_OUT_FLAG"='b1' AND ("AGR_VALID_STOP" IS NULL OR
"AGR_VALID_STOP">=TRUNC(SYSDATE@!)) AND "AGR_VALID_START"<=TRUNC(SYSDATE@!))
20 - access("AGR_STY_CODE"='b2')
22 - filter("ARA_VALID_STOP" IS NULL OR "ARA_VALID_STOP">=TRUNC(SYSDATE@!))
23 - access("ARA_FAC_CODE"='b7' AND "ARA_VALID_START"<=TRUNC(SYSDATE@!))
24 - filter("AGL_LOC_PREFIX"=SUBSTR('b6',TO_NUMBER('b5'),LENGTH("AGL_LOC_PREFIX")) AND
("AGL_VALID_STOP" IS NULL OR "AGL_VALID_STOP">=TRUNC(SYSDATE@!)) AND
"AGL_VALID_START"<=TRUNC(SYSDATE@!) AND DECODE("AGR_TMBAND_STATUS",'N','%','Y',"ARA_TBT_CODE")=DE
CODE("AGR_TMBAND_STATUS",'N','%','Y',"PK_IS_PRICING"."FN_GET_TBT_INSIDE_SQL"('b3',"AGL_MAJ_CODE")
) AND "AGL_RGP_CODE"="ROU_RGP_CODE" AND "AGL_AGR_SEQ"="AGR_SEQ")
25 - access("ARA_AGL_SEQ"="AGL_SEQ")
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
