Thursday, December 13, 2012

GoldenGate performance Tuning - 1

Problem : Replicat was doing Full table scan on Updating 804000 rows.

Source. = 11.2.0.3 /TARGG

Below is update statement which was issued to update 804000 rows.
This completed in 15 seconds on SOURCE side DB.

sql_id :ghsutzgq0m8j9 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID ghsutzgq0m8j9
--------------------
UPDATE dummy11.OS_HISTORYSTEP_BLD SET ENTRY_ID=5555, STEP_ID=100000,
ACTION_ID=20000, OWNER='JIGNESHKANKRECHA',
START_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
FINISH_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
DUE_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000 PM','fmMMfm/fmDDfm/YYYY
fmHH12fm:MI:SS.FF AM'), STATUS='QC Validation failed', CALLER='KALIYA'
where ID  BETWEEN 1220000 AND 5999999

Plan hash value: 3578452229

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |   376 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |    47 |  4277 |   376   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------


------------------------------------------------------------------
Target. = 10.2.0.4 / SOUGG

Below statment Replicat executed ,
Because i had BATCHSQL replicat made BATCH of few rows and send for update.
that is the reason we see COUNT STOPKEY in execution plan.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID b4t7hv6p53165
--------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM = 1

Plan hash value: 185372276

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |                    |       |       |   304 (100)|          |
|   1 |  UPDATE             | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   COUNT STOPKEY     |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |     1 |    91 |   304   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

--Excerpt from Report file.

BATCHSQL BATCHESPERQUEUE 100, BATCHTRANSOPS 10000, OPSPERBATCH 10000, OPSPERQUEUE 100000

MAP resolved (entry dummy11.OS_HISTORYSTEP_BLD):
  MAP "dummy11"."OS_HISTORYSTEP_BLD", TARGET dummy11.OS_HISTORYSTEP_BLD, KEYCOLS(ID);

2012-12-12 12:00:30  WARNING OGG-00869  No unique key is defined for table 'OS_HISTORYSTEP_BLD'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Using following columns in default map by name:
  ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, FINISH_DATE,
  DUE_DATE, STATUS, CALLER
Using the following key columns for target table dummy11.OS_HISTORYSTEP_BLD: ID.

--------some statistic of replicat. 
2012-12-12 12:18:22  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].
              104578 records processed as of 2012-12-12 12:21:05 (rate 84,delta 236)
              457309 records processed as of 2012-12-12 13:35:07 (rate 80,delta 79)
              913119 records processed as of 2012-12-12 13:39:47 (rate 153,delta 1628)
              943118 records processed as of 2012-12-12 13:41:25 (rate 155,delta 305)
              953118 records processed as of 2012-12-12 13:42:43 (rate 155,delta 127)
              963118 records processed as of 2012-12-12 13:44:25 (rate 154,delta 98)
              973118 records processed as of 2012-12-12 13:46:29 (rate 153,delta 80)
              983118 records processed as of 2012-12-12 13:48:57 (rate 151,delta 67)
              993118 records processed as of 2012-12-12 13:51:47 (rate 148,delta 58)
             1003118 records processed as of 2012-12-12 13:54:59 (rate 146,delta 51)
             1013118 records processed as of 2012-12-12 13:58:35 (rate 143,delta 46)


SOLUTION:

1) kill session of replicat which was running update statement ,,This will abend replicat.
2) Add Index on ID column which was in KEYCOLS Of MAP statement.
3) start replicat .

And we are on Road again....

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:40

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:42

GGSCI (catlmsxt205) 6> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:33:06

GGSCI (catlmsxt205) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      00:00:03

Execution plan of Update after Index.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4t7hv6p53165, child number 0
-------------------------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM =
1

Plan hash value: 2252287618

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |     5 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|*  2 |   COUNT STOPKEY    |                    |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| OS_HIST_BLDIDX     |     1 |    91 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM=1)
   3 - access("ID"=TO_NUMBER(:B0))

No comments:

Post a Comment