RSS Feed

Method R Discussion Group

To brief you, we have a data warehouse database running on Oracle 10.2.0.3. Mostly all the tables are partitioned by month and few have two partitions for a month.

Daily at night, data is populated from multiple feeder databases (Oracle, SQL Server, Main Frames, text files,....) into this database.

Whenever, batch loads data into new partition, it takes very long to complete. After a day or two the batch functions smootly, i.e. completes on time.

I have captured Execution Plans for data loads when the batch completes on time (i.e, at the month end) and when it suffers performance issues (i.e. loading new partitions).
I see the executions plans differ and the elapsed time differs in multiples as a result. Attached you can see the plans for the same query.

Following is the background information, I think I should share with you:

Database: Oracle 10g (10.2.0.3)
OS: IBM - Aix 5.3 - ML -5
Processors: 10 Physical (40 Logical)
Memory: 14 GB

Initialization Parameter that might be of significance:
CURSOR_SHARING = FORCE

Snapshot Interval: 30 Minutes

Table Name: Authorization
Paritions: 49 (13 partitions contain full months data and remaining partitions have only the last day's data)
Partitioned on: As_Of_Date (column)
Partition Type: One Gregorian Month
Tablesize: 13.5 GB
Active Partitions Size: 1 GB (for the 13 partitions)
Rows: 80 Million

Total Indexes: 3
Partitioned Indexes: 2
Global Indexes: 1

Index Name:    SYS_C0010238
Unique:        Yes
Partitioned:    No
Index Columns:  AUTH_SEQ_KEY & AS_OF_DATE
CF:            91452068
Blocks:        375419
Rows:          91452068
Size (GB):      5.75



Index Name:    IDX_AUTHORIZATION_1
Unique:        No
Partitioned:    Yes
Index Columns:  AS_OF_DATE
CF:            940360
Blocks:        153460
Rows:          81106590
Size (GB):      2.59

Index Name:    IDX_AUTHORIZATION_2
Unique:        No
Partitioned:    Yes
Index Columns:  CUST_CODE
CF:            42264920
Blocks:        116710
Rows:          79968760
Size (GB):      2.03



Below is the *GOOD* plan

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name        DB Id    Instance    Inst Num Release    RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
OFDMP        2543030685 OFDMP              1 10.2.0.3.0  NO  ofdmprd

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      4187 01-ÇÈÑêä -08 04:30:      98      2.9
  End Snap:      4188 01-ÇÈÑêä -08 05:00:      98      2.9
  Elapsed:              29.85 (mins)
  DB Time:              39.65 (mins)

SQL Summary                            DB/Inst: OFDMP/OFDMP  Snaps: 4187-4188

                Elapsed
  SQL Id      Time (ms)
------------- ----------
6k5jkqzvfz7gs      3,807
Module: uvsh.exe
UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = :B4 , OVER
STEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_OF_DATE = :B1

          -------------------------------------------------------------

SQL ID: 6k5jkqzvfz7gs                  DB/Inst: OFDMP/OFDMP  Snaps: 4187-4188
-> 1st Capture and Last Capture Snap IDs
  refer to Snapshot IDs witin the snapshot range
-> UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = ...

    Plan Hash          Total Elapsed                1st Capture  Last Capture
#  Value                    Time(ms)    Executions      Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1  1474318199                  3,807      22,5629          4188          4188
          -------------------------------------------------------------


Plan 1(PHV: 1474318199)
-----------------------

Plan Statistics                        DB/Inst: OFDMP/OFDMP  Snaps: 4187-4188
-> % Total DB Time is the Elapsed Time of the SQL statement divided
  into the Total Database Time multiplied by 100

Stat Name                                Statement  Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                            3,807            0.0    0.2
CPU Time (ms)                                3,807            0.0    0.4
Executions                                  225,629            N/A    N/A
Buffer Gets                                676,889            3.0    8.2
Disk Reads                                        1            0.0    0.0
Parse Calls                                      1            0.0    0.0
Rows                                              0            0.0    N/A
User I/O Wait Time (ms)                          7            N/A    N/A
Cluster Wait Time (ms)                            0            N/A    N/A
Application Wait Time (ms)                        0            N/A    N/A
Concurrency Wait Time (ms)                        0            N/A    N/A
Invalidations                                    0            N/A    N/A
Version Count                                    1            N/A    N/A
Sharable Mem(KB)                                22            N/A    N/A
          -------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT  |              |      |      |    3 (100)|          |
|  1 |  UPDATE            | AUTHORIZATION |      |      |            |          |
|  2 |  INDEX UNIQUE SCAN| SYS_C0010238  |    1 |    27 |    2  (0)| 00:00:01 |
------------------------------------------------------------------------------------



Full SQL Text

SQL ID      SQL Text
------------ -----------------------------------------------------------------
6k5jkqzvfz7g UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_A
            AMT = :B4 , OVERSTEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_O
            F_DATE = :B1





Here's what happens when data is populated into new partition:

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name        DB Id    Instance    Inst Num Release    RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
OFDMP        2543030685 OFDMP              1 10.2.0.3.0  NO  ofdmprd

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      4235 02-ÇÈÑêä -08 04:30:      98      3.1
  End Snap:      4236 02-ÇÈÑêä -08 05:00:      98      3.2
  Elapsed:              30.51 (mins)
  DB Time:              85.49 (mins)

SQL Summary                            DB/Inst: OFDMP/OFDMP  Snaps: 4235-4236

                Elapsed
  SQL Id      Time (ms)
------------- ----------
6k5jkqzvfz7gs    683,494
Module: uvsh.exe
UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = :B4 , OVER
STEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_OF_DATE = :B1

          -------------------------------------------------------------

SQL ID: 6k5jkqzvfz7gs                  DB/Inst: OFDMP/OFDMP  Snaps: 4235-4236
-> 1st Capture and Last Capture Snap IDs
  refer to Snapshot IDs witin the snapshot range
-> UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_AMT = ...

    Plan Hash          Total Elapsed                1st Capture  Last Capture
#  Value                    Time(ms)    Executions      Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1  3727740582                683,494          3381          4236          4236
          -------------------------------------------------------------


Plan 1(PHV: 3727740582)
-----------------------

Plan Statistics                        DB/Inst: OFDMP/OFDMP  Snaps: 4235-4236
-> % Total DB Time is the Elapsed Time of the SQL statement divided
  into the Total Database Time multiplied by 100

Stat Name                                Statement  Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                          683,494          202.2    13.3
CPU Time (ms)                              643,617          190.4    20.7
Executions                                    3,381            N/A    N/A
Buffer Gets                              8,727,370        2,581.3    5.8
Disk Reads                                    2,572            0.8    0.6
Parse Calls                                      1            0.0    0.1
Rows                                              0            0.0    N/A
User I/O Wait Time (ms)                      1,648            N/A    N/A
Cluster Wait Time (ms)                            0            N/A    N/A
Application Wait Time (ms)                        0            N/A    N/A
Concurrency Wait Time (ms)                        1            N/A    N/A
Invalidations                                    0            N/A    N/A
Version Count                                    1            N/A    N/A
Sharable Mem(KB)                                22            N/A    N/A
          -------------------------------------------------------------

Execution Plan
----------------------------------------------------------------------------------------------------
--------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)|
Time    | Pstart| Psto
----------------------------------------------------------------------------------------------------
--------------------
|  0 | UPDATE STATEMENT                    |                    |      |      |    1 (100)|   
      |      |
|  1 |  UPDATE                            | AUTHORIZATION      |      |      |            |   
      |      |
|  2 |  PARTITION RANGE SINGLE            |                    |    1 |    56 |    1  (0)|
00:00:01 |  KEY |  KE
|  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| AUTHORIZATION      |    1 |    56 |    1  (0)|
00:00:01 |  KEY |  KE
|  4 |    INDEX RANGE SCAN                | IDX_AUTHORIZATION_1 |    1 |      |    1  (0)|
00:00:01 |  KEY |  KE
----------------------------------------------------------------------------------------------------
--------------------



Full SQL Text

SQL ID      SQL Text
------------ -----------------------------------------------------------------
6k5jkqzvfz7g UPDATE AUTHORIZATION SET AMORTZ_UNIT_AMT = :B5 , INCON_SECURITY_A
            AMT = :B4 , OVERSTEP_DATE = :B3 WHERE AUTH_SEQ_KEY = :B2 AND AS_O
            F_DATE = :B1




I can upload the table (rows=no) with statistics, if required.

When I change CURSOR_SHARING=EXACT on my test database (before executing the UPDATE statement), it picks the right index. As this is the production database and performance issues happen once in 15 days, I would like to make sure that I pick the right change.

Below is the script of my test case:

***********************************************************************************
                Worst Plan
***********************************************************************************
comment on table authorization is 'worse_2';

alter session set cursor_sharing = force;

alter session set tracefile_identifier = 'Worse';

alter session set events '10053 trace name context forever, level 1';

UPDATE AUTHORIZATION
  SET AMORTZ_UNIT_AMT = 1 ,  INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/01/08',
'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');

UPDATE AUTHORIZATION
  SET AMORTZ_UNIT_AMT = 1 ,  INCON_SECURITY_AMT = 2 , OVERSTEP_DATE = to_date('04/06/08',
'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/06/08', 'MM/DD/YY');


alter session set events '10053 trace name context off';

exit

Execution plan from the trace file:

------------------------------------------------------------------+---------------------------------
--+---------------+
| Id  | Operation                            | Name              | Rows  | Bytes | Cost  | Time   
  | Pstart| Pstop |
------------------------------------------------------------------+---------------------------------
--+---------------+
| 0  | UPDATE STATEMENT                    |                    |      |      |    1 |       
  |      |      |
| 1  |  UPDATE                              | AUTHORIZATION      |      |      |      |       
  |      |      |
| 2  |  PARTITION RANGE SINGLE            |                    |    1 |    56 |    1 | 
00:00:01 | KEY  | KEY  |
| 3  |    TABLE ACCESS BY LOCAL INDEX ROWID | AUTHORIZATION      |    1 |    56 |    1 | 
00:00:01 | KEY  | KEY  |
| 4  |    INDEX RANGE SCAN                | IDX_AUTHORIZATION_1|    1 |      |    1 | 
00:00:01 | KEY  | KEY  |
------------------------------------------------------------------+---------------------------------
--+---------------+
Predicate Information:
----------------------
3 - filter("AUTH_SEQ_KEY"=:SYS_B_4)
4 - access("AS_OF_DATE"=TO_DATE(:SYS_B_5,:SYS_B_6))



***********************************************************************************
                Best Plan
***********************************************************************************

comment on table authorization is 'best';

alter session set tracefile_identifier = 'Best';

alter session set events '10053 trace name context forever, level 1';

UPDATE AUTHORIZATION
  SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 ,  OVERSTEP_DATE = to_date('04/01/08',
'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');

UPDATE AUTHORIZATION
  SET AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2 ,  OVERSTEP_DATE = to_date('04/06/08',
'MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02' AND AS_OF_DATE = to_date('04/06/08', 'MM/DD/YY');


alter session set events '10053 trace name context off';

Execution plan from the trace file:

============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation          | Name        | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0  | UPDATE STATEMENT    |              |      |      |    3 |          |
| 1  |  UPDATE            | AUTHORIZATION|      |      |      |          |
| 2  |  INDEX UNIQUE SCAN | SYS_C0010238 |    1 |    53 |    2 |  00:00:01 |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("AUTH_SEQ_KEY"='CPT22113622211362C02' AND "AS_OF_DATE"=TO_DATE('04/01/08','MM/DD/YY'))
 





I hope, I have furnished all the required information for you to analyze my situation. If you need any other information, please let me know.

Your comments are appreciated.

Take care !!!

Warm Regards

Momen

I think you hit upon the crux of the issue:  CURSOR_SHARING.  I'll also add that statistics may have something to do with it.  In a new partition, since there are no statistics, when data is inserted into that partition for the first time, the optimizer doesn't have anything to work with for the statistics so will either dynamically sample or use defaults or use the current table stats (without having benefit of knowing about the new data). 

In the "bad" plan you show, notice a couple of things:
1) That there are no time estimates in the plan display. This is an indicator that the optimizer didn't have the info it needed. 
2)  Note the system generated bind variables in the Predicate information:  SYS_B_4 and so on.  This is showing how the setting of FORCE replaces the literals with binds.  That means the optimizer will peek at the value in the bind and come up with a plan.  But, the absence of literals and perhaps also statistics for that partition work together to set the optimizer to have to "guess" much more than usual about what plan operations would be optimal.

So, I'd suggest a couple of things:
1) If you are able to do so, set cursor_sharing to EXACT before the job executes on the days of the cycle when you know you have problems.  You've got good tests that show this gets you the performance you want, so use it if you can.
2) Since these statements are UPDATEs, that means you've already inserted the data into the new partition before running this.  So, make absolutely sure that your stats are up to date before doing the update to make sure the optimizer knows what's out there and doesn't have to use defaults or attempt dynamic sampling.  You can gather or set the statistics for the new partition before you execute these update statements. 

Finally, I don't know if it's true or not, but your UPDATE statement, as written, can possibly be doing alot of work (particularly redo) that it doesn't need to.  Your query is written like this:

UPDATE AUTHORIZATION
  SET AMORTZ_UNIT_AMT = 1 ,  INCON_SECURITY_AMT = 2 ,
      OVERSTEP_DATE = to_date('04/01/08','MM/DD/YY')
 WHERE AUTH_SEQ_KEY =  'CPT22113622211362C02'
  AND AS_OF_DATE = to_date('04/01/08', 'MM/DD/YY');

Well, what if AMORTZ_UNIT_AMT = 1 , INCON_SECURITY_AMT = 2,  and OVERSTEP_DATE = to_date('04/01/08','MM/DD/YY') already have those values in place?  If this is the case, you would be updating rows you don't need to.  You could add three additional checks to your WHERE clause to make sure you don't update rows you don't need to (if that's a possiblity).  Just add WHERE AMORTZ_UNIT_AMT <> 1 , INCON_SECURITY_AMT <> 2,  and OVERSTEP_DATE <> to_date('04/01/08','MM/DD/YY').

I don't think this is part of this problem but I think if you have this possibility then you could save work by eliminating the updates on rows that already have the values you need.

Hi Morton,

Congratulations !!!

The issue is resolved. Yesterday the batch completed in time. I opted the *dirty trick* by adding a hint to the UPDATE statement. This was the only procedure which was causing problems on 16th of every month.

But a bigger challenge is yet to be faced, i.e. 1st of every month. There are many procedures which are behaving in this way. For the next run, I will NOT gather statistics on 1st and let the optimizer do dynamic sampling and come up with the best plan. Before doing this in production, of course I will be testing it in a test environment.

I will also take into consideration of modifying the UPDATE statement, so that it generates less redo and updates only the rows required.

I have also contacted Jonathan Lewis on this issue and as usual, he guided me into the right direction.

Thanks again for your time.

Take care !!!!

Regards

Asif Momen
http://momendba.blogspot.com