Home > SQL Tuning, TroubleShooting > Intermittent high responding time

Intermittent high responding time

September 28, 2010 Leave a comment Go to comments

The application developer reported that there was intermittent high responding time for the following SQL

SELECT  pmt.orderid,
pmt.trandate                                  AS orderdate,
odritms.productid,
odritms.chiname,
odritms.engname,
odritms.providerid,
odritms.skuid,
odritms.quantity,
odritms.paymethod,
odritms.paytype,
odritms.movieclass,
odritms.licenseenddate,
pdr.smallimgname,
pdr.producttype,
( odritms.totalmaxcount – odritms.viewcount ) AS remainviewcount,
pmt.paystatus,
pmt.unitprice,
pmt.discountprice
FROM   orderitem odritms,
product pdr,
payment pmt
WHERE  pmt.userid = ‘24264983’
AND ( pmt.paystatus = ‘1’
OR pmt.paystatus = ‘-1’ )
AND pmt.orderid = odritms.orderid
AND odritms.productid = pdr.productid
AND ( pdr.producttype = ‘MOVIE’
OR pdr.producttype = ‘MTV’ )
AND odritms.productid = ‘LINK00000016’
AND To_char(odritms.licenseenddate, ‘YYYYMMDD’) >= To_char(sysdate, ‘YYYYMMDD’)
AND ( odritms.totalmaxcount – odritms.viewcount ) >= 0
ORDER  BY odritms.orderid,
odritms.itemnum
/

Symptoms

1. The responding time of the SQL is about 2~3 mins at first time

2. The responding time of the SQL is about 0.4 second when it was reun.

3. There are no problems found on explain plans

PLAN_TABLE_OUTPUT
————————————————————————————————————————————

———————————————————————————–
| Id  | Operation                       |  Name           | Rows  | Bytes | Cost  |
———————————————————————————–
|   0 | SELECT STATEMENT                |                 |       |       |       |
|   1 |  SORT ORDER BY                  |                 |       |       |       |
|   2 |   CONCATENATION                 |                 |       |       |       |
|   3 |    NESTED LOOPS                 |                 |       |       |       |
|   4 |     NESTED LOOPS                |                 |       |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| PAYMENT         |       |       |       |
|*  6 |       INDEX RANGE SCAN          | PAYMENT_IDX     |       |       |       |
|*  7 |      TABLE ACCESS BY INDEX ROWID| ORDERITEM       |       |       |       |
|*  8 |       INDEX RANGE SCAN          | ORDERITEM1_IDX  |       |       |       |
|*  9 |     TABLE ACCESS BY INDEX ROWID | PRODUCT         |       |       |       |
|* 10 |      INDEX UNIQUE SCAN          | PK_PRODUCT      |       |       |       |
|  11 |    NESTED LOOPS                 |                 |       |       |       |
|  12 |     NESTED LOOPS                |                 |       |       |       |
|  13 |      TABLE ACCESS BY INDEX ROWID| PAYMENT         |       |       |       |
|* 14 |       INDEX RANGE SCAN          | PAYMENT_IDX     |       |       |       |
|* 15 |      TABLE ACCESS BY INDEX ROWID| ORDERITEM       |       |       |       |
|* 16 |       INDEX RANGE SCAN          | ORDERITEM1_IDX  |       |       |       |
|* 17 |     TABLE ACCESS BY INDEX ROWID | PRODUCT         |       |       |       |
|* 18 |      INDEX UNIQUE SCAN          | PK_PRODUCT      |       |       |       |
———————————————————————————–

Predicate Information (identified by operation id):
—————————————————

6 – access(“PMT”.”USERID”=’24264983′ AND “PMT”.”PAYSTATUS”=’-1′)
7 – filter(“ODRITMS”.”TOTALMAXCOUNT”-“ODRITMS”.”VIEWCOUNT”>=0 AND
TO_CHAR(“ODRITMS”.”LICENSEENDDATE”,’YYYYMMDD’)>=TO_CHAR(SYSDATE@!,’YYYYMMDD’)) <– Then, filter the orderid & productid by licenseenddate
8 – access(“PMT”.”ORDERID”=”ODRITMS”.”ORDERID” AND
“ODRITMS”.”PRODUCTID”=’LINK00000016′) <– First, ORDERITEM1_IDX is used to search ORDERID & PRODUCTID on ORDERITEM table
9 – filter(“PDR”.”PRODUCTTYPE”=’MOVIE’ OR “PDR”.”PRODUCTTYPE”=’MTV’)
10 – access(“ODRITMS”.”PRODUCTID”=”PDR”.”PRODUCTID”)
14 – access(“PMT”.”USERID”=’24264983′ AND “PMT”.”PAYSTATUS”=’1′)
15 – filter(“ODRITMS”.”TOTALMAXCOUNT”-“ODRITMS”.”VIEWCOUNT”>=0 AND
TO_CHAR(“ODRITMS”.”LICENSEENDDATE”,’YYYYMMDD’)>=TO_CHAR(SYSDATE@!,’YYYYMMDD’))
16 – access(“PMT”.”ORDERID”=”ODRITMS”.”ORDERID” AND
“ODRITMS”.”PRODUCTID”=’LINK00000016′)
17 – filter(“PDR”.”PRODUCTTYPE”=’MOVIE’ OR “PDR”.”PRODUCTTYPE”=’MTV’)
18 – access(“ODRITMS”.”PRODUCTID”=”PDR”.”PRODUCTID”)

Note: rule based optimization

4. Flush cache memory by the command ‘alter session set events = ‘immediate trace name flush_cache’,

– the responding time of the SQL is high again

5. Enable session trace by ‘alter session set events=’10046 trace name context forever, level 12′

– There are more than ten thousands lines like below in the trace file

EXEC #1:c=0,e=991,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=66264756152003
WAIT #1: nam=’SQL*Net message to client’ ela= 4 p1=1650815232 p2=1 p3=0
WAIT #1: nam=’db file sequential read’ ela= 25205 p1=7 p2=854286 p3=1
WAIT #1: nam=’db file sequential read’ ela= 20295 p1=7 p2=854289 p3=1
WAIT #1: nam=’db file sequential read’ ela= 22670 p1=7 p2=854292 p3=1
WAIT #1: nam=’db file sequential read’ ela= 20550 p1=7 p2=855113 p3=1
WAIT #1: nam=’db file sequential read’ ela= 27226 p1=7 p2=855128 p3=1
WAIT #1: nam=’db file sequential read’ ela= 28417 p1=7 p2=857666 p3=1
WAIT #1: nam=’db file sequential read’ ela= 4772 p1=11 p2=210671 p3=1
WAIT #1: nam=’db file sequential read’ ela= 18945 p1=7 p2=851190 p3=1
WAIT #1: nam=’db file sequential read’ ela= 19504 p1=10 p2=130069 p3=1
WAIT #1: nam=’db file sequential read’ ela= 16400 p1=7 p2=851254 p3=1
WAIT #1: nam=’db file sequential read’ ela= 12534 p1=7 p2=851447 p3=1
WAIT #1: nam=’db file sequential read’ ela= 24912 p1=7 p2=851466 p3=1
WAIT #1: nam=’db file sequential read’ ela= 27478 p1=7 p2=851496 p3=1
WAIT #1: nam=’db file sequential read’ ela= 6864 p1=11 p2=210193 p3=1
WAIT #1: nam=’db file sequential read’ ela= 16136 p1=7 p2=851416 p3=1
WAIT #1: nam=’db file sequential read’ ela= 13395 p1=7 p2=851418 p3=1
WAIT #1: nam=’db file sequential read’ ela= 16521 p1=7 p2=851419 p3=1
WAIT #1: nam=’db file sequential read’ ela= 58 p1=7 p2=851425 p3=1
WAIT #1: nam=’db file sequential read’ ela= 11117 p1=7 p2=851431 p3=1
WAIT #1: nam=’db file sequential read’ ela= 54 p1=7 p2=851435 p3=1
WAIT #1: nam=’db file sequential read’ ela= 423 p1=7 p2=851436 p3=1
WAIT #1: nam=’db file sequential read’ ela= 10136 p1=7 p2=851360 p3=1

– Show that the session wait for a index object ORDERITEM1_IDX

SQL> @get_segment_name.sql
Enter value for p1_file_id: 7
old   3: WHERE file_id = &P1_FILE_ID
new   3: WHERE file_id = 7
Enter value for p2_block_no: 854286
old   4: AND &P2_BLOCK_NO BETWEEN block_id AND block_id + blocks -1
new   4: AND 854286 BETWEEN block_id AND block_id + blocks -1

OWNER
——————————
SEGMENT_NAME
——————————————————————————–
SEGMENT_TYPE
——————
PCTVPROD
ORDERITEM1_IDX
INDEX

6.  It concludes that the 10 thousands database blocks are caused by  reading  ORDERITEM1_IDX ,

i.e The index ORDERITEM1_IDX selectivity is Low.

Index                          Column Name          Type      Size (bytes)
—————————— ——————– ——— ————
PCTVPROD.ORDERITEM1_IDX        ORDERID              NUMBER              22
PRODUCTID            VARCHAR2            20

Fix

– Create another a more selectivity composite index replacing it, based on the predicate, adding the licenseenddate field like below,

SQL> exec DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT (‘LR00’);

SQL> create index orderitem2_idx on ORDERITEM ( ORDERID, PRODUCTID, to_char(licenseenddate, ‘YYYYMMDD’)) parallel 4;

or

SQL> create index orderitem2_idx on ORDERITEM ( ORDERID, PRODUCTID, to_char(licenseenddate, ‘YYYYMMDD’)) online;   ( online create )

SQL> alter session set events = ‘immediate trace name flush_cache’;

Session altered.

Elapsed: 00:00:00.39
SQL> @pg15 <– the problem  SQL

no rows selected

Elapsed: 00:00:00.00
SQL>

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: