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