Home » RDBMS Server » Performance Tuning » sql consumes more cpu (oracle 10g, 10.2.0.1, Linux AS 3)
sql consumes more cpu [message #338830] Wed, 06 August 2008 01:21 Go to next message
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 775 times)
Re: sql consumes more cpu [message #338832 is a reply to message #338830] Wed, 06 August 2008 01:24 Go to previous messageGo to next message
guyj
Messages: 31
Registered: September 2005
Member
please find the attached sql.
Re: sql consumes more cpu [message #338848 is a reply to message #338830] Wed, 06 August 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Many of us can't or don't want to download files.
Use SQL*PLus and copy and paste your stuff.
Don't forget to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: sql consumes more cpu [message #338940 is a reply to message #338848] Wed, 06 August 2008 05:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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")
Re: sql consumes more cpu [message #338960 is a reply to message #338958] Wed, 06 August 2008 05:56 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Fisrt check this:-

You are having MERGE JOIN CARTESIAN??

You are missing some join conditions.

Regards,
Rajat
Previous Topic: to decide pctused,pctfree in Oracle8i (merged)
Next Topic: How gathering of stats/ analyze stats work?
Goto Forum:
  


Current Time: Tue Dec 03 21:48:07 CST 2024