Home » RDBMS Server » Performance Tuning » Fetch is taking too long (10.2.0.3)
Fetch is taking too long [message #377409] Mon, 22 December 2008 17:07 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #377700 is a reply to message #377579] Wed, 24 December 2008 06:47 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member

5M rows question was an overlook, sorry.

Thanks,
Sri
Re: Fetch is taking too long [message #377858 is a reply to message #377579] Thu, 25 December 2008 23:23 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Fetch is taking too long [message #378251 is a reply to message #377858] Mon, 29 December 2008 08:26 Go to previous message
srinivas4u2
Messages: 66
Registered: June 2005
Member

Thanks a lot Ross!
Previous Topic: Thread 1 cannot allocate new log
Next Topic: Sql query performance
Goto Forum:
  


Current Time: Sat Dec 03 05:56:43 CST 2016

Total time taken to generate the page: 0.04457 seconds