Simulating Exadata Storage Performance over Traditional Storage

Exadata Server Storage Software plays a crucial role in delivering Extreme Performance and Scalability for all database Application and this includes OLTP, Datawarehouse and mix of both. Performance bottleneck, due to data growth and Increase in the Userbase, the customers are now aware of the fact that they need to adopt this technology. There have been instances when Customers, especially in India, request a POC to measure the performance benefit of EXADATA over Traditional Storage. Starting Oracle 11g R2, Oracle provides certain API’s that can be give a fair amount of accurate performance statistics for the application queries and the way these will behave post-implementing exadata. This can also be done using Oracle Enterprise Manager 11g, but I used Oracle API’s to simulate and get the exadata performance matrix on a production system at a customer site.

One of my customer is evaluating Oracle Exadata and the mandate, in this case, is clearly increasing Market competition and sustenance. While the team were busy preparing a presentation, we felt, it would be a good idea to present the customer with the artifacts of the benefits in the actual production queries from their own production system. Post running these scripts a text file was presented to the customer. The customer was amazed to see the performance benefits.

Before we get into the API’s, a brief explanation on Exadata. EXADATA is a fully loaded Server, that comes in different configuration and is plug-and-play machine. It is basically an storage aware software and this intelligence is built in Storage Cells. These Cells store the data they contain and therefore, it is easier to get into the data block that contain the relevant rows directly. Unlike traditional storage that returns all the blocks to the Database Server, Exadata Storage Software returns only the relevant blocks that contain the data. Therefore, the main performance gain observed is due to the amount of data that travels between the Storage System and Database Server. Usually, the I/O bottleneck is observed in the production system if the amount of data that travels is enormous. With Exadata, these unwanted data is filtered at the storage level itself and relevant data traverses from the I/O subsystem to the Database Server. Further, its also reduces the amount of Latching, that would have otherwise required, to protect the blocks reads from the Storage into the SGA. Reduction in Latch Gets means a Scalable Application.

Following are the Scripts that were executed at the Customer site to simulate Exadata Performance and get the Performance Matrix of Application Queries. As mentioned earlier, Exadata Storage Cells provide the intelligence, these scripts use SQL Peformance Analyzer (SPA) to test cell storage in simulation mode and the matrix that is compared is IO_INTERCONNECT_BYTES. This is the amount of data, in bytes, sent by the Storage to the Database Server.

We start by creating SQL Tuning Sets. Once the STS is created, we need to capture all the Cursors (SQL Queries) in the Cache for a certain period, which is speficied in seconds. In my case, I captured the Queries for 180 Seconds. On a production system, this should be enough as the Shared Pool is usually warm and most of the queries are already found in the cache. If this value is increased, the Analysis (simulation) would take time. Once the Queries are captured, the Simulation is initiated, first with CELL_SIMULATION_ENABLED set to FALSE and then with CELL_SIMULATION_ENABLED set to TRUE. When the simulation is run, the queries are internally executed to generate and capture the io_interconnect_bytes value. Finally, it is time to compare the matrix and generate a TEXT or HTML file.

/* Create Tuning SQLSet */

begin 
  dbms_sqltune.create_sqlset( 
    sqlset_name => 'Trad_2_Exadata_Simulation',
    description => 'SQL Tuning Set for Exadata Simulation');
end;
/

/* time_limit is the time in number of seconds the Queries will be captured. */
/* In this case, it is 180 Seconds */
begin
  dbms_sqltune.capture_cursor_cache_sqlset (
    sqlset_name     => 'Trad_2_Exadata_Simulation',
    time_limit      => 180,
    repeat_interval => 10
  );
end;
/


variable Ret_Val clob
set long 999999
begin   
  :Ret_Val :=  
       dbms_sqlpa.create_analysis_task(
	sqlset_name       => 'Trad_2_Exadata_Simulation',
	task_name         => 'Trad_2_Exadata_Simulation_SPA');
end;
/

begin
   dbms_sqlpa.set_analysis_task_parameter ('Trad_2_Exadata_Simulation_SPA','TIME_LIMIT','UNLIMITED');
   dbms_sqlpa.set_analysis_task_parameter ('Trad_2_Exadata_Simulation_SPA','LOCAL_TIME_LIMIT',300);
   :Ret_Val := dbms_sqlpa.execute_analysis_task (
	task_name        => 'Trad_2_Exadata_Simulation_SPA',
	execution_type   => 'test execute',
	execution_name   => 'TRADITIONAL',
	execution_params => DBMS_ADVISOR.arglist ('cell_simulation_enabled','FALSE'),
	execution_desc   => 'Exadata simulation disabled'
    );
end;
/


begin
   :Ret_Val := dbms_sqlpa.execute_analysis_task (
	task_name        => 'Trad_2_Exadata_Simulation_SPA',
	execution_type   => 'test execute',
	execution_name   => 'EXADATA',
	execution_params => dbms_advisor.arglist ('cell_simulation_enabled','TRUE'),
	execution_desc   => 'Exadata simulation enabled'
    );
end;
/

begin
   :Ret_Val := dbms_sqlpa.execute_analysis_task (
	task_name       => 'Trad_2_Exadata_Simulation_SPA',
	execution_type  => 'compare performance',
	execution_params=> dbms_advisor.arglist('comparison_metric','io_interconnect_bytes')
  );
end;
/


set lines 300 
set pages 0 
set trimspool on 
set long 999999
spool report.txt
select dbms_sqlpa.report_analysis_task ('Trad_2_Exadata_Simulation_SPA','TEXT','TYPICAL','ALL') from dual;
spool off

Oracle also provides with a script tcellsim.sql under $ORACLE_HOME/rdbms/admin. This can also be used to run the simulation. The output from the production enviroment is pasted below. I have trimmed the output to make this short.

General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:

 ---------------------------------------------  ---------------------------------------------
  Task Name    : Trad_2_Exadata_Simulation_SPA   SQL Tuning Set Name        : Trad_2_Exadata_Simulation
  Task Owner   : SYS                             SQL Tuning Set Owner       : SYS
  Description  :                                 Total SQL Statement Count  : 1335

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name             : EXEC_10626             Started             : 12/30/2010 14:34:36
  Execution Type             : COMPARE PERFORMANCE    Last Updated        : 12/30/2010 14:38:18
  Description                :                        Global Time Limit   : UNLIMITED
  Scope                      : COMPREHENSIVE          Per-SQL Time Limit  : 300

  Status                     : COMPLETED              Number of Errors    : 7

  Number of Timeouts         : 4

  Number of Unsupported SQL  : 77

SQL Details:
-----------------------------
 Object ID            : 3160

 Schema Name          : F_LEA

 SQL ID               : 5um8xff95jn6x

 Execution Frequency  : 266122

 SQL Text             : SELECT 'x' FROM L_AGREEMENT_DTL WHERE AGREEMENTID =

                      :b1 AND STATUS IN ( 'A','L','O','X','C' )


Execution Statistics:
-----------------------------
----------------------------------------------------------------------
|                       | Impact on | Value      | Value    | Impact |
| Stat Name             | Workload  | Before     | After    | on SQL |
----------------------------------------------------------------------
| elapsed_time          |           |  57.165548 |          |        |
| parse_time            |           |    .000424 |          |        |
| cpu_time              |           |      14.94 |          |        |
| user_io_time          |           | 1181.09798 |          |        |
| buffer_gets           |           |    1360874 |          |        |
| cost                  |        0% |          2 |        2 |     0% |
| reads                 |           |     340101 |          |        |
| writes                |           |          0 |          |        |
| io_interconnect_bytes |    84.13% | 2786107392 | 12337536 | 99.56% |
| rows                  |           |          1 |        1 |        |
----------------------------------------------------------------------
SQL Details:
-----------------------------
 Object ID            : 2839

 Schema Name          : F_LEA

 SQL ID               : 1xny80dtnbf6f

 Execution Frequency  : 157981

 SQL Text             : SELECT INSTALTYPE,EMI FROM L_AGREEMENT_DTL WHERE

                      AGREEMENTID = :b1


Execution Statistics:
-----------------------------
---------------------------------------------------------------------
|                       | Impact on | Value     | Value    | Impact |
| Stat Name             | Workload  | Before    | After    | on SQL |
---------------------------------------------------------------------
| elapsed_time          |           |  3.045475 |          |        |
| parse_time            |           |   .000509 |          |        |
| cpu_time              |           |       .65 |          |        |
| user_io_time          |           | 65.463246 |          |        |
| buffer_gets           |           |     57237 |          |        |
| cost                  |        0% |         2 |        2 |     0% |
| reads                 |           |     54172 |          |        |
| writes                |           |         0 |          |        |
| io_interconnect_bytes |     7.77% | 443777024 | 12243136 | 97.24% |
| rows                  |           |         1 |        1 |        |
---------------------------------------------------------------------

From this Simulation, it is clearly visible that the performance benefit using Exadata is tremendous. To make this shoirt, I have pasted only two comparisons, but there were other queries as well that showed a significant amount of performance benefit. In the first query above, the performance benefit on the overall system load is around 85% and the amout of data that traverses between the storage to database server has dropped by 99.56% (from 2.7 GB to 12 MB). This is significant. In the second query as well, since the table is same, the drop is from 443 MB to 12MB, a saving of 97%.

Oracle Enterprise Manager Grid Control ! A must read Book…

My Oracle Colleague, Porus has written a book “Oracle Enterprise Manager Grid Control: Advanced OEM Techniques for the Real World” which has recently been published:

http://www.rampant-books.com/book_1001_advanced_techniques_oem_grid_control.htm

The Book Synopsis is as follows:

Oracle’s Enterprise Manager Grid Control is recognized as the IT Industry’s leading Oracle database administration and management tool. It is unrivalled in its ability to monitor, manage, maintain and report on entire enterprise grids that comprise hundreds (if not thousands) of Oracle databases and servers following an approach that is consistent and repeatable.

However, Enterprise Manager Grid Control may seem daunting even to the most advanced Oracle Administrator. The problem is you know about the power of Enterprise Manager but how do you unleash that power amongst what initially appears to be a maze of GUI-based screens that feature a myriad of links to reports and management tasks that in turn lead you to even more reports and management tasks?

This book shows you how to unleash that power.

Based on the Author’s considerable and practical Oracle database and Enterprise Manager Grid Control experience you will learn through illustrated examples how to create and schedule RMAN backups, generate Data Guard Standbys, clone databases and Oracle Homes and patch databases across hundreds and thousands of databases. You will learn how you can unlock the power of the Enterprise Manager Grid Control Packs, PlugIns and Connectors to simplify your database administration across your company’s database network, as also the management and monitoring of important Service Level Agreements (SLAs), and the nuances of all important real-time change control using Enterprise Manager.

There are other books on the market that describe how to install and configure Enterprise Manager but until now they haven’t explained using a simple and illustrated approach how to get the most out of your Enterprise Manager. This book does just that.

This book covers everything on Enterprise Manager Grid Control 11g. I am sure that this book should help you in managing and maintaining a large Enterprise site using Grid Control.

Yet another Query Plan Change ! Cost Based Optimization…

Optimizer relies on Statistics to compute an Optimal Plan, which includes : Choice between Index Scan or Table Scan, in case of Index Scan, which index to choose, and more importantly, the Join Order and Join Type. Therefore, it is quite evident that accurate statistics means a good and accurate estimation of Cardinality, which in turn, results in optimal execution plan. But there are cases, when accurate statistics is not good enough for an Optimizer to come out with an Optimal plan. In this blog, I will post a real life issue which is an interesting case, in which, the Optimizer generated plan, impacted the performance of a query whereas the underlying object statistics were accurate. Accurate statistics means the the statistics that reflect the accurate picture of the data in the underlying table.

In many of the events that I presented, one of the common statement that I emphasize is “Differentiate Performance Symptom and Performance Problem”. In this case too, the inhouse team was investigating and focussing more on the Symptom while the problem was somewhere else. We will walk through this as well.

Recently, at a customer site, the performance of one of a critical performance, that runs every few minutes, got impacted. The inhouse adminitrator time could investigate that execution time of one particular query, that usually takes less than a second, changed to 30-40 minutes. Fortunately, the customer maintains Query Baselines and this helped them further delve into the issue and they observed that the plan was changed. While they were investigating the issue and the reason for this plan change, the issue was temporarily fixed using SQL Plan Management. The customer wanted to know the reason for this behaviour, so as to avoid, this in future.

This was a custom process. The original query and the good / bad plan is pasted below.

SELECT WIP_ENTITY_NAME FROM WIP_DJ_CLOSE_TEMP 
WHERE	WIP_ENTITY_ID IN
	(SELECT wdct.WIP_ENTITY_ID            
	FROM	WIP_TRANSACTIONS wt,
		WIP_DJ_CLOSE_TEMP wdct             
	WHERE	wdct.GROUP_ID = :b1             
	AND	wdct.ORGANIZATION_ID = :b2
	AND	wdct.WIP_ENTITY_ID = wt.WIP_ENTITY_ID             
	AND	wt.ORGANIZATION_ID = :b3             
	AND	wt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE   
	UNION    
	SELECT wdct.WIP_ENTITY_ID            
	FROM	MTL_MATERIAL_TRANSACTIONS mmt,
		WIP_DJ_CLOSE_TEMP wdct             
	WHERE	wdct.GROUP_ID = :b1             
	AND	wdct.ORGANIZATION_ID = :b2
	AND	wdct.WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID            
	AND	mmt.TRANSACTION_SOURCE_TYPE_ID = 5             
	AND	mmt.ORGANIZATION_ID= :b3
	AND mmt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE)

Good Plan

Plan hash value: 2500423228

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |       |       |  6017 (100)|          |       |       |
|   1 |  NESTED LOOPS                           |                              |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                          |                              |    70 |  1960 |  6017   (1)| 00:00:36 |       |       |
|   3 |    VIEW                                 | VW_NSO_1                     |    70 |   910 |  5947   (1)| 00:00:35 |       |       |
|   4 |     SORT UNIQUE                         |                              |    70 |  3040 |  5947  (92)| 00:00:35 |       |       |
|   5 |      UNION-ALL                          |                              |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                      |                              |       |       |            |          |       |       |
|   7 |        NESTED LOOPS                     |                              |    45 |  1890 |   490   (1)| 00:00:03 |       |       |
|*  8 |         TABLE ACCESS BY INDEX ROWID     | WIP_DJ_CLOSE_TEMP            |    80 |  1920 |     9   (0)| 00:00:01 |       |       |
|*  9 |          INDEX RANGE SCAN               | WIP_DJ_CLOSE_TEMP_N1         |   797 |       |     2   (0)| 00:00:01 |       |       |
|* 10 |         INDEX RANGE SCAN                | WIP_TRANSACTIONS_N1          |    24 |       |     0   (0)|          |       |       |
|* 11 |        TABLE ACCESS BY INDEX ROWID      | WIP_TRANSACTIONS             |     1 |    18 |     6   (0)| 00:00:01 |       |       |
|  12 |       NESTED LOOPS                      |                              |       |       |            |          |       |       |
|  13 |        NESTED LOOPS                     |                              |    25 |  1150 |  5455   (1)| 00:00:32 |       |       |
|* 14 |         TABLE ACCESS BY INDEX ROWID     | WIP_DJ_CLOSE_TEMP            |    80 |  1920 |     9   (0)| 00:00:01 |       |       |
|* 15 |          INDEX RANGE SCAN               | WIP_DJ_CLOSE_TEMP_N1         |   797 |       |     2   (0)| 00:00:01 |       |       |
|  16 |         PARTITION RANGE ITERATOR        |                              |     2 |       |    67   (0)| 00:00:01 |   KEY |    36 |
|* 17 |          INDEX RANGE SCAN               | MTL_MATERIAL_TRANSACTIONS_N2 |     2 |       |    67   (0)| 00:00:01 |   KEY |    36 |
|* 18 |        TABLE ACCESS BY LOCAL INDEX ROWID| MTL_MATERIAL_TRANSACTIONS    |     1 |    22 |    68   (0)| 00:00:01 |     1 |     1 |
|* 19 |    INDEX UNIQUE SCAN                    | WIP_DJ_CLOSE_TEMP_U1         |     1 |       |     0   (0)|          |       |       |
|  20 |   TABLE ACCESS BY INDEX ROWID           | WIP_DJ_CLOSE_TEMP            |     1 |    15 |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------

Bad Plan

Plan hash value: 821054275

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |       |       |  7295 (100)|          |       |       |
|   1 |  NESTED LOOPS                           |                              |    19 |   532 |  7295   (1)| 00:00:43 |       |       |
|   2 |   VIEW                                  | VW_NSO_1                     |    19 |   247 |  7276   (1)| 00:00:43 |       |       |
|   3 |    SORT UNIQUE                          |                              |    19 |   826 |  7276  (88)| 00:00:43 |       |       |
|   4 |     UNION-ALL                           |                              |       |       |            |          |       |       |
|*  5 |      TABLE ACCESS BY INDEX ROWID        | WIP_TRANSACTIONS             |     1 |    18 |     6   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                      |                              |    12 |   504 |   930   (1)| 00:00:06 |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID      | WIP_DJ_CLOSE_TEMP            |   152 |  3648 |    16   (0)| 00:00:01 |       |       |
|*  8 |         INDEX RANGE SCAN                | WIP_DJ_CLOSE_TEMP_N1         |  1522 |       |     4   (0)| 00:00:01 |       |       |
|*  9 |        INDEX RANGE SCAN                 | WIP_TRANSACTIONS_N1          |    24 |       |     0   (0)|          |       |       |
|* 10 |      HASH JOIN                          |                              |     7 |   322 |  6344   (1)| 00:00:37 |       |       |
|* 11 |       TABLE ACCESS BY INDEX ROWID       | WIP_DJ_CLOSE_TEMP            |   152 |  3648 |    16   (0)| 00:00:01 |       |       |
|* 12 |        INDEX RANGE SCAN                 | WIP_DJ_CLOSE_TEMP_N1         |  1522 |       |     4   (0)| 00:00:01 |       |       |
|  13 |       PARTITION RANGE ALL               |                              | 25149 |   540K|  6327   (1)| 00:00:37 |     1 |    36 |
|  14 |        TABLE ACCESS BY LOCAL INDEX ROWID| MTL_MATERIAL_TRANSACTIONS    | 25149 |   540K|  6327   (1)| 00:00:37 |     1 |    36 |
|* 15 |         INDEX RANGE SCAN                | MTL_MATERIAL_TRANSACTIONS_N8 | 25149 |       |   207   (1)| 00:00:02 |     1 |    36 |
|  16 |   TABLE ACCESS BY INDEX ROWID           | WIP_DJ_CLOSE_TEMP            |     1 |    15 |     1   (0)| 00:00:01 |       |       |
|* 17 |    INDEX UNIQUE SCAN                    | WIP_DJ_CLOSE_TEMP_U1         |     1 |       |     0   (0)|          |       |       |
----------------------------------------------------------------------------------------------------------------------------------------

Before diving into the analysis, let us compare both the plans and this will be from the customer’s perspective. They compared the plans and noticed that the Tables and Indexes selected by the optimizer, in both the cases, are same except for MTL_MATERIAL_TRANSACTION Table. The good plan uses N2 Index while the bad plan uses N8. Therefore, the customer hinted the query with N2 index and fixed the plan using SPM. Once fixed, they started their investigation on the reason for this change from N2 and N8. As mentioned earlier, this was just a Symptom while the underlying problem was something else. This something triggered the change in Index Selection and we will investigate this next.

Analysis

Comparing both the plans, it was now evident that the problem is with the second part of the Query, therefore, I took this part out for investigation. This is now a simple and small query, as show below :

SELECT wdct.WIP_ENTITY_ID            
FROM	apps.MTL_MATERIAL_TRANSACTIONS mmt,
	wip.WIP_DJ_CLOSE_TEMP wdct             
WHERE	wdct.GROUP_ID = :b1             
AND	wdct.ORGANIZATION_ID = :b2
AND	wdct.WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID            
AND	mmt.TRANSACTION_SOURCE_TYPE_ID = 5             
AND	mmt.ORGANIZATION_ID= :b3             
AND	mmt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE

The another change that I noticed here was the change of Nested Loop Join to Hash Join. The customer had a clone environment of this database which was 2 months old and I executed this query on the clone enviroment. The Query executed in less than a second on this and therefore, the investigation wa further made simpler for me. The Query plan on the clone and production is pasted below :

Production Plan : BAD
Plan hash value: 3088015387

------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Rows  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              |       |
|*  1 |  HASH JOIN                          |                              |    49 |
|*  2 |   TABLE ACCESS BY INDEX ROWID       | WIP_DJ_CLOSE_TEMP            |   155 |
|*  3 |    INDEX RANGE SCAN                 | WIP_DJ_CLOSE_TEMP_N1         |  1552 |
|   4 |   PARTITION RANGE ALL               |                              | 24662 |
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID| MTL_MATERIAL_TRANSACTIONS    | 24662 |
|*  6 |     INDEX RANGE SCAN                | MTL_MATERIAL_TRANSACTIONS_N8 | 24662 |
------------------------------------------------------------------------------------

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

   1 - access("WDCT"."WIP_ENTITY_ID"="MMT"."TRANSACTION_SOURCE_ID")
       filter("MMT"."TRANSACTION_DATE">"WDCT"."ACTUAL_CLOSE_DATE")
   2 - filter("WDCT"."ORGANIZATION_ID"=:ORGANIZATION_ID)
   3 - access("WDCT"."GROUP_ID"=:GROUP_ID)
   6 - access("MMT"."TRANSACTION_SOURCE_TYPE_ID"=5 AND "MMT"."ORGANIZATION_ID"=:ORGANIZATION_ID)

Clone DB Plan : GOOD
Plan hash value: 569619275

-----------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |       |
|   1 |  NESTED LOOPS                      |                              |       |
|   2 |   NESTED LOOPS                     |                              |    26 |
|*  3 |    TABLE ACCESS BY INDEX ROWID     | WIP_DJ_CLOSE_TEMP            |    80 |
|*  4 |     INDEX RANGE SCAN               | WIP_DJ_CLOSE_TEMP_N1         |   798 |
|   5 |    PARTITION RANGE ITERATOR        |                              |     2 |
|*  6 |     INDEX RANGE SCAN               | MTL_MATERIAL_TRANSACTIONS_N2 |     2 |
|*  7 |   TABLE ACCESS BY LOCAL INDEX ROWID| MTL_MATERIAL_TRANSACTIONS    |     1 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("WDCT"."ORGANIZATION_ID"=:ORGANIZATION_ID)
   4 - access("WDCT"."GROUP_ID"=:GROUP_ID)
   6 - access("WDCT"."WIP_ENTITY_ID"="MMT"."TRANSACTION_SOURCE_ID" AND "MMT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND
              "MMT"."TRANSACTION_DATE">"WDCT"."ACTUAL_CLOSE_DATE")
   7 - filter("MMT"."TRANSACTION_SOURCE_TYPE_ID"=5)

Notice the estimated rows calculated by the optimizer for the driving Index and Table. In clone db, the optimizer says 798 rows from an Index TEMP_N1 and then 80 Rows from the Table Access component. On production, the optimizer computes 1552 rows from the driving Index TEMP_N1 and then 155 rows from the Table Access. The optimizer further computes that 155 Iteration of MTL_MATERIAL_TRANSACTION (Nested Loop) will be a costly affair and therefore opts for a Hash Join. Since the clone db was 2 months old, the statistics were also old. Once I was convinced that estimated cardinality for the driving table seems to be an issue, I imported the production stats to clone and simulated the issue. I imported the stats of only the WIP_DJ_CLOSE_TEMP table on clone and could simulate the performance impact. The Table and relevant Column Level statistics of WIP_DJ_CLOSE_TEMP is pasted below. I have removed unwanted column stats from this post. This is from the clone and production.

Clone Stats - Performance Optimal

SQL> @table_stats
Enter value for 1: WIP_DJ_CLOSE_TEMP
old   2: where table_name='&1'
new   2: where table_name='WIP_DJ_CLOSE_TEMP'

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO T
------------------------------ --- ---------- ---------- --------- --- -
WIP                            NO      118836        829 26-SEP-10 YES N

Elapsed: 00:00:00.23
SQL> @column_stats
Enter value for 1: WIP
old   3: where owner='&1'
new   3: where owner='WIP'
Enter value for 2: WIP_DJ_CLOSE_TEMP
old   4: and   table_name='&2'
new   4: and   table_name='WIP_DJ_CLOSE_TEMP'

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
GROUP_ID                                149          0 .006711409 NONE
ORGANIZATION_ID                          10          0         .1 NONE

Production Stats - Performance sub-optimal

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
WIP                            NO      116383        829 28-NOV-10 YES

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
GROUP_ID                                 75          0 .013333333 NONE
ORGANIZATION_ID                          10          0         .1 NONE

Index Details of MTL_MATERIAL_TRANSACTIONS

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
MTL_MATERIAL_TRANSACTIONS_N2   TRANSACTION_SOURCE_ID                        1
                               ORGANIZATION_ID                              2
                               TRANSACTION_DATE                             3

MTL_MATERIAL_TRANSACTIONS_N8   TRANSACTION_SOURCE_TYPE_ID                   1
                               ORGANIZATION_ID                              2
                               TRANSACTION_DATE                             3
                               TRANSACTION_SOURCE_NAME                      4

Issue

As mentioned at the start of this post, while the statistics are relevant for selection between an Index Scan and Table Scan and in case of Index Scan, which Index to select; the most important factor is the Computed Cardinality of the driving table as this has an impact on the Join Order and Join Type. Further, the Join Type dictates the selection of an Index for the other tables. The difference between the Nested Loop Join and Hash Join, in terms of Index Selection is : Nested Loop Join will select rows from the driving table and then use an Index on the Join Column, if it exists (else it will be an hash join), of the second table. Therefore, in case of a good plan, with the Nested Loop Join in place, the optimizer opted for N2 Index of MTL_MATERIAL_TRANSACTION table which is an Index on Join Column. In case of Hash Join, the Optimizer will try to filter out as many rows as possible for both the tables using an Index on filter predicates and once the rows are filtered, will join these using hash function on the Join Columns. Therefore, in case of the Hash Join, the index N8 of MTL_MATERIAL_TRANSACTION is on filter column and then the rows filtered were joined using hash function on the Join Column. See the PREDICATE INFORMATION for both the Good and Bad Plans above and the Index Column details for MTL_MATERIAL_TRANSACTION pasted above.

The Calculation : Clone Old Stats

Let us get into the computed cardinality calculation for the driving table, as this had an impact on the Join Type. On clone the number of Rows are : 118836 and the WHERE predicate for this table is as under :

WHERE	wdct.GROUP_ID = :b1             
AND	wdct.ORGANIZATION_ID = :b2

The Table has an Index on GROUP_ID column and the calculation says 118836*1/149 = 797.55, rounded off to 798. Further, it has to filter based on non-indexed column, which is Organization_ID and the calculation says 798*1/10=79.8 rounded off to 80. The clone db (good) plan shows the same.

The Calculation : Production Stats

On Production, the computed cardinality calculation for the index scan step is 116383*1/75 = 1551.77, rounded off to 1552. Further, the filter based on non-indexed column is 1552*1/10 = 155.2, rounded off to 155. The production plan shows the same.

In the case of production statistics, the statistics were collected on Sunday and this triggered the change in plan. Further, these stats were accurate, even then, the optimizer came out with a plan that was sub-optimal. As mentioned, we could simulate the issue on Clone DB after importing the statistics of the driving table – WIP_DJ_CLOSE_TEMP. As a resolution and to check the impact, we modified the column level statistics of GROUP_ID column on clone db to 200 and the plan changed to good.

As the name of the driving table implies – WIP_DJ_CLOSE_TEMP, this is an interface table and the rows are deleted and inserted into this table. The table usually contain More or Less 0.1 Million Rows. Therefore, as a final action, once the column level statistics for GROUP_ID column was fixed on production, we locked the statistics of this table. This was to ensure that the plan of this query will not be impacted after periodic statistics generation and remains stable.

After manually setting the column level statistics for GROUP_ID column to 200, the plan is as under :

Plan hash value: 569619275

-----------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |       |
|   1 |  NESTED LOOPS                      |                              |       |
|   2 |   NESTED LOOPS                     |                              |    18 |
|*  3 |    TABLE ACCESS BY INDEX ROWID     | WIP_DJ_CLOSE_TEMP            |    58 |
|*  4 |     INDEX RANGE SCAN               | WIP_DJ_CLOSE_TEMP_N1         |   582 |
|   5 |    PARTITION RANGE ITERATOR        |                              |     2 |
|*  6 |     INDEX RANGE SCAN               | MTL_MATERIAL_TRANSACTIONS_N2 |     2 |
|*  7 |   TABLE ACCESS BY LOCAL INDEX ROWID| MTL_MATERIAL_TRANSACTIONS    |     1 |
-----------------------------------------------------------------------------------

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

   3 - filter("WDCT"."ORGANIZATION_ID"=:ORGANIZATION_ID)
   4 - access("WDCT"."GROUP_ID"=:GROUP_ID)
   6 - access("WDCT"."WIP_ENTITY_ID"="MMT"."TRANSACTION_SOURCE_ID" AND "MMT"."ORGANIZATION_ID"=:ORGANIZATION_ID AND
              "MMT"."TRANSACTION_DATE">"WDCT"."ACTUAL_CLOSE_DATE")
   7 - filter("MMT"."TRANSACTION_SOURCE_TYPE_ID"=5)

Production Stats - Performance sub-optimal

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
WIP                            NO      116383        829 28-NOV-10 YES

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
GROUP_ID                                200          0 .013333333 NONE
ORGANIZATION_ID                          10          0         .1 NONE

The calculation now stands as Index Step : 116383*1/200 = 581.91, rounded off to 582. Table Access : 582*1/10 = 58.2, rounded off to 58. This resolved the issue.

In this scenario, Investigating the statistics on MTL_MATERIAL_TRANSACTIONS table would have resulted futile,as this change was just a symptom due to the problem with stats on the driving table.

Cardinality Feedback ! 11g New Auto Optimization feature..

Few days back, I was working on a minor performance issue post 11g upgrade. The reason I mention minor issue because the customer did extensive testing, as a result the performance and execution plan of all, except 1 or 2, queries were optimal. While I was working on the optimizer behaviour in 11g that would have caused this plan change for the two sql’s, I came across “Cardinality Feedback used for this statement” message just under the runtime plan. This was something new to me and therefore, I started my investigation on this. While I was still on the job of investigation, the onsite dba asked me whether I have made some changes at the db level. He wanted to confirm this because all of a sudden the Execution Plan for these 2 queries changed from worse to good. This was a surprise. This change in plan was due to the Cardinality Feedback, introduced in 11g.

Today, I was at another customer site for a small round of discussion when someone mentioned that post 11g Upgrade, one of their batch processes takes longer and they have found out that the plan for one main query has changed for bad. I logged in to the system and again noticed “Cardinality Feedback used for this statement” message. I made a small change in the query (this change was just a change of select to Select) so that it is hard parsed. The Query came out in less than a minute, which was the case earlier i.e 10g. I re-executed the query and this time I had to cancel it after 10 minutes. The plan changed at runtime and this change made this query change the execution time from less than a minute to more than 10 minute and still executing. This time, at this customer site, the runtime plan change was from good to bad.

What is Cardinality Feedback ?

As of now, there is not much documentation available on this optimization strategy or feature and therefore, whatever I write on this is purely a guesswork. I am still investigating on this and something, if available, will feature in my next blogs.

I assume that the strategy works on the internal mechanism of GATHER_PLAN_STATISTICS hint that calculates the Estimated Rows and Actual Rows. In this case, when the Query is Hard Parsed, the optimizer generates an execution plan based on the estimated rows. As usual, this estimation is from the object and column statistics. At the end of the query execution, the Optimizer also retains the Actual rows fetched by each of the steps. If a huge difference is observed between the two statistics (E-Rows and A-Rows), during subsequent executions of the same query, the optimizer generates another child cursor with a new execution plan.

This optimization strategy is controlled by an underscore parameter _optimizer_use_feedback that defaults to true.

Cost based Optimizer ! Inefficient input yields inefficient Output…

Since the time my old blog went inaccessible, and still is, I have been receiving several mails on my previous blogs that helped some of my readers relate and resolve their performance issues. They wanted access to my previous blogs and I have been requesting them to let me know any particular blog that was of interest and I may publish them right here. There were many requests to publish blogs on Cost Based Optimizer and therefore, thought of publishing some of them. While these blogs may not be relevant for the current versions of Oracle, there are still some production systems that are still on Oracle 8i and 9i, and these blogs may be of help for those instances. Wherever any of the Cost Based Optimization blog that are relevant for certain versions of Oracle, I will specifically mention them for the benefits of the readers.

This blog is relevant for Oracle 8i and 9i (without System Statistics)

Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers. The calculation made by the Optimizer to come-up with an Execution Plan has been doubted by many of the Oracle users, especially, when (to name a few) :

  1. The Application Performance Degrades after Version Upgrade
  2. Plan of a Query change after gathering of fresh Statistics, and
  3. The Performance of a Query with higher cost is much better that the plan with a least cost

Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade and leave no room with rigorous testing of the application before production upgrade. It is fact no.2 and 3 that raises a doubt over Cost Based Optimization.

Cost Based Optimizer is a piece of code, wherein, you provide Inputs, by way of Optimizer Parameters, Statistics and it computes and generates an Output which is seen as an Execution Plan. It is not only the Optimizer Parameters and Statistics, which is passed as an Input. There is one more component, which is mostly overlooked, a QUERY. The initial input passed is the Query and based on the way it is written, optimizer does its cost calculation by taking into account the other two : Parameters and Statistics. In this blog, I will address the last two facts stated above, plus, a common misconception that Index Scan is better that Full Table Scan and finally, the issue with the critical first component passed as an input to CBO, A Query.

Recently, was working on a performance issue, related to a critical process. The overall performance of the system was OK, but one critical process started taking 18-20 hours, failing several times with ora-1555. This process, usually takes 1 hour 30 minutes. Therefore, all of a sudden, why this degradation ?, was the question raised by the customer.

The Database version is 8174. This is one of a critical process and a part of seeded application. It connects to a user, dedicated for this process, therefore, when this process is running, it is very easy to get the sid of the session executing this process. While monitoring the performance of this process, by querying v$session_wait for the sid, I could see one particular query doing exorbitant amount of Logical Reads. The text of the query and execution plan is as under :

explain plan for
select CS.CO_ID ,CS.SPCODE ,CS.SNCODE ,CS.TMCODE ,CS.CS_SEQNO ,CS.CS_STAT_CHNG ,
to_char(CS.TRIAL_END_DATE,'YYYYMMDDHH24MISS')
from       CR_SERVICES CS
where      (CS.CO_ID not  in (select CO_ID
                             from CO_ALL
                             where (CO_ID=CS.CO_ID and CUSTOMER_ID=DEALER_ID))
and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (SNCODE=CS.SNCODE and RATING_IND='Y')));

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 141575 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   NESTED LOOPS            |                    |     1K|   77K| 141575 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS BY INDEX R|CR_SERVICES         |     3M|  120M|   1913 |       |       |
|     INDEX RANGE SCAN      |I1_CR_SERVICES      |     3M|      |   1616 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

There are three tables in the query. The Driving Table, from the explain plan, MPV is a small table with 220 rows and 80 Blocks, while the other two are huge tables with 69 Million and 30 Million Rows. Usually, such situation can crop-up due to following reasons :

  1. Change in Optimizer Parameters
  2. Change in Statistics
  3. Creation of New Index

Initial Investigation revealed that the Statistics of CR_SERVICES table and indexes were gathered the previous night. Once this was confirmed, Cost Based Optimization was at the receiving end. This is quite natural as without any change in the application query, the plan changed for bad, due to CBO. Therefore, thorough investigation was required about the cause of this plan change. Seeing the query and other optimizer statistics, Full Table Scan on CR_SERVICES would have been a better choice and therefore, my investigation was around this. Let us revisit the relevant portion of the Execution Plan, the statistics and the Query.

## Table Statistics of CR_SERVICES

OWNER                          PAR   NUM_ROWS     BLOCKS 
------------------------------ --- ---------- ---------- 
SM                             NO    69875510    1107733 

## Column Stats of the Columns used on the Query

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ ------------ ---------- ----------
CO_ID                               6863377          0 1.4570E-07
SNCODE                                  370          0 .002702703  @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
|   NESTED LOOPS            |                    |     1K|   77K| 141575 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS BY INDEX R|CR_SERVICES         |     3M|  120M|   1913 |       |       |
|     INDEX RANGE SCAN      |I1_CR_SERVICES      |     3M|      |   1616 |       |       |

The Query is using an Index on SNCODE column, which is a very low cardinality column. Therefore, this was my primary target and I worked on the Optimizer Calculation to get the possible cause of this Index Scan.

Optimizer Calculation


The Cost Calculation of an Index Scan and Table Scan via Index is

Index Cost = Blevel+ceil(leaf_blocks*ix_selectivity) 
Table Scan = ceil(clustering_factor*table_selectivity) 

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370) from dual;
Index Cost = 1616 ## This matches the execution plan
Table Scan = 109533
Final Cost = 1616+109533=111149

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370*1/370) from dual;
Index Cost = 1616
Table Scan = 297
Final Cost = 1616+297=1913 ## This Matches with the Cost calculated by the Optimizer

This additional 1/370, was due to the fact that SNCODE column is used twice in the query. One for Index Filter, therefore considered only once for an index cost, and twice for Table cost due to SNCODE=CS.SNCODE join. The final Nested Loop Cost is 141575 and this is calculated as

NL Cost = Cost of an Outer Table + (Cost of Inner Table * Cardinality of Outer Table)
NL Cost = 13 + (1913*74)
NL Cost = 141575

Cost of Full Table Scan = Num Blocks / 6.59 (8k block size)
Cost of Full Table Scan = 1107733 / 6.59
Cost of Full Table Scan = 168093.02

Clearly, the cost of a Full Table Scan is higher than the Nested Loop Join and optimizer opted for the cheaper costed plan, which is, a Nested Loop Join and this requires an Index Scan of an Inner Table. When I checked the execution plan of this query with a FULL hint on CR_SERVICES, the FTS cost was 168200. I am yet to figure out the difference between my calculation and optimizer calculation, but for investigation purpose, I considered the calculation done by the optimizer. Based on this calculation, I came out with three possible solutions, these are :

  1. Modify the Query (Immediately Not possible, as it is a seeded query)
  2. Change Optimizer Statistics
  3. Change Optimizer Parameters

The first optimization was a better and safe approach, but was not possible immediately. The other two required some testing, as change in Optimizer Statistis can have negative impact on other queries using these objects and, change in optimizer parameters at db level can have an global impact on the overall performance. As mentioned earlier, this process connects to a user dedicated for this process, therefore, ON-LOGON trigger with the Optimizer Parameter change at session level was thought of as a better option without impacting overall application performance. The parameter, I could immediately think of was, OPTIMIZER_INDEX_COST_ADJ. This parameter was default i.e.100. For this query, to force Index Cost be Costlier than FTS, the calculation is OICA = (FTS Cost/NL Cost)*100 = (168200/141575)*100 = 118.80. Any value above this, would compute the NL cost be costlier than Full Table Cost and would opt for FTS and Hash Join. Initially, the Change was done at session level and the plan changed.

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 168219 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   HASH JOIN               |                    |     1K|   77K| 168219 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS FULL      |CR_SERVICES         |     3M|  120M| 168200 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

Finally, ON-LOGON trigger was created to set this parameter to 120, whenever, this user logged on to the database. The process, when re-executed, completed in 1 Hour 15 minutes and the desired performance was achieved.

Optimizer Calculation with OICA as 120


NL Table Scan Cost = (Current Cost of Inner Table Scan * Outer Table Card)*OICA/100
NL Table Scan Cost = (1913*74)*120/100
NL Table Scan Cost = (1913*74)*120/100
NL Table Scan Cost = 169874.4 > 168200 (FTS)

With this setting, the plan changed. Now for the reverse engineering. I wanted to check for any of the Index Statistics, say Clustering Factor, that would have calculated NL Join costlier than Full Table Scan. The Cost of NL Join with OICA is 169874.4. The cost of Table Scan via Index should be round(169874.4/74), which is 2296. Index Scan Cost is 1616, so the Table Scan cost component should be 2296-1616 is 680. Based on this, I calculated that if the Clustering Factor is changed to 93092000, then without any parameter setting, the query would go for a Full Table Scan. 

SQL> exec dbms_stats.SET_INDEX_STATS('SM','I1_CR_SERVICES',CLSTFCT=>93092000);

PL/SQL procedure successfully completed.

SQL> @index_stats
Enter value for o: SM
old   3: where table_owner='&O'
new   3: where table_owner='SM'
Enter value for t: CR_SERVICES
old   4: and   table_name='&T'
new   4: and   table_name='CR_SERVICES'

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
I1_CR_SERVICES                   70724121          3      596745      11207514          93092000 

12 rows selected.

explain plan for
select CS.CO_ID ,CS.SPCODE ,CS.SNCODE ,CS.TMCODE ,CS.CS_SEQNO ,CS.CS_STAT_CHNG ,
to_char(CS.TRIAL_END_DATE,'YYYYMMDDHH24MISS')
from       CR_SERVICES CS
where      (CS.CO_ID not  in (select CO_ID
                             from CO_ALL
                             where (CO_ID=CS.CO_ID and CUSTOMER_ID=DEALER_ID))
and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (SNCODE=CS.SNCODE and RATING_IND='Y')));

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 168219 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   HASH JOIN               |                    |     1K|   77K| 168219 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS FULL      |CR_SERVICES         |     3M|  120M| 168200 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

11 rows selected.

Back to the problem, does it look like to be an Optimizer Issue ? This table has a very high growth rate. While the num_blocks value of a table grows faster, the statistics of Leaf Blocks, BLevel and Clustering Factor does not change significantly. This is enough to blow up the cost of a Full Table Scan. If you see the query and the relevant portion of the query, pasted above, the join on SNCODE is really not required as it uses an IN clause. This unwanted join, forced the optimizer to consider the Density of SNCODE twice in Table Scan cost calculation.

## Relevant portion of the Query and Plan

and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (SNCODE=CS.SNCODE and RATING_IND='Y')));

## Optimizer Calculation (1/370 is considered twice to get Table Scan cost of 297, thus making it cheaper).
Index Cost = Blevel+ceil(leaf_blocks*ix_selectivity) 
Table Scan = ceil(clustering_factor*table_selectivity) 

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370*1/370) from dual;
Index Cost = 1616
Table Scan = 297
Final Cost = 1616+297=1913 

If this portion of the query is modified as :


and        CS.SNCODE in (select distinct SNCODE  from MPV
                     where (RATING_IND='Y')));

## Optimizer Calculation, with this change
Index Cost = Blevel+ceil(leaf_blocks*ix_selectivity) 
Table Scan = ceil(clustering_factor*table_selectivity) 

select 3+ceil(596745*1/370) "Index Scan Cost", ceil(40527179*1/370) from dual;
Index Cost = 1616
Table Scan = 109533
Final Cost = 1616+109533= 111149

## With this Cost, the cost of NL Join would have been

NL Cost = Cost of an Outer Table + (Cost of Inner Table * Cardinality of Outer Table)
NL Cost = 13 + (111149*74)
NL Cost = 8225039 > 168200 (FTS)

One unwanted Join Condition made Cost based Optimizer to come up with an in-efficient plan. Therefore, as mentioned earlier, my Initial and Crucial input to the optimizer is a Query and if these are not optimally written, Cost Based Optimizer is bound to compute an sub-optimal plan. The Query, modified by removing unwanted join and no parameter change or statistics change, gave the desired and better plan (see below ).

explain plan for
select CS.CO_ID ,CS.SPCODE ,CS.SNCODE ,CS.TMCODE ,CS.CS_SEQNO ,CS.CS_STAT_CHNG ,
to_char(CS.TRIAL_END_DATE,'YYYYMMDDHH24MISS')
from       CR_SERVICES CS  
where      (CS.CO_ID not  in (select CO_ID
                               from CO_ALL
                               where (CO_ID=CS.CO_ID and CUSTOMER_ID=DEALER_ID))
and        CS.SNCODE in (select distinct SNCODE  from MPV
                       where (RATING_IND='Y')));

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1K|   77K| 168219 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   HASH JOIN               |                    |     1K|   77K| 168219 |       |       |
|    TABLE ACCESS FULL      |MPV                 |    74 |  444 |     13 |       |       |
|    TABLE ACCESS FULL      |CR_SERVICES         |     3M|  120M| 168200 |       |       |
|   FILTER                  |                    |       |      |        |       |       |
|    INDEX RANGE SCAN       |IDX_CUST_CO_DEALER  |     1 |   14 |      2 |       |       |
--------------------------------------------------------------------------------

Many developers are of the Opinion that an Execution Plan with cheaper cost is better than a Plan with higher cost, and Index Scans are better than Full Table Scans. This blog demonstrates that these are just misconceptions and it is the application design and knowledge that should be taken into account while writing an effecient code.

To sum up, it is just not the Optimizer Statistics and Parameter that control the Cost Based Optimizer, Queries play an important role too. A well written code should never bother or doubt about Cost Based Optimization.

PL/SQL Parallelism made easier in 11g

Today was the first day of the two day AIOUG Event Sangam 2010. Jonathan Lewis was the Key Speaker this year and I presented a session on “Developing a Scalable Application”. One of my slide mentioned about PL/SQL Parallelism. This is in continuation to my previous blog where I posted about Manual Parallelism that takes the ROWID’s of the table and distributes it across multiple chunks. The limitation with Manual Parallelism is that even though a pl/sql block needs to process 5% to 10% of the rows from a huge table, the Manual Parallelism will select and split the ROWIDs of the entire table. This means, many of the chunks may process very less data or almost zero data.

Oracle Database 11g Release 2 introduced a package dbms_parallel_execute, that makes it easier to split a table into multiple chunks, based on ROWID’s or SQL or by Number Column. During my AIOUG session, I demonstrated the use of this package to split a table into multiple chunks and schedule a batch process in parallel. The best part of this package is that the chunks can be split based on a condition and only the chunks that contain to-be-processed data will be created. Thus the rows to be processed by each of the chunks will have uniform distribution.

As mentioned in my earlier blog, I will reiterate the benefit of pl/sql parallelism, which is, optimal use of available resources. In this blog, I will post the test case that I executed during the AIOUG session and this should help you reproduce this in your environment.

In this example, consider a Nightly Batch Job NIGHTLY_JOB and this job processes the rows from VIVEK_TEST Table using an approach which is widely implemented by the developers or the dba’s. This is single job processing the required rows and thus is a very slow and an inefficient approach. There are 7200 rows in this table to be processed and assuming that the business logic in the pl/sql takes atleast 1 second and then moves on to another row. Which means, at a minimum, each row to be processed will take atleast 1 second, thus the entire process will take atleast 7200 second (2 Hours). This is the code of a batch process :

create table vivek_test pctfree 50 pctused 50 as
select * from all_objects a
where rownum=1000 then
    commit;
    l_limit:=0;
  end if;
  end loop;
end;
/
exec nightly_batch;

Once the job is executed, you can monitor the progress of this batch process from another session. V$SESSION will report the progress in MODULE column, as posted by dbms_application_info procedure.

The changes required to parallize the process is pasted below. To monitor the progress and maintain the log of each of the CHUNKS, I have created a process_status table. One thing worth noticing is the PCTFREE and PCTUSED setting, which is set to 50. The reason I have this setting is that the number of chunks to be created is dependant on the number of extents in the table and I wanted multiple extents for my table. In a production environment, number of extents should not be an issue.

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 or replace procedure parallel_job(chks in number) is
l_statement	varchar2(1000);
l_jobno		number;
l_retry		number;
l_status	   number;
l_chunkid	   number;
l_srowid	   rowid;
l_erowid	   rowid;
l_anyrows   	boolean;
l_errcode	   pls_integer;
begin

  execute immediate 'truncate table process_status';
  /* CREATE A PARALLEL TASK */
  dbms_parallel_execute.create_task('NIGHT_JOB');

  dbms_parallel_execute.create_chunks_by_rowid('NIGHT_JOB','VIVEK','VIVEK_TEST',true, chks);
 /* INSERT THE CHUNKS INTO PROCESS_STATUS TO LOG THE STATUS OF EACH JOB */
  insert into process_status(process_name, job_id, s_rowid, e_rowid)
  select 'NIGHT_JOB', chunk_id, start_rowid, end_rowid from user_parallel_execute_chunks
  where task_name='NIGHT_JOB' order by 1;
  commit;
  /* Run the Procedure here */
  LOOP
      -- Get a chunk to process; if there is nothing to process, then exit the 
      -- loop;
      DBMS_PARALLEL_EXECUTE.GET_ROWID_CHUNK('NIGHT_JOB',l_chunkid, l_srowid, l_erowid,l_anyrows);
      IF (l_anyrows = false) THEN EXIT; END IF;
      BEGIN
	l_statement:='nightly_batch('||l_chunkid||','||''''||l_srowid||''''||','||''''||l_erowid||''''||');';
	dbms_job.submit(l_jobno,l_statement);
	update process_status set completed_yn='P', start_time=sysdate where job_id=l_chunkid;
	commit;
        DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('NIGHT_JOB',l_chunkid,DBMS_PARALLEL_EXECUTE.PROCESSED);
      EXCEPTION WHEN OTHERS THEN
        DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('NIGHT_JOB', l_chunkid,
          DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR, SQLCODE, SQLERRM);
	  l_errcode:=sqlcode;
  	update process_status set completed_yn='F', end_time=sysdate, status='FAILED WITH ERROR '||l_errcode||'.' where job_id=l_chunkid;
      END;
  commit;
  END LOOP;
  dbms_parallel_execute.drop_task('NIGHT_JOB');
end;
/

The only change required in our NIGHTLY_BATCH is the introduction of INPUT parameters and update of the process_status at the completion or failure of a chunk.

create or replace procedure nightly_batch(jobid number, low_rowid rowid, high_rowid rowid) is
l_count		number:=0;
l_module	    varchar2(30);
l_limit		number:=0;
l_errcode	    pls_integer;
cursor c_main is
select rowid, owner, object_name, object_type from vivek_test
where nvl(processed_flag,'N')='N'
and	rowid between low_rowid and high_rowid;
begin
  for i in c_main
  loop
  l_count:=l_count+1;
  l_limit:=l_limit+1;
  l_module:=l_count||' out of 7200/40=180';
  dbms_application_info.set_module('PL_SQL',l_module);
  dbms_lock.sleep(1); /* Assuming Processing of a row takes 1 second */
  update vivek_test set processed_flag='Y' where rowid = i.rowid;
  if l_limit>=1000 then
    commit;
    l_limit:=0;
  end if;
  end loop;
  update process_status set completed_yn='Y', end_time=sysdate, processed_rows=l_count 
  where job_id = jobid;
  commit;
  exception when others then
  l_errcode:=sqlcode;
  update process_status set completed_yn='F', status='FAILED WITH '||l_errcode||'.' where job_id = jobid;
  commit;
end;
/
exec parallel_job(100);

Monitor the progress of this job and you would see a significant improvement as the job completes in less than 5 minutes, as against 2 Hours, and utilizes the hardware resources optimally.

Manual Parallelism – Rowid Based for Partitioned / Sub Partitioned Tables

Currently, I am working on an optimization project where few Scheduler Jobs are Scheduled and Run every night at 9:00 pm using dbms_scheduler. These jobs are then stopped every day morning 9:00 a:m, so that, these do not impact the OLTP sessions. Customer has implemented VPD and each of these job is scheduled from multiple users, so that, the records are processed based on the policy function and the access predicates for that user. The flaw with this logic is that the total rows to be processed across each of the users are not uniform, which means, if a job for all the users takes 45-50 minutes, one user processing takes almost 3-4 hours.

For optimizing these, the first thought came to the mind was to distribute the rows to be processed uniformly across each of the jobs and then to apply vpd predicates at runtime. This required splitting of the main processing cursor into multiple chunks. Therefore, the table involved in the main cursor query was taken out with an intention to use ROWID Manual Parallelism, introduced by Thomas Kyte. This would help us distribute the rows uniformly across each of the jobs with an efficient use of available resources. I demonstrated a case study on one of my previous blog, where I used this to purge data from a table. The link is as under :

I have come across many pl/sql procedures that processes data and take certain amount of time that can be brought down. Moreover, whenever these procedures are scheduled or run, the CPU Utilization is very less. I remember a discussion with a customer, wherein, they mentioned that one of my “Annual Interest Calculation” job is running very slow and the CPU is 90 to 95% IDLE. This is an expected behaviour as a job, when run as a single process, will be scheduled on only one Processor and will not take the advantage of available resources. Therefore, splitting the job into multiple chunks should help in reducing the process completion time by way of better resource utilization.

As an example, a pl/sql block, as mentioned below, can be optimized and run in parallel. If a single job process 10000 Rows and takes 30 minutes, running it into 10 parallel stream should bring it down to 3-5 minutes.

create procedure test_job as
cursor main is
select column1, column2, column3
from	test
where x=1
and   y=2;
begin
  for i in main
   loop
     some processing;
   end loop;
end;


create procedure test_job(job_id number, low_rid rowid, hi_rid rowid) as
cursor main is
select column1, column2, column3
from	test
where x=1
and   y=2
and   rowid between low_rid and hi_rid;
begin
  for i in main
   loop
     some processing;
   end loop;
end;

This change may require minor changes in the procedure which is worth implementing.

Back to the Original case study. As mentioned earlier, for splitting the rows into multiple chunks, I executed the Query on dba_extents and dba_objects, to get the Start and End ROWID based on the number of chunks. This Query usually takes 2-3 minutes, but was taking almost 45-50 minutes and had to be cancelled. I checked the plan of this Query and it was doing a Merge Join Cartesian. Further digging into the issue, I could notice that the table which was to be splitted was a partitioned and subpartitioned table. This was for the first time I faced this issue and therefore had to modify the logic of the Query to accomodate Partitioned and Sub-Partitioned Objects.

I created a pl/sql block to check whether the table is a Non-Partitioned or Partitioned or Sub-Partitioned and based on this, a loop is executed. Below is the code that accomplished this task for me. An explanation on this is as under :

  1. Input Values are Table_Owner, Process_name, Table_name.
  2. L_CHKS, in my case, is a Global Variable declared in Package specification and defaults to the number of chunks to be created.
  3. For a Partitioned or Sub-Partitioned Objects, the number of chunks will be more than L_CHKS, therefore, at the end of the loop, these are updated again using mod function.
  procedure manual_parallelism(t_owner varchar2, p_name varchar2, t_name varchar2) as
   /* l_chks is dynamic and should be based on available CPU's and is standard for all the jobs */
   /* This value can be changed at later stage if the hardware is upgraded */
   l_part	varchar2(3);
   l_subpart	varchar2(1);
   part_name	varchar2(30);
   l_dataobjectid number;
   cursor partitioned(t_owner varchar2, t_name varchar2) is
   select partition_name from dba_tab_partitions
   where table_owner = t_owner
   and   table_name = t_name
   order by partition_position desc;

   cursor sub_partitioned(t_owner varchar2, t_name varchar2, part_name varchar2) is
   select subpartition_name from dba_tab_subpartitions s
   where s.table_owner = t_owner
   and   s.table_name = t_name
   and   s.partition_name = part_name;
  begin
   /* Assumption in this case is that at any given point in time l_chks Jobs can be easily scheduled */
     select partitioned into l_part from dba_tables where owner=t_owner and table_name=t_name;
     if l_part='YES' then
       begin
	   select 'Y' into l_subpart from dba_tab_subpartitions 
	   where	table_owner=t_owner 
	   and		table_name=t_name
	   and		rownum=1;
       exception when no_data_found then
	   l_subpart:='N';
       end;
       if l_subpart='Y' then /* if Sub Partitioned */
	   for i in partitioned(t_owner, t_name)
	   loop
	     for j in sub_partitioned(t_owner, t_name, i.partition_name)
	     loop
	     insert into ast.process_status(process_name, job_id, s_rowid, e_rowid)
	     select p_name, grp,
  		   dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
		   dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
	     from (
		   select distinct grp,
		   first_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_fno,
		   first_value(block_id) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_block,
		   last_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_fno,
		   last_value(block_id+blocks-1) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_block,
		   sum(blocks) over (partition by grp) sum_blocks
		   from (
		   select	relative_fno,
				block_id,
				blocks,
				trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
				(sum(blocks) over ()/l_chks) ) grp
		   from		dba_extents
		   where	segment_name = t_name
		   and		owner = t_owner
		   and		partition_name=j.subpartition_name
		   order by block_id)
		   ),
	     (select data_object_id from dba_objects 
	     where	object_name = t_name 
	     and	subobject_name=j.subpartition_name 
	     and	owner=t_owner);
	     end loop; /* End loop of Sub Partitioned cursor */
	   end loop; /* End loop of Partitioned Cursor */
	   commit;
       else /* it means only Partitioned no Sub Partitions */
	   for i in partitioned(t_owner, t_name)
	   loop
	     insert into ast.process_status(process_name, job_id, s_rowid, e_rowid)
	     select p_name, grp,
		   dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
		   dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
	     from (
		   select distinct grp,
		   first_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_fno,
		   first_value(block_id) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) lo_block,
		   last_value(relative_fno) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_fno,
		   last_value(block_id+blocks-1) over (partition by grp order by relative_fno, 
		   block_id rows between unbounded preceding and unbounded following) hi_block,
		   sum(blocks) over (partition by grp) sum_blocks
		   from (
		   select	relative_fno,
				block_id,
				blocks,
				trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
				(sum(blocks) over ()/l_chks) ) grp
		   from		dba_extents
		   where	segment_name = t_name
		   and		owner = t_owner
		   and		partition_name=i.partition_name
		   order by block_id)
		   ),
	     (select data_object_id from dba_objects 
	     where	object_name = t_name 
	     and	subobject_name=i.partition_name 
	     and	owner=t_owner);
	   end loop; /* End loop of Partitioned Cursor */
	   commit;
       end if; /* End if of l_Subpart='Y' */
     else /* Insert Statement for Non-Partitioned Object */ 
  	   insert into process_status(process_name, job_id, s_rowid, e_rowid)
	   select p_name, grp, 
		   dbms_rowid.rowid_create(1,data_object_id, lo_fno, lo_block,0) min_rid,
		   dbms_rowid.rowid_create(1,data_object_id, hi_fno, hi_block,0) max_rid
	   from (
	   select 	distinct grp,
			first_value(relative_fno) over (partition by grp order by relative_fno, 
			block_id rows between unbounded preceding and unbounded following) lo_fno,
			first_value(block_id) over (partition by grp order by relative_fno, 
			block_id rows between unbounded preceding and unbounded following) lo_block,
			last_value(relative_fno) over (partition by grp order by relative_fno, 
			block_id rows between unbounded preceding and unbounded following) hi_fno,
			last_value(block_id) over (partition by grp order by relative_fno, block_id 
			rows between unbounded preceding and unbounded following) hi_block,
			sum(blocks) over (partition by grp) sum_blocks from (
	   select 	relative_fno, block_id, blocks, 
			sum(blocks) over (order by relative_fno, block_id) cum_blocks,
			sum(blocks) over () tot_blocks,
			trunc((sum(blocks) over (order by relative_fno, block_id)-0.01) /
			(sum(blocks) over ()/l_chks)) grp
	   from		dba_extents
	   where 	segment_name=t_name
	   and		owner=t_owner
	   order by relative_fno, block_id)),
	   (select data_object_id from dba_objects where owner=t_owner and object_name=t_name);
	   commit;
     end if; /* END IF of L_PART='Y' */
     update ast.process_status set job_id=mod(rownum,l_chks) where process_name=p_name;
     commit;
  end manual_parallelism;

You can define the value of L_CHKS in the procedure and the procedure will split the table into multiple chunks with minimum group as 0 and maximum group as LCHKS-1. Once these chunks are created, additional intelligence can be built to schedule these L_CHKS jobs and spawn the new jobs based on the completion status of the running job. The Inputs to the Scheduled procedure will be the the Job_ID, which is GRP in our case, Starting Rowid, Ending Rowid, Process_Name. In my case, I scheduled 80 jobs at a time and wrote a logic at the end of the scheduled procedure wherein the process immediately starts the next job with the same job_id, which means, if a process with job_id 1 completes, it will spawn another job with job_id as 1 and so on. This will ensure that at any given point of time, there are 80 jobs running and no manual intervention is required.

Once this pl/sql parallelism was implemented, the total completion time of the process was around 30-35 minutes. Further, ROWID parallelism also ensured that there was no Block Level Contention between each of the jobs thus improving the performance.

Oracle 11g provides a package to accomplish this very easily using dbms_parallel_execute and developers may leverage the benefit of this readymade package to split a long running pl/sql block in parallel, thus improving the performance.

Optimizer Hint that Ignores Histogram and Column Stats

There was an Internal Forum and the question raised was “is there any optimizer hint that causes the optimizer to ignore histogram information ?” The question seemed to be interesting and thought of posting my reply to the question.

CARDINALITY hint is the one that directs the Optimizer to take the value of the Number of Rows to be processed by a Rowset or a Table. This hint is particularly usefull in Versions prior to 10g due to Optimizer Calculation mismatch with dependant columns. A quick demonstration below :

  1. I created a table vivek_test with 10000 rows
  2. Gathered Stats on this table, with Histogram on TEMPORARY column
  3. Then I executed the first query without any Hint and the Optimizer accurately calculated 53 rows for TEMPORARY=’Y’
  4. Then the second query with CARDINALITY hint and the Optimizer ignored histogram and column stats
create table vivek_test as
select * from all_objects where rownum<=10000;

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',null,null,null,'FOR ALL COLUMNS SIZE 1 FOR COLUMNS TEMPORARY SIZE 10');
SQL> select temporary, count(*) from vivek_test group by temporary;

T   COUNT(*)
- ----------
Y         53
N       9947

select /*+ NO_HINT */ owner, object_name
from vivek_test
where temporary='Y';

column sql_text for a50 wrap

select sql_id, sql_text, buffer_gets, executions, version_count
from	v$sqlarea
where	sql_text like 'select /*+ NO_HINT%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
6xhaxy9xb82zn select /*+ NO_HINT */ owner, object_name from vive         132          1             1
              k_test where temporary='Y'


Elapsed: 00:00:00.23

select plan_table_output from table(dbms_xplan.display_cursor('6xhaxy9xb82zn'));

SQL_ID  6xhaxy9xb82zn, child number 0
-------------------------------------
select /*+ NO_HINT */ owner, object_name from vivek_test where
temporary='Y'

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |    53 |  1431 |    85   (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"='Y')


19 rows selected.


select /*+ CARDINALITY(vivek_test,300) */ owner, object_name
from vivek_test
where temporary='Y';



select sql_id, sql_text, buffer_gets, executions, version_count
from	v$sqlarea
where	sql_text like 'select /*+ CARDINALITY(vivek_test,300%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
5xw7ckfhkz07p select /*+ CARDINALITY(vivek_test,300) */ owner, o         132          1             1
              bject_name from vivek_test where temporary='Y'


Elapsed: 00:00:00.02

select plan_table_output from table(dbms_xplan.display_cursor('5xw7ckfhkz07p'));

SQL_ID  5xw7ckfhkz07p, child number 0
-------------------------------------
select /*+ CARDINALITY(vivek_test,300) */ owner, object_name from
vivek_test where temporary='Y'

Plan hash value: 3275957777

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |    85 (100)|          |
|*  1 |  TABLE ACCESS FULL| VIVEK_TEST |   300 |  8100 |    85   (2)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"='Y')


19 rows selected.

Agenda for Sangam 2010, Hyderabad

Got couple of mails from my customer and many of these were interested on a detailed explanation on the optmization done at their site. While some of these will be covered during the presentation, some of these may miss out due to the time slot alloted to me. I have prioritized these optimizations and the interesting ones have been on my ppt’s.

In case, any one wants me to speak on a specific optimization feature, do update the blog (comment) and I would try to relate it to my Real Life Optimizations and present it with good examples.

One Interesting topic to be covered will be “Relation between Column Ordering of a Table and CPU time required to process a column”. This will also feature in an Oracle Connect Magazine to be circulated by AIOUG.

Next, I will also answer a common question raised by a Customer to Performance Tuning Specialist “Till Yesterday, the performance of the system was perfectly fine, How come the performance degraded all of a sudden ?”

Upgrade from 10g to 11g

For past few days, I was busy with critical upgrades of some mission critical databases from 10g to 11g. Apart from some minor issues, no performance issues have been reported post-upgrade. This should be a major relief for most of the customers planning to upgrade their system from 10g to 11g. Optimizer has not changed much between these two versions and therefore, unlike 9i to 10g, the upgrade from 10g to 11g should be smooth.

While comparing the Execution Plan of the critical queries during UAT testing, only one change observed is the introduction of Nested Loop Batching and this may change the plan hash value between the two versions. No performance degradation is observed with the introduction of NL Batching.

Post-Upgrade, it is now time to implement 11g new features and I may write on these, as and when, I have something interesting to share on these.