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 |
-------------------