Home » RDBMS Server » Performance Tuning » Query running long time (Oracle ,10.2.0.4.0 , SunOS)
| Query running long time [message #537037] |
Mon, 26 December 2011 05:38  |
 |
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
|
Senior Member |
|
|
Hi,
The below query is running for a long time.
SELECT MIN(minbkt),
maxbkt,
Substrb(Dump(MIN(val), 16, 0, 32), 1, 120) minval,
Substrb(Dump(MAX(val), 16, 0, 32), 1, 120) maxval,
SUM(rep) sumrep,
SUM(repsq) sumrepsq,
MAX(rep) maxrep,
COUNT(*) bktndv,
SUM(CASE
WHEN rep = 1 THEN 1
ELSE 0
END) unqrep
FROM (SELECT val,
MIN(bkt) minbkt,
MAX(bkt) maxbkt,
COUNT(val) rep,
COUNT(val) * COUNT(val) repsq
FROM (SELECT
/*+ parallel(t,128) parallel_index(t,128) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ Substrb("CLAIM_NO", 1, 32) val,
Ntile(254) over (ORDER BY Nlssort(Substrb("CLAIM_NO", 1, 32),
'NLS_SORT = binary')) bkt
FROM "DBO"."TAB_VERSION" t
WHERE
Tbl$or$idx$part$num("DBO"."CLAIM_VERSION", 0, 4, 0, "ROWID") =
:objn
AND Substrb("CLAIM_NO", 1, 32) IS NOT NULL)
GROUP BY val)
GROUP BY maxbkt
ORDER BY maxbkt
Explain plan for the above query is
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1862590303
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65184 | 7511K| | 747 (13)| 00:00:09 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,04 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,04 | PCWP | |
| 5 | PX SEND RANGE | :TQ10003 | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,03 | P->P | RANGE |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | SORT GROUP BY | | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,02 | P->P | HASH |
| 9 | SORT GROUP BY | | 65184 | 7511K| | 747 (13)| 00:00:09 | Q1,02 | PCWP | |
| 10 | VIEW | | 65184 | 7511K| | 746 (13)| 00:00:09 | Q1,02 | PCWP | |
| 11 | HASH GROUP BY | | 65184 | 5028K| | 746 (13)| 00:00:09 | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 65184 | 5028K| | 746 (13)| 00:00:09 | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 65184 | 5028K| | 746 (13)| 00:00:09 | Q1,01 | P->P | HASH |
| 14 | HASH GROUP BY | | 65184 | 5028K| | 746 (13)| 00:00:09 | Q1,01 | PCWP | |
| 15 | VIEW | | 65184 | 5028K| | 745 (13)| 00:00:09 | Q1,01 | PCWP | |
| 16 | WINDOW SORT | | 65184 | 1591K| 4632K| 745 (13)| 00:00:09 | Q1,01 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 17 | PX RECEIVE | | 65184 | 1591K| | 744 (13)| 00:00:09 | Q1,01 | PCWP | |
| 18 | PX SEND RANGE | :TQ10000 | 65184 | 1591K| | 744 (13)| 00:00:09 | Q1,00 | P->P | RANGE |
| 19 | PX BLOCK ITERATOR | | 65184 | 1591K| | 744 (13)| 00:00:09 | Q1,00 | PCWC | |
|* 20 | INDEX FAST FULL SCAN| XIF1TAB_VERSION | 65184 | 1591K| | 744 (13)| 00:00:09 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
20 - filter(SUBSTRB("CLAIM_NO",1,32) IS NOT NULL AND TBL$OR$IDX$PART$NUM("JACK"."TAB_VERSION",0,4,0,ROWID)=TO_NUMBER(:OBJN))
This query is actually gathering table stats whose count of rows is 130459996and the size is 110 GB.
This stats is running for more than 6 hours and activity percentage is 46.55% which comes in Top SQL activity.
Ww are gathering this particular table statistics manually.
Can anybody advice to tune the above query as per explain plan?
Thank you
|
|
|
|
|
|
|
|
| Re: Query running long time [message #537349 is a reply to message #537224] |
Tue, 27 December 2011 19:43  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
1) what is "LONG TIME"
2) the plan seems quite reasonable assuming you are looking at all rows in the table.
3) I think you are lieing to us via a cut/paster error. It seems to me that if you are using the alias "t" for the table DBO then the expression "DBO"."CLAIM_VERSION" should raise an INVALID IDENTIFIER error. This is further witness by line 20 in your plan:
20 - filter(SUBSTRB("CLAIM_NO",1,32) IS NOT NULL AND TBL$OR$IDX$PART$NUM("JACK"."TAB_VERSION",0,4,0,ROWID)=TO_NUMBER(:OBJN))
Who is JACK?
4) the query seems flawed. The expression Substrb("CLAIM_NO", 1, 32) IS NOT NULL is only valid if claim_no is null in its entirity. Not sure what you are getting at with that. Did you really mean to use the byte version of substr()? Maybe I am mis-reading this or my head is dense tonight, but I suggest you rethink what you are looking for in the query.
This stuff aside, assuming there is an actual query behind all this that contains the parts we see, then I suspect your time is being lost in the table function Tbl$or$idx$part$num().
Again, if you are expecting to visit more that 1% of the rows in the table, then this plan seems quite reasonable to me.
Kevin
|
|
|
|
|
|
| Re: Query running long time [message #537462 is a reply to message #537365] |
Wed, 28 December 2011 09:48 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Yes, I understand. I really only used the term loosely. I did not mean to imply you were intentionally trying to decieve anyone. Only to emphasize the need to make sure any code posted actually works before you post it.
What did you think of my commentary otherwise. Are you looking at your table function as the source of the slowdown? Do you accept the idea that your plan may actually be OK?
Kevin
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 15:05:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
