Using sqlprofile to optimize slow SQL statements in Goldengate

Tag:oracle   sql    optimization     Execution Plan    gg    

HP UNIX monitors a process that consumes high CPU and later finds that there is a problem with the statement execution plan in GG, which causes the CPU to be high. Since the statements in GG cannot be changed, how can you change the execution plan of statements that cannot be changed, which can be optimized using Sqlprofile. [[email protected]] $ top-hsystem:szodsd01 Wed Nov 5 10:17:16 2014Load Averag es:0.42, 0.47, 0.491007 processes:599 sleeping, 408 runningcpu states: (avg) LOAD USER nice SYS IDLE BLOCK SW AIT INTR Ssys 0.42 19.8% 0.0% 5.2% 75.0% 0.0% 0.0% 0.0% 0.0%system Page size:4kbytesmemory:40107992k (     35984460K) Real, 53292056K (48597616K) virtual, 12423080K free page# 1/44cpu TTY PID USERNAME PRI NI SIZE RES State  Time%wcpu%cpu COMMAND 3? 1697 Oracle 178 25460M 6420K run 1780:54 95.10 94.93 oracleszodsd1 11? 28561 Oracle 178 25461M 7236K sleep 55:06 8.01 8.00 oracleszodsd1 sql> @getsql_spidEnter value for OS Pid:1697addr----------------c00000037a0351c8sid_serial sql_id_num p_name p_value sql_id USER        NAME program EVENT        STATUS blocking_session----------------------------------------------------------------------------------- ------------------------------------------------------3152,32913 6khsffxsn05tg,0 driver ID 1413697536 6KHSFF                                        XSN05TG GGS [email protected] sql*net message from ACTIVE #bytes 1 (TNS v1-v3) client 0sql_fulltext------------ --------------------------------------------------------------------DELETE from "SPS_SZ". " Tb_xx_xxxx_xxxx_xxx "WHERE" log_id "=: B0 and" plan_id "=:b1 and" log_no "=: B2 and" Plan_result "=: B3 and" Staff_code " =: B4 and "work_staff" =: B5 and "op_date" =: B6 and "op_time" =: B7 and "log_notes" =: B8 and "unit_id" =: B9 and ROWNUM = The above by Replicat and GGs visible is GG's statement caused-see GG in tb_xx_xxxx_xxxx_xxx corresponding process for rszspsesql> @getggmapSession altered. Enter value for Ttable:tb_xx_xxxx_xxxx_xxxenter value for TOWNER:SPS_SZSTATS log_time PNAME source_owner source_tablename targe  T_owner target_tablename ggs_owner-------------------------------------------------------------------------- --------------------------------------------------------------------------------------------stats rszspse Table * Tb_xx_xxxx_xxxx_xxx 2014-11-05 00:00:18 rszspse sps_sz_inst tb_xx_xxxx_xxxx_xxx SPS_SZ TB_ Xx_xxxx_xxxx_xxx szggs11 Row selected. sql>--really delayed a long time, 6 clock Ggsci (SZODSD01) 2> info rszspsereplicat rszspse last Started 2014-11-01 the Status Runni Ngcheckpoint Lag 06:14:03 (updated 00:02:40 ago) Log Read Checkpoint File/odsd/szggs01/ggdata/sz/rszspsa/dirdat/si2 48293 2014-11-05 04:08:32.005523 RBA 1349428--View Execution plan and outline information, followed by Sqlprofile optimization sql> explain plan fo R 2 DELETE from "SPS_SZ". " Tb_xx_xxxx_xxxx_xxx "3 WHERE" log_id "=: B0 4 and" plan_id "=: B1 5 AND "Log_no" =: B2 6 and "Plan_result" =: B3 7 and "Staff_code" =: B4 8 and "work_staff" =: B5 9 and "O P_date "=: B6 and" op_time "=: B7 and" log_notes "=: B8 and" unit_id "=: B9 and ROWNUM = 1; explained.sql> @getplan ' general,outline,starts ' Enter value for plan Type:outlineplan_table_ OUTPUT----------------------------------------------------------------------------------------------------------------Pla N Hash value:692294925------------------------------------------------------------------------------------------ ---------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |----------------------------------------------------------------------------------------------------------   -----| 0 |                          DELETE STATEMENT |     |   1 | 352 | 16760 (2) |   00:03:55 | |  1 | DELETE |      tb_xx_xxxx_xxxx_xxx | |            |       | ||   * 2 |                          COUNT Stopkey |       |       |            |       | ||    * 3 | TABLE ACCESS by INDEX ROWID |     tb_xx_xxxx_xxxx_xxx |   1 | 352 | 16760 (2) |   00:03:55 | |     4 |                          BITMAP CONVERSION to ROWIDS |       |       |            |       |   ||      5 |                          BITMAP and |       |       |            |       |   ||       6 |                          BITMAP CONVERSION from rowids|       |       |            |       | ||        * 7 | INDEX RANGE SCAN |  ix_aa_aaa_aaaaaaa |       1757k|    | 14 (79) |   00:00:01 | |       8 |                          BITMAP CONVERSION from rowids|       |       |            |       | ||        * 9 | INDEX RANGE SCAN |  Ix_aa_aaa_aaaaaaa_planid |       1757k|    | 14 (79) |  00:00:01 | |       10 |                          BITMAP CONVERSION from rowids|       |       |         |   | ||        * 11 | INDEX RANGE SCAN |  ix_bb_bbb_bbbb_bbbbbb |       1757k|  | 2787 (1) |  00:00:40 | |       12 |                          BITMAP CONVERSION from rowids|       |       |            |       | ||        * 13 | INDEX RANGE SCAN |  IX_CC_CCC_CCCC_CCCCCC |       1757k| | 13793 (1) | 00:03:14 |------------------------------------------------------------------------------------------------------ ---------Query Block name/object Alias (identified by Operation ID):-------------------------------------------------      ------------1-del$1 3-del$1/[email protected]$1outline Data-------------/*+ begin_outline_data Bitmap_tree (@ "del$1" "tb_xx_xxxx_xxxx_xxx" @ "del$1" and ("Tb_xx_xxxx_xxxx_xxx". " log_id ") (" Tb_xx_xxxx_xxxx_xxx ". plan_id ") (" Tb_xx_xxxx_xxxx_xxx ". Op_date ") (" Tb_xx_xxxx_xxxx_xxx ". Plan_result ")) Outline_leaf (@" del$1 ") all_rows optimizer_features_enable (' 10.2.0.4 ') IGNore_optim_embedded_hints end_outline_data */predicate Information (identified by Operation ID):-------------------- -------------------------------2-filter (rownum=1) 3-filter ("Staff_code" =:b4 and "Work_staff" =:b5 and "Op_time" =:b 7 and "Log_notes" =:b8 and "Log_no" =to_number (: B2) and "unit_id" =to_number (: B9)) 7-access ("log_id" =to_numb ER (: B0)) 9-access ("plan_id" =to_number (: B1)) 11-access ("Op_date" =:b6) 13-access ("Plan_result" =to_number (: B3)) Note ------Dynamic sampling used for this statement--executes a large number of bitmap keywords in the plan, it is easy to mistakenly think that these indexes are bitmap indexes, which are actually normal indexes. --Get information about tables and indexes owner table_name object_type STA last_analyzed-------------------------- ----------------------------------------------------------SPS_SZ tb_xx_xxxx_xxxx_xxx table--. Table TB_XX_XXXX_XXXX_XXX Statistics expiration owner Segment_name Segment_type Size (Mb)------------- ------- ------------------------------ ------------------------------SPS_SZ IX_CC_CCC_CCCC_CCCCCC INDEX 8434.625SPS_SZ       IX_AA_AAA_AAAAAAA INDEX 11176.625SPS_SZ Ix_aa_aaa_aaaaaaa_planid               Index 11484.4375SPS_SZ IX_BB_BBB_BBBB_BBBBBB Index 13138.5SPS_SZ                     TB_XX_XXXX_XXXX_XXX Table 38322--The index is bigger than the table. Owner Index_name TABLE_NAME PAR uniquenes degree index_type leaf_blocks blevel clustering_fact OR%------------------------------------------------------------------------------------------------------- ---------------------------------------------------------SPS_SZ ix_aa_aaa_aaaaaaa tb_xx_xxx               X_xxxx_xxx NO nonunique 1 NORMAL 284214 2 185996490 100SPS_SZ  Ix_cc_ccc_cccc_cccccc        Tb_xx_xxxx_xxxx_xxx NO nonunique 1 NORMAL 206567 2 6963531.000006899SPS          _SZ ix_bb_bbb_bbbb_bbbbbb tb_xx_xxxx_xxxx_xxx NO nonunique 1 NORMAL 299483             2 6898441.000049673SPS_SZ Ix_aa_aaa_aaaaaaa_planid tb_xx_xxxx_xxxx_xxx NO nonunique 1 NORMAL 284214 2 31263628 19.8460594--The cluster factor of index IX_AA_AAA_AAAAAAA 100,distinct _KEYS/NUM_ROWS*100=100, the selectivity is very good, similar to the primary key, basically returns 1 rows. --The other 3 indexes are very rubbish and can be used completely. If you walk 4 indexes, the volume is larger than the full table scan, and the index RANGE scan is a single block read, the whole table is more than a block of reading, it is better to walk the whole table. --The following information is obtained from the outline data information above: Bitmap_tree (@ "del$1" "tb_xx_xxxx_xxxx_xxx" @ "del$1" and ("Tb_xx_xxxx_xxxx_xxx". " log_id ") (" Tb_xx_xxxx_xxxx_xxx ". plan_id ") (" Tb_xx_xxxx_xxxx_xxx ". Op_date ") (" Tb_xx_xxxx_xxxx_xxx ". Plan_result "))--here to walk bitmap must be wrong, here can walk index IX_AA_AAA_AAAAAAA, but how to get walk ix_aa_aaa_aaaaaaa hint? Sql> explain plan for 2 DELETE/*+index (A,IX_AA_AAA_AAAAAAA) */From "SPS_SZ". " Tb_xx_xxxx_xxxx_xxx "A 3 WHERE" log_id "=: B0 4 and" plan_id "=: B1 5 and" log_no "=: B2 6 and" Plan_res ULT "=: B3 7 and" Staff_code "=: B4 8 and" work_staff "=: B5 9 and" op_date "=: B6 and" op_time "=: B7 and "log_notes" =: B8 and "unit_id" =: B9 and ROWNUM = 1; explained.sql> @getplan ' general,outline,starts ' Enter value for plan Type:outlineplan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- ------Plan Hash value:2457304297-------------------------------------------------------------------------------- --------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |----------------------------------------------------------------------------------------------------| 0 |                    DELETE STATEMENT |     |   1 |   352 | 744K (1) |   02:53:43 | |  1 | DelETE |       Tb_xx_xxxx_xxxx_xxx|       |            |          | ||   * 2 |                    COUNT Stopkey |       |       |            |          | ||    * 3 | TABLE ACCESS by INDEX rowid|     Tb_xx_xxxx_xxxx_xxx|   1 |   352 | 744K (1) | 02:53:43 | |     * 4 | INDEX RANGE SCAN |  ix_aa_aaa_aaaaaaa |       1757k|    | 14 (79) | 00:00:01 |---------------------------------------------------------------------------------------------------- Query Block name/object Alias (identified by Operation ID):---------------------------------------------------------- ---1-del$1 3-del$1/[email protected]$1 4-del$1/[Email protected]$1outline Data-------------/* + Begin_outline_data Index_rs_asc (@ "del$1" "A" @ "del$1" ("Tb_xx_xxxx_xxxx_xxx"). log_id ")--to use INDEX_RS_ASC this hint, replace a with a table name is Outline_leaf (@" del$1 ") all_rows Optimizer_features_e Nable (' 10.2.0.4 ') ignore_optim_embedded_hints end_Outline_data */predicate Information (identified by Operation ID):--------------------------------------------------              -2-filter (Rownum=1) 3-filter ("Staff_code" =:b4 and "Work_staff" =:b5 and "Op_date" =:b6 and "Op_time" =:b7 and "Log_notes" =:b8 and "plan_id" =to_number (: B1) and "Log_no" =to_number (: B2) and "Plan_result" =to_number (: B3) and "unit_id" =to_number (: B9)) 4-access ("log_id" =to_number (: B0)) Note------Dynamic sampling used for this statem ENT--Using profile declare v_hints sys.sqlprof_attr;begin v_hints: = sys.sqlprof_attr (' index_rs_asc @ ' D El$1 "" Tb_xx_xxxx_xxxx_xxx "@" del$1 "(" Tb_xx_xxxx_xxxx_xxx ".")    log_id ")); Dbms_sqltune.import_sql_profile (' DELETE from ' SPS_SZ. ")  Tb_xx_xxxx_xxxx_xxx "WHERE" log_id "=: B0 and" plan_id "=: B1 and" log_no "=: B2 and" Plan_result "=: B3 and" Staff_code "  =: B4 and "work_staff" =: B5 and "op_date" =: B6 and "op_time" =: B7 and "log_notes" =: B8 and "unit_id" =: B9 and ROWNUM              = 1 ',                     V_hints, ' sps_sz_tb_xx_xxxx_xxxx_xxx ', Force_match = True); end;/--verifies that the execution plan only goes ix_aa_aaa_aaaaaaa:sql> explain plan for 2 DELETE from "SPS_SZ". " Tb_xx_xxxx_xxxx_xxx "3 WHERE" log_id "=: B0 4 and" plan_id "=: B1 5 and" log_no "=: B2 6 and" Plan_resul  T "=: B3 7 and" Staff_code "=: B4 8 and" work_staff "=: B5 9 and" op_date "=: B6 and" op_time "=: B7 One and "log_notes" =: B8 and "unit_id" =: B9 and ROWNUM = 1; explained.sql> @getplan ' general,outline,starts ' Enter value for plan Type:plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- Plan Hash value:2457304297-------------------------------------------------------------------------------------- --------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |----------------------------------------------------------------------------------------------------| 0 |                    DELETE STATEMENT |     |   1 |   352 | 744K (1) |   02:53:43 | |  1 | DELETE |       tb_xx_xxxx_xxxx_xxx |       |            |          | ||   * 2 |                    COUNT Stopkey |       |       |            |          | ||    * 3 | TABLE ACCESS by INDEX rowid|     tb_xx_xxxx_xxxx_xxx |   1 |   352 | 744K (1) | 02:53:43 | |     * 4 | INDEX RANGE SCAN |  ix_aa_aaa_aaaaaaa |       1757k|    | 14 (79) | 00:00:01 |---------------------------------------------------------------------------------------------------- predicate information (identified by Operation ID):---------------------------------------------------2-filter ( Rownum=1) 3-filter ("Staff_code" =:b4 and "Work_staff" =:b5 and "Op_date" =:b6 and "Op_time" =:b7 and "Log_not ES "=:b8 and" plan_id "=to_number (: B1) and" Log_no "=to_number (: B2) and "Plan_result" =to_number (: B3) and "unit_id" =to_number (: B9)) 4-access ("log_id" =to_number (: B0)) Note---- --Dynamic sampling used for this statement-sql profiles "sps_sz_tb_xx_xxxx_xxxx_xxx" used for this statement--execution The plan was right. Although the implementation plan was corrected, but after all, index IX_AA_AAA_AAAAAAA 11g,cpu or a bit high, scanning up or a bit slow, only delete operation will be down. But now the efficiency is much better. --related views sql> select name,created from Dba_sql_profiles ORDER by Created;name created------- ------------------------------------------sps_sz_tb_xx_xxxx_xxxx_xxx 2014-11-05 10:49:585 rows selected. Sql> Col attr_val for a80sql> select Name,attr_val from Dba_sql_profiles A, sys.sqlprof$attr B 2 where A.signatur E = b.signature 3 and a.name = ' sps_sz_tb_xx_xxxx_xxxx_xxx '; name Attr_val------------------ --------------------------------------------------------------------------------------------Sps_sz_tb_xx_xxxx_ Xxxx_xxx Index_rs_asc (@ "del$1" "tb_xx_xxxx_xxxx_xxx" @ "del$1" ("Tb_xx_xxxx_xxxx_xxx "." log_id ")) 1 row selected. [email protected] Script @getsql_spidset lines pages a long 100000col USERNAME for A10col program for A15col EVENT fo R a20col sid_serial for A10col sql_id_num for A15col p_name for A15col p_value for A15col addr new_value addrcol sql_id NE  W_value Sql_idselect addr from gv$process c WHERE c.spid = &ospid; Select A.sid | | ', ' | | a.serial# sid_serial,a.sql_id | | ', ' | | A.sql_child_number sql_id_num,p1text| | Chr (10) | | p2text| | Chr (10) | | P3text p_name,a.p1| | Chr (10) | | a.p2| | Chr (10) | | A.P3 P_value,decode (a.sql_id,null,a.prev_sql_id,a.sql_id) sql_id,a.username,a.program,a.event,a.status,a. Blocking_session from Gv$session a where a.paddr = ' &addr ', select C.sql_fulltext from Gv$sqlarea C where c.sql_id= ' &A MP;SQL_ID '; Cl col[email protected]set feedback offpro ' general,outline,starts ' proacc type prompt ' Enter value for Plan type: ' Default ' General ' SELECT * from table (dbms_xplan.display) where ' &&type ' = ' generAl '; Select * FROM table (dbms_xplan.display (null, NULL, ' advanced-projection ')) where ' &&type ' = ' outline '; SELECT * from TABLE (Dbms_xplan. Display_cursor (Null,null, ' allstats last ')) where ' &&type ' = ' starts '; Set feedback onundef type

Using sqlprofile to optimize slow SQL statements in Goldengate