利用sqlprofile优化goldengate中慢的sql语句，sql语句优化
HP unix中监控到一个进程占用cpu较高，后来发现是gg中的语句执行计划有问题，导致cpu偏高。由于gg中的语句不能更改，怎样才能改变不能更改的语句的执行计划呢，这里可以采用sqlprofile来优化。
[szggs1@szodsd01] $ top -h
System: szodsd01 Wed Nov 5 10:17:16 2014
Load averages: 0.42, 0.47, 0.49
1007 processes: 599 sleeping, 408 running
Cpu 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% 0.0%
System Page Size: 4Kbytes
Memory: 40107992K (35984460K) real, 53292056K (48597616K) virtual, 12423080K free Page# 1/44
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
3 ? 1697 oracle 178 20 25460M 6420K run 1780:54 95.10 94.93 oracleszodsd1
11 ? 28561 oracle 178 20 25461M 7236K sleep 55:06 8.01 8.00 oracleszodsd1
SQL> @getsql_spid
Enter value for ospid: 1697
ADDR
----------------
C00000037A0351C8
SID_SERIAL SQL_ID_NUM P_NAME P_VALUE SQL_ID USERNAME PROGRAM EVENT STATUS BLOCKING_SESSION
---------- --------------- --------------- --------------- ------------- ---------- --------------- -------------------- -------- ----------------
3152,32913 6khsffxsn05tg,0 driver id 1413697536 6khsffxsn05tg GGS replicat@szodsd SQL*Net message from ACTIVE
#bytes 1 01 (TNS V1-V3) client
0
SQL_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 = 1
--上面由replicat和GGS可见是gg的语句造成的
--查看gg中TB_XX_XXXX_XXXX_XXX对应的进程为RSZSPSE
SQL> @getggmap
Session altered.
Enter value for ttable: TB_XX_XXXX_XXXX_XXX
Enter value for towner: SPS_SZ
STATS LOG_TIME PNAME SOURCE_OWNER SOURCE_TABLENAME TARGET_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 szggs1
1 row selected.
SQL>
--果然延迟了很长时间，6个钟
GGSCI (szodsd01) 2> info RSZSPSE
REPLICAT RSZSPSE Last Started 2014-11-01 14:00 Status RUNNING
Checkpoint Lag 06:14:03 (updated 00:02:40 ago)
Log Read Checkpoint File /odsd/szggs01/ggdata/sz/rszspsa/dirdat/si248293
2014-11-05 04:08:32.005523 RBA 1349428
--查看执行计划和outline信息，后面用sqlprofile优化
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_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:outline
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan 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 / 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):
---------------------------------------------------
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 - access("OP_DATE"=:B6)
13 - access("PLAN_RESULT"=TO_NUMBER(:B3))
Note
-----
- dynamic sampling used for this statement
--执行计划中一大堆的BITMAP关键字，很容易误以为这几个索引是bitmap索引，其实是普通索引来的。
--获取表和索引的相关信息
OWNER TABLE_NAME OBJECT_TYPE STA LAST_ANALYZED
-------------------- ------------------------------ ------------ --- -------------------
SPS_SZ TB_XX_XXXX_XXXX_XXX TABLE
--上表TB_XX_XXXX_XXXX_XXX统计信息过期
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb)
-------------------- ------------------------------ -------------------- ----------
SPS_SZ IX_CC_CCC_CCCC_CCCCCC INDEX 8434.625
SPS_SZ IX_AA_AAA_AAAAAAA INDEX 11176.625
SPS_SZ IX_AA_AAA_AAAAAAA_PLANID INDEX 11484.4375
SPS_SZ IX_BB_BBB_BBBB_BBBBBB INDEX 13138.5
SPS_SZ TB_XX_XXXX_XXXX_XXX TABLE 38322
--索引加起来都要比表大
OWNER INDEX_NAME TABLE_NAME PAR UNIQUENES DEGREE INDEX_TYPE LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR %
-------------------- ------------------------------ ------------------------------ --- --------- ---------- ---------- ----------- ---------- ----------------- ----------
SPS_SZ IX_AA_AAA_AAAAAAA TB_XX_XXXX_XXXX_XXX NO NONUNIQUE 1 NORMAL 284214 2 185996490 100
SPS_SZ IX_CC_CCC_CCCC_CCCCCC TB_XX_XXXX_XXXX_XXX NO NONUNIQUE 1 NORMAL 206567 2 6963531 .000006899
SPS_SZ IX_BB_BBB_BBBB_BBBBBB TB_XX_XXXX_XXXX_XXX NO NONUNIQUE 1 NORMAL 299483 2 6898441 .000049673
SPS_SZ IX_AA_AAA_AAAAAAA_PLANID TB_XX_XXXX_XXXX_XXX NO NONUNIQUE 1 NORMAL 284214 2 31263628 19.8460594
--由上可知，索引IX_AA_AAA_AAAAAAA的集群因子100，distinct_keys/NUM_ROWS*100=100，选择性非常好，类似于主键，基本返回1行。
--其他3个索引非常垃圾，完全可以不用。如果走4个索引的话，体积都大于全表扫描了，而且INDEX RANGE SCAN 是单块读，全表是多块读，还不如走全表了。
--由上面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")))
--这里走bitmap肯定是错的，这里可以走索引IX_AA_AAA_AAAAAAA，但如何得到走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_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:outline
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 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
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")) --要用INDEX_RS_ASC这个hint，把A替换成表名就是了
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_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
--使用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 "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;
/
--验证执行计划是否只走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_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
-------------------------------------------------------------------------------------------------------------------
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_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 statement
--执行计划对了。虽然纠正了执行计划，但毕竟索引IX_AA_AAA_AAAAAAA有11G，cpu还是有点偏高，扫描起来还是有点慢的，只有delete操作完了后才会降下来。但是现在效率上已经好很多了。
--相关的视图
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:58
5 rows selected.
SQL> col attr_val for a80
SQL> select name,attr_val from dba_sql_profiles a, sys.sqlprof$attr b
2 where a.signature = 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.
--@脚本
@getsql_spid
set lines 200 pages 200 long 100000
col USERNAME for a10
col PROGRAM for a15
col EVENT for a20
col sid_serial for a10
col sql_id_num for a15
col p_name for a15
col p_value for a15
col addr new_value addr
col sql_id new_value sql_id
SELECT 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='&sql_id';
cl col
--@getplan
set feedback off
pro 'general,outline,starts'
pro
acc 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 on
undef type
/** 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
where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。