OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Tune sql query (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tune sql query [message #452089]

Tue, 20 April 2010 00:08

ankush_chawla
Messages: 136
Registered: November 2006

Senior Member

SQL> SELECT
2 case.account_id, count(case.account_id) count
3 FROM
4 clrods.case@clrods.equant.com case, clrods.case_service@clrods.equant.com case_service,
5 table_x_cwp_tickect_details_vw t
6 WHERE
7 CASE.case_condition_cd IN ('OPEN', 'OPEN-DISPATCH', 'OPEN-REJECT', 'OPEN-RETURNED') and
8 case.case_type_cd in ('CUSTOMER FAULT', 'CHRONIC', 'SCHEDULED ACTIVITY') AND
9 case.case_objid = case_service.case_objid and
10 case.case_objid = t.ticket_objid AND
11 ( ( case.account_id = '284' AND UPPER(case.site_country) in ('AUSTRIA','BELGIUM','BRAZIL','DENMARK','FINLAND','FRANCE','SOUTH AFRICA','SWEDEN','SWITZERLAND','UNITED KINGDOM','UNITED STATES') ) OR ( case.account_id = '4142' ) OR ( case.account_id = '674826' ) )
12 group by case.account_id;

ACCOUNT_ID COUNT
---------------------------------------- ----------
674826 1229
284 67
4142 43

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
--------------------------------

| Id | Operation | Name | Rows |
Bytes | Cost | Inst |IN-OUT|

--------------------------------------------------------------------------------
--------------------------------

| 0 | SELECT STATEMENT | | 2 |
658 | 521 | | |

| 1 | HASH GROUP BY | | 2 |
658 | 521 | | |

| 2 | NESTED LOOPS OUTER | | 67 |
22043 | 520 | | |

| 3 | NESTED LOOPS | | 67 |
21574 | 520 | | |

| 4 | NESTED LOOPS | | 67 |
20703 | 518 | | |

| 5 | NESTED LOOPS | | 67 |
20234 | 517 | | |

| 6 | NESTED LOOPS | | 67 |
19765 | 516 | | |

| 7 | NESTED LOOPS | | 67 |
19296 | 515 | | |

| 8 | NESTED LOOPS | | 67 |
18358 | 462 | | |

| 9 | NESTED LOOPS | | 67 |
17889 | 461 | | |

| 10 | HASH JOIN | | 67 |
16482 | 407 | | |

| 11 | HASH JOIN | | 67 |
16013 | 405 | | |

| 12 | HASH JOIN | | 67 |
15544 | 402 | | |

| 13 | HASH JOIN | | 67 |
15075 | 400 | | |

| 14 | NESTED LOOPS | | 67 |
14606 | 397 | | |

| 15 | REMOTE | CASE | 67 |
10452 | 343 | CLRODS | R->S |

| 16 | TABLE ACCESS BY INDEX ROWID| TABLE_CASE | 1 |
62 | 1 | | |

| 17 | INDEX UNIQUE SCAN | C_CASE_OBJINDEX | 1 |
| 1 | | |

| 18 | INDEX FAST FULL SCAN | GBST_ELM_OBJINDEX | 1123 |
7861 | 2 | | |

| 19 | INDEX FAST FULL SCAN | GBST_ELM_OBJINDEX | 1123 |
7861 | 2 | | |

| 20 | INDEX FAST FULL SCAN | GBST_ELM_OBJINDEX | 1123 |
7861 | 2 | | |

| 21 | INDEX FAST FULL SCAN | GBST_ELM_OBJINDEX | 1123 |
7861 | 2 | | |

| 22 | TABLE ACCESS BY INDEX ROWID | TABLE_SITE | 1 |
21 | 1 | | |

| 23 | INDEX UNIQUE SCAN | SITE_OBJINDEX | 1 |
| 1 | | |

| 24 | INDEX UNIQUE SCAN | BUS_ORG_OBJINDEX | 1 |
7 | 1 | | |

| 25 | TABLE ACCESS BY INDEX ROWID | TABLE_ADDRESS | 1 |
14 | 1 | | |

| 26 | INDEX UNIQUE SCAN | ADDRESS_OBJINDEX | 1 |
| 1 | | |

| 27 | INDEX UNIQUE SCAN | COUNTRY_OBJINDEX | 1 |
7 | 1 | | |

| 28 | INDEX UNIQUE SCAN | CONTACT_OBJINDEX | 1 |
7 | 1 | | |

| 29 | INDEX UNIQUE SCAN | CONDITION_OBJINDEX | 1 |
7 | 1 | | |

| 30 | REMOTE | CASE_SERVICE | 1 |
13 | 1 | CLRODS | R->S |

| 31 | INDEX UNIQUE SCAN | SITE_PART_OBJINDEX | 1 |
7 | 1 | | |

--------------------------------------------------------------------------------
--------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

15 - SELECT "CASE_OBJID","CASE_CONDITION_CD","ACCOUNT_ID","CASE_TYPE_CD","SITE
_COUNTRY" FROM

"CLRODS"."CASE" "CASE" WHERE (("ACCOUNT_ID"='4142' OR "ACCOUNT_ID"='6748
26') OR "ACCOUNT_ID"='284' AND

(UPPER("SITE_COUNTRY")='AUSTRIA' OR UPPER("SITE_COUNTRY")='BELGIUM' OR U
PPER("SITE_COUNTRY")='BRAZIL'

OR UPPER("SITE_COUNTRY")='DENMARK' OR UPPER("SITE_COUNTRY")='FINLAND' OR
UPPER("SITE_COUNTRY")='FRANCE'

OR UPPER("SITE_COUNTRY")='SOUTH AFRICA' OR UPPER("SITE_COUNTRY")='SWEDEN
' OR

UPPER("SITE_COUNTRY")='SWITZERLAND' OR UPPER("SITE_COUNTRY")='UNITED KIN
GDOM' OR

UPPER("SITE_COUNTRY")='UNITED STATES')) AND ("CASE_TYPE_CD"='CHRONIC' OR
"CASE_TYPE_CD"='CUSTOMER

FAULT' OR "CASE_TYPE_CD"='SCHEDULED ACTIVITY') AND ("CASE_CONDITION_CD"=
'OPEN' OR

"CASE_CONDITION_CD"='OPEN-DISPATCH' OR "CASE_CONDITION_CD"='OPEN-REJECT'
OR

"CASE_CONDITION_CD"='OPEN-RETURNED') (accessing 'CLRODS.EQUANT.COM' )

30 - SELECT "CASE_OBJID" FROM "CLRODS"."CASE_SERVICE" "CASE_SERVICE" WHERE :1=
"CASE_OBJID" (accessing

'CLRODS.EQUANT.COM' )

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
21973 consistent gets
15 physical reads
0 redo size
660 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

Kindly help to tune the above query

Report message to a moderator

Re: Tune sql query [message #452090 is a reply to message #452089]

Tue, 20 April 2010 00:12

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

100+ posts and still unable to format.

/forum/fa/1637/0/

Regards
Michel

Report message to a moderator

Re: Tune sql query [message #452092 is a reply to message #452089]

Tue, 20 April 2010 00:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>SQL> SELECT
>2 case.account_id, count(case.account_id) count
>3 FROM
>4 clrods.case@clrods.equant.com case, clrods.case_service@clrods.equant.com case_service,
>5 table_x_cwp_tickect_details_vw t

Eliminate out of FROM clause clrods.case_service@clrods.equant.com case_service and table_x_cwp_tickect_details_vw t because they contribute no data to SELECT clause and subordinate into WHERE clause

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888

Report message to a moderator

Re: Tune sql query [message #452094 is a reply to message #452092]

Tue, 20 April 2010 00:58

ankush_chawla
Messages: 136
Registered: November 2006

Senior Member

How we can eliminate the tables from from clause when they are used in the where clause.

Tkprof output

SELECT
case.account_id, count(case.account_id) count
FROM
clrods.case@clrods.equant.com case, clrods.case_service@clrods.equant.com case_service,
table_x_cwp_tickect_details_vw t
WHERE
CASE.case_condition_cd IN ('OPEN', 'OPEN-DISPATCH', 'OPEN-REJECT', 'OPEN-RETURNED') and
case.case_type_cd in ('CUSTOMER FAULT', 'CHRONIC', 'SCHEDULED ACTIVITY') AND
case.case_objid = case_service.case_objid and
case.case_objid = t.ticket_objid AND
( ( case.account_id = '284' AND UPPER(case.site_country) in ('AUSTRIA','BELGIUM','BRAZIL','DENMARK','FINLAND','FRANCE','SOUTH AFRICA','SWEDEN','SWIT
ZERLAND','UNITED KINGDOM','UNITED STATES') ) OR ( case.account_id = '4142' ) OR ( case.account_id = '674826' ) )
group by case.account_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.71 2.00 5 22 1 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 0.34 7.43 65 22261 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.05 9.46 70 22283 1 3

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
3 HASH GROUP BY (cr=22261 pr=65 pw=0 time=7461916 us)
1339 CONCATENATION (cr=22261 pr=65 pw=0 time=5213258 us)
67 NESTED LOOPS (cr=1422 pr=22 pw=0 time=617808 us)
67 NESTED LOOPS (cr=1353 pr=22 pw=0 time=617336 us)
67 NESTED LOOPS (cr=1284 pr=22 pw=0 time=616799 us)
67 NESTED LOOPS (cr=1215 pr=22 pw=0 time=616198 us)
67 NESTED LOOPS OUTER (cr=1146 pr=22 pw=0 time=615658 us)
67 NESTED LOOPS (cr=1010 pr=20 pw=0 time=614315 us)
70 NESTED LOOPS (cr=1010 pr=20 pw=0 time=446754 us)
70 NESTED LOOPS (cr=868 pr=20 pw=0 time=445845 us)
70 NESTED LOOPS (cr=726 pr=6 pw=0 time=444934 us)
70 NESTED LOOPS (cr=724 pr=6 pw=0 time=444442 us)
70 NESTED LOOPS (cr=512 pr=6 pw=0 time=443039 us)
70 NESTED LOOPS (cr=440 pr=6 pw=0 time=442267 us)
70 NESTED LOOPS (cr=228 pr=0 pw=0 time=440726 us)
78 REMOTE CASE (cr=0 pr=0 pw=0 time=485492 us)
70 TABLE ACCESS BY INDEX ROWID TABLE_CASE (cr=228 pr=0 pw=0 time=1452 us)
70 INDEX UNIQUE SCAN C_CASE_OBJINDEX (cr=158 pr=0 pw=0 time=707 us)(object id 52157)
70 TABLE ACCESS BY INDEX ROWID TABLE_SITE (cr=212 pr=6 pw=0 time=1614 us)
70 INDEX UNIQUE SCAN SITE_OBJINDEX (cr=142 pr=0 pw=0 time=613 us)(object id 53585)
70 INDEX UNIQUE SCAN BUS_ORG_OBJINDEX (cr=72 pr=0 pw=0 time=495 us)(object id 52106)
70 TABLE ACCESS BY INDEX ROWID TABLE_ADDRESS (cr=212 pr=0 pw=0 time=1053 us)
70 INDEX UNIQUE SCAN ADDRESS_OBJINDEX (cr=142 pr=0 pw=0 time=638 us)(object id 51895)
70 INDEX UNIQUE SCAN COUNTRY_OBJINDEX (cr=2 pr=0 pw=0 time=241 us)(object id 52427)
70 INDEX UNIQUE SCAN CONTACT_OBJINDEX (cr=142 pr=14 pw=0 time=95333 us)(object id 52298)
70 INDEX UNIQUE SCAN CONDITION_OBJINDEX (cr=142 pr=0 pw=0 time=541 us)(object id 52285)
67 REMOTE CASE_SERVICE (cr=0 pr=0 pw=0 time=319212 us)
33 INDEX UNIQUE SCAN SITE_PART_OBJINDEX (cr=136 pr=2 pw=0 time=45228 us)(object id 53618)
67 INDEX UNIQUE SCAN GBST_ELM_OBJINDEX (cr=69 pr=0 pw=0 time=358 us)(object id 52802)
67 INDEX UNIQUE SCAN GBST_ELM_OBJINDEX (cr=69 pr=0 pw=0 time=294 us)(object id 52802)
67 INDEX UNIQUE SCAN GBST_ELM_OBJINDEX (cr=69 pr=0 pw=0 time=309 us)(object id 52802)
67 INDEX UNIQUE SCAN GBST_ELM_OBJINDEX (cr=69 pr=0 pw=0 time=296 us)(object id 52802)
1272 NESTED LOOPS OUTER (cr=20839 pr=43 pw=0 time=4828730 us)
1272 NESTED LOOPS (cr=18329 pr=40 pw=0 time=4810911 us)
1272 NESTED LOOPS (cr=18329 pr=40 pw=0 time=296501 us)
1272 NESTED LOOPS (cr=15783 pr=40 pw=0 time=285043 us)
1272 NESTED LOOPS (cr=13237 pr=14 pw=0 time=274856 us)
1272 NESTED LOOPS (cr=13235 pr=14 pw=0 time=268489 us)
1272 NESTED LOOPS (cr=9417 pr=14 pw=0 time=250669 us)
1272 NESTED LOOPS (cr=8143 pr=14 pw=0 time=241758 us)
1272 HASH JOIN (cr=4325 pr=0 pw=0 time=213748 us)
1272 HASH JOIN (cr=4319 pr=0 pw=0 time=208077 us)
1272 HASH JOIN (cr=4313 pr=0 pw=0 time=204619 us)
1272 HASH JOIN (cr=4307 pr=0 pw=0 time=198751 us)
1272 NESTED LOOPS (cr=4301 pr=0 pw=0 time=195955 us)
1273 REMOTE CASE (cr=0 pr=0 pw=0 time=16016154 us)
1272 TABLE ACCESS BY INDEX ROWID TABLE_CASE (cr=4301 pr=0 pw=0 time=21084 us)
1272 INDEX UNIQUE SCAN C_CASE_OBJINDEX (cr=2548 pr=0 pw=0 time=8650 us)(object id 52157)
1123 INDEX FAST FULL SCAN GBST_ELM_OBJINDEX (cr=6 pr=0 pw=0 time=50 us)(object id 52802)
1123 INDEX FAST FULL SCAN GBST_ELM_OBJINDEX (cr=6 pr=0 pw=0 time=43 us)(object id 52802)
1123 INDEX FAST FULL SCAN GBST_ELM_OBJINDEX (cr=6 pr=0 pw=0 time=20 us)(object id 52802)
1123 INDEX FAST FULL SCAN GBST_ELM_OBJINDEX (cr=6 pr=0 pw=0 time=20 us)(object id 52802)
1272 TABLE ACCESS BY INDEX ROWID TABLE_SITE (cr=3818 pr=14 pw=0 time=33210 us)
1272 INDEX UNIQUE SCAN SITE_OBJINDEX (cr=2546 pr=0 pw=0 time=8917 us)(object id 53585)
1272 INDEX UNIQUE SCAN BUS_ORG_OBJINDEX (cr=1274 pr=0 pw=0 time=6662 us)(object id 52106)
1272 TABLE ACCESS BY INDEX ROWID TABLE_ADDRESS (cr=3818 pr=0 pw=0 time=16233 us)
1272 INDEX UNIQUE SCAN ADDRESS_OBJINDEX (cr=2546 pr=0 pw=0 time=9161 us)(object id 51895)
1272 INDEX UNIQUE SCAN COUNTRY_OBJINDEX (cr=2 pr=0 pw=0 time=4228 us)(object id 52427)
1272 INDEX UNIQUE SCAN CONTACT_OBJINDEX (cr=2546 pr=26 pw=0 time=160445 us)(object id 52298)
1272 INDEX UNIQUE SCAN CONDITION_OBJINDEX (cr=2546 pr=0 pw=0 time=9243 us)(object id 52285)
1272 REMOTE CASE_SERVICE (cr=0 pr=0 pw=0 time=6462029 us)
1132 INDEX UNIQUE SCAN SITE_PART_OBJINDEX (cr=2510 pr=3 pw=0 time=34461 us)(object id 53618)

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.71 2.00 5 22 1 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 0.34 7.43 65 22261 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.05 9.46 70 22283 1 3

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.01 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.01 5 22 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.01 0.01 5 22 0 5

Misses in library cache during parse: 2
Misses in library cache during execute: 2

1 user SQL statements in session.
8 internal SQL statements in session.
9 SQL statements in session.
********************************************************************************
Trace file: clarify_ora_3934.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
1 user SQL statements in trace file.
8 internal SQL statements in trace file.
9 SQL statements in trace file.
8 unique SQL statements in trace file.
190 lines in trace file.
9 elapsed seconds in trace file.

Report message to a moderator

Re: Tune sql query [message #452145 is a reply to message #452094]

Tue, 20 April 2010 04:11

ankush_chawla
Messages: 136
Registered: November 2006

Senior Member

any other info required to tune this query

Report message to a moderator

Re: Tune sql query [message #452190 is a reply to message #452094]

Tue, 20 April 2010 09:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How we can eliminate the tables from from clause when they are used in the where clause.

Eliminate out of FROM clause ... subordinate into WHERE clause

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Please FORMAT your posts so are readable!

Report message to a moderator

Re: Tune sql query [message #452194 is a reply to message #452089]

Tue, 20 April 2010 10:06

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

Blackswan means you should do this:

SELECT
case.account_id, count(case.account_id) count
FROM
clrods.case@clrods.equant.com CASE
WHERE CASE.case_condition_cd IN ('OPEN', 'OPEN-DISPATCH', 'OPEN-REJECT', 'OPEN-RETURNED') 
AND case.case_type_cd in ('CUSTOMER FAULT', 'CHRONIC', 'SCHEDULED ACTIVITY') 
AND case.case_objid IN (SELECT case_objid FROM clrods.case_service@clrods.equant.com)
AND case.case_objid IN (SELECT ticket_objid table_x_cwp_tickect_details_vw)
AND ((case.account_id = '284' 
      AND UPPER(case.site_country) in ('AUSTRIA','BELGIUM','BRAZIL','DENMARK','FINLAND','FRANCE',
                                       'SOUTH AFRICA','SWEDEN','SWITZERLAND','UNITED KINGDOM','UNITED STATES') 
     ) 
     OR ( case.account_id = '4142' ) 
     OR ( case.account_id = '674826' ) )
group by case.account_id

And can you please next time use code tags for code as I have done here. Details are in the posting guidelines that Blackswan has linked to above.

Report message to a moderator

Re: Tune sql query [message #452289 is a reply to message #452194]

Wed, 21 April 2010 02:52

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

There's absolutely no guarantee that doing this will cause any improvement in the performance.

The last tiem @BlackSwan an I went round this one, as I remember, I produced an example showing this technique egraing the performance, and he failed to produce an example showing it improving it.

It may be worth a try, but it comes under the heading of 'trying things at in the hope they'll make it better'

Report message to a moderator

Re: Tune sql query [message #452321 is a reply to message #452289]

Wed, 21 April 2010 05:32

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

JRowbottom wrote on Wed, 21 April 2010 08:52

technique egraing the performance

Should be:

technique degrading the performance

Report message to a moderator

Re: Tune sql query [message #452330 is a reply to message #452321]

Wed, 21 April 2010 06:29

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

So it should.
Pixies seem to have temporarily disabled the 'd' key on my keyboard.

Report message to a moderator

Previous Topic:	Analse tables
Next Topic:	Tuning query having not exists clause.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 22:09:49 CDT 2024