Archive

Archive for the ‘SQL Tuning’ Category

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

June 24, 2011 Leave a comment

Today, I try to change a invisible index to visible, I get the following messages

“ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”. Before 11g, we can only retry and retry until get the resource.

In 11gR, we can change the session to wait for the resource lock by the command below

SQL> ALTER SESSION SET ddl_lock_timeout=40;<- this means that try to lock resource s until ddl_lock_timeout ( 40 sec)

Session altered.

SQL> alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible;
alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ( after 40 second, still can’t lock the resources)

SQL> / <– retry again, it is ok

Advertisements

Create Invisible Index in 11gR1 for SQL Performance Tuning

June 24, 2011 Leave a comment

Today,  I need to create a index to test the performance of a SQL.In order to impact current SQL,  I use invisible option to create indexes. The following is the example to use the invisible option

SQL> create bitmap index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 on cmsnews.DATAFEED_NEWS_CONTROLFILE (Ltrim(Rtrim(Lower(filename)))) invisible;

SQL> set autotrace on

SQL> @pq01

lan hash value: 2791608648

—————————————————————————————————–
| Id  | Operation          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————————–
|   0 | SELECT STATEMENT   |                                |     1 |  1011 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                                |     1 |  1011 |            |          |
|*  2 |   FILTER           |                                |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| DATAFEED_NEWS_CONTROLFILE_IDX1 |     1 |  1011 |     0   (0)| 00:00:01 |
—————————————————————————————————–

It shows that the index DATAFEED_NEWS_CONTR_INDEX2 is not used

SQL> ALTER SESSION SET “optimizer_use_invisible_indexes” = TRUE; <– alter session to  use the visible index

SQL> @pq01

Plan hash value: 73755227

——————————————————————————————————————–
| Id  | Operation                         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————————–
|   0 | SELECT STATEMENT                  |                                |     1 |  1011 |     2  (50)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |                                |     1 |  1011 |            |          |
|*  2 |   FILTER                          |                                |       |       |            |          |
|   3 |    BITMAP CONVERSION COUNT        |                                |     1 |  1011 |     2  (50)| 00:00:01 |
|   4 |     BITMAP AND                    |                                |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                                |       |       |            |          |
|   6 |       SORT ORDER BY               |                                |       |       |            |          |
|*  7 |        INDEX RANGE SCAN           | DATAFEED_NEWS_CONTROLFILE_IDX1 |       |       |     0   (0)| 00:00:01 |
|*  8 |      BITMAP INDEX SINGLE VALUE    | DATAFEED_NEWS_CONTR_INDEX2     |       |       |            |          |
——————————————————————————————————————–
It shows that the new index is used by the session

Finally, make the indexes visible to all sessions, by the command below

SQL> alter index cmsnews.DATAFEED_NEWS_CONTR_INDEX2 visible

 

Categories: SQL Tuning

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>

Upper Function suppresses index

December 7, 2009 Leave a comment
Founding:
 
– The upper function is used doubtfully on the p.fsa since the p.fsa are numeric. 
 After removing the upper function, the SQL gets hundred times faster than before.
– The detailed comparsion is shown as below
 

Before Tuning After Tuning
Responding time=2 sec. Responding Time=0.01sec
SQL Statistics
———————————————————-
          0  recursive calls
          0  db block gets
      35689  consistent gets
          0  physical reads
          0  redo size
       6346  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL Statistics
———————————————————-
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       6346  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

  
Tuned SQL
 
SELECT *
FROM (SELECT row_number() over (PARTITION BY fsa ORDER BY stb_num) AS rn, stb_id_act AS stb_id_1,
      LEAD(stb_id_act) OVER (PARTITION BY fsa ORDER BY stb_num) AS stb_id_2, res.*
FROM
(SELECT pa.stb_num, pa.stb_id AS stb_id_act, p.*
                FROM
                VI_PROFILE_T p,
                VI_PROFILE_ACTIVATION_T pa
                WHERE
                p.fsa = pa.fsa(+)
                AND p.fsa IN (select p.fsa
                                from
                                VI_PROFILE_T p,
                                VI_PROFILE_ACTIVATION_T pa
                                where
                                p.fsa = pa.fsa(+)
                                — AND  (UPPER(p.fsa) LIKE UPPER(‘10000599’) ESCAPE ‘\’) <– the upper function suppress the index and it is doubtful because all data of fsa are numeric
                                  AND  (p.fsa LIKE upper(‘10000599’) ESCAPE ‘\’)  <– remove the upper function like that
                                AND  (UPPER(status) = UPPER(‘CLOSED’) )
                                )
ORDER BY pa.stb_num ) res
)
WHERE rn = 2
/
Categories: SQL Tuning