Home » RDBMS Server » Performance Tuning » Tune sql query (10.2.0.4)
Tune sql query [message #452089] |
Tue, 20 April 2010 00:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Re: Tune sql query [message #452094 is a reply to message #452092] |
Tue, 20 April 2010 00:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
|
Re: Tune sql query [message #452194 is a reply to message #452089] |
Tue, 20 April 2010 10:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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.
|
|
|
Re: Tune sql query [message #452289 is a reply to message #452194] |
Wed, 21 April 2010 02:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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'
|
|
|
Re: Tune sql query [message #452321 is a reply to message #452289] |
Wed, 21 April 2010 05:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
JRowbottom wrote on Wed, 21 April 2010 08:52technique egraing the performance
Should be:
technique degrading the performance
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 10:36:53 CDT 2024
|