Query Optimization ! An example of a better rewrite…

In January 2012, I wrote about a Query Performance Issue due to NVL(:b1,column_name) predicates in the WHERE clause. Due to these predicates, the Optimizer computed Incorrect Cardinality and came out with a Sub-Optimal Plan. In Sangam 2012, I demostrated an issue because of these predicates. This blog is again on one of the issue that a customer reported and was solved by way of a workaround. Since, this query is a seeded query, the permanent fix (suggested in this blog) is expected by way of an Application Patch.

I will demonstrate this on my SCOTT Schema and a Query on EMP table. This will be easier for me to explain as well. Based on the EMP table, the requirement is to write a report that takes 2 Inputs. These are EMPNO and DEPTNO. The users can run this report for any of the following conditions :

1. EMPNO and DEPTNO are NOT NULL
2. EMPNO IS NULL and DEPTNO IS NOT NULL
3. EMPNO IS NOT NULL AND DEPTNO IS NULL
4. EMPNO and DEPTNO are both NULL

The way Original Query is written, I assumed the Developers had above 4 requirements in mind. However, at the Customer place, the customer confirmed that only the 1st two conditions are applicable. Out of the total execution, 1st condition contributes to around 70% and 2nd contributes to 30%.

Back to our example on EMP table. With the 4 conditions in mind, any Developer would write a query as mentioned below.

select empno, ename, deptno, sal
from	emp
where	deptno = nvl(:deptno,deptno)
and	empno = nvl(:empno,empno)
order by empno;

## Execution of this query for each of the combination
## Both are NOT NULL (For a Deptno & for an Employee)
exec :deptno:=30; :empno:=7499; 

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7499 ALLEN              30       1600

## EMPNO IS NULL (For a Deptno and all Employees)
exec :deptno:=30; :empno:=null;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7499 ALLEN              30       1600
      7521 WARD               30       1250
      7654 MARTIN             30       1250
      7698 BLAKE              30       2850
      7844 TURNER             30       1500
      7900 JAMES              30        950

## DEPTNO IS NULL (For an Employee)
exec :deptno:=null; :empno:=7499;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7499 ALLEN              30       1600

## Both are NULL (for all Deptno and all Employees)
exec :deptno:=null; :empno:=null;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7369 SMITH              20        800
      7499 ALLEN              30       1600
      7521 WARD               30       1250
      7566 JONES              20       2975
      7654 MARTIN             30       1250
      7698 BLAKE              30       2850
      7782 CLARK              10       2450
      7788 SCOTT              20       3000
      7839 KING               10       5000
      7844 TURNER             30       1500
      7876 ADAMS              20       1100
      7900 JAMES              30        950
      7902 FORD               20       3000
      7934 MILLER             10       1300

A single query meets the requirement for all the 4 combinations. The Developer, in this case, has done his job. However, they have not considered the fact that 2 out of 4 of the above combinations would end up doing a Full Table Scan of EMP table. In case of the customer case, since only the 1st two combinations are applicable and with 30% of the executions on combination 2, 30% of the time, the Optimizer would opt for a Full Table Scan. Before, we get into the Original case, let us check the runtime execution plan for the Query on EMP Table.

select empno, ename, deptno, sal from emp where deptno =
nvl(:deptno,deptno) and empno = nvl(:empno,empno) order by empno

Plan hash value: 3923409422

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |       |       |     6 (100)|          |
|   1 |  SORT ORDER BY                 |        |     7 |   322 |     6  (17)| 00:00:01 |
|   2 |   CONCATENATION                |        |       |       |            |          |
|*  3 |    FILTER                      |        |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | EMP    |     6 |   276 |     3   (0)| 00:00:01 |
|*  5 |    FILTER                      |        |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    46 |     2   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - filter(:EMPNO IS NULL)
   4 - filter(("EMPNO" IS NOT NULL AND "DEPTNO"=NVL(:DEPTNO,"DEPTNO")))
   5 - filter(:EMPNO IS NOT NULL)
   6 - filter("DEPTNO"=NVL(:DEPTNO,"DEPTNO"))
   7 - access("EMPNO"=:EMPNO)

From the predicate information, the Optimizer choice becomes very clear, which is “IF :EMPNO is NULL then FTS of EMP and IF :EMPNO is NOT NULL then Table Access by Index Rowid and a Unique Scan of PK_EMP”. This means, if a User Inputs the value for an EMPNO (irrespective of what has been passed for DEPNO), then the Optimizer will always do an Index Scan.

Now the Original Case
The Original Query and the Plan is pasted below, before we discuss on this.

SELECT TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	SHIPMENT_LINE_ID = NVL(:B2 , SHIPMENT_LINE_ID) 
AND 	TRANSACTION_ID = NVL(:B1 , TRANSACTION_ID) 
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) 
ORDER BY TRANSACTION_ID;

Plan hash value: 4081860556

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |       |       |   621K(100)|          |
|   1 |  SORT ORDER BY                 |                         |     2 |    94 |   621K (11)| 00:02:24 |
|   2 |   CONCATENATION                |                         |       |       |            |          |
|*  3 |    FILTER                      |                         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | JAI_RCV_TRANSACTIONS    |     1 |    47 |   621K (11)| 00:02:24 |
|*  5 |    FILTER                      |                         |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| JAI_RCV_TRANSACTIONS    |     1 |    47 |     3   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | JAI_RCV_TRANSACTIONS_PK |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   3 - filter(:B1 IS NULL)
   4 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND "SHIPMENT_LINE_ID"=NVL(:B2,"SHIPMENT_LINE_ID")
              AND "TRANSACTION_ID" IS NOT NULL))
   5 - filter(:B1 IS NOT NULL)
   6 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND
              "SHIPMENT_LINE_ID"=NVL(:B2,"SHIPMENT_LINE_ID")))
   7 - access("TRANSACTION_ID"=:B1)

As mentioned earlier, in case of the Customer case, only the 1st two combinations are applicable. These are (columns from Original Query) :

1. TRANSACTION_ID and SHIPMENT_LINE_ID are NOT NULL
2. TRANSACTION_ID IS NULL and SHIPMENT_LINE_ID IS NOT NULL

There is a Unique Index on TRANSACTION_ID and another Unique Index on (SHIPMENT_LINE_ID,TRANSACTION_ID). This query is a part of a Transaction and for the cases, where the value to :B1 (Transaction_Id) is null, the transaction takes around 20 minutes to complete. The table has around 9 Million Rows and does a Full Table Scan for such cases.

Since the customer mentioned that in their case, value for TRANSACTION_ID column can only be passed as NULL, an option of changing “:B1 is NULL” to “:B2 is NULL” (see PREDICATE INFORMATION) seemed to be a better choice. Remember, :B1 is for TRANSACTION_ID and :B2 is for SHIPMENT_LINE_ID. This change will check, if :B2 is NULL then FTS on the Table and if :B2 is NOT NULL then an Index Scan on SHIPMENT_LINE_ID. The Uniqueness of TRANSACTION_ID is more than SHIPMENT_LINE_ID, and therefore, optimizer picks TRANSACTION_ID as more cheaper and opts for an Index Scan on this column. Below are the column statistics for the two columns.

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
SHIPMENT_LINE_ID                    4351257          0 2.2982E-07 NONE
TRANSACTION_ID                      9092033          0 1.0999E-07 NONE

As a temporary workaround, the column statistics for the two columns were swapped. Before doing that, it was critically important to actually check the number of queries with TRANSACTION_ID column in the WHERE predicate. This is ensure that this temporary fix should not impact other queries. Once it was confirmed that this change will not impact, the column stats were swapped. This swapping was intended to see below mentioned change in the plan :

Original – if :B1 is NULL then FTS. If :B1 is NOT NULL then Unique Index Scan on TRANSACTION_ID
Changed – if :B2 is NULL then FTS. If :B2 is NOT NULL then Range Scan on SHIPMENT_LINE_ID

Since the value to :B2 is never passed as NULL, this change will also make sure this 9 Million Row table is never Full Scanned. The swapping of statistics worked and the plan changed for better. Some of the transaction that took around 20 Minutes started completing in a second.

exec dbms_stats.set_column_stats(ownname=>'JA',tabname=>'JAI_RCV_TRANSACTIONS',colname=>'TRANSACTION_ID',DISTCNT=>4351257);
exec dbms_stats.set_column_stats(ownname=>'JA',tabname=>'JAI_RCV_TRANSACTIONS',colname=>'SHIPMENT_LINE_ID',DISTCNT=>9092033);

Plan hash value: 3580471929

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |       |       |   621K(100)|          |
|   1 |  SORT ORDER BY                 |                           |     2 |    94 |   621K (11)| 00:02:24 |
|   2 |   CONCATENATION                |                           |       |       |            |          |
|*  3 |    FILTER                      |                           |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | JAI_RCV_TRANSACTIONS      |     1 |    47 |   621K (11)| 00:02:24 |
|*  5 |    FILTER                      |                           |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| JAI_RCV_TRANSACTIONS      |     1 |    47 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | JA_IN_RCV_TRANSACTIONS_N5 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - filter(:B2 IS NULL)
   4 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND "TRANSACTION_ID"=NVL(:B1,"TRANSACTION_ID")
              "SHIPMENT_LINE_ID" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND "TRANSACTION_ID"=NVL(:B1,"TRANSACTION_ID")
   7 - access("SHIPMENT_LINE_ID"=:B2)

While this was a temporary workaround to fix the problem, a better strategy would have been to write a query that take the advantage of the available indexes and works optimally for any of the 4 combinations mentioned above. A better query is as under :

select * from (
SELECT /*+ WITH BOTH BINDS AS NOT NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	SHIPMENT_LINE_ID = :B2
AND 	TRANSACTION_ID = :B1
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) 
union all
SELECT /*+ WITH B1 AS NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	SHIPMENT_LINE_ID = :B2 AND :B1 is NULL
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) 
union all
SELECT /*+ WITH B2 AS NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	TRANSACTION_ID = :B1 AND :B2 IS NULL
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) )
UNION ALL
SELECT /*+ WITH BOTH BINDS AS NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	:B1 IS NULL AND :B2 IS NULL
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) )
ORDER BY TRANSACTION_ID;

The benefit of this modified query is :<

1. If both BINDS are NOT NULL, use a Composite Unique Index on SHIPMENT_LINE_ID, TRANSACTION_ID
2. If BIND :B1 on TRANSACTION_ID is NULL then a Range Scan on SHIPMENT_LINE_ID
3. If Bind :B2 on SHIPMENT_LINE_ID is NULL then a Unique Scan on TRANSACTION_ID
4. If Both Binds are NULL, then a Full Table Scan (this is unavoidable).

dbms_parallel_execute ! still unused feature of Oracle Database 11g

I still find many developers unaware of dbms_parallel_execute, which is a better and efficient way of distributing the data across multiple chunks. Recently, I came across one such implementation, where the Developers introduced their own way of creating multiple chunks using ROWNUM pseudo column and then distributing these records into required streams. In this case, the Developers intended to spawn 64 Streams. Further, they created an Index on the some of the columns to Update the records that were processed. The drawback of this implementation was :

1. Block Level Contention for Tables, as each Streams can request and process Blocks from other Streams
2. Index Block Contention, as multiple Streams would update same Block. A Better way would be to select ROWID and UPDATE based on ROWID.

This Implemention required around 50 Million Rows to be processed in parallel streams of 64. Due to the issues mentioned above, the process took abnormal time and was kept on hold for production movement. Our advice was sought on the Optimization and we could see that the Developers still unware of a fantastic and readymade approach, called dbms_parallel_execute. I had one of my previous Blog on the same topic and wanted them to go through it for some examples. We also explained them the benefit of this approach over their current implementation. They readily accepted this and thought of giving it a try before moving it on production. I was expecting some queries from the Developers, as this was an overall new concept for them and Yes, the first concern raised was over the CHUNK_SIZE=>64 value in dbms_parallel_execute.create_chunks_by_rowid proceedure. The Developers expected this procedure to create 64 Chunks of the entire table, which can then be processed by 64 parallel jobs. In this case, since the table was huge, it created around 5000+ Chunks. This was a bit surprising for them. Further, even if it created more number of chunks, the Challenge here was building of an additional logic to run only 64 Streams at any given point in time. Further, the logic should also take care of the finished jobs and to spawn a new one automatically.

First, let us address and explain the CHUNK_SIZE argument of create_chunks_by_rowid procedure. This value works with BY_ROWS argument. If BY_ROWS is specified as TRUE, the number of chunks are created based on the approximate number of rows divided into each stream. For example, in case of CHUNK_SIZE=>64 and BY_ROWS=>TRUE, each stream will have approximately 64 or less rows. With CHUNK_SIZE=>64 and BY_ROWS=>FALSE, the division is based on BLOCKS, with each stream containing approximately 64 or less blocks. Further, each stream will not span across multiple extents and therefore, if a table has smaller extents, you may see a high count of CHUNKS created (as 5000+ in the case of above mentioned example).

The Start & End ROWID of each of the Chunks are stored in user_parallel_execute_chunks view. Since this is a SYS Object and we do not have much control over this. I usually create my own table, where I Insert the data from USER_PARALLEL_EXECUTE_CHUNKS. Once the data into our own table is Inserted, it is easier to update our table with additional information required for analysis. Let us walk through a simple example.

## Create our own table to track the status of each job.

create table process_status (
process_name            varchar2(30),
job_id                  number,
s_rowid                 rowid,
e_rowid                 rowid,
start_time              date,
end_time                date,
processed_rows          number,
completed_yn            varchar2(1),
status                  varchar2(1000));

## Create a Parallel Job Task
exec  dbms_parallel_execute.create_task('VIVEK_JOB');

## Create Chunks, with the approximate Blocks / Rows (if BY_ROWS=>FALSE, it will distribute on Blocks else on Rows)
exec  dbms_parallel_execute.create_chunks_by_rowid('VIVEK_JOB','FLEX','CH_OD_AGREEMENT',false, 100);

## Insert the Chunks in our process_status table
insert into process_status(process_name, job_id, s_rowid, e_rowid)
select 'VIVEK_JOB', chunk_id, start_rowid, end_rowid from user_parallel_execute_chunks
where task_name='VIVEK_JOB' order by 1;
commit;

## Our Procedure that will run in parallel. We need to pass start_rowid and end_rowid as the Input parameters

create or replace procedure FLEX.vivek_proc (st_rowid in rowid, ed_rowid in rowid) as
l_cnt      number;
begin
update FLEX.process_status set start_time=sysdate, status='RUNNING' 
 where s_rowid=st_rowid;  
 commit;  
 select count(*) into l_cnt from FLEX.CH_OD_AGREEMENT 
 where rowid between st_rowid and ed_rowid;  
 update FLEX.process_status set status='COMPLETED', end_time=sysdate, processed_rows=l_cnt 
 where s_rowid=st_rowid;  
 commit; 
end; 
/

VIVEK_PROC is the procedure that is required to be run in parallel streams. The Input Parameters are START & END ROWID. I have also modified the procedure to update the PROCESS_STATUS table, with the start_time, status and at the completion of the main business logic, the end_time, Processed_rows and status. Further this data can be maintained as a History (by inserting into history table before the next run). Due to this flexibility, I choose creation of PROCESS_STATUS table.

The total number of chunks created by dbms_parallel_execute for my test table was around 3500 and I wanted to spawn only 64 streams. Many Developers chose restricting job_queue_processes to 64 and schedule all the 3500 jobs together. Restricting job_queue_processes to 64 will ensure only 64 jobs running. This is achievable, but would be a very crude way of doing so. Further, this would only work if there are no other jobs scheduled during this time, else it will also be queued. A best option is again using dbms_parallel_execute. There is a procedure called RUN_TASK and an argument PARALLEL_LEVEL limits the number of parallel jobs to be run at any given point of time. No additional logic is required to check for the completion of a job and scheduling of a new job. Oracle will do it for us.

## Run the Task, with number of parallel job queue processes. This is controlled using PARALLEL_LEVEL

begin
      dbms_parallel_execute.run_task
      ( task_name      => 'VIVEK_JOB',
        sql_stmt       => 'begin FLEX.vivek_proc( :start_id, :end_id ); end;',
        language_flag  => DBMS_SQL.NATIVE,
        parallel_level => 64 );
      commit;
end;
/
## Once the Jobs are done, drop the Job Task
exec dbms_parallel_execute.drop_task('VIVEK_JOB');

DROP_TASK will remove the entries from USER_PARALLEL_EXECUTE_CHUNKS table.

OTN Yathra ! A Six City Tour

OTN Yathra starts from 16th February 2013. This is a collaborative effort between OTN & AIOUG. This is a 6 City Tour, the first destination being Delhi. I am presenting in Delhi, Mumbai and Hyderabad. I am excited to present my first ever presentation in Delhi. For the details and registration, please visit :

http://aioug.org

Regards
Vivek

OEM 12c ! A must have book.

My friend and former ACE Director Porus Homi Havewala’s new book on
Oracle Enterprise Manager 12c Cloud Control, is now available, this is
the First published EM 12c Cloud Control book in the world.

OEM 12c simplifies many of the administrative tasks and also is helpful in monitoring the critical aspect of a Production system. The book should help you in understanding and learning about the capabilities of Enterprise Manager 12c. Electronic copies are available too.

http://www.packtpub.com/oracle-enterprise-manager-12c-cloud-control/book

Oracle Enterprise Manager 12c Cloud Control: Managing Data Center Chaos

Intra Block Chaining ! Impact on Logical Reads

I presented this during Sangam 12 (User Group Conference) and many participants wanted me to post a Blog on this. It is important to know the impact of Intra Block Chaining, which occurs, if the number of columns in a table exceed 255. If a table has more than 255 columns, the row pieces are stored in multiple row pieces of 255 each. Now, whether it stores columns 1 to 255 in one Row Piece and 256 to the remaining columns in next row piece, is an interesting fact that everyone wants to know. Through this blog, I will try to demonstrate and answer following facts :

1. If a table consists of 300 Columns, which all columns will constitute the 2 row pieces ? i.e. Row Piece 1 : 1-255 Columns and Row Piece 2 : 256-300 Columns, or will it be something else ?
2. What will be the performance impact of Intra Block Chaining ?

The reason, I picked this as one of the topic that the Developers and the DBA’s should know, is based on a Real Life Example. One of my customer complained of a Severe Performance issues, post applying an Application Patch. This patch was to introduce certain new functionality for their end-users. Further, this functionality change modified many of the critical tables used by the application. This change was : introduction of new columns. Few tables, which had less than 255 columns, were altered and new columns were added, which grew these numbers to more than 255. This was enough to cause the performance issues, which went unnoticed, during a test run, which was with done with less user load. On production, the user load was way high, and therefore, any increase in the I/O was capable enough to increase the CPU Utilization.

Let us start with a Demonstration, which is an easiest way to understand the facts. We will first create a table, with required number of columns. First, a table with 300 Columns and then I will insert single row into this table. Following two pl/sql blocks uses a Dynamic SQL to accomplish the task.

variable b1 number;
exec :b1:=&number_of_columns; -- Pass the Number of Columns to be required in a Table 

-- Dynamically Create Intra_Block table with required number of rows. This block constructs the CREATE TABLE Statement.

declare
l_statement	long:='create table intra_block (';
begin
  for i in 1..:b1
  loop
    if i<:b1 then
    l_statement:=l_statement||' A'||i||' number(3),';
    else
    l_statement:=l_statement||' A'||i||' number(3))';
    end if;
  end loop;
  execute immediate l_statement;
end;
/

-- Insert Single Row into the table. This block constructs the INSERT Statement.

declare
l_statement	long:='insert into intra_block(';
begin
   for i in 1..:b1
   loop
     if i<:b1 then
     l_statement:=l_statement||'A'||i||',';
     else
     l_statement:=l_statement||'A'||i||') values(';
     end if;
   end loop;
   for i in 1..:b1
   loop
     if i<:b1 then
     l_statement:=l_statement||i||',';
     else
     l_statement:=l_statement||i||')';
     end if;
   end loop;
   execute immediate l_statement;   
   commit;
end;
/
-- Gather Statistics
exec dbms_stats.gather_table_stats(user,'INTRA_BLOCK');

select table_name, blocks, num_rows from dba_tables where table_name='INTRA_BLOCK';

OWNER           PAR   NUM_ROWS     BLOCKS
--------------- --- ---------- ----------
VIVEK           NO           1          5

I created this table with 300 columns and inserted one row. DBA_Tables shows 5 blocks in the table. Next, the following queries show that the entire 300 columns fit into 1 block, block#182 in my case.

select dbms_rowid.rowid_block_number(rowid) bno, count(*) from intra_block group by dbms_rowid.rowid_block_number(rowid);

       BNO   COUNT(*)
---------- ----------
       182          1

Next, we shall execute a Query against the first column, which is A1, and then on the last column, which is A300. We will compare the I/O’s done by each of these queries.

set autot on stat

select a1 from intra_block;

        A1
----------
         1

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets <--- 7 Logical I/O's

select A300 from intra_block;

      A300
----------
       300

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets <-- One Additional Logical I/O's

Query on the last column (for a table with more than 255 columns) incurred one Additional Logical I/O. Let us now check, row piece structure. We now know that with 300 columns, the Rows will be inserted into 2 Row Pieces. One Row Piece will incur less I/O as compared to the another Row Piece. I want to check the columns in Row Piece 1 and Columns in Row Piece 2, which will help us know, which column onwards the query will start incurring additional I/O. Following pl/sql executes a query on each of the columns and we shall check for the I/O’s (from buffer_gets column of v$sqlarea).

 declare
l_statement varchar2(132);
l_value number;
begin
  for i in 1..:b1
  loop
    l_statement:='select /*+ intra_'||lpad(i,3,0)||' */ A'||i||' from intra_block';
    execute immediate l_statement into l_value;
  end loop;
end;
/

select sql_id, sql_text, buffer_gets, executions, rows_processed
from v$sqlarea where sql_text like 'select /*+ intra%'
order by to_number(substr(sql_text,instr(sql_text,'i',1)+6,3));

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS ROWS_PROCESSED
------------- -------------------------------------------------- ----------- ---------- --------------
47u9afvh40vqf select /*+ intra_001 */ A1 from intra_block                  7          1              1
cvjvxgnfz7h78 select /*+ intra_002 */ A2 from intra_block                  7          1              1
cbdajswujwnyf select /*+ intra_003 */ A3 from intra_block                  7          1              1
8g18r5syanmbx select /*+ intra_004 */ A4 from intra_block                  7          1              1
d5kyqtgd2d4q3 select /*+ intra_005 */ A5 from intra_block                  7          1              1
c0yvymb9p7gj0 select /*+ intra_006 */ A6 from intra_block                  7          1              1
.....
..... (some entries trimmed)
.....
f1rrwb22u89gf select /*+ intra_029 */ A29 from intra_block                 7          1              1
1556fp9466r4x select /*+ intra_030 */ A30 from intra_block                 7          1              1
3sk2zpnyqanmc select /*+ intra_031 */ A31 from intra_block                 7          1              1
asp7vk16b07sb select /*+ intra_032 */ A32 from intra_block                 7          1              1
9ts2g9w5dra0q select /*+ intra_033 */ A33 from intra_block                 7          1              1
c9dnc235v6w00 select /*+ intra_034 */ A34 from intra_block                 7          1              1
a532dxfj0d0w6 select /*+ intra_035 */ A35 from intra_block                 7          1              1
1p2yzsxax20qm select /*+ intra_036 */ A36 from intra_block                 7          1              1
6zb16tkbyrbhm select /*+ intra_037 */ A37 from intra_block                 7          1              1
38zqdzgvvg70w select /*+ intra_038 */ A38 from intra_block                 7          1              1
51f39r5tnw73d select /*+ intra_039 */ A39 from intra_block                 7          1              1
f1fywzj4avnz0 select /*+ intra_040 */ A40 from intra_block                 7          1              1
46015j3s4trsk select /*+ intra_041 */ A41 from intra_block                 7          1              1
1sdrag4sxbcjh select /*+ intra_042 */ A42 from intra_block                 7          1              1
a0t2nrpb8r83s select /*+ intra_043 */ A43 from intra_block                 7          1              1
aj2w0gvj5zy1g select /*+ intra_044 */ A44 from intra_block                 7          1              1
6nk7x7430k9uk select /*+ intra_045 */ A45 from intra_block                 7          1              1
ahdr7bqsm18x8 select /*+ intra_046 */ A46 from intra_block                 8          1              1
1vsus8qg2rsft select /*+ intra_047 */ A47 from intra_block                 8          1              1
86njp3z8adan3 select /*+ intra_048 */ A48 from intra_block                 8          1              1
6v6u6hp71vtwb select /*+ intra_049 */ A49 from intra_block                 8          1              1
7a9a7gbyhrvkr select /*+ intra_050 */ A50 from intra_block                 8          1              1
8wp83m0fn4kgw select /*+ intra_051 */ A51 from intra_block                 8          1              1
7q464wb184tpu select /*+ intra_052 */ A52 from intra_block                 8          1              1
.....
..... (some entries trimmed)
.....
9dmv986638rk4 select /*+ intra_235 */ A235 from intra_block                8          1              1
d2ks041mqfv1b select /*+ intra_236 */ A236 from intra_block                8          1              1
b0muufu2qqs7y select /*+ intra_237 */ A237 from intra_block                8          1              1
fbd5x0z45udvr select /*+ intra_238 */ A238 from intra_block                8          1              1
c31yuufwr2wzc select /*+ intra_239 */ A239 from intra_block                8          1              1
2aczq7gsf0680 select /*+ intra_240 */ A240 from intra_block                8          1              1
7us824yhm9c4s select /*+ intra_241 */ A241 from intra_block                8          1              1
c26kyzhmm1ad1 select /*+ intra_242 */ A242 from intra_block                8          1              1
dqrp4n7a5uzjp select /*+ intra_243 */ A243 from intra_block                8          1              1
4wzw76dh7sa6h select /*+ intra_244 */ A244 from intra_block                8          1              1
b4qwud4k9j9qg select /*+ intra_245 */ A245 from intra_block                8          1              1
6cyzxr6c7t4km select /*+ intra_246 */ A246 from intra_block                8          1              1
7u8krqjy6aq0k select /*+ intra_247 */ A247 from intra_block                8          1              1
8sxmn5ukxzqad select /*+ intra_248 */ A248 from intra_block                8          1              1
a66frsvy8q8vq select /*+ intra_249 */ A249 from intra_block                8          1              1
9htnkqmfchypc select /*+ intra_250 */ A250 from intra_block                8          1              1
058f3hvkf2y6m select /*+ intra_251 */ A251 from intra_block                8          1              1
1m9wfv39zmn6v select /*+ intra_252 */ A252 from intra_block                8          1              1
23mmdngutgbc6 select /*+ intra_253 */ A253 from intra_block                8          1              1
bnxt1ujafku6s select /*+ intra_254 */ A254 from intra_block                8          1              1
a4gv26ujgjhjv select /*+ intra_255 */ A255 from intra_block                8          1              1
draytdg79hc5c select /*+ intra_256 */ A256 from intra_block                8          1              1
4gr6ntv7zx764 select /*+ intra_257 */ A257 from intra_block                8          1              1
csfz96fkfz2vj select /*+ intra_258 */ A258 from intra_block                8          1              1
1v4sjc8740kdc select /*+ intra_259 */ A259 from intra_block                8          1              1
b38nmwf9bnfv0 select /*+ intra_260 */ A260 from intra_block                8          1              1
b52d8sd512zpr select /*+ intra_261 */ A261 from intra_block                8          1              1
6s8963hfnpt9b select /*+ intra_262 */ A262 from intra_block                8          1              1
5h19qru6dsudz select /*+ intra_263 */ A263 from intra_block                8          1              1
fpnvs4ry55qqg select /*+ intra_264 */ A264 from intra_block                8          1              1
2us1f52hmqz4j select /*+ intra_265 */ A265 from intra_block                8          1              1
g7xbq7nvg119d select /*+ intra_266 */ A266 from intra_block                8          1              1
78z5hjw7nam1n select /*+ intra_267 */ A267 from intra_block                8          1              1
82w5nq265jyfp select /*+ intra_268 */ A268 from intra_block                8          1              1
fh0hnjydm2unq select /*+ intra_269 */ A269 from intra_block                8          1              1
39tqqf1p9jm1s select /*+ intra_270 */ A270 from intra_block                8          1              1
cn4np8vkgk4mc select /*+ intra_271 */ A271 from intra_block                8          1              1
.....
..... (some entries trimmed)
.....
19m0kx9w80afs select /*+ intra_296 */ A296 from intra_block                8          1              1
1q9kg224txpqu select /*+ intra_297 */ A297 from intra_block                8          1              1
36dya8xmc8a0a select /*+ intra_298 */ A298 from intra_block                8          1              1
abn6s5jr2bac3 select /*+ intra_299 */ A299 from intra_block                8          1              1
grnbn11qw3xjk select /*+ intra_300 */ A300 from intra_block                8          1              1

300 rows selected.

From the output above, it can be seen that for 300 Columns, the I/O’s increased from 46th Column (A46), which is 300-255=45. This means, columns A300-A46 (255 columns) are together stored in One Row Piece, whereas, A45-A1 are stored in another Row Piece. This information was a bit interesting. Initially I thought, any query that queries columns A256 and above, would incur additional I/O, but the rows are stored backward and 255 limit also starts from back i.e.last column onwards.

For curiosity, I ran the entire test again for 256 columns. The table was dropped and recreated to have A1..A256 columns. With this, I could see that the I/O’s increased by 1 from column A2 onwards (256-255=1). Again, Columns A2..A256 were stored in one Row Piece and A1 in another. This also means, if you have a table with 255 columns, you add one column and would immediately see I/O’s go up by 1 for any column starting 2nd Column.

You may run this for any number of columns, and should see the same results. The third test I did was on a 600 Column Table. The I/O’s were as under :

Columns A1..A90 --- 7 I/O's
Columns A91..A345 --- 8 I/O's
Columns A346 onwards --- 9 I/O's 

600-255=345
345-255=90

This test proves that the Table Design requires careful planning. There is hardly any reason, why a Table needs to be created with more than 255 columns. However, I have seen many applications that create tables with more number of columns. This blog should be an eye opener for them. Any additional (unwanted) I/O’s that are saved will have dramatic impact on Application performance. This will improve the Scalability of the Application.

Update on Sangam 12

Resumed work after attending Sangam 12 in Bangalore. The event was a great success and this time, the crowd was more as compared to last year. This is a good news for Oracle Community in India, as more and more people are now taking interest. The entire first day was full of entertainment and technical stuff from Real World Performance Team. It was nice to see Andrew, Tom Kyte and Graham Wood together at the Podium sharing their experiences, answering to participants queries.

This year, I received a “Speaker of the Year” Award and am very much delighted to see the love and affection from my All India Users. I presented on 3rd November on “Ensuring Optimal Performance”. Every Session that I present is a new learning experience for me as well. It requires extensive study and an in-depth knowlegde of the subject. Apart from the technical knowledge, it requires proper Time-Management skill, which (let me be upfront), I lack. This is the reason, last 20 minutes, I had to be a bit fast and therefore, I feel, it was an injustice to my participants. Anyways, as mentioned, this is also a new learning for me. Next time, time-management will be one of my primary focus, which will ensure that whatever I want to communicate to my participants, will be communicated in a timely manner.

Sangam 12 ! All India Oracle User Group Annual Conference

It’s time to be at Bangalore for an Annual Oracle User Group Conference – Sangam 2012. This is scheduled to be on 2nd and 3rd of November 2012. I am presenting a 1 hour session on “Ensuring Optimal Performance”. This is on 3rd of November. Unlike the events in the past, where the Key Speakers speak for the 1st half of the day, this year, the Key Speakers will speak for the entire day. This year, Thomas Kyte will make his second appearance in India and alongside him, the Podium will be shared by Andrew Holdsworth and Graham Wood, both of them are from Real World Performance Team. This will be a complete Interactive Session, with all three experts answering (with live demonstrations) to the questions being asked by the attendees. It will be a great learning, with fun. If not registered yet, register on http://aioug.org.

Last year due to Business commitments, I could not present and I missed this a lot. This year will be a comeback. Looking forward to meet you all at the event.

Unacceptable Performance post Implementing Exadata….

UPDATED ON 17th APRIL 2012.

Tanel Poder, in one of his blog, has demonstrated that the Queries on Sequences are Offloadable. This clearly means, my conclusion was wrong and needs investigation. I had a 10046 trace during the problem time, which says the waits on “cell multiblock physical read”, which means, the query was not offloaded. Post commenting the Sequences, the query response time came down to 3-4 minutes and therefore, I concluded this to be an issue. The Customer also mentioned that this time, he could see “cell smart table scan” and this lead to the wrong conclusion. The learning from this is : Until and Unless, you physically see the impact of the change and are 100% assured, never conclude.

One of the primary reason, I started blogging is to share my analysis. All these are my own views and are not supported or assisted by anyone. Therefore, there are times, when I am wrong. Blogging helps me connect the experts worldwide. Their comments on my writing (be it a positive or negative) serves my purpose of self learning. I appreciate the comments where I am wrong, as it helps me rectifying my mistakes. The latest blog is one such example, which Tanel has helped me understand that my analysis was wrong. I appreciate his comments and the demonstration. Click on the link below to access the blog written by Tanel.

http://blog.tanelpoder.com/2012/04/13/exadata-smart-scan-predicate-offloading-and-sequence-nextval/

As mentioned in one of my previous blog, Exadata has changed the Performance perception and is therefore has generated widespread interest amongst Oracle Community. To be very honest, due to the cost involved in investing Exadata, everyone expects it to deliver more than what is promised. Again, honestly, I have seen a tremendous performance improvement, that too AS-IS, which means, a significant improvement without actually making any changes and exadata features in action. During one of my POC for a banking customer, we just restored the Customer Database onto an Exadata Machine and observed significant improvement without actually making any changes or tweaking it for Exadata. Back to my current topic, as mentioned, Customer expects a significant improvement post implementing Exadata and this blog is on an issue that bothered one of my customer a lot.

The background of the issue is : A Customer supporting a large application invested in an Exadata Machine, with a business justification of providing an SLA for critical business processes. Some of these processes have been violating the SLA’s and therefore, with all the options of Optimization faded, the change was important. During the POC phase, all but one process showed significant improvement. One process took longer than expected and was a threat to the business justification provided before investing.

About the Process

It’s a Data Loading process that creates a new table from another table based on some conditions. This newly created table is then used by other processes. On a Non-Exadata Machine, the table creation takes around 3 hours. Customer expected this to come down to less than 10 minutes and was shocked when it took more than 2 hours. The completion time on Exadata is not known, as the customer (due to sheer frustration) killed the process. Fortunately (atleast for me), when the process did not complete, the administrator enabled 10046 trace for the process and, in this blog, I will share a critical information from the trace that helped optimizing the process.

Before I dive into the issue and the resolution, let us understand a critical aspect of Exadata. A Query (FTS) executed against Exadata Machine, the required blocks can be read from the storage in two different approaches. These are : (1) either read the required columns (column projection) or the required rows (predicate filtering) from the Storage using iDB and/or (2) read the entire block (and finally all the blocks) into the SGA. The former approach is called as Smart Scan (or Query Offloading), whereas, the later is similar to the strategy used by Non-Exadata Oracle Implementation.

Back to the issue : Involvement, post-performance issues, requires analyzing traces and in our case, it was 10046 trace file. From the trace file, I could see that the Query was doing a Full Table Scan for loading the data into another table. Further, the prominent waits associated with this Query were “cell multiblock physical read” and “cell single block physical read”. From these waits, it was evident that the Query was not offloaded to the Exadata Storage and therefore, the performance of this query would not benefit from Exadata Storage. As mentioned in my previous paragraph, if the query is not offloaded to the Exadata Storage, the behavior and performance would be nearly similar to that of non-exadata storage. Please note, this is a Full Table Scan and therefore, flash cache will not be of any use here, unless, the table in the query is specifically cached using KEEP clause.

The Query that creates a table is mentioned below and you need to believe me that it does a Full Table Scan on ST Table.

create table D_ME1 as  
select 	ST.SRC_SYS_REF_ID SRC_SYS_REF_ID, ST.XCD_SRC_ENTY_REF_ID SRC_ENTY_REF_ID,  
	ST.XCD_SRC_ID SRC_SYS_ID,  
	(select REF.REF_ID from test.reference_v REF where REF.REF_TYP_NAME='OGRDS ENTITY TYPE' and REF.REF_VAL = 'CROSSCODE') OGRDS_ENTY_REF_ID,
	'XCD' OGRDS_ENTY_NAME,  
	XCD_ID_SEQ.NEXTVAL OGRDS_ID,  
	'PNB_MDL_LOAD' CRTD_BY,  
	sys_extract_utc(systimestamp) CRTD_DTTM,  
	'PNB_MDL_LOAD' UPD_BY,  
	sys_extract_utc(systimestamp) UPD_DTTM  
from 	P2_MDL_E2E.ST ST  
where  	(1=1)    
And 	(ST.SRC_SYS_REF_ID=1)    
AND 	('Y')=(SELECT INTF_A.RUN_IND FROM p2_mdl_ctrl_e2e.CT_LST INTF_A
		WHERE 	INTF_A.INTF_NAME='PNB_MDL_INT_XREF_XCD_B' 
		AND 	INTF_A.SRC_SYS_REF_ID=1
              	AND 	INTF_A.SCEN_NAME='PNB_MDL_PKG_XREF_XCD_B' 
              	AND	INTF_A.FLOW_NAME='EIMDB_INITIAL_LOAD');              

A simple change in the query improved the performance and the execution time was 3-4 minutes. This was primarily due to Query Offloading, which forces the processing of the Query at the Storage level and sent only the relevant data to the Database. This is the first approach mentioned above. What was the change that triggered the important Optimization of Offloading ? In other words, What prevented the Original Query to be Offloaded to the Exadata Storage ? The answer is the XCD_ID_SEQ.NEXTVAL. With Oracle Sequences, the database engine has to generate and maintain the values in the SGA. Oracle Sequences cannot be offloaded and therefore, if these are specified in the query, the optimization fallsback to the traditional method of sending each block to the SGA. For testing purpose, when the Sequence was replaced by rownum (and as a final workaround to to_char(sysdate,’DDMMYYYYHH24MISS’)||rownum) offloading came into effect and the execution time dropped to 3-4 minutes. The fresh 10046 traces also revealed that the wait events for the modified query changed to “cell smart table scan” which means a Smart Scan. The final modified query is as under :

create table D_ME1 as  
select 	ST.SRC_SYS_REF_ID SRC_SYS_REF_ID, ST.XCD_SRC_ENTY_REF_ID SRC_ENTY_REF_ID,  
	ST.XCD_SRC_ID SRC_SYS_ID,  
	(select REF.REF_ID from test.reference_v REF where REF.REF_TYP_NAME='OGRDS ENTITY TYPE' and REF.REF_VAL = 'CROSSCODE') OGRDS_ENTY_REF_ID,
	'XCD' OGRDS_ENTY_NAME,  
	to_char(sysdate,'DDMMYYYYHH24MISS')||rownum OGRDS_ID,  /* this creates a Unique value forcing Offloading */
	'PNB_MDL_LOAD' CRTD_BY,  
	sys_extract_utc(systimestamp) CRTD_DTTM,  
	'PNB_MDL_LOAD' UPD_BY,  
	sys_extract_utc(systimestamp) UPD_DTTM  
from 	P2_MDL_E2E.ST ST  
where  	(1=1)    
And 	(ST.SRC_SYS_REF_ID=1)    
AND 	('Y')=(SELECT INTF_A.RUN_IND FROM p2_mdl_ctrl_e2e.CT_LST INTF_A
		WHERE 	INTF_A.INTF_NAME='PNB_MDL_INT_XREF_XCD_B' 
		AND 	INTF_A.SRC_SYS_REF_ID=1
              	AND 	INTF_A.SCEN_NAME='PNB_MDL_PKG_XREF_XCD_B' 
              	AND	INTF_A.FLOW_NAME='EIMDB_INITIAL_LOAD');              

In a nutshell, while Exadata will give you tremendous performance, especially in case of a Full Table Scan, there are cases that require human intelligence . In this case, while both the queries perform a Full Table Scan, the Original prevented Optimization by way of Offloading (Smart Scan), while the modified code smartly offloaded the query to the Storage bringing down the execution time drastically.

AIOUG Tech Event in Chennai ! 21st April 2012

A Tech Day is scheduled in Chennai, India on 21st April 2012. I will be presenting a One Hour session on “All about Performance”. Hope to see many of my followers during the event. For registration, visit http://www.aioug.org.

Query Performance Issue ! SQL*Plus v/s Oracle Reports…

Have editted on 1st March to include the Execution Plan of the Modified Query. See at the end.

Recently, got a mail from one of my customer on a Query Performance Issue. The Customer mentioned that the Query, when executed from SQL*Plus, takes around 10 Seconds, whereas, when the pasted in Oracle Reports and run through Concurrent Manager, it runs from around 30-40 minutes and is finally killed. The Customer also confirmed that the Query is exactly same i.e it is purely a Copy-Paste of the Query that was run from SQL*Plus and that the Bind Type & Length too are same. The run time plan for both the executions are totally different, with the plan of SQL*Plus better than the plan generated by Oracle Reports. The Question was, “Why is the plan different between SQL*Plus and Oracle Reports ?”

As usual, I asked for a 10053 trace for both the executions, which is one of the best way in investigating Optimizer Calculations. The 10053 trace revealed some important facts about the Query Performance, and therefore helped resolve the issue. Before we dive into the investigation, let me paste the Query and both the Execution Plans.

This is the Query.

SELECT /*+ INDEX(WDD WSH_DELIVERY_DETAILS_TI4) */
             WDD.ORGANIZATION_ID, 
             COUNT(*) LPNS,
             TRP.TRIP_ID,
             RC.CUSTOMER_NUMBER,
             RC.CUSTOMER_NAME, 
             RAA.ADDRESS1 TO_ADDRESS1, 
             RAA.CITY RAA.STATE TO_CITY_STATE, 
             HLA.DESCRIPTION FROM_DESC, 
             HLA.ADDRESS_LINE_1||' '||HLA.TOWN_OR_CITY||' '||HLA.TOWN_OR_CITY)  FROM_ADDRESS1_CITY, 
             WDA.DELIVERY_ID, 
             WDA.PARENT_DELIVERY_DETAIL_ID,
             WDD.DELIVERY_DETAIL_ID, 
             WDD1.CONTAINER_NAME    CONTAINER_NAME, 
             SUM(WDD.REQUESTED_QUANTITY) REQUESTED_QUANTITY, 
             WDD.REQUESTED_QUANTITY_UOM, 
             SUM(ROUND(WDD.GROSS_WEIGHT,0)) GROSS_WEIGHT, 
             WDD.WEIGHT_UOM_CODE,
             WT.VEHICLE_NUM_PREFIX ||' '||WT.VEHICLE_NUMBER VEHICLE_NUMBER,
             MSI.SEGMENT1 ITEM_CODE, 
	     MSI.ATTRIBUTE10 PRT_DESC 
FROM 	apps.WSH_DELIVERY_DETAILS WDD,
     	apps.WSH_DELIVERY_DETAILS WDD1,
        apps.WSH_DELIVERY_ASSIGNMENTS WDA ,
        apps.WSH_DELIVERY_ASSIGNMENTS WDA1,    
        apps.WSH_TRIPS WT, 
        apps.MTL_SYSTEM_ITEMS MSI, 
	TI_RA_ADDRESSES_V RAA,
	TI_RA_CUSTOMERS_V RC ,
        apps.HR_LOCATIONS_ALL HLA,
    	(SELECT DISTINCT  T.TRIP_ID, WDA.DELIVERY_ID 
    	FROM	apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
          	apps.WSH_DELIVERY_LEGS DL,
          	apps.WSH_TRIP_STOPS PICKUP_STOP,  
             	apps.WSH_TRIPS T
	WHERE	WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  = : P_TRIPID_FR 
AND 	TRP.TRIP_ID <= : P_TRIPID_TO 
GROUP BY WDD.ORGANIZATION_ID , TRP.TRIP_ID , RC.CUSTOMER_NUMBER , RC.CUSTOMER_NAME , RAA.ADDRESS1 , 
RAA.CITY , RAA.STATE , HLA.DESCRIPTION , HLA.ADDRESS_LINE_1 , HLA.TOWN_OR_CITY , WDA.DELIVERY_ID , 
WT.VEHICLE_NUM_PREFIX , WT.VEHICLE_NUMBER , WDA.PARENT_DELIVERY_DETAIL_ID , WDD.DELIVERY_DETAIL_ID , 
WDD1.CONTAINER_NAME , WDD.REQUESTED_QUANTITY_UOM , WDD.WEIGHT_UOM_CODE , MSI.SEGMENT1 , MSI.ATTRIBUTE10

The Runtime Plan of the Query when Executed from SQL*Plus.

-----------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                        | Rows  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                             |       |
|   1 |  SORT GROUP BY                                  |                             |     1 |
|   2 |   VIEW                                          | VM_NWVW_1                   |     1 |
|   3 |    SORT UNIQUE                                  |                             |     1 |
|*  4 |     FILTER                                      |                             |       |
|   5 |      NESTED LOOPS                               |                             |       |
|   6 |       NESTED LOOPS                              |                             |     1 |
|   7 |        NESTED LOOPS                             |                             |     1 |
|   8 |         NESTED LOOPS                            |                             |     1 |
|   9 |          NESTED LOOPS                           |                             |     5 |
|  10 |           NESTED LOOPS                          |                             |     5 |
|  11 |            NESTED LOOPS                         |                             |     5 |
|  12 |             NESTED LOOPS                        |                             |     5 |
|  13 |              NESTED LOOPS OUTER                 |                             |    27 |
|  14 |               NESTED LOOPS OUTER                |                             |    27 |
|  15 |                NESTED LOOPS                     |                             |    27 |
|  16 |                 NESTED LOOPS                    |                             |  1908 |
|  17 |                  NESTED LOOPS                   |                             |   427 |
|  18 |                   NESTED LOOPS                  |                             |   127 |
|  19 |                    MERGE JOIN                   |                             |    72 |
|  20 |                     TABLE ACCESS BY INDEX ROWID | WSH_TRIP_STOPS              |   187 |
|* 21 |                      INDEX RANGE SCAN           | WSH_TRIP_STOPS_N1           |   187 |
|* 22 |                     SORT JOIN                   |                             |    72 |
|  23 |                      TABLE ACCESS BY INDEX ROWID| WSH_TRIPS                   |    72 |
|* 24 |                       INDEX RANGE SCAN          | WSH_TRIPS_U1                |    72 |
|  25 |                    TABLE ACCESS BY INDEX ROWID  | WSH_DELIVERY_LEGS           |     2 |
|* 26 |                     INDEX RANGE SCAN            | WSH_DELIVERY_LEGS_N2        |     2 |
|* 27 |                   INDEX RANGE SCAN              | WSH_DELIVERY_ASSIGNMENTS_N1 |     3 |
|  28 |                  TABLE ACCESS BY INDEX ROWID    | WSH_DELIVERY_ASSIGNMENTS    |     4 |
|* 29 |                   INDEX RANGE SCAN              | WSH_DELIVERY_ASSIGNMENTS_N1 |     4 |
|* 30 |                 TABLE ACCESS BY INDEX ROWID     | WSH_DELIVERY_DETAILS        |     1 |
|* 31 |                  INDEX RANGE SCAN               | WSH_DELIVERY_DETAILS_TI4    |     1 |
|* 32 |                INDEX RANGE SCAN                 | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
|  33 |               TABLE ACCESS BY INDEX ROWID       | WSH_DELIVERY_DETAILS        |     1 |
|* 34 |                INDEX UNIQUE SCAN                | WSH_DELIVERY_DETAILS_U1     |     1 |
|  35 |              TABLE ACCESS BY INDEX ROWID        | MTL_SYSTEM_ITEMS_B          |     1 |
|* 36 |               INDEX UNIQUE SCAN                 | MTL_SYSTEM_ITEMS_B_U1       |     1 |
|  37 |             TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCOUNTS            |     1 |
|* 38 |              INDEX UNIQUE SCAN                  | HZ_CUST_ACCOUNTS_U1         |     1 |
|  39 |            TABLE ACCESS BY INDEX ROWID          | HZ_LOCATIONS                |     1 |
|* 40 |             INDEX UNIQUE SCAN                   | HZ_LOCATIONS_U1             |     1 |
|  41 |           TABLE ACCESS BY INDEX ROWID           | HZ_PARTY_SITES              |     1 |
|* 42 |            INDEX RANGE SCAN                     | HZ_PARTY_SITES_N2           |     1 |
|* 43 |          TABLE ACCESS BY INDEX ROWID            | HZ_CUST_ACCT_SITES_ALL      |     1 |
|* 44 |           INDEX RANGE SCAN                      | HZ_CUST_ACCT_SITES_N1       |     1 |
|  45 |         TABLE ACCESS BY INDEX ROWID             | HZ_PARTIES                  |     1 |
|* 46 |          INDEX UNIQUE SCAN                      | HZ_PARTIES_U1               |     1 |
|* 47 |        INDEX UNIQUE SCAN                        | HR_LOCATIONS_PK             |     1 |
|  48 |       TABLE ACCESS BY INDEX ROWID               | HR_LOCATIONS_ALL            |     1 |
-----------------------------------------------------------------------------------------------

Runtime Plan of the Query when Executed from Oracle Reports.

  
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                                       | Name                        | Rows  |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                |                             |       |
  |   1 |  SORT ORDER BY                                  |                             |     1 |
  |   2 |   SORT GROUP BY                                 |                             |     1 |
  |   3 |    VIEW                                         | VM_NWVW_1                   |     1 |
  |   4 |     SORT UNIQUE                                 |                             |     1 |
  |*  5 |      FILTER                                     |                             |       |
  |   6 |       NESTED LOOPS                              |                             |       |
  |   7 |        NESTED LOOPS                             |                             |     1 |
  |   8 |         NESTED LOOPS                            |                             |     1 |
  |   9 |          NESTED LOOPS                           |                             |     4 |
  |  10 |           NESTED LOOPS                          |                             |     4 |
  |  11 |            NESTED LOOPS                         |                             |     1 |
  |  12 |             NESTED LOOPS OUTER                  |                             |     1 |
  |  13 |              NESTED LOOPS OUTER                 |                             |     1 |
  |  14 |               NESTED LOOPS                      |                             |     1 |
  |* 15 |                HASH JOIN                        |                             |     1 |
  |  16 |                 TABLE ACCESS FULL               | HZ_CUST_ACCT_SITES_ALL      | 36983 |
  |* 17 |                 HASH JOIN                       |                             | 23952 |
  |  18 |                  TABLE ACCESS FULL              | HZ_PARTY_SITES              | 76710 |
  |* 19 |                  HASH JOIN                      |                             | 22674 |
  |  20 |                   TABLE ACCESS FULL             | HZ_LOCATIONS                | 72598 |
  |* 21 |                   HASH JOIN                     |                             | 22737 |
  |  22 |                    TABLE ACCESS FULL            | HZ_CUST_ACCOUNTS            | 14291 |
  |* 23 |                    HASH JOIN                    |                             | 22801 |
  |  24 |                     TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B          | 11073 |
  |* 25 |                      INDEX RANGE SCAN           | MTL_SYSTEM_ITEMS_B_N16      | 11073 |
  |* 26 |                     HASH JOIN                   |                             |   130K|
  |  27 |                      TABLE ACCESS FULL          | HR_LOCATIONS_ALL            |   919 |
  |* 28 |                      TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |   130K|
  |* 29 |                       INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_TI4    |   143K|
  |* 30 |                TABLE ACCESS BY INDEX ROWID      | WSH_DELIVERY_ASSIGNMENTS    |     1 |
  |* 31 |                 INDEX RANGE SCAN                | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
  |* 32 |               INDEX RANGE SCAN                  | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
  |  33 |              TABLE ACCESS BY INDEX ROWID        | WSH_DELIVERY_DETAILS        |     1 |
  |* 34 |               INDEX UNIQUE SCAN                 | WSH_DELIVERY_DETAILS_U1     |     1 |
  |  35 |             TABLE ACCESS BY INDEX ROWID         | HZ_PARTIES                  |     1 |
  |* 36 |              INDEX UNIQUE SCAN                  | HZ_PARTIES_U1               |     1 |
  |* 37 |            INDEX RANGE SCAN                     | WSH_DELIVERY_ASSIGNMENTS_N1 |     4 |
  |  38 |           TABLE ACCESS BY INDEX ROWID           | WSH_DELIVERY_LEGS           |     1 |
  |* 39 |            INDEX RANGE SCAN                     | WSH_DELIVERY_LEGS_N1        |     1 |
  |* 40 |          TABLE ACCESS BY INDEX ROWID            | WSH_TRIP_STOPS              |     1 |
  |* 41 |           INDEX UNIQUE SCAN                     | WSH_TRIP_STOPS_U1           |     1 |
  |* 42 |         INDEX UNIQUE SCAN                       | WSH_TRIPS_U1                |     1 |
  |  43 |        TABLE ACCESS BY INDEX ROWID              | WSH_TRIPS                   |     1 |
  -----------------------------------------------------------------------------------------------

Since the investigation is around the Cardinality of each of the row source, I have removed other information (irrelevant for this discussion) from the execution plan. The Query contains a Subquery with alias as TRP. The Tables queried in the Subquery Drives the Good Plan, whereas, the Subquery is unnested and joined at a later stage in the Problematic Plan (Step 37-43). In the Problematic Plan, the Driving table is HR_LOCATIONS_ALL, which is hash joined to WSH_DELIVERY_DETAILS. The Investigation was around the change in the Execution Path. One of the primary goal of Optimizer is to calculate the Number of rows to be returned by each of the Filter Predicate and then evaluate the Access Patch, including the Driving Table, based on the predicate that fetches the least number of rows. It then also evaluates the Join Method, which is to consider Nested Loop Join for small set of rows or Hash Join / Sort Merge Join for larger set of rows.

The text of the TRP Subquery is pasted below

    	(SELECT DISTINCT  T.TRIP_ID, WDA.DELIVERY_ID 
    	FROM	apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
          	apps.WSH_DELIVERY_LEGS DL,
          	apps.WSH_TRIP_STOPS PICKUP_STOP,  
             	apps.WSH_TRIPS T
	WHERE	WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  <=  : P_TRIPID_TO )  TRP

The Good Plan for this subquery is a Sort Merge Join on WSH_TRIPS & WSH_TRIP_STOPS. Both the tables are accessed via an Index on TRIP_ID. Note that though the range predicate is on TRIP_ID of WSH_TRIPS, the column is joined to TRIP_ID of WSH_TRIP_STOPS and therefore, the optimizer scans an Index on these columns to get the relevant rows from the table. The Optimizer calculation says 72 rows from WSH_TRIPS and 187 rows from WSH_TRIP_STOPS, which are merged join to get final 72 rows from the two tables. This calculation is based on the Input values that Optimized peeked into during the hard parse. For the same set of input values, we have a 10053 trace of the Bad Plan and therefore, I immediately concentrated on the cardinality calculation for the two tables mentioned here. Surprisingly, the first caveat that I observed in the 10053 trace was missing Bind Peeked Data and this was enough to get into the Optimizer calculations.

The Peeked Bind Values section of the 10053 trace shows following information.

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=72 off=0
  No bind buffers allocated
 Bind#1
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=24
  No bind buffers allocated
 Bind#2
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=48
  No bind buffers allocated
 Bind#3
  No oacdef for this bind.
 Bind#4
  No oacdef for this bind.

When the Query was executed from SQL*Plus, the values for the Binds were populated and were visible in the Peeked Bind values section of 10053 trace. The missing information means, the Optimizer would fall back to the default calculation for a range predicate. From the trace, the computed cardinality for the two tables, WSH_TRIPS & WSH_TRIP_STOPS, are as under :

Access path analysis for WSH_TRIP_STOPS
***************************************
Access path analysis for WSH_TRIP_STOPS
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for WSH_TRIP_STOPS[PICKUP_STOP] 

  Table: WSH_TRIP_STOPS  Alias: PICKUP_STOP
    Card: Original: 4666397.000000  Rounded: 11666  Computed: 11665.99  Non Adjusted: 11665.99

Access path analysis for WSH_TRIPS
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for WSH_TRIPS[T] 

  Table: WSH_TRIPS  Alias: T
    Card: Original: 1802653.000000  Rounded: 4507  Computed: 4506.63  Non Adjusted: 4506.63

In the absence of the actual values provided to the Bind Variables, the Optimizer computed the Cardinality based on defaults, which in this case is 0.25% (5% for >= and 5% of = : P_TRIPID_FR and TRIP_ID <= : P_TRIPID_TO is too high. Another predicate in the Outer query is ORGANIZATION_ID and based on this predicate, optimizer generates a plan with the smallest rowsource first. The smallest rowsource is HR_LOCATIONS_ALL with 919 rows and is joined to WSH_DELIVERY_DETAILS table, which is accessed via hinted index WSH_DELIVERY_DETAILS_TI4.

To check and confirm this behaviour, I executed an EXPLAIN PLAN FOR for this query, without setting the values for the bind variables, from SQL*Plus. With no values to the Bind, the EXPLAIN PLAN FOR computed the BAD plan from SQL*Plus. Therefore, now I was sure that the problem occurs when the peeked values to the bind are not available. The next concern was “Why Oracle Report does not peek into the Bind Variables before actually generating the plan ?”.

I assume this to be a default behaviour, where the query is parsed first and then binded. Unfortunately, there is not much written on this. A better option is to write a query in an efficient way or hint the query. To resolve the issue, I modified the query and this modified version worked perfectly well on both the environments i.e. SQL*Plus and Oracle Reports. The modification was in the Subquery TRP. The change is pasted below.

    (SELECT TRIP_ID, DELIVERY_ID FROM 
    (SELECT T.TRIP_ID, WDA.DELIVERY_ID, row_number() over(partition by t.trip_id, wda.delivery_id) rnum 
                FROM   apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
                                apps.WSH_DELIVERY_LEGS DL,
                                apps.WSH_TRIP_STOPS PICKUP_STOP,  
                                apps.WSH_TRIPS T
                WHERE   WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  <=  : P_TRIPID_TO)
     WHERE RNUM=1)  TRP

The modification made sure that the TRP subquery becomes the Driving Query. The relevant portion of the execution plan post modification is attached below. The calculation for WSH_TRIPS is still at 0.25% and is therefore computed as 4507, even then, the Optimizer takes this as a Driving Table, which is what we wanted. This again leads to a common argument, which is, Optimizer is a piece of Code that works on Statistics. It is the way we write a query that dominates the Optimizer.

|* 19 |                  WINDOW SORT PUSHED RANK         |                             | 26689 |
|* 20 |                   FILTER                         |                             |       |
|  21 |                    NESTED LOOPS                  |                             | 26689 |
|  22 |                     NESTED LOOPS                 |                             |  7968 |
|  23 |                      NESTED LOOPS                |                             |  4516 |
|* 24 |                       INDEX RANGE SCAN           | WSH_TRIPS_U1                |  4507 |
|  25 |                       TABLE ACCESS BY INDEX ROWID| WSH_TRIP_STOPS              |     1 |
|* 26 |                        INDEX RANGE SCAN          | WSH_TRIP_STOPS_N1           |     1 |
|  27 |                      TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_LEGS           |     2 |
|* 28 |                       INDEX RANGE SCAN           | WSH_DELIVERY_LEGS_N2        |     2 |
|* 29 |                     INDEX RANGE SCAN             | WSH_DELIVERY_ASSIGNMENTS_N1 |     3 |

  20 - filter(:P_TRIPID_FR=:P_TRIPID_FR AND "T"."TRIP_ID"=:P_TRIPID_FR AND "PICKUP_STOP"."TRIP_ID"<=:P_TRIPID_TO))
  28 - access("DL"."PICK_UP_STOP_ID"="PICKUP_STOP"."STOP_ID")
  29 - access("WDA"."DELIVERY_ID"="DL"."DELIVERY_ID")
       filter("WDA"."DELIVERY_ID" IS NOT NULL)