Use sqlprofile to optimize slow SQL statements in goldengate and SQL statements

Use sqlprofile to optimize slow SQL statements in goldengate and SQL statements

In HP unix, a process is monitored to occupy a high cpu. Later, it was found that the statement execution plan in gg had a problem, resulting in a high cpu. Since the statements in gg cannot be changed, how can we change the execution plan of statements that cannot be changed? sqlprofile can be used here for optimization. [Szggs1 @ szodsd01] $ top-hSystem: szodsd01 Wed Nov 5 10:17:16 2014 Load averages: 0.42, 0.47, 0.491007 processes: 599 sleeping, 408 runningCpu states: (avg) load user nice sys idle block swait intr ssys 0.42 19.8% 0.0% 5.2% 75.0% 0.0% 0.0% 0.0% System Page Size: 4 KbytesMemory: 0.0% K (40107992 K) real, 35984460 K (53292056 K) virtual, 12423080 K free Page #1/44CPU TTY PID USERNAME PRI NI SIZE RES State time % WCPU % cpu command 3? 1697 oracle 178 20 25460 M 6420 K run 1780: 54 95.10 94.93 oracleszodsd1 11? 28561 oracle 178 20 25461 M 7236 K sleep 55: 06 8.01 8.00 oracleszodsd1 SQL> @ getsql_spidEnter value for ospid: 1697ADDR----------------C00000037A0351C8SID_SERIAL SQL _ID_NUM P_NAME P_VALUE SQL _ID USERNAME PROGRAM EVENT STATUS BLOCKING_SESSION ---------- --------------- ----------------- ------------ --------------------- begin -------- ------------------ limit 6k Ffxsn05tg, 0 driver id 1413697536 6khsffxsn05tg GGS replicat @ szodsd SQL * Net message from ACTIVE # bytes 1 01 (TNS V1-V3) client Received FROM "SPS_SZ ". "tb_xx_xxxx_xx_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_D ATE "=: b6 AND" OP_TIME "=: b7 AND" LOG_NOTES "=: b8 AND" UNIT_ID "=: b9 and rownum = 1 -- the above is caused by the statement where replicat and ggs are visible AND gg -- check that the process corresponding to TB_XX_XXXX_XXXX_XXX in gg is RSZSPSESQL> @ getggmapSession altered. enter value for ttable: TB_XX_XXXX_XXXX_XXXEnter value for towner: SPS_SZSTATS LOG_TIME PNAME SOURCE_OWNER SOURCE_TABLENAME TARGET_OWNER TARGET_TABLENAME GGS_OWNER = ---------------------------------------------------- ------- --------------- Begin certificate -------------------- ---------------------- stats RSZSPSE table * 00:00:18 RSZSPSE SPS_SZ_INST limit SPS_SZ limit szggs11 row selected. SQL> -- a long delay, 6 minutes GGSCI (szodsd01) 2> info RSZSPSEREPLICAT RSZSPSE Last Started Status RUNNINGCheckpoint Lag 00:02:40 (updated 04:08:32 ago) Log Read Checkpoint File/odsd/szggs01/ggdata/sz/rszspsa/dirdat/si248293. 005523 RBA 1349428 -- view the execution plan and outline information, and use sqlprofile to optimize SQL> explain plan for 2 DELETE FROM "SPS_SZ ". "tb_xx_xxxx_xx_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 "OP_DATE" =: B6 10 AND "OP_TIME" =: b7 11 AND "LOG_NOTES" =: b8 12 AND "UNIT_ID" =: b9 13 and rownum = 1; Explained. SQL> @ getplan 'General, outline, starts 'enter value for plan type: outlinePLAN_TABLE_OUTPUT partition Plan hash value: 692294925 ------------------------------------------------------------------------- ------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | consumed | 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_bbbbbb_bbbbbb | 1757K | 2787 (1) | 00:00:40 | 12 | bitmap conversion from rowids | * 13 | index range scan | ix_cc_ccc_cccccc_cccccccc | 1757K | 13793 (1) | 00:03:14 | invalid Query Block Name/Object Alias (identified by operation id ):-------------------- Export 1-DEL $1 3-DEL $1/TB_XX_XXXX_XXXX_XXX @ DEL $1 Outline 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): Limit 2-filter (ROWNUM = 1) 3-filter ("STAFF_CODE" =: b4 AND "WORK_STAFF" =: B5 AND "OP_TIME" =: B7 AND "LOG_NOTES" =: B8 AND "LOG_NO" = TO_NUMBER (: B2) AND "UNIT_ID" = TO_NUMBER (: b9) 7-access ("LOG_ID" = TO_NUMBER (: B0) 9-access ("PLAN_ID" = TO_NUMBER (: B1) 11-ac Cess ("OP_DATE" =: B6) 13-access ("PLAN_RESULT" = TO_NUMBER (: B3 )) note ------dynamic sampling used for this statement -- a lot of BITMAP keywords in the execution plan, it is easy to mistakenly think that these indexes are bitmap indexes, which are actually common indexes. -- Get information about tables and indexes OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED -------------------- tables ------------ --- --------------------- SPS_SZ internal TABLE -- the preceding TABLE expires the OWNER SEGMENT_NAME SEGMENT_TYPE Size) -------------------- ---------------------------- -------------------- ---------- SPS_SZ IX_CC_CCC_CCCC_CCCCCC INDEX 8434.625SPS _ SZ IX_AA_AAA_AAAAAAA IN DEX 11176.625SPS _ SZ limit INDEX Limit _ SZ limit INDEX 13138.5SPS _ SZ limit TABLE 38322 -- the indexes are larger than the table owner INDEX_NAME TABLE_NAME par uniquenes degree INDEX_TYPE LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR % too many ----------------------------------------------------------------------------------- --------------------------------------------------------- ---------- SPS_SZ IX_AA_AAA_AAAAAAA TB_XX_XXXX_XXXX_XXX no nonunique 1 NORMAL 284214 2 185996490 100SPS_SZ limit TB_XX_XXXX_XXXX_XXX no nonunique 1 NORMAL 206567 2 6963531. 000006899SPS_SZ ix_bb_bbb_bbbbbb_bbbbbb TB_XX_XXXX_XXXX_XXX no nonunique 1 NORMAL 299483 2 6898441. listen 49673sps_sz IX_AA_AAA_AAAAAAA_PLANID TB_XX_XXXX_XXXX_XXX no nonunique 1 NORMAL 284214 2 31263628 19. 8460594 -- From the above we can see that the cluster Factor 100, distinct_keys/NUM_ROWS * 100 = 100 for indexing ix_aa_aaa_aaaaa is very selective. Similar to the primary key, one row is basically returned. -- The other three indexes are very junk and can be used completely. If four indexes are taken, the volume is larger than the full table SCAN, and the index range scan is a single read, and the full table is multiple reads, it is better to go through the full table. -- Obtain the following information from 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") -- Here bitmap is definitely wrong. Here we can index IX_AA_AAA_AAAAAAA, but how can we get the hint that follows IX_AA_AAA_AAAAAAA? SQL> explain plan for 2 DELETE/* + index (a, IX_AA_AAA_AAAAAAA) */FROM "SPS_SZ ". "tb_xx_xxxx_xx_xxx" a 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 "OP_DATE" =: b6 10 AND "OP_TIME" =: b7 11 AND "LOG_NOTES" =: b8 12 AND "UNIT_ID" =: b9 13 and rownum = 1; Explained. SQL> @ getplan 'General, outline, starts' Enter value for plan type: outlinePLAN_TABLE_OUTPUT partition Plan hash value: 2457304297 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ------------------------------------------ ---------------------------------------------------------- | 0 | delete statement | 1 | 352 | 744 K (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 | 744 K (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 ): export 1-DEL $1 3-DEL $1/A @ DEL $1 4-DEL $1/A @ DEL $1 Outline Data -------------/* + BEGIN_OUTLINE_DATA INDEX_RS_ASC (@ "DEL $1 "" A "@" DEL $1 "(" TB_XX_XXXX_XXXX_XXX ". "LOG_ID") -- use the hint INDEX_RS_ASC to replace A with the table name 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): Limit 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_NU MBER (: B3) AND "UNIT_ID" = TO_NUMBER (: B9) 4-access ("LOG_ID" = TO_NUMBER (: B0 )) note ------dynamic sampling used for this statement -- use profile declare v_hints sys. sqlprof_attr; begin v_hints: = sys. sqlprof_attr ('index _ RS_ASC (@ "DEL $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 "P LAN_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;/-- verify that the execution plan only follows 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_ I D "=: b1 5 AND" LOG_NO "=: b2 6 AND" PLAN_RESULT "=: b3 7 AND" STAFF_CODE "=: b4 8 AND" WORK_STAFF "=: b5 9 AND "OP_DATE" =: b6 10 AND "OP_TIME" =: b7 11 AND "LOG_NOTES" =: b8 12 AND "UNIT_ID" =: b9 13 and rownum = 1; explained. SQL> @ getplan 'General, outline, starts' Enter value for plan type: PLAN_TABLE_OUTPUT Partition --------------------------------------------------------------------------------------------- ---------------------- Plan hash value: 2457304297 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | STATEMENT | 0 | delete statement | 1 | 352 | 744 K (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 | 744 K (1) | 02:53:43 | * 4 | index range scan | IX_AA_AAA_AAAAAAA | 1757K | 14 (79) | 00:00:01 | descripredicate 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 statement-SQL profile "SPS_SZ_TB_XX_XXXX_XXXX_XXX" used for this st Atement -- the execution plan is correct. Although the execution plan has been corrected, the index IX_AA_AAA_AAAAAAA has 11 GB, the cpu usage is still a little high, and scanning is still a little slow. It will only be downgraded after the delete operation is complete. But now the efficiency is much better. -- View SQL> select name, created from dba_ SQL _profiles order by created; NAME CREATED ---------------------------------- ----------------- 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. signature = B. signature 3 and. name = 'sps _ upload'; NAME ATTR_VAL has been pinned into INDEX_RS_ASC (@ "DEL $1" "TB_XX_XXXX_XXXX_XXX" @ "DEL $1" ("TB_XX_XXXX_XXXX_XXX ". "LOG_ID") 1 row selected. -- @ script @ getsql_spidset lines 200 pages 200 long 100000col USERNAME for a10col PROGRAM for a15col EVENT for a20col syntax for a10col syntax for a15col p_name for a15col p_value for a15col addr new_value addrcol SQL _id new_value limit addr FROM gv $ process c WHERE c. spid = & ospid; select. sid | ',' |. SERIAL # sid_serial,. SQL _ID | ',' |. SQL _child_number SQL _id_num, P1TEXT | chr (10) | P2TEXT | chr (10) | P3TEXT p_name,. p1 | chr (10) |. p2 | chr (10) |. p3 p_value, decode (. SQL _ID, null,. PREV_ SQL _ID,. SQL _ID) SQL _id,. USERNAME,. PROGRAM,. EVENT,. STATUS,. BLOCKING_SESSION from gv $ session a where. PADDR = '& addr'; select c. SQL _FULLTEXT from gv $ sqlarea c where c. SQL _ID = '& SQL _id'; cl col -- @ getplanset 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 la ')) where '& type' = 'starts'; set feedback onundef type

SQL statement Optimization in oracle, these two SQL statements, because the data volume is very large, a few G queries are very slow to help optimize

/** Try this :*/

-- 1)
Select
C. characvalueid,
C. sequencekey,
L. resourceid
From tr_characvalue c
Join tr_resourcecharac l on c. characvalueid = l. characvalueid
Where 1 = 1
And (1 = 2
Or exists (select 1
FROM tr_teammember
WHERE teamid = 20160
And resourceid = l. resourceid
)
Or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
And resourceid = l. resourceid
)
)
Order by c. characid, c. sequencekey

-- 2)
Select u. resourceid, u. userfieldid
From tr_resourcevalue u
Join tr_userfield p on u. userfieldid = p. userfieldid
Where 1 = 1
And (1 = 2
Or exists (select 1
FROM tr_teammember
WHERE teamid = 20160
And resourceid = u. resourceid
)
Or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
And resourceid = u. resourceid
)
)
Order by u. userfieldid

Why does the Oracle optimizer ignore indexes when using functions for columns with indexes in the where clause of SQL statements?

The where clause performs function operations on fields, which causes the engine to stop using indexes for full table scanning.
Because SQL parses local variables only at runtime, the optimizer cannot postpone the selection of the access plan to runtime; it must be selected at compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection.