Archive

Archive for September, 2010

Intermittent high responding time

September 28, 2010 Leave a comment

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>

ORA-01017: invalid username/password; logon denied (via database link)

September 1, 2010 7 comments

Symptoms

Today, I create a database link from 10g to 11g database as the following

SQL> create database link vodcms connect to vod identified by vod using ‘vodcms’;

When I test the database link connectivity,

SQL> select tname from tab@vodcms;
select tname from tab@vodcms
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from VODCMS

Causes

The target database 11g has enabled the case sensitive logon.

SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE
———————————— ——————————–
VALUE
——————————
sec_case_sensitive_logon             boolean
TRUE

Fix

Recreate the database link by using double quotes to enclose lower cases for username and password

SQL> create database link vodcms connect to “vod” identified by “vod” using ‘vodcms’;

Test the connectivity

SQL> select tname from tab@vodcms;

TNAME
——————————
AQUA_EXPLAIN_990744
PF_VOD_SCHED_PROD_STATUS
TEMP_VI_VODTEMPLATE_MAP_T
TEMP_VOD_APPLIED_IMAGE_T
VI_CONTENT_PROVIDER_CATEGORY_T
VI_CONTENT_PROVIDER_DESC_T
VI_CONTENT_PROVIDER_GP_MAP_T
VI_CONTENT_PROVIDER_GP_T
VI_CONTENT_PROVIDER_T
VI_CONTENT_PROVIDER_TYPE_T
VI_CONTENT_TAG_MASTER_T

Categories: TroubleShooting