Query Performance & Join Conditions – Update

Friends, on one of my previous blog of Query Performance & Join Conditions, I have received comments from anonymous reader and Vivek Sharma specific to statement “As per mathematics rules, when we say a=b and b=c, we can conclude a=c. But in oracle this is not the case. Oracle never concludes a=c.” According to their comments, this is wrong and oracle translates a=c if we provide a=b and b=c as join conditions. This update of blog is to accept their comments and I hereby confirm that oracle does transitive closure where it concludes a=c.

However, this statement is partially true and applies to constant filter conditions. For example, when I execute the below query, optimizer applies a.col1=20 predicate to all three tables. This means, when we pass bind variable or literal value to filter predicate, optimizer does transitive closure and with a=b and b=c concludes a=c.

SQL> select a.col2, b.col2, c.col2
  2  from  test1 a,
  3        test2 b,
  4        test3 c
  5  where a.col1 = b.col1
  6  and   b.col1 = c.col1
  7  and   a.col1 = 20;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1011508479

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    24 |     6   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN         |       |     1 |    24 |     6   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |       |     1 |    16 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST2 |     1 |     8 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | IND2  |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| TEST1 |     1 |     8 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IND1  |     1 |       |     1   (0)| 00:00:01 |
|   7 |   BUFFER SORT                 |       |     1 |     8 |     4   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| TEST3 |     1 |     8 |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN          | IND3  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   4 - access("B"."COL1"=20)
   6 - access("A"."COL1"=20)
   9 - access("C"."COL1"=20)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        464  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Now, as I mentioned it is partially true, optimizer does not apply this logic to join condition. Let’s go to our example from previous blog and lets execute simple form of the query (i.e. without connect by prior clause) –

SQL> SELECT ca.*, cu.*
  2  FROM   ca_receipt CA,
  3         cu_all CU
  4  WHERE  CA.CUSTID = CU.CUSTID
  5  AND    CA.CUSTID IN (SELECT CUSTID FROM cu_all where addr='abcdef')
  6  ORDER BY ACCTYPE DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 4100014117

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |    50 |  5700 |   582   (1)| 00:00:07 |
|   1 |  SORT ORDER BY                  |                       |    50 |  5700 |   582   (1)| 00:00:07 |
|   2 |   NESTED LOOPS                  |                       |    50 |  5700 |   581   (1)| 00:00:07 |
|   3 |    NESTED LOOPS                 |                       |    50 |  5700 |   581   (1)| 00:00:07 |
|   4 |     NESTED LOOPS                |                       |    50 |  2900 |   531   (1)| 00:00:07 |
|*  5 |      TABLE ACCESS FULL          | CU_ALL                |     1 |    36 |   479   (1)| 00:00:06 |
|   6 |      TABLE ACCESS BY INDEX ROWID| CA_RECEIPT            |    50 |  1100 |    52   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | IND_CA_RECEIPT_CUSTID |    50 |       |     2   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN           | PK_CU_ALL_IND         |     1 |       |     0   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | CU_ALL                |     1 |    56 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   5 - filter("ADDR"='abcdef')
   7 - access("CA"."CUSTID"="CUSTID")
   8 - access("CA"."CUSTID"="CU"."CUSTID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1701  consistent gets
       1694  physical reads
          0  redo size
        868  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Here if you would have observed, query of “IN” clause does not make any sense and one could have merged it with main query. Now lets change the join condition and observe the execution plan –

SQL> SELECT ca.*, cu.*
  2  FROM   ca_receipt CA,
  3         cu_all CU
  4  WHERE  CA.CUSTID = CU.CUSTID
  5  AND    CU.CUSTID IN (SELECT CUSTID FROM cu_all where addr='abcdef')
  6  ORDER BY ACCTYPE DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1700919772

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |    50 |  3900 |   532   (1)| 00:00:07 |
|   1 |  SORT ORDER BY                |                       |    50 |  3900 |   532   (1)| 00:00:07 |
|   2 |   NESTED LOOPS                |                       |    50 |  3900 |   531   (1)| 00:00:07 |
|   3 |    NESTED LOOPS               |                       |    50 |  3900 |   531   (1)| 00:00:07 |
|*  4 |     TABLE ACCESS FULL         | CU_ALL                |     1 |    56 |   479   (1)| 00:00:06 |
|*  5 |     INDEX RANGE SCAN          | IND_CA_RECEIPT_CUSTID |    50 |       |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| CA_RECEIPT            |    50 |  1100 |    52   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   4 - filter("ADDR"='abcdef' AND "CUSTID" IS NOT NULL)
   5 - access("CA"."CUSTID"="CUSTID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1701  consistent gets
       1694  physical reads
          0  redo size
        868  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Bingo!!! With simple change of join condition, optimizer does query transformation and unnecessary access of CU_ALL table has been removed.

To see other part of this behavior, let us execute the below query only with join condition –

SQL> create table test1 as select round(dbms_random.value(1,10000)) col1, level col2 from dual connect by level <= 1000;

Table created.

SQL> create table test2 as select round(dbms_random.value(1,10000)) col1, level col2 from dual connect by level <= 2000;

Table created.

SQL> create table test3 as select round(dbms_random.value(1,10000)) col1, level col2 from dual connect by level <= 3000;

Table created.

SQL> set autot trace
SQL> select a.col2, b.col2, c.col2
  2  from  test1 a,
  3        test2 b,
  4        test3 c
  5  where a.col1 = b.col1
  6  and   b.col1 = c.col1;

65 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1064705885

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |  1297 | 31128 |    11   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |       |  1297 | 31128 |    11   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |       |  1106 | 17696 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST1 |  1000 |  8000 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST2 |  2000 | 16000 |     4   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | TEST3 |  3000 | 24000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("B"."COL1"="C"."COL1")
   2 - access("A"."COL1"="B"."COL1")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
       2333  bytes sent via SQL*Net to client
        563  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         65  rows processed

Here we have joined three tables with two join conditions. Make note of optimizer’s final select selectivity estimate as 1297 rows.

Below we will execute the query with all three possibilities of join conditions –

SQL> select a.col2, b.col2, c.col2
  2  from  test1 a,
  3        test2 b,
  4        test3 c
  5  where a.col1 = b.col1
  6  and   b.col1 = c.col1
  7  and   a.col1 = c.col1;

65 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1064705885

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     1 |    24 |    11   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |       |     1 |    24 |    11   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |       |  1106 | 17696 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST1 |  1000 |  8000 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST2 |  2000 | 16000 |     4   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | TEST3 |  3000 | 24000 |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("B"."COL1"="C"."COL1" AND "A"."COL1"="C"."COL1")
   2 - access("A"."COL1"="B"."COL1")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
       2333  bytes sent via SQL*Net to client
        563  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         65  rows processed

Here we go… The optimizer has concluded final select selectivity as 1 with applied third join condition.

To conclude, optimizer applies transitive closure to constant filter condition and not to join conditions.

Oracle Database Migration across Platforms

For past few years, data is growing tremendously across the globe. The problem starts with managing this high volume data. Technology is also getting advanced with competition and to be in competition, high performance, availability and consistency is one of the mandatory requirement. This brings up another level of complications of data migration from one platform to another. Availability is being a major concern, the major challenge is migrate large data with minimum downtime. Here we will discuss the methods of migration database techniques.

Before we start for migration techniques, we need to understand database endianness. Based on platform architecture, endian can be either of format BIG or LITTLE. Big-endian and little-endian are terms that describe the order in which a sequence of bytes are stored in computer memory. Big-endian is an order in which the “big end” (most significant value in the sequence) is stored first (at the lowest storage address). Little-endian is an order in which the “little end” (least significant value in the sequence) is stored first. For example, in a big-endian computer, the two bytes required for the hexadecimal number 4F52 would be stored as 4F52 in storage (if 4F is stored at storage address 1000, for example, 52 will be at address 1001). In a little-endian system, it would be stored as 524F (52 at address 1000, 4F at 1001).

Database migration methodology is depends on source and target platform endianness format. Once you decide to migrate database across the platform, the available documented methods are as follows –

Cross or Same Endian

Migration Method Advantages Disadvantages
Export/Import or Expdp/Impdp 1.   Simple method

2.   Best when character set change is involved or database version upgrade is required

3.   Ideal for small size databases

1.    Re-org happens of database objects which can impact the performance of database.

2.    Time consuming and resource intensive method

Transportable Tablespace 1.   Suitable when set of tablespaces are to be migrated

2.   Can be used for large databases with less downtime

3.   Saves time consuming and resource intensive steps of export/import.

1.   Only applicable to self-contained set of tablespaces

2.   Tablespaces must be in READ-ONLY mode during migration

3.   Source and target must is same characterset

SQL Apply –

Logical Standby, Golden Gate, Streams or Third party like CDC

1.   Source and target database can be opened in READ-WRITE mode

2.   Less migration time

3.   Best for set of objects to be replicated

1.   May not be suitable for high transaction databases

2.   Replication maintenance is required

3.   Difficult to debug incase replication is not working

CTAS or Insert through DB link 1.   Best for set of tables or very small database or character set change is involved.

2.   Can be consider as replacement for export/import method

1.   Time consuming

2.   Only applicable for table migration

3.   PL/SQL objects to be migrated manually

Transportable Tablespace:

Though the transportable tablespace feature was introduced in Oracle version 8, cross platform transportable tableaspace feature was enabled in Oracle Version 10g. Following is the feature wise compatibility matrix for transportable tablespace.

Transport Scenario Source Min. Compatibility Target Min. Compatibility
Databases on Same platform 8.0 8.0
Tablespaces with different block size than the target database 9.0 9.0
Databases on different platforms 10.0 10.0

Following are brief steps about transportable tablespace –

  1. Check the tablespace for self-contained set
  2. Convert tablespace to READ ONLY mode
  3. Copy datafiles of tablespace to target system
  4. Convert them to target system format using RMAN convert command
  5. Use expdp/exp utility to take export (of metadata) using transportable tablespace clause
  6. Transfer the export dump file to target system
  7. Plug-in tablespace to target system using impdp/imp utility

Note – Here the conversion steps can be done on source or target system.

Same Endian

Transportable Database If source and target database’s endian format is same, this is the best available method for database migration. This method can be used databases having version greater than 10g. Following will be brief steps about migrating database with this method.

  1. Shutdown source database with immediate option
  2. Copy datafiles from source to target server
  3. Convert SYSTEM and UNDO tablespace’s datafiles
  4. Create new controlfile with RESETLOGS option
  5. Open database in resetlogs mode
  6. Create temporary tablespace files
  7. Shutdown database and open in UPGRADE mode
  8. Execute scripts – utlirp.sql and utlrp.sql
  9. Shutdown and startup database in normal mode for application use

Transfer of datafiles from source to target database is time consuming job in this method. For the same, you can try out below options –

  • RMAN compressed backup can be used to take backup on common mount point and then restore it. RMAN scans used blocks hence if backup and restore time can be reduced instead of copying complete database files.
  • If using ASM as storage, mounting of source Diskgroup disks to target system and RMAN “BACKUP AS COPY” command can be used to copy the datafiles. In this case, sector size of the Diskgroup matters and only Diskgroup of same sector size can be mounted/read by other system. This means, by default HPUX is having 1024 bytes sector size whereas AIX or SUN servers are having sector size of 512 bytes. So HPUX disks cannot be mounted on Sun or AIX Servers and vice versa.
  • If third party filesystem is being used, which is common to both the environments, like Veritas file system, then the migration will be very simple as mount point are portable across the system. In this case, storage level flash copy techniques can also be leveraged.
  • DBMS_FILE_TRANSFER procedure can also be used to copy the files between servers. In this method, database link has to be created. Since all database files are to be transferred, we need to have dummy database to create database link.

Standby Database – Some of the platforms supports cross platform standby database. Oracle support MOS# 413484.1 can be used to check the compatibility matrix for cross platform physical standby support. If source and target platform supports physical standby, then this will be the best method to migrate database with safest and smallest downtime for migration. The migration will happen through database switchover.

Functions and Performance

Function plays an important role in development environment. The functions can be implicit (defined by oracle) or explicit (defined by user – PL/SQL code). Developers use the PL/SQL functions as they simplify the large code.

The function may accept the value and must return the value. The user-defined functions are easy to use but they may degrade the performance badly. Hence it is always recommended to use the functions only and only when they are unavoidable. I have demonstrated the performance benefits by the simple query over user-defined functions.

If the condition does not satisfy, function will return null value. It means the output rows will be the number of rows satisfied by the main query. For e.g. if suppose the main query returns 1000 rows and function satisfies only 400 rows, then the output will have 1000 rows, wherein the function column will show 600 null values and 400 value returned by the function. Hence we must have to use outer join whenever we are merging function into the simple query. The example below uses select sub-query that plays the role of function.

SQL> create table fn_tables as select * from dba_tables;

Table created.

SQL> create table fn_indexes as select * from dba_indexes;

Table created.

SQL> create index fn_ind_tables on fn_tables (table_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'FN_TABLES', cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user, 'FN_INDEXES', cascade=>true);

PL/SQL procedure successfully completed.

SQL> create or replace function fn_test (tabowner varchar2, tabname varchar2)
  2     return varchar2 is
  3  tbsname varchar2(200);
  4  begin
  5     select tablespace_name into tbsname
  6     from    fn_tables
  7     where   owner=tabowner
  8     and     table_name=tabname;
  9  return tbsname;
 10  end;
 11  /

Function created.
SQL> set autot trace
SQL> select a.owner, a.index_name, a.status,
  2     fn_test(a.owner, a.table_name) "TBS_NAME"
  3  from       fn_indexes a
  4  /

1072 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2626245312

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1072 | 51456 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| FN_INDEXES |  1072 | 51456 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       1097  recursive calls
          0  db block gets
       3308  consistent gets
          0  physical reads
          0  redo size
      36706  bytes sent via SQL*Net to client
       1162  bytes received via SQL*Net from client
         73  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1072  rows processed
SQL> select a.owner, a.index_name, a.status,
  2        (select tablespace_name from fn_tables b
  3         where b.owner=a.owner
  4         and   a.table_name=b.table_name) "TBS_NAME"
  5  from  fn_indexes a
  6  /

1072 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 321380953

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1072 | 51456 |    9| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| FN_TABLES    |     1 |    29 |    2| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FN_IND_TABLES|     1 |       |    1| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | FN_INDEXES   |  1072 | 51456 |    9| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("B"."OWNER"=:B1)
   2 - access("B"."TABLE_NAME"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1573  consistent gets
          0  physical reads
          0  redo size
      36706  bytes sent via SQL*Net to client
       1162  bytes received via SQL*Net from client
         73  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1072  rows processed
SQL> select a.owner, a.index_name, a.status, b.tablespace_name "TBS_NAME"
  2  from   fn_indexes a,
  3         fn_tables b
  4  where  b.owner(+) = a.owner
  5  and    b.table_name(+) = a.table_name
  6  /

1072 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 893717710

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |  1072 | 82544 |    18   (6)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|            |  1072 | 82544 |    18   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | FN_TABLES  |   879 | 25491 |     8   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | FN_INDEXES |  1072 | 51456 |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - access("B"."OWNER"(+)="A"."OWNER" AND
              "B"."TABLE_NAME"(+)="A"."TABLE_NAME")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        135  consistent gets
          0  physical reads
          0  redo size
      36706  bytes sent via SQL*Net to client
       1162  bytes received via SQL*Net from client
         73  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1072  rows processed

Place all above required values in tabular format.

Query No. Query Criteria Cost Recursive Calls Consistent Gets
1 With Function 9 1097 3308
2 With Sub-Query 9 0 1573
3 With Simple Query 18 0 135

As shown in above table, though the cost of the third query is high, the number of consistent gets has been reduced tremendously. When we are using function, the recursive calls and consistent gets are very high. But when using sub-query both the values have been reduced but this query also suffers from performance compared to third query.

Recently I had got the following query from one of our customer. Due to confidentiality, I will not be posting the original query from the customer site but in this example I have made similar kind of query.

create or replace function get_minamount (cust number) 
return number is
amt number;

begin
     select min (amount) into amt 
     from contract 
     where customer=cust and status = 'a';

     if (amt is null) then
        select min (amount) into amt 
        from   contract 
        where  customer=cust and status = 'd';
     end if;

     if (amt is null) then
        select min (amount) into amt 
        from   contract 
        where  customer=cust and status = 's';
     end if;

     Return amt;

End;
/

The main query is as follows.

select customer, name, address, 
       get_minamount (customer) "MIN_AMT"
from   customer_master 
where  custcode is like 'Large%';

The customer_master is having millions of records and to execute this query it was taking around 8 Hrs. The given suggestion on this query is as follows.

select customer, name, address, min_amt, status
from  (select a.customer, name, address, status, 
              min(case when status in ('a','s','d') then amount end) 
   over (partition by b.customer, b.status) "MIN_AMT", 
              rank() over (partition by b.customer 
              order by (case when status = 'a' and amount is not null then 1
                             when status = 's' and amount is not null then 2
                             when status = 'd' and amount is not null then 3 
                       end), rownum) "RNK"
       from   customer_master a, 
              contract b
       where  a.customer = b.customer(+))
where rnk = 1;

After implementing the query the performance gain was very high. The modified query is taking around 20 minutes to execute against 8 Hrs.

To sum up, the user-defined functions may contribute in performance degradation of the query. The simple query will give major performance benefits against the query with functions. The performance of the query is inversely proportional to the number of consistent gets. Hence to improve the performance, we must minimize the consistent gets.

Do we need stats on all columns?

Recently I had visited one of the customers for performance review. As per the current stats gathering policy, they use following method to gather stats on the tables.

dbms_stats.gather_table_stats (user, tabname, cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS 75');

As can be seen from the command, the input from method_opt is to gather stats on all the indexed columns with bucket size as 75. This means the stats on the other non-index columns will not be collected. This is a wrong practice and stats on the columns of the table are crucial, particularly if these are use in queries.

When I was discussed with the customer, the customer had following misconception.

1. To make decision for index scan; the stats on indexed columns are available.
2. When there is no index available on column, why oracle needs stats on it. It will use FTS for such queries.

This is purely a misconception, even if a column, use in a query, is not indexed; the correct cardinality is very crucial in determining a correct join order. To prove this point, I have created following small test case.

I created two tables (test1 and test2) and indexes on it.

SQL> create table test1 as
  2  select mod(LVL, 3)  ID, LVL VAL from
  3  (select level "LVL" from dual connect by level <= 100000);

Table created.

SQL> create index ind_test1 on test1 (id);

Index created.

SQL> create table test2 as
  2  select lvl "ID", dbms_random.string('A',10) "STR" from (select
  3  level "LVL" from dual connect by level <=50000);

Table created.

SQL> create index ind_test2 on test2 (id);

Index created.

Let us generate optimizer statistics based on the policy as the customer used.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST1', cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 75');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST2', cascade=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 75');

PL/SQL procedure successfully completed.

The stats on the table are

SQL> select owner, table_name, partitioned, num_rows, blocks
  2  from dba_tables
  3  where table_name in ('TEST1','TEST2')
  4  and   owner = 'TEST';

OWNER      TABLE_NAME PAR   NUM_ROWS     BLOCKS
---------- ---------- --- ---------- ----------
TEST       TEST1      NO      100000        186

TEST       TEST2      NO       50000        156
break on table_name skip 1
select table_name, column_name,num_distinct, num_nulls, density
from dba_tab_columns
where table_name in ('TEST1','TEST2')
and   owner='TEST'
order by table_name;

TABLE_NAME COLUMN_NAM NUM_DISTINCT  NUM_NULLS    DENSITY
---------- ---------- ------------ ---------- ----------
TEST1      ID                    3          0    .000005
           VAL             

TEST2      ID                50000          0     .00002
           STR

Based on the input to dbms_stats, stats are collected only on indexed columns and not for other non-indexed columns.
The following query is used to check the execution plan.

SQL> set autot trace
SQL> select a.id, a.val, b.str
  2  from    test1 a,
  3          test2 b
  4  where   a.id = b.id
  5  and     a.val = 40;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=667 Bytes=77372)
   1    0   HASH JOIN (Cost=46 Card=667 Bytes=77372)
   2    1     TABLE ACCESS (FULL) OF 'TEST1' (Cost=19 Card=1000 Bytes=16000)
   3    1     TABLE ACCESS (FULL) OF 'TEST2' (Cost=17 Card=50000 Bytes=5000000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        327  consistent gets
        315  physical reads
          0  redo size
        489  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now, let’s delete and gather new statistics on the same tables.

SQL> exec dbms_stats.delete_table_stats (user, 'TEST1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_table_stats (user, 'TEST2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST1', cascade=>true, method_opt=>'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'TEST2', cascade=>true, method_opt=>'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

Let us check the stats again.

TABLE_NAME COLUMN_NAM NUM_DISTINCT  NUM_NULLS    DENSITY
---------- ---------- ------------ ---------- ----------
TEST1      ID                    3          0    .000005
           VAL              100000          0     .00001

TEST2      ID                50000          0     .00002
           STR               50000          0     .00002

The plan by the same query is

SQL> select a.id, a.val, b.str
  2  from    test1 a,
  3          test2 b
  4  where   a.id = b.id
  5  and     a.val = 40;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=1 Bytes=22)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (Cost=2 Card=1 Bytes=15)
   2    1     NESTED LOOPS (Cost=21 Card=1 Bytes=22)
   3    2       TABLE ACCESS (FULL) OF 'TEST1' (Cost=19 Card=1 Bytes=7)
   4    2       INDEX (RANGE SCAN) OF 'IND_TEST2' (NON-UNIQUE) (Cost=1 Card=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        183  consistent gets
        174  physical reads
          0  redo size
        489  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The comparison between both of the above plans is –

Plan when stats are gathered Cardinality of TEST1 table Consistent Gets
Only on indexed columns 1000 327
On all columns 1 183

Why such difference in both the execution plans?

Cardinality plays a very big role in opting an execution plan. In first case, as the stats are not available on VAL column of the table TEST1.

The cardinality when statistics are available only on indexed columns

The filter condition is on ID column of table TEST1 and as there are no stats available on this column, oracle will take the default selectivity as 1/100 and calculated cardinality as

num_rows*selectivity = 100000*(1/100) = 1000

As per the plan, the TEST1 is driving table and cardinality is very high and hence oracle opted the FTS for TEST2 table and cardinality is calculated as

num_rows = 50000

When stats are available on both the all column of the tables, for TEST1 table cardinality is calculated as

Num_rows*(“ID column selectivity” * “VAL column Selectivity)
= round (100000 * (1/3 * 1/100000))
= 0

As cardinality cannot be 0 it will consider as 1.

As the cardinality for TEST1 table is very low i.e. 1, TEST2 will be opted for index scan and hence cardinality is calculated as

Num_rows * (1/distinct) 
= round (50000 * (1/50000)) 
= 1

From above discussion it is concluded that the stats are necessary on all columns. In the above-mentioned test case, we have calculated the stats with histograms but the same result can be found without histograms, as the column stats are getting updated.

To sum up, the stats on all columns are required for optimal execution plan.

Note – This post was created long back and tested on 10.2.0.4 version. Since I have migrated some of the posts from my earlier blog site, the blog post date here is showing recent date. Over the period of time, optimizer behavior has changed in 12c version with new feature – adaptive query optimization. The test case shown above may not re-produce on Oracle versions greater than 10gR2.

Query Performance & Join Conditions

Oracle database performance is measured by application queries those are having impact on host resources. Oracle generates plan as per the input values and that decides the resource usage effeciency.

While working on performance issue, one of the problem reported by the user that one of the module is not functioning and having some issue with query. When we traced the session found one of the query doing FTS and retriving few records only. This was one of the top resource consumer query in the trace file.

The query was looking very normal and checked for the indexes, statistics of the tables etc. Though the statistics were collected recently, it was not the problem with that.

After investigation, it was found that one of the condition was not correctly defined and the same has been confirmed by developer. When you have multi table join in the query, we have to be very careful about equility predicates.

Here I want to remind one of the fact in oracle –
As per mathematics rules, when we say a=b and b=c, we can conclude a=c. But in oracle this is not the case. Oracle never concludes a=c.

To illustrate this, let us create a test case –

Create two tables and insert some random records in it. Also create required indexes. As the query provided was from the production, here I am not using the same table names. Also I have picked up only the problematic part of the query.

SQL> create table cu_all (custid number, addr varchar2(200), ph number, cano number, acctype varchar2(10));

Table created.

SQL> create table ca_receipt (custid number, caamt number, cadt date, totbal number);

Table created.

SQL> insert into cu_all
  2  select     lvl,
  3          dbms_random.string('A',30),
  4          round(dbms_random.value(1,100000)),
  5          round(dbms_random.value(1,10000)),
  6          dbms_random.string('A',10)
  7  from       (select level "LVL" from dual connect by level <=200000);

200000 rows created.

SQL> insert into ca_receipt
  2  select     round(dbms_random.value(1,10000)),
  3          round(dbms_random.value(1,100000)),
  4          sysdate - round(dbms_random.value(1,100000)),
  5          round(dbms_random.value(1,100000))
  6  from       (select level "LVL" from dual connect by level <=500000);

500000 rows created.

SQL> create unique index pk_cu_all_ind on cu_all(custid);

Index created.

SQL> create index ind2_cu_all on cu_all(CANO);

Index created.

SQL> create index ind_ca_receipt_custid on ca_receipt(custid);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'CU_ALL', cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'CA_RECEIPT', cascade=>true);

PL/SQL procedure successfully completed.

Now let us execute the query with trace on. This is the similar query which was provided to me.

SQL> set autot trace
SQL> SELECT     ca.*, cu.*
  2  FROM ca_receipt CA,
  3       cu_all CU
  4  WHERE       CA.CUSTID = CU.CUSTID
  5  AND         CA.CUSTID IN (SELECT CUSTID FROM cu_all START WITH custid = 2353
  6                CONNECT BY PRIOR CUSTID = CANO)
  7  ORDER BY ACCTYPE DESC;

289 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3186098611

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |  1000 | 81000 |   504   (2)| 00:00:07 |
|   1 |  SORT ORDER BY                      |                       |  1000 | 81000 |   504   (2)| 00:00:07 |
|*  2 |   HASH JOIN                         |                       |  1000 | 81000 |   503   (2)| 00:00:07 |
|   3 |    NESTED LOOPS                     |                       |       |       |            |          |
|   4 |     NESTED LOOPS                    |                       |  1000 | 26000 |   112   (1)| 00:00:02 |
|   5 |      VIEW                           | VW_NSO_1              |    20 |   100 |    21   (0)| 00:00:01 |
|   6 |       HASH UNIQUE                   |                       |    20 |   180 |            |          |
|*  7 |        CONNECT BY WITH FILTERING    |                       |       |       |            |          |
|   8 |         TABLE ACCESS BY INDEX ROWID | CU_ALL                |     1 |     9 |     2   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN          | PK_CU_ALL_IND         |     1 |       |     1   (0)| 00:00:01 |
|  10 |         NESTED LOOPS                |                       |       |       |            |          |
|  11 |          CONNECT BY PUMP            |                       |       |       |            |          |
|  12 |          TABLE ACCESS BY INDEX ROWID| CU_ALL                |    20 |   180 |    21   (0)| 00:00:01 |
|* 13 |           INDEX RANGE SCAN          | IND2_CU_ALL           |    20 |       |     1   (0)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN               | IND_CA_RECEIPT_CUSTID |    50 |       |     2   (0)| 00:00:01 |
|  15 |     TABLE ACCESS BY INDEX ROWID     | CA_RECEIPT            |    50 |  1050 |    52   (0)| 00:00:01 |
|  16 |    TABLE ACCESS FULL                | CU_ALL                |   200K|    10M|   389   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------

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

   2 - access("CA"."CUSTID"="CU"."CUSTID")
   7 - access("CANO"=PRIOR "CUSTID")
   9 - access("CUSTID"=2353)
  13 - access("CANO"=PRIOR "CUSTID")
  14 - access("CA"."CUSTID"="CUSTID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2249  consistent gets
         25  physical reads
          0  redo size
      11748  bytes sent via SQL*Net to client
        729  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
        289  rows processed

If you look at the query, it seems to be normal one.

But the problem is here-

Query is having two tables CA and CU. From the inner CU table query, it fetches records and joins with CA table an CA table Joins with CU table using the same column.
Here the inner query joins with CA table and cardinality of the query gets changed. So it is opting FTS when joining to CU table again.
This is causing the performance bottleneck. So to resolve the issue, I have change the joining condition.

Now if we check, following is the proper execution plan. Also the consistents gets have been reduced to 797 against 2249 in original query.

SQL> SELECT     ca.*, cu.*
  2  FROM ca_receipt CA,
  3       cu_all CU
  4  WHERE       CA.CUSTID = CU.CUSTID
  5  AND         CU.CUSTID IN (SELECT CUSTID FROM cu_all START WITH custid = 2353
  6                CONNECT BY PRIOR CUSTID = CANO)
  7  ORDER BY ACCTYPE DESC;

289 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3713271440

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |  1000 | 81000 |   133   (2)| 00:00:02 |
|   1 |  SORT ORDER BY                      |                       |  1000 | 81000 |   133   (2)| 00:00:02 |
|   2 |   NESTED LOOPS                      |                       |       |       |            |          |
|   3 |    NESTED LOOPS                     |                       |  1000 | 81000 |   132   (1)| 00:00:02 |
|   4 |     NESTED LOOPS                    |                       |    20 |  1200 |    42   (3)| 00:00:01 |
|   5 |      VIEW                           | VW_NSO_1              |    20 |   100 |    21   (0)| 00:00:01 |
|   6 |       HASH UNIQUE                   |                       |    20 |   180 |            |          |
|*  7 |        CONNECT BY WITH FILTERING    |                       |       |       |            |          |
|   8 |         TABLE ACCESS BY INDEX ROWID | CU_ALL                |     1 |     9 |     2   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN          | PK_CU_ALL_IND         |     1 |       |     1   (0)| 00:00:01 |
|  10 |         NESTED LOOPS                |                       |       |       |            |          |
|  11 |          CONNECT BY PUMP            |                       |       |       |            |          |
|  12 |          TABLE ACCESS BY INDEX ROWID| CU_ALL                |    20 |   180 |    21   (0)| 00:00:01 |
|* 13 |           INDEX RANGE SCAN          | IND2_CU_ALL           |    20 |       |     1   (0)| 00:00:01 |
|  14 |      TABLE ACCESS BY INDEX ROWID    | CU_ALL                |     1 |    55 |     1   (0)| 00:00:01 |
|* 15 |       INDEX UNIQUE SCAN             | PK_CU_ALL_IND         |     1 |       |     0   (0)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN                | IND_CA_RECEIPT_CUSTID |    50 |       |     2   (0)| 00:00:01 |
|  17 |    TABLE ACCESS BY INDEX ROWID      | CA_RECEIPT            |    50 |  1050 |    52   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

   7 - access("CANO"=PRIOR "CUSTID")
   9 - access("CUSTID"=2353)
  13 - access("CANO"=PRIOR "CUSTID")
  15 - access("CU"."CUSTID"="CUSTID")
  16 - access("CA"."CUSTID"="CU"."CUSTID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        797  consistent gets
          1  physical reads
          0  redo size
      11748  bytes sent via SQL*Net to client
        729  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
        289  rows processed

Hence to sum up, oracle gives us the output based on our input values/conditions. Any query must have proper joining condition when multiple tables are involved.

Index Internals.

Tables can grow large, and when they do, it becomes difficult for users to quickly find the data they need. Oracle offers indexes as a method of speeding database performance when accessing tables with a lot of data. The b-tree index is the traditional and most useful indexing mechanism. It stores the data in a tree like structure.

The b-tree index structure is made up of root block, branch blocks and leaf blocks. The root block is an entry point where search for data in index starts. Any index contains only and only one root block. The root block is always physically the same block. The branch blocks are next level of the root block that is having pointers to leaf blocks in the index.

The leaf blocks are the highest level of the index, which contain indexed column values and the corresponding ROWIDs. Each leaf block is comprised of double-linked list structure. It means each leaf block is linked to the other block on its left and right, in order to make it possible to search in both the directions through a range of values in index. The index entries are always in ordered.

The oracle’s index is always maintains the balanced structure. To understand this, it is necessary to understand block split operation in index. There are two ways of block splits

50-50 BLOCK SPLIT

The 50-50-block split can occur when there is an insert operation of a non-maximum value and when the corresponding block is full. The indexed column update operation for an index is internally delete followed by an insert. The split operation steps are as follows –

1. Request for new block from free-list/bitmap structure (Depending on non-ASSM and ASSM tablespace option)
2. Distribute existing block so that upper half volume of an index move to the new requested block
3. Insert the column value in appropriate block.
4. Update the leaf block pointers such that previously full block right pointer will point to the new block and new block’s right pointer will point to the right pointed block of previously full block.
5. Finally update the branch block to reference previous full block and add a new entry for to point to new leaf block.

The similar kind of operation is applicable to the branch and root block split. Even branch and root block split is more expensive as it involves corresponding next level pointer updations. Root block split allocate two new blocks wherein data is evenly distributed and root block is updated such that it will now point to these new blocks. So root block will always physically the same block. The root block split can increase the height of the index by 1.

90-10 BLOCK SPLIT

The 90-10 block split can occur, when the new indexed column entry is the maximum value. In this case, new block will be requested and corresponding branch blocks are updated accordingly.

Can deleted space of an index be reused?

There are multiple answers to this question –
1. Index will never use deleted space.
2. Index will use deleted space if the same column value is inserted again.

But in reality, both the above statements are myth. Index will use deleted space even when the new inserted value is not same.

Test Case –

To validate above statemenet, we will create the test table and insert some records into it. Here temp table contains the serial values.

SQL> create table temp as select rownum "A" from dba_objects a, dba_objects;

Table created.

SQL> create table test (a number, b number);

Table created.

SQL> create index ind_test on test(a);

Index created.

SQL> insert into test select a, a+50000 from temp where a >10000 and a<=20000; 

10000 rows created. 

SQL> commit;

Commit complete.

SQL> analyze index ind_test validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows, used_space
  2  from index_stats where name='IND_TEST';

NAME                              LF_ROWS DEL_LF_ROWS USED_SPACE
------------------------------ ---------- ----------- ----------
IND_TEST                            10000           0     160127

As LF_ROWS column shows, there are 10000 records present in the index. Now check the values after deletion of some records.

SQL> delete from test where a > 14000 and a <= 16000; 

2000 rows deleted. 

SQL> commit;

Commit complete.

SQL> analyze index ind_test validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows, used_space
  2  from index_stats where name='IND_TEST';

NAME                              LF_ROWS DEL_LF_ROWS USED_SPACE
------------------------------ ---------- ----------- ----------
IND_TEST                            10000        2000     160127

SQL> select count(*) from test;

COUNT(*)
 ----------
 8000

Here the number of rows (LF_ROWS – DEL_LF_ROWS) = (10000-2000) = 8000 which matches the count of the test table.

To check whether index uses the deleted space, we will add new records which are not same as the current or deleted rows.

SQL> insert into test select a, a+50000 from temp where a > 20000 and a<=22000; 

2000 rows created. 

SQL> commit;

Commit complete.

SQL> analyze index ind_test validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows, used_space
  2  from index_stats where name='IND_TEST';

NAME                              LF_ROWS DEL_LF_ROWS USED_SPACE
------------------------------ ---------- ----------- ----------
IND_TEST                            10500         500     168145

SQL> select count(1) from test;

COUNT(1)
 ----------
 10000

The total added rows are 2000. As the del_lf_rows column shows, there are only 500 deleted records found. Which means, out of 2000 deleted records, the index has used deleted space of 1500 records. Now check the status after insertion of the some previously deleted records. We had deleted records between 14000 and 16000 values, and now will insert any of these 500 values.

SQL> insert into test select a, a+1000 From temp where a > 14500 and a <= 15500 and mod(a,2)=0; 

500 rows created. 

SQL> commit;

Commit complete.

SQL> analyze index ind_test validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows, used_space from index_stats where name='IND_TEST';

SQL> select name, lf_rows, del_lf_rows, used_space
  2  from index_stats where name='IND_TEST';

NAME                              LF_ROWS DEL_LF_ROWS USED_SPACE
------------------------------ ---------- ----------- ----------
IND_TEST                            10500           0     168141

The above statistics shows that index will always use the deleted space.

Conclusion –

1. Rows deletions will either make the blocks empty or some space in block.
2. The empty blocks can be used for any rows irrespective of previous deleted column values.
3. If there is space available in existing blocks, space can be used if it satifies the column value between lower and upper existing values in that block.

In many cases, it has also been observed that DBA’s rebuild indexes to reclaim deleted space, but the test case above shows that the deleted space is reclaimed and hence does not require any rebuilding. Hence, if this is one of the primary reason to rebuild the indexes, then, dba’s now can wonder whether do they really require index rebuilding?