Amit Bansal

Creating ASM devices on AIX

I thought of sharing few tips on creating ASM devices on AIX which I will be helpful to Oracle DBA’s. Suppose SysAdmin gives you list of Serial numbers for LUN instead of device Name

pcmpath query device
DEV#:  33  DEVICE NAME: hdisk33  TYPE: 2107900  ALGORITHM:  Load Balance
SERIAL: 75DM011<span style="color: #ff0000;"><strong>1101</strong></span>
===========================================================================
Path#      Adapter/Path Name          State     Mode     Select     Errors
    0           fscsi0/path0          CLOSE   NORMAL          9          0
    1           fscsi1/path1          CLOSE   NORMAL          8          0

In case there are lot many disks, then it could be a tiring task of running above command and finding each device. You can use below code which will list name of  devices and size (In MB) of disk.

for i in 1000 1100    1018    1118    1030    1130    104C    114C    1068    1168    1080    1180
do
j=`pcmpath query device|grep -p $i"$"|grep DEVICE|awk -F ":" '{print }'|awk '{print }`
k=`bootinfo -s $j`
echo $i $j $k
done

This would return following output

1000 hdisk4 65536
1100 hdisk10 65536
1018 hdisk5 65536
1118 hdisk11 65536
1030 hdisk6 65536
1130 hdisk12 65536
104C hdisk7 65536
114C hdisk13 65536
1068 hdisk8 65536
1168 hdisk14 65536
1080 hdisk9 65536
1180 hdisk15 65536

Now if you need to create new device name, you need to use mknod command and pass on major and minor numbers. Following code can be used to perform same

#export m=0
 # for i in hdisk4  hdisk10 hdisk5  hdisk11 hdisk6  hdisk12 hdisk7  hdisk13 hdisk8  hdisk14 hdisk9  hdisk15
 do
 j=`ls -la /dev/$i |awk '{print }'|awk -F "," '{print }'`
  k=`ls -la /dev/$i |awk '{print }'`
 m=`expr $m + 1` ;echo "mknod /dev/asm_disk"$m "c "$j $k
 done

 mknod /dev/asm_disk1 c 21 4
 mknod /dev/asm_disk2 c 21 12
 mknod /dev/asm_disk3 c 21 13
 mknod /dev/asm_disk4 c 21 15
 mknod /dev/asm_disk5 c 21 5
 mknod /dev/asm_disk6 c 21 6
 mknod /dev/asm_disk7 c 21 8
 mknod /dev/asm_disk8 c 21 7
 mknod /dev/asm_disk9 c 21 14
 mknod /dev/asm_disk10 c 21 10
 mknod /dev/asm_disk11 c 21 9
 mknod /dev/asm_disk12 c 21 11

Now you can change the ownership to oracle:dba and permission to 660. I have 12 disks , so using list of 12 variables. In case you have more disks , then you can add more variables

# for i in 1 2 3 4 5 6 7 8 9 10 11 12
do
chown oracle:dba /dev/asm_disk$i
chmod 660 /dev/asm_disk$i
done

crw-rw----    1 oracle   dba          21, 11 Jan 28 17:10 /dev/asm_disk12
crw-rw----    1 oracle   dba          21,  9 Jan 28 17:10 /dev/asm_disk11
crw-rw----    1 oracle   dba          21, 10 Jan 28 17:10 /dev/asm_disk10
crw-rw----    1 oracle   dba          21, 14 Jan 28 17:04 /dev/asm_disk9
crw-rw----    1 oracle   dba          21,  7 Jan 28 17:04 /dev/asm_disk8
crw-rw----    1 oracle   dba          21,  8 Jan 28 17:04 /dev/asm_disk7
crw-rw----    1 oracle   dba          21,  6 Jan 28 17:04 /dev/asm_disk6
crw-rw----    1 oracle   dba          21,  5 Jan 28 17:04 /dev/asm_disk5
crw-rw----    1 oracle   dba          21, 15 Jan 28 17:04 /dev/asm_disk4
crw-rw----    1 oracle   dba          21, 13 Jan 28 17:04 /dev/asm_disk3
crw-rw----    1 oracle   dba          21, 12 Jan 28 17:04 /dev/asm_disk2
crw-rw----    1 oracle   dba          21,  4 Jan 28 17:04 /dev/asm_disk1

In case you need to use same logic for creating OCR and Voting disks on RAC system, replace /dev/asm with /dev/ocr or /dev/voting . I hope this would save some time and also prevent errors 🙂

In case you have disks in ordered number,say 53 to 62 then you can also use for loop as below. 

#bash
bash-3.00#
#export m=0
#for ((i=53;i<=62;i++))
do
 j=`ls -la /dev/hdisk$i |awk '{print }'|awk -F "," '{print }'`
  k=`ls -la /dev/hdisk$i |awk '{print }'`
   m=`expr $m + 1` ;echo "mknod /dev/asm_disk"$m "c "$j $k
 done
#for ((i=1;i<=10;i++))
do
chown oracle:dba /dev/asm_disk$i
chmod 660 /dev/asm_disk$i
done

I would suggest anyone using the scripts to first check in a test environment.

1 February, 2009 by 10g 7

CRS Fails to Start – 10.2.0.1 RAC Install on AIX

I was installing 10.2.0.1 on IBM AIX 5L and while running root.sh from first node (as part of Clusterware installation) got following messages

Now formatting voting device: /dev/voting_disk01
Now formatting voting device: /dev/voting_disk02
Now formatting voting device: /dev/voting_disk03
Format of 3 voting devices complete.
Startup will be queued to init within 30 seconds

I waited for quite some time and found that it was stuck. To check what was status of CSS, I did a grep for CSS and found that it was running /etc/init.cssd startcheck css script. This indicated that Oracle was stuck trying to start CSS. Following errors were recorded in /tmp/crsct.7459

Failure in CSS initialization opening OCR.

Metalink notes suggested checking OCR Disk permission , though in my case they had correct permissions i.e ownership as oracle:dba and permission set to 660. To diagnose further, I checked $ORA_CRS_HOME/log to check for errors. All the logfiles related to CRS,CSS and EVMD are stored in $ORA_CRS_HOME/log/<hostname>.

/oracle/crs_base/app/product/crs10gR2/log>ls -ltr
total 0
drwxrwx---    2 oracle   dba             256 Jan 28 18:46 crs
drwx------    3 root     system          256 Jan 28 18:53 chd0196
drwxr-xr-t    8 root     dba             256 Jan 28 18:53 rac01

Hostname for the server was rac01 and not chd0196. This was a new server and also directories could not be present earlier as it was a fresh installation.  Oracle was picking two hostname which was quite strange. I checked for HACMP filesets  and found that they were present

# lslpp -l |grep -i hacmp
  rsct.basic.hacmp           2.4.9.0  COMMITTED  RSCT Basic Function (HACMP/ES
  rsct.compat.basic.hacmp    2.4.9.0  COMMITTED  RSCT Event Management Basic
                                                 Function (HACMP/ES Support)
  rsct.compat.clients.hacmp  2.4.9.0  COMMITTED  RSCT Event Management Client
                                                 Function (HACMP/ES Support)

10g RAC does not require Vendor clusterware as Oracle provides it own clusterware called “Oracle Clusterware”.We got these packages un-installed and got both server rebooted. After cleaning up RAC installation, we restarted installation . You can use  Metalink Note 239998.1 – 10g RAC: How to Clean Up After a Failed CRS Install for cleanup.  On re-running root.sh installation, installation went fine.

1 February, 2009 by 10g 0

Input is too long (> 2499 characters) – line ignored

There are times when you observe above mentioned error while creating view and mview from mview definition taken from Toad or indexfile option. vi editor comes in handy to resolve this issue. Copy the code in Text file and while in escape mode enter following command

:%s/,/,^M/g

Please note that to insert ^M you need to press Ctrl+V+M keys together.We are basically using the command to enter a line break after each comma. 

30 January, 2009 by Unix 0

Recreating Database Link

I was trying to recreate database links as part of database migration activity. This involved mixture of 8i,9i and 10g database.

In case of 8i/9i it is very easy to find the old password which was used to create database link as passwords for database links are stored in Clear Text (decrypted format) in SYS.LINK$ .As a result we can easily re-create database links. Find below steps to perform the same

SQL> set pages 1000 lines 80
SQL> col db_link for a20
SQL> col host for a20
SQL> col user_id for a20
SQL> SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

NAME            HOST                 USERID                 PASSWORD
-------------- -------------------- ----------------- -----------------
TO_AMIT         AMITDB             	 AMIT                 AMIT123

create database link TO_AMIT connect to AMIT identified by AMIT123 using 'AMITDB';
Database link created.

In case of 10g passwords are stored in encrypted format in SYS.LINK$. I tried to workaround this by recreating using identified by values.

SQL> create database link TO_AMIT connect to AMIT identified by values '14573425Z6D15503' using 'AMITDB';

Database link created.

SQL> select sysdate from dual@TO_AMIT;
select sysdate from dual@TO_APREAD
                         *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], []

I researched metalink and found Note 456320.1 according to which this is expected behavior and ‘identified by values’ can be used only by Oracle Internal utilities. Therefore only way to re-create database link is to use exp/imp. As this is 10g, EXPDP and IMPDP is better suited as it will help in  exporting/importing database links only.


SQL> select * from DATABASE_EXPORT_OBJECTS where object_path like '%LINK%';

OBJECT_PATH                              COMMENTS                                           N
---------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/SCHEMA/DB_LINK           Private and public database links
DB_LINK                                  Private and public database links                  Y
SCHEMA/DB_LINK                           Private and public database links

From above we can find that we need to use DB_LINK in INCLUDE clause for exporting Database Links. Find below steps which I used to re-create database link in another schema

SQL> conn test/test123
Connected.
SQL> create database link TO_AMIT connect to AMIT identified by amit123 using 'test10';

Database link created.

SQL> select sysdate from dual@to_amit;

SYSDATE
---------
18-JAN-09

SQL> select owner,db_link,host from dba_db_links;

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

D:\>expdp system/oracle schemas=TEST include=DB_LINK dumpfile=expdp.dmp

Export: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:18:06

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=TEST include=DB_LINK dumpfile=expdp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  G:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST10\DPDUMP\EXPDP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:18:10

D:\>impdp system/oracle remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp

Import: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:20:38

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:20:40

D:\>exit

SQL> l
  1* select owner,db_link,host from dba_db_links
SQL> /

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10
AMIT                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

SQL> conn amit/amit123
Connected.
SQL> select sysdate from dual@to_Amit;

SYSDATE
---------
18-JAN-09
18 January, 2009 by 10g 4

Changes To 10g OCA Certification

People planning to appear for  10g OCA Exam will be startled to find that they need to pass additional exam to get the certificate. Starting from December 1, 2008, in addition to the existing exam (Oracle Database 10g: Administration I (1Z0-042)), candidates are also required to pass any one of the four following Oracle SQL exams to earn the certification:

Introduction to Oracle: SQL® and PL/SQL™ (1Z0-001) or,
Introduction to Oracle9i: SQL (1Z0-007) or,
Oracle Database 11g: SQL Fundamentals I (1Z1-051) or,
Oracle Database SQL Expert (1Z0-047)

People who have cleared the IZ0-042 exam before 30 Nov 2008 are not required to give the exam. Below is snapshot from Oracle website detailing new Certification Path. You can find the details here

10g_certification1

18 December, 2008 by 10g 5

Index Access best approach?? Not Always…

There is a common misconception that Index access is the best access method for query execution. This approach leads to people concentrate on removing Full Table Scans (FTS) from the Query execution plan. I recently worked on a performance tuning issue where I found such scenario. Basically customer was trying to generate report for 3 years and query was running for more then 8 hours without producing any results. We were asked to look into this problem.

Following diagnostic data was collected to diagnose the issue.
1)    10046 trace at level 12
2)    Execution plan from V$SQL_PLAN (Database version is 9.2)
3)    Explain plan for offending query

From 10046 trace and V$session_wait, I found out query was waiting on  “db file sequential read” wait event for table SCOTT_BILL. This indicated that we were reading this table using index rowid’s. Please find below explain plan for the problematic query.

SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------
| Id  | Operation                         |  Name          | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |     1 |   161 | 66436 |
|   1 |  SORT ORDER BY                    |                |     1 |   161 | 66433 |
|*  2 |   FILTER                          |                |       |       |       |
|   3 |    NESTED LOOPS                   |                |     1 |   161 | 66424 |
|   4 |     NESTED LOOPS                  |                |     1 |   141 | 66421 |
|   5 |      NESTED LOOPS                 |                |     1 |   126 | 66419 |
|   6 |       NESTED LOOPS                |                |   391 | 27761 | 65246 |
|*  7 |        TABLE ACCESS FULL          | SCOTT_PO        | 21716 |   254K|    98 |
|   8 |        TABLE ACCESS BY INDEX ROWID| SCOTT_POIC      |     1 |    59 |     3 |
|*  9 |         INDEX RANGE SCAN          | SCOTT_POIC_MK3  |     1 |       |     2 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | SCOTT_BILL      |     1 |    55 |     3 |
|* 11 |        INDEX RANGE SCAN           | SCOTT_BILL_FK7  |     1 |       |     2 |
|* 12 |      TABLE ACCESS BY INDEX ROWID  | SCOTT_CUST      |     1 |    15 |     2 |
|* 13 |       INDEX RANGE SCAN            | SCOTT_CUST_FK1  |     1 |       |     1 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | SCOTT_DSST      |     1 |    20 |     3 |
|* 15 |      INDEX UNIQUE SCAN            | SCOTT_DSST_PK   |     1 |       |     2 |
|  16 |       SORT AGGREGATE              |                |     1 |    17 |       |
|  17 |        FIRST ROW                  |                |     1 |    17 |     4 |
|* 18 |         INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK   |   166M|       |     4 |
|  19 |    SORT AGGREGATE                 |                |     1 |    36 |       |
|* 20 |     INDEX RANGE SCAN              | SCOTT_POIC_UK1  |     1 |    36 |     3 |
------------------------------------------------------------------------------------

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

   2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO")
              FROM "MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2
              AND "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
   7 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
   9 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
              "SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
       filter("SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
  10 - filter("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-11-30
              23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01
              ' AND "SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND
              "SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS NULL AND
              NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
              "SYS_ALIAS_6"."BILL_INER_CODE" IS NULL AND
              "SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
              "SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO")
  11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
       filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
              ))
  12 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
              "SCOTT_CUST"."CUST_INER_CODE" IS NULL)
  13 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
  15 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND
              "C"."DSST_PROC_GROUP"=1 AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"=
              (SELECT /*+ */ MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE
              "D"."DSST_PROCESS"='PSRV' AND "D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
  18 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
              "D"."DSST_PROCESS"='PSRV')
  20 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
              "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
              "B"."POIC_CTRT_TYPE"='BAS')
       filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')

Looking at explain plan (and plan from V$SQL_PLAN) confirmed that we were using index SCOTT_BILL_FK7. Plan shows that CBO estimates only 1 row for this operation. But since we have been on this wait even for close to 8 hours, it was clear that we are accessing more then 1 row.Normally this query was run for small range(date column) and used to complete in 10 mins. e.g Below is explain plan for the same query with 4 day’s range. 

SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------
| Id  | Operation                             |  Name           | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     1 |   161 | 28876 |
|   1 |  SORT ORDER BY                        |                 |     1 |   161 | 28873 |
|*  2 |   FILTER                              |                 |       |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID        | SCOTT_CUST       |     1 |    15 |     2 |
|   4 |     NESTED LOOPS                      |                 |     1 |   161 | 28864 |
|   5 |      NESTED LOOPS                     |                 |     1 |   146 | 28862 |
|   6 |       NESTED LOOPS                    |                 |     1 |   134 | 28861 |
|   7 |        NESTED LOOPS                   |                 |    56 |  4200 | 28581 |
|*  8 |         TABLE ACCESS BY INDEX ROWID   | SCOTT_BILL       |  2123 |   114K| 22212 |
|*  9 |          INDEX RANGE SCAN             | SCOTT_BILL_MK10  | 26081 |       |    85 |
|  10 |         TABLE ACCESS BY INDEX ROWID   | SCOTT_DSST       |     1 |    20 |     3 |
|* 11 |          INDEX UNIQUE SCAN            | SCOTT_DSST_PK    |     1 |       |     2 |
|  12 |           SORT AGGREGATE              |                 |     1 |    17 |       |
|  13 |            FIRST ROW                  |                 |     1 |    17 |     4 |
|* 14 |             INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK    |   166M|       |     4 |
|* 15 |        TABLE ACCESS BY INDEX ROWID    | SCOTT_POIC       |     1 |    59 |     5 |
|* 16 |         INDEX RANGE SCAN              | SCOTT_POIC_MK3   |     1 |       |     2 |
|* 17 |       TABLE ACCESS BY INDEX ROWID     | SCOTT_PO         |     1 |    12 |     1 |
|* 18 |        INDEX UNIQUE SCAN              | SCOTT_PO_PK      |     1 |       |       |
|* 19 |      INDEX RANGE SCAN                 | SCOTT_CUST_FK1   |     1 |       |     1 |
|  20 |    SORT AGGREGATE                     |                 |     1 |    36 |       |
|* 21 |     INDEX RANGE SCAN                  | SCOTT_POIC_UK1   |     1 |    36 |     3 |
-----------------------------------------------------------------------------------------

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

   2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO") FROM
              "MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2 AND
              "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
   3 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
              "SCOTT_CUST"."CUST_INER_CODE" IS NULL)
   8 - filter(NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01' AND
              "SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND "SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS
              NULL AND NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
              "SYS_ALIAS_6"."BILL_INER_CODE" IS NULL)
   9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
              'yyyy-mm-dd hh24:mi:ss'))
  11 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND "C"."DSST_PROC_GROUP"=1
              AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"= (SELECT /*+ */
              MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE "D"."DSST_PROCESS"='PSRV' AND
              "D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
  14 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
              "D"."DSST_PROCESS"='PSRV')
  15 - filter("SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO" AND
              NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0))
  16 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
              "SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
              "SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
  17 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
  18 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
  19 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
  21 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
              "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND "B"."POIC_CTRT_TYPE"='BAS')
       filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
              NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')

Note: cpu costing is off

We can see that we used SCOTT_BILL_MK10 index here which was on the date column. This is also clear from predicate information available from explain plan.

   9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
              'yyyy-mm-dd hh24:mi:ss'))

Coming back to our case, we see that index SCOTT_BILL_FK7 is being used here.This index is created on BILL_PO_NO, BILL_PO_SEQ_NO and BILL_RQMT_NO columns.

From explain plan, we see that following condition is used due to which we access the index

   11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
              "SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
       filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
              ))

Checking the column values, we see following

COLUMN_NAME          NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
BILL_PO_NO             31841        636
BILL_PO_SEQ_NO          4       1296
BILL_RQMT_NO           437   27333280

I believe lot of null records present in the table along with NVL function usage is causing this issue. We can see from above data, that column BILL_RQMT_NO has 27 million null records. As B-Tree index does not index Null columns(B-tree indexes index null columns too if index is a concatenated index and the other column has non-null value. Refer to this article from Richard Foote). CBO assumed that only 1 row will be returned and it choose index SCOTT_BILL_FK7 as best access method. But because of NVL function, this returns more rows instead of just one row. This is my hypothesis and not sure , if its correct 🙂
As a quick fix , I suggested using FULL hint on SCOTT_BILL table so that we can perform a Full table Scan (FTS). This helped and query completed in 28 mins. If we look at long term solution, having a default value of 0 for this column could also be looked at. But all these things need to be decided in consideration with application design.

This is also a reminder to a fact that index access is not always the best access method. It is suitable if we are looking for very small data set and could become problem if data set increases.Whereas Full table Scan (FTS)  perform better in such cases as we perform multiblock I/O.

17 December, 2008 by Performance 6

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers