| Fetch is taking too long [message #377409] |
Mon, 22 December 2008 17:07  |
srinivas4u2
Messages: 66
Registered: June 2005
|
Member |
|
|
Hi all,
Would like to discuss the below performance issue we are having.
In the below query - the tds_tran_log table has about 70M records and has about 1240 partitions and is also hash sub-partitioned. It's partitioned on the tran_time column.
We have possibly exhausted all options at I/O level as multiple stripe widths have been tested.
Not sure if the range scan is the culprit here? Does anyone have any idea why the fetch could take so long?
Between, the block size of that index is 8k, would increasing it to 32k help?
Ross?
SELECT b.*,
rownum rnum
FROM
(SELECT /*+ FIRST_ROWS(25) */
TO_CHAR(a.tran_time, 'mm/dd/yyyy') t,
a.cust_nbr ,
a.merch_chain_nbr ,
a.pan ,
a.global_merchant_id ,
NVL(a.tran_amt, 0) ,
NVL(a.tran_amt_display, 0) ,
a.tran_nbr ,
a.auth_code ,
a.term_id
FROM tds.tds_tran_log a
WHERE a.tran_time BETWEEN
to_date(TO_CHAR(SYSDATE, 'DD-MON-YYYY')|| ' 00:00:00', 'DD-MON-YYYY HH24:MI:SS') - 12
AND
to_date(TO_CHAR(SYSDATE, 'DD-MON-YYYY') || ' 23:59:59', 'DD-MON-YYYY HH24:MI:SS')-9
and cust_nbr=470707
ORDER BY a.tran_time DESC
) b
WHERE rownum <= 25
ORDER BY b.t DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 8.20 268.54 35159 35162 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8.28 268.60 35159 35162 0 3
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT ORDER BY (cr=35162 pr=35159 pw=0 time=268540731 us)
3 COUNT STOPKEY (cr=35162 pr=35159 pw=0 time=268540672 us)
3 VIEW (cr=35162 pr=35159 pw=0 time=268540662 us)
3 SORT ORDER BY STOPKEY (cr=35162 pr=35159 pw=0 time=268540646 us)
3 FILTER (cr=35162 pr=35159 pw=0 time=268540563 us)
3 TABLE ACCESS BY GLOBAL INDEX ROWID TDS_TRAN_LOG PARTITION: ROW LOCATION ROW LOCATION (cr=35162 pr=35159 pw=0 ti
me=268540537 us)
5299375 INDEX RANGE SCAN TDS_TRAN_LOG_IX10 (cr=35157 pr=35157 pw=0 time=265113361 us)(object id 117305)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 35159 0.28 261.97
SQL*Net message from client 2 179.91 179.91
********************************************************************************
|
|
|
|
| Re: Fetch is taking too long [message #377412 is a reply to message #377409] |
Mon, 22 December 2008 22:25  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
There looks to be a bit of redundant IO going on here: 5 millions rows scanned in the index for a total payload of just 3 matching rows!
You need an index on (cust_nbr, tran_time) in that order.
Ross Leishman
|
|
|
|
| Re: Fetch is taking too long [message #377579 is a reply to message #377412] |
Tue, 23 December 2008 10:36 |
srinivas4u2
Messages: 66
Registered: June 2005
|
Member |
|
|
Ross...man I don't know what to say...you are a GENIUS!!!
Query rocks now!!!
Thanks a ton for your help on this!!!
Could you tell what prompted you to create this index, other than fact that tran_time is the column on which it's partitioned? Any clue from the tkprof output?
And how did you get the 5M rows from the index?
Thanks,
Sri
|
|
|
|
|
|
| Re: Fetch is taking too long [message #377858 is a reply to message #377579] |
Thu, 25 December 2008 23:23 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| srinivas4u2 wrote on Wed, 24 December 2008 03:36 |
Could you tell what prompted you to create this index, other than fact that tran_time is the column on which it's partitioned? Any clue from the tkprof output?
|
Your query looks at CUST_NBR and TRAN_TIME in the WHERE clause. It has nothing to do with the partitioning, you just index the columns on which you are filtering.
The only question is the order: CUST_NBR first, or TRAN_TIME first. Since you are querying TRAN_TIME with a range predicate (BETWEEN), it must go last, otherwise columns that follow it will not participate in the scan.
| srinivas4u2 wrote on Wed, 24 December 2008 03:36 | And how did you get the 5M rows from the index?
|
5299375 INDEX RANGE SCAN TDS_TRAN_LOG_IX10 (cr=35157 pr=35157 pw=0 time=265113361 us)(object id 117305)
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
