性能优化案例

1. 性能故障说明

维护人员说某套系统CPU使用率最近一直是用满的状态,打算对CPU进行扩容,观察vmstat性能如下:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
  r  b  swpd  free  buff  cache  si  so    bi    bo  in  cs us sy id wa st
44  0      0 16822984 1105404 8724292    0    0    3  5325 28630 15461 94  6  0  0  0
25  0      0 16799116 1105404 8724332    0    0    3  3300 29980 17449 92  8  0  0  0
28  0      0 16793636 1105404 8724304    0    0    3  3659 30249 18937 91  9  0  0  0
22  0      0 16853312 1105404 8724488    0    0    3  3319 29179 15595 93  7  0  0  0
23  0      0 16858788 1105408 8724548    0    0    3  3349 31673 18385 93  7  0  0  0
25  0      0 16848492 1105408 8724636    0    0    3  3659 31221 19682 93  7  0  0  0
27  0      0 16848980 1105408 8724680    0    0    3  3009 30906 17680 94  6  0  0  0
27  0      0 16857392 1105408 8724680    0    0    3  6823 31478 20574 91  9  0  0  0
23  0      0 16858784 1105408 8724680    0    0    3  2776 29082 17251 92  8  0  0  0
18  0      0 16860192 1105408 8724776    0    0    3  2264 29445 16736 92  8  0  0  0
21  0      0 16859984 1105408 8724776    0    0    3  2310 30328 18046 92  8  0  0  0
17  0      0 16861028 1105408 8724776    0    0    3  3873 30016 18716 91  8  0  0  0
21  0      0 16868188 1105408 8724780    0    0    3  2342 28785 15878 93  7  1  0  0
19  0      0 16864384 1105408 8724816    0    0    3  3234 30149 19112 88  8  4  0  0
22  0      0 16859508 1105408 8724872    0    0    3  3277 30439 18367 93  7  0  0  0
15  0      0 16852080 1105408 8724880    0    0    3  3099 31660 19688 90  9  1  0  0
21  0      0 16852724 1105408 8724960    0    0    3  7193 31512 20733 91  7  2  0  0
21  0      0 16846104 1105412 8724992    0    0    3 17155 31024 20015 92  8  0  0  0
25  0      0 16849696 1105412 8725004    0    0    3 17167 28412 19286 92  7  1  0  0
19  0      0 16847732 1105412 8725032    0    0    3  3790 30366 17010 93  7  0  0  0

查看数据库等待如下:

SQL> set linesize 200
SQL> set pagesize 999
SQL> column username format a15
SQL> column sql_id format a15
SQL> column event format a40
SQL> column p1 format 999999999999999999999
SQL> column p2 format 999999999999999
SQL> column p3 format 99999999999
column sql_child_number format 9999 heading 'SQL Child|Number'
SQL> SQL> select sid,username, event,sql_id, sql_child_number, p1,p2,p3,last_call_et from v$session
  2  where status = 'ACTIVE'
  3  and type = 'USER'
  4  order by event, sql_id, p1;

                                                                                    SQL Child
      SID USERNAME        EVENT                                    SQL_ID            Number                    P1              P2          P3 LAST_CALL_ET
---------- --------------- ---------------------------------------- --------------- --------- ---------------------- ---------------- ------------ ------------
      3967 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      8532 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      7962 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      7945 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      7929 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      7911 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      7377 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      7348 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            2
      6812 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      5158 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      5129 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      5081 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      4586 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      4520 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      3463 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      3419 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            3
      2879 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      2853 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      2834 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      2327 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      2301 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      1750 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            2
      1715 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      1204 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            0
      645 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            2
      619 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      602 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            1
      594 BOSSWG          SQL*Net message from client              111unfphab6v6          0              675562835                1            0            2
      8539 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            1
      4017 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            1
      4594 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            2
      6232 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            2
      3961 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            0
      1164 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            1
      5647 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            1
      3443 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            0
      5157 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            0
      2874 BOSSWG          SQL*Net message from client              9p8448sgdhvm7          0              675562835                1            0            0
      3965 BOSSWG          SQL*Net message from client              a0y8k7t9ztxcy          0              675562835                1            0            3
      8498 BOSSWG          SQL*Net message from client              gq2pd81wjhwuy          0            1413697536                1            0            0
      3980 BOSSWG          SQL*Net message to client                87v9qh9vtsf43          1            1413697536                1            0            3
      1194 SYS            SQL*Net message to client                g598t7yfwp5az          0            1650815232                1            0            0
      7358 BOSSWG          db file sequential read                  0xn6jnh1bg857          0                    67          177105            1          90
        72 BOSSWG          direct path read                        bwrw2kw9sdtff          0                    13          193920          32            5
      638 BOSSWG          direct path read                        bwrw2kw9sdtff          0                    13          193984          32            5
      7355                jobq slave wait                                                  0                      0                0            0          13
      6781                jobq slave wait                                                  0                      0                0            0            3
      6847 BOSSWG          latch: cache buffers chains              111unfphab6v6          0            33331760392              177            0            2
      1703 BOSSWG          latch: cache buffers chains              111unfphab6v6          0            33336120992              177            0            3
      6798 BOSSWG          latch: cache buffers chains              111unfphab6v6          0            33338691792              177            0            3
      581 BOSSWG          latch: cache buffers chains              111unfphab6v6          0            33338691792              177            0            2
      2849 BOSSWG          latch: cache buffers chains              111unfphab6v6          0            33479366192              177            0            2
      8512 BOSSWG          latch: cache buffers chains              111unfphab6v6          0            33484793592              177            0            2
        34 BOSSWG          latch: cache buffers chains              74q49fftwkhfx          0            33338279792              177            0        2071
      3458 BOSSWG          latch: cache buffers chains              9p8448sgdhvm7          0            33337193192              177            0            0
      6801 BOSSWG          latch: cache buffers chains              burxsp6vxpddy          0            33329751592              177            0          29
      4548 BOSSWG          latch: cache buffers chains              fmqwd5r9rsbgj          0            33485708592              177            0          870
      2296 BOSSWG          library cache: mutex X                  111unfphab6v6          0              603609850                0            4            1

58 rows selected.

主要问题集中在111unfphab6v6这个SQL上,查看当前的执行计划如下:

SQL> set linesize 160 pagesize 999
SQL> select * from table(dbms_xplan.display_cursor('111unfphab6v6',null, 'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  111unfphab6v6, child number 0
-------------------------------------
MERGE INTO perf_last_data p USING (SELECT collect_time,
c.class_id,        config_name,        table_field_id, decode(
b.table_field_id,191020,HOST_STATE ) perf_value, field_name_cn
perf_field_name_cn  FROM (SELECT * /*+ index(t) */          FROM
PERT_HOST_MONITOR          WHERE ne_id=:1 and collect_time=:2) a,
(select *          from data_table_fields d          where
d.table_name = upper('PERT_HOST_MONITOR')            and d.field_name
not in ('NE_ID','COLLECT_TIME','CREATE_TIME','CONFIG_NAME','CLASSNAME')
          /*and d.field_type = 'NUMBER'*/) b,            (select
class_id from ci_base_element where instance_id=:3) c ) np ON
(p.ci_name = np.config_name AND p.class_id = np.class_id AND
p.table_field_id = np.table_field_id) WHEN NOT MATCHED THEN  INSERT
VALUES    (np.config_name,      np.class_id,      np.table_field_id,
  np.perf_field_name_cn,      np.perf_value,      np.collect_time,
null) WHEN MATCHED THEN  UPDATE      SET p.collect_time = np.collect

Plan hash value: 327937592

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|  0 | MERGE STATEMENT                          |                        |      |      |      | 13599 (100)|          |      |      |
|  1 |  MERGE                                  | PERF_LAST_DATA          |      |      |      |            |          |      |      |
|  2 |  VIEW                                  |                        |      |      |      |            |          |      |      |
|*  3 |    HASH JOIN OUTER                      |                        |  252K|    89M|    75M| 13599  (1)| 00:02:44 |      |      |
|  4 |    VIEW                                |                        |  252K|    72M|      |    18  (0)| 00:00:01 |      |      |
|  5 |      MERGE JOIN CARTESIAN                |                        |  252K|    53M|      |    18  (0)| 00:00:01 |      |      |
|  6 |      NESTED LOOPS                      |                        |    12 |  708 |      |    6  (0)| 00:00:01 |      |      |
|  7 |        TABLE ACCESS BY INDEX ROWID      | CI_BASE_ELEMENT        |    1 |    11 |      |    3  (0)| 00:00:01 |      |      |
|*  8 |        INDEX UNIQUE SCAN                | PK_CI_BASE_ELEMENT      |    1 |      |      |    2  (0)| 00:00:01 |      |      |
|*  9 |        TABLE ACCESS BY INDEX ROWID      | DATA_TABLE_FIELDS      |    12 |  576 |      |    3  (0)| 00:00:01 |      |      |
|* 10 |        INDEX RANGE SCAN                | IDX_DATA_TABLE_NAME    |    13 |      |      |    1  (0)| 00:00:01 |      |      |
|  11 |      BUFFER SORT                        |                        | 20624 |  3262K|      |    15  (0)| 00:00:01 |      |      |
|  12 |        PARTITION RANGE SINGLE            |                        | 20624 |  3262K|      |    1  (0)| 00:00:01 |  KEY |  KEY |
|* 13 |        TABLE ACCESS BY LOCAL INDEX ROWID| PERT_HOST_MONITOR      | 20624 |  3262K|      |    1  (0)| 00:00:01 |  KEY |  KEY |
|* 14 |          INDEX RANGE SCAN                | IDX_PERT_HOST_MONITOR_1 |  1127 |      |      |    1  (0)| 00:00:01 |  KEY |  KEY |
|  15 |    TABLE ACCESS FULL                    | PERF_LAST_DATA          |  1705K|  110M|      |  3373  (1)| 00:00:41 |      |      |
--------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

  1 - MRG$1
  3 - SEL$F5BB74E1
  4 - SEL$C9CA1E20 / NP@SEL$1
  5 - SEL$C9CA1E20
  7 - SEL$C9CA1E20 / CI_BASE_ELEMENT@SEL$6
  8 - SEL$C9CA1E20 / CI_BASE_ELEMENT@SEL$6
  9 - SEL$C9CA1E20 / D@SEL$5
  10 - SEL$C9CA1E20 / D@SEL$5
  13 - SEL$C9CA1E20 / PERT_HOST_MONITOR@SEL$4
  14 - SEL$C9CA1E20 / PERT_HOST_MONITOR@SEL$4
  15 - SEL$F5BB74E1 / P@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$C9CA1E20")
      MERGE(@"SEL$4")
      MERGE(@"SEL$5")
      MERGE(@"SEL$6")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$06F95CF3")
      MERGE(@"SEL$10")
      MERGE(@"SEL$8")
      MERGE(@"SEL$9")
      OUTLINE_LEAF(@"MRG$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$9")
      NO_ACCESS(@"MRG$1" "from$_subquery$_018"@"MRG$1")
      FULL(@"MRG$1" "P"@"MRG$1")
      NO_ACCESS(@"MRG$1" "NP"@"MRG$1")
      LEADING(@"MRG$1" "from$_subquery$_018"@"MRG$1" "P"@"MRG$1" "NP"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "P"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "NP"@"MRG$1")
      NO_ACCESS(@"SEL$F5BB74E1" "NP"@"SEL$1")
      FULL(@"SEL$F5BB74E1" "P"@"SEL$2")
      LEADING(@"SEL$F5BB74E1" "NP"@"SEL$1" "P"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "P"@"SEL$2")
      INDEX(@"SEL$06F95CF3" "CI_BASE_ELEMENT"@"SEL$10" ("CI_BASE_ELEMENT"."INSTANCE_ID"))
      INDEX_RS_ASC(@"SEL$06F95CF3" "D"@"SEL$9" ("DATA_TABLE_FIELDS"."TABLE_NAME"))
      INDEX_RS_ASC(@"SEL$06F95CF3" "PERT_HOST_MONITOR"@"SEL$8" ("PERT_HOST_MONITOR"."COLLECT_TIME" "PERT_HOST_MONITOR"."CONFIG_NAME"))
      LEADING(@"SEL$06F95CF3" "CI_BASE_ELEMENT"@"SEL$10" "D"@"SEL$9" "PERT_HOST_MONITOR"@"SEL$8")
      USE_NL(@"SEL$06F95CF3" "D"@"SEL$9")
      USE_MERGE_CARTESIAN(@"SEL$06F95CF3" "PERT_HOST_MONITOR"@"SEL$8")
      INDEX_RS_ASC(@"SEL$C9CA1E20" "CI_BASE_ELEMENT"@"SEL$6" ("CI_BASE_ELEMENT"."INSTANCE_ID"))
      INDEX_RS_ASC(@"SEL$C9CA1E20" "D"@"SEL$5" ("DATA_TABLE_FIELDS"."TABLE_NAME"))
      INDEX_RS_ASC(@"SEL$C9CA1E20" "PERT_HOST_MONITOR"@"SEL$4" ("PERT_HOST_MONITOR"."COLLECT_TIME" "PERT_HOST_MONITOR"."CONFIG_NAME"))
      LEADING(@"SEL$C9CA1E20" "CI_BASE_ELEMENT"@"SEL$6" "D"@"SEL$5" "PERT_HOST_MONITOR"@"SEL$4")
      USE_NL(@"SEL$C9CA1E20" "D"@"SEL$5")
      USE_MERGE_CARTESIAN(@"SEL$C9CA1E20" "PERT_HOST_MONITOR"@"SEL$4")
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

  1 - :1 (VARCHAR2(30), CSID=852): '74817660'
  2 - :2 (DATE): 07/20/2017 22:50:29
  3 - :3 (VARCHAR2(30), CSID=852): '74817660'

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("P"."TABLE_FIELD_ID"="NP"."TABLE_FIELD_ID" AND "P"."CLASS_ID"="NP"."CLASS_ID" AND "P"."CI_NAME"="NP"."CONFIG_NAME")
  8 - access("INSTANCE_ID"=TO_NUMBER(:3))
  9 - filter(("D"."FIELD_NAME"<>'NE_ID' AND "D"."FIELD_NAME"<>'COLLECT_TIME' AND "D"."FIELD_NAME"<>'CREATE_TIME' AND
              "D"."FIELD_NAME"<>'CONFIG_NAME' AND "D"."FIELD_NAME"<>'CLASSNAME'))
  10 - access("D"."TABLE_NAME"='PERT_HOST_MONITOR')
  13 - filter("NE_ID"=TO_NUMBER(:1))
  14 - access("COLLECT_TIME"=:2)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

  1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
  2 - "P"."PERF_VALUE"[NUMBER,22], "NP"."COLLECT_TIME"[DATE,7], "NP"."CLASS_ID"[NUMBER,22], "NP"."CONFIG_NAME"[VARCHAR2,250],
      "NP"."TABLE_FIELD_ID"[NUMBER,22], "NP"."PERF_VALUE"[NUMBER,22], "NP"."PERF_FIELD_NAME_CN"[VARCHAR2,250]
  3 - (#keys=3) "NP"."TABLE_FIELD_ID"[NUMBER,22], "P"."TABLE_FIELD_ID"[NUMBER,22], "NP"."CLASS_ID"[NUMBER,22],
      "P"."CLASS_ID"[NUMBER,22], "NP"."CONFIG_NAME"[VARCHAR2,250], "P"."CI_NAME"[VARCHAR2,250], "NP"."COLLECT_TIME"[DATE,7],
      "NP"."PERF_VALUE"[NUMBER,22], "NP"."PERF_FIELD_NAME_CN"[VARCHAR2,250], "P".ROWID[ROWID,10], "P"."PERF_VALUE"[NUMBER,22],
      "P"."COLLECT_TIME"[DATE,7], "P"."PERF_STATE_VALUE"[VARCHAR2,50], "P"."PERF_FIELD_NAME_CN"[VARCHAR2,200]
  4 - "NP"."COLLECT_TIME"[DATE,7], "NP"."CLASS_ID"[NUMBER,22], "NP"."CONFIG_NAME"[VARCHAR2,250], "NP"."TABLE_FIELD_ID"[NUMBER,22],
      "NP"."PERF_VALUE"[NUMBER,22], "NP"."PERF_FIELD_NAME_CN"[VARCHAR2,250]
  5 - "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22], "CLASS_ID"[NUMBER,22], "D".ROWID[ROWID,10],
      "D"."TABLE_FIELD_ID"[NUMBER,22], "D"."TABLE_NAME"[VARCHAR2,50], "D"."FIELD_NAME"[VARCHAR2,50], "D"."FIELD_NAME_CN"[VARCHAR2,250],
      "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7], "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250],
      "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  6 - "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22], "CLASS_ID"[NUMBER,22], "D".ROWID[ROWID,10],
      "D"."TABLE_FIELD_ID"[NUMBER,22], "D"."TABLE_NAME"[VARCHAR2,50], "D"."FIELD_NAME"[VARCHAR2,50], "D"."FIELD_NAME_CN"[VARCHAR2,250]
  7 - "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22], "CLASS_ID"[NUMBER,22]
  8 - "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22]
  9 - "D".ROWID[ROWID,10], "D"."TABLE_FIELD_ID"[NUMBER,22], "D"."TABLE_NAME"[VARCHAR2,50], "D"."FIELD_NAME"[VARCHAR2,50],
      "D"."FIELD_NAME_CN"[VARCHAR2,250]
  10 - "D".ROWID[ROWID,10], "D"."TABLE_NAME"[VARCHAR2,50]
  11 - (#keys=0) "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  12 - "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  13 - "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  14 - "PERT_HOST_MONITOR".ROWID[ROWID,10], "COLLECT_TIME"[DATE,7], "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250]
  15 - "P".ROWID[ROWID,10], "P"."CI_NAME"[VARCHAR2,250], "P"."CLASS_ID"[NUMBER,22], "P"."TABLE_FIELD_ID"[NUMBER,22],
      "P"."PERF_FIELD_NAME_CN"[VARCHAR2,200], "P"."PERF_VALUE"[NUMBER,22], "P"."COLLECT_TIME"[DATE,7], "P"."PERF_STATE_VALUE"[VARCHAR2,50]

Note
-----
  - dynamic sampling used for this statement (level=2)


168 rows selected.

2. SQL性能分析

完整SQL如下(已经在表名前加上了shcema名称):

MERGE INTO BOSSWG.perf_last_data p
USING (SELECT collect_time,
              c.class_id,
              config_name,
              table_field_id,
              decode(b.table_field_id, 191020, HOST_STATE) perf_value,
              field_name_cn perf_field_name_cn
        FROM (SELECT * /*+ index(t) */
                FROM BOSSWG.PERT_HOST_MONITOR
                WHERE ne_id = '74817660'
                  and collect_time = to_date('07/20/2017 22:50:29', 'MM/DD/YYYY HH24:MI:SS')) a,
              (select *
                from BOSSWG.data_table_fields d
                where d.table_name = upper('PERT_HOST_MONITOR')
                  and d.field_name not in ('NE_ID',
                                          'COLLECT_TIME',
                                          'CREATE_TIME',
                                          'CONFIG_NAME',
                                          'CLASSNAME')
              /*and d.field_type = 'NUMBER'*/
              ) b,
              (select class_id from BOSSWG.ci_base_element where instance_id = '74817660') c) np
ON (p.ci_name = np.config_name AND p.class_id = np.class_id AND p.table_field_id = np.table_field_id)
WHEN NOT MATCHED THEN
  INSERT
  VALUES
    (np.config_name,
    np.class_id,
    np.table_field_id,
    np.perf_field_name_cn,
    np.perf_value,
    np.collect_time,
    null)
WHEN MATCHED THEN
  UPDATE
    SET p.collect_time = np.collect_time,
        p.perf_value  = nvl(np.perf_value, p.perf_value)

从执行计划来看,可能和PERF_LAST_DATA的全表扫描有关,

SQL> ora size bosswg.PERF_LAST_DATA
    USER# OWNER    OBJECT_NAME    PARTITION_NAME OBJECT_TYPE SIZE_MB SEQ SIZE_GB EXTENTS SEGMENTS INIT_KB NEXT_KB TABLESPACE_NAME  ROWS#
    ----- ------ ----------------- -------------- ----------- ------- --- ------- ------- -------- ------- ------- --------------- -------
      93 BOSSWG PERF_LAST_DATA                  TABLE          229  1  0.224    102        1      64      8 BOSSWG_CFG      2903639
      93 BOSSWG PK_PERF_LAST_DATA                INDEX        202.38  2  0.198    236        1      64    1024 BOSSWG_INDEX

代入绑定变量后,NP查出来,只有一行记录,逻辑读也不高

SQL> set autotrace traceonly exp stat
SQL> set linesize 180
SQL> select * from 
  2  (SELECT collect_time,
  3                c.class_id,
  4                config_name,
  5                table_field_id,
  6                decode(b.table_field_id, 191020, HOST_STATE) perf_value,
  7                field_name_cn perf_field_name_cn
  8          FROM (SELECT * /*+ index(t) */
  9                  FROM BOSSWG.PERT_HOST_MONITOR
10                  WHERE ne_id = '74817660'
11                    and collect_time = to_date('07/20/2017 22:50:29', 'MM/DD/YYYY HH24:MI:SS')) a,
12                (select *
                from BOSSWG.data_table_fields d
13  14                  where d.table_name = upper('PERT_HOST_MONITOR')
                  and d.field_name not in ('NE_ID',
15  16                                            'COLLECT_TIME',
17                                            'CREATE_TIME',
18                                            'CONFIG_NAME',
19                                            'CLASSNAME')
20                /*and d.field_type = 'NUMBER'*/
21                ) b,
22                (select class_id from BOSSWG.ci_base_element where instance_id = '74817660') c) np;


Execution Plan
----------------------------------------------------------
Plan hash value: 1714412308

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                        | 31542 |  6807K|  294  (0)| 00:00:04 |      |      |
|  1 |  MERGE JOIN CARTESIAN                |                        | 31542 |  6807K|  294  (0)| 00:00:04 |      |      |
|  2 |  NESTED LOOPS                      |                        |    12 |  708 |    6  (0)| 00:00:01 |      |      |
|  3 |    TABLE ACCESS BY INDEX ROWID      | CI_BASE_ELEMENT        |    1 |    11 |    3  (0)| 00:00:01 |      |      |
|*  4 |    INDEX UNIQUE SCAN                | PK_CI_BASE_ELEMENT      |    1 |      |    2  (0)| 00:00:01 |      |      |
|*  5 |    TABLE ACCESS BY INDEX ROWID      | DATA_TABLE_FIELDS      |    12 |  576 |    3  (0)| 00:00:01 |      |      |
|*  6 |    INDEX RANGE SCAN                | IDX_DATA_TABLE_NAME    |    13 |      |    1  (0)| 00:00:01 |      |      |
|  7 |  BUFFER SORT                        |                        |  2575 |  407K|  291  (0)| 00:00:04 |      |      |
|  8 |    PARTITION RANGE SINGLE            |                        |  2575 |  407K|    24  (0)| 00:00:01 |    7 |    7 |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID| PERT_HOST_MONITOR      |  2575 |  407K|    24  (0)| 00:00:01 |    7 |    7 |
|* 10 |      INDEX RANGE SCAN                | IDX_PERT_HOST_MONITOR_1 |  2042 |      |    24  (0)| 00:00:01 |    7 |    7 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  4 - access("INSTANCE_ID"=74817660)
  5 - filter("D"."FIELD_NAME"<>'NE_ID' AND "D"."FIELD_NAME"<>'COLLECT_TIME' AND "D"."FIELD_NAME"<>'CREATE_TIME' AND
              "D"."FIELD_NAME"<>'CONFIG_NAME' AND "D"."FIELD_NAME"<>'CLASSNAME')
  6 - access("D"."TABLE_NAME"='PERT_HOST_MONITOR')
  9 - filter("NE_ID"=74817660)
  10 - access("COLLECT_TIME"=TO_DATE(' 2017-07-20 22:50:29', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
  - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        22  consistent gets
          0  physical reads
          0  redo size
        952  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

所以这个问题的主要原因就在于bosswg.PERF_LAST_DATA的全表扫描了,观察SQL,在bosswg.PERF_LAST_DATA表上,关于
merge into on 关联上的字段是有索引的,但实际的执行划未用上。

SQL> desc BOSSWG.perf_last_data
    TABLE : BOSSWG.PERF_LAST_DATA
    ====================================================================================================
    NO#        NAME          DATA_TYPE  NULLABLE Default Hidden? AVG_LEN  NDV  Nulls(%) CARDINALITY    HISTOGRAM    COMMENTS
    --- ------------------ ------------- -------- ------- ------- ------- ------ -------- ----------- --------------- ----------
      1 CI_NAME            VARCHAR2(250) NOT NULL        NO          22 122978        0      13.87                CI名称
      2 CLASS_ID          NUMBER(9)    NOT NULL        NO            6    16        0  106576.88 FREQUENCY      CI类标识
      3 TABLE_FIELD_ID    NUMBER(12)    NOT NULL        NO            5    202        0    8441.73 FREQUENCY      字段标识
      4 PERF_FIELD_NAME_CN VARCHAR2(200)                  NO          25    196    0.02    8698.26                性能项名称
      5 PERF_VALUE        NUMBER(30,5)                  NO            2 107665    57.76        6.69 HEIGHT BALANCED 性能项值
      6 COLLECT_TIME      DATE          NOT NULL sysdate NO            8  14998        0      113.7 HEIGHT BALANCED 采集时间
      7 PERF_STATE_VALUE  VARCHAR2(50)                  NO                                                          性能状态值

    ====================================================================================================
      INDEX_NAME    INDEX_TYPE UNIQUE PARTITIONED LOCALITY STATUS BLEVEL LEAF_BLOCKS DISTINCT_KEYS    LAST_ANALYZED    NO#  COLUMN_NAME  DESCEND
    ----------------- ---------- ------ ----------- -------- ------ ------ ----------- ------------- ------------------- --- -------------- -------
    PK_PERF_LAST_DATA NORMAL    YES    NO          GLOBAL  VALID      2      14659      1705230 2017-01-12 19:57:37  1 CI_NAME        ASC
                                                                                                                          2 CLASS_ID      ASC
                                                                                                                          3 TABLE_FIELD_ID ASC

    ====================================================================================================
          CONSTRAINT_NAME        CTYPE      R_TABLE          R_CONSTRAINT    C_CONDITION STATUS  DEFERRED  VALIDATED  COLUMN_NAME
    ------------------------------ ----- ----------------- -------------------- ----------- ------- --------- --------- --------------
    FK_PERLASDATA_CLID_R_CICLATREE R    CI_CLASS_TREE    PK_CI_CLASS_TREE                ENABLED IMMEDIATE VALIDATED CLASS_ID
    FK_PERLASDATA_TAID_R_DATTABFIE R    DATA_TABLE_FIELDS PK_DATA_TABLE_FIELDS            ENABLED IMMEDIATE VALIDATED TABLE_FIELD_ID
    PK_PERF_LAST_DATA              P                                                        ENABLED IMMEDIATE VALIDATED CI_NAME
                                                                                                                        CLASS_ID
                                                                                                                        TABLE_FIELD_ID

所以,可以通过hint来修改执行计划,测试如下:

SQL> MERGE /*+leading(np) no_merge(np) use_nl(np, p)*/ INTO BOSSWG.perf_last_data p
  2  USING (SELECT collect_time,
  3                c.class_id,
  4                config_name,
  5                table_field_id,
  6                decode(b.table_field_id, 191020, HOST_STATE) perf_value,
  7                field_name_cn perf_field_name_cn
  8          FROM (SELECT * /*+ index(t) */
  9                  FROM BOSSWG.PERT_HOST_MONITOR
10                  WHERE ne_id = '74817660'
11                    and collect_time = to_date('07/20/2017 22:50:29', 'MM/DD/YYYY HH24:MI:SS')) a,
12                (select *
13                  from BOSSWG.data_table_fields d
14                  where d.table_name = upper('PERT_HOST_MONITOR')
15                    and d.field_name not in ('NE_ID',
16                                            'COLLECT_TIME',
17                                            'CREATE_TIME',
18                                            'CONFIG_NAME',
19                                            'CLASSNAME')
20                /*and d.field_type = 'NUMBER'*/
21                ) b,
22                (select class_id from BOSSWG.ci_base_element where instance_id = '74817660') c) np
23  ON (p.ci_name = np.config_name AND p.class_id = np.class_id AND p.table_field_id = np.table_field_id)
24  WHEN NOT MATCHED THEN
25    INSERT
26    VALUES
27      (np.config_name,
28      np.class_id,
29      np.table_field_id,
30      np.perf_field_name_cn,
31      np.perf_value,
32      np.collect_time,
33      null)
34  WHEN MATCHED THEN
35    UPDATE
36      SET p.collect_time = np.collect_time,
37          p.perf_value  = nvl(np.perf_value, p.perf_value);

1 row merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 2979300314

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|  0 | MERGE STATEMENT                          |                        | 31542 |    11M| 31851  (1)| 00:06:23 |      |      |
|  1 |  MERGE                                  | PERF_LAST_DATA          |      |      |            |          |      |      |
|  2 |  VIEW                                  |                        |      |      |            |          |      |      |
|  3 |    NESTED LOOPS OUTER                    |                        | 31542 |    11M| 31851  (1)| 00:06:23 |      |      |
|  4 |    VIEW                                |                        | 31542 |  9302K|  294  (0)| 00:00:04 |      |      |
|  5 |      MERGE JOIN CARTESIAN                |                        | 31542 |  6807K|  294  (0)| 00:00:04 |      |      |
|  6 |      NESTED LOOPS                      |                        |    12 |  708 |    6  (0)| 00:00:01 |      |      |
|  7 |        TABLE ACCESS BY INDEX ROWID      | CI_BASE_ELEMENT        |    1 |    11 |    3  (0)| 00:00:01 |      |      |
|*  8 |        INDEX UNIQUE SCAN                | PK_CI_BASE_ELEMENT      |    1 |      |    2  (0)| 00:00:01 |      |      |
|*  9 |        TABLE ACCESS BY INDEX ROWID      | DATA_TABLE_FIELDS      |    12 |  576 |    3  (0)| 00:00:01 |      |      |
|* 10 |        INDEX RANGE SCAN                | IDX_DATA_TABLE_NAME    |    13 |      |    1  (0)| 00:00:01 |      |      |
|  11 |      BUFFER SORT                        |                        |  2575 |  407K|  291  (0)| 00:00:04 |      |      |
|  12 |        PARTITION RANGE SINGLE            |                        |  2575 |  407K|    24  (0)| 00:00:01 |    7 |    7 |
|* 13 |        TABLE ACCESS BY LOCAL INDEX ROWID| PERT_HOST_MONITOR      |  2575 |  407K|    24  (0)| 00:00:01 |    7 |    7 |
|* 14 |          INDEX RANGE SCAN                | IDX_PERT_HOST_MONITOR_1 |  2042 |      |    24  (0)| 00:00:01 |    7 |    7 |
|  15 |    TABLE ACCESS BY INDEX ROWID          | PERF_LAST_DATA          |    1 |    68 |    2  (0)| 00:00:01 |      |      |
|* 16 |      INDEX UNIQUE SCAN                  | PK_PERF_LAST_DATA      |    1 |      |    1  (0)| 00:00:01 |      |      |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  8 - access("INSTANCE_ID"=74817660)
  9 - filter("D"."FIELD_NAME"<>'NE_ID' AND "D"."FIELD_NAME"<>'COLLECT_TIME' AND "D"."FIELD_NAME"<>'CREATE_TIME' AND
              "D"."FIELD_NAME"<>'CONFIG_NAME' AND "D"."FIELD_NAME"<>'CLASSNAME')
  10 - access("D"."TABLE_NAME"='PERT_HOST_MONITOR')
  13 - filter("NE_ID"=74817660)
  14 - access("COLLECT_TIME"=TO_DATE(' 2017-07-20 22:50:29', 'syyyy-mm-dd hh24:mi:ss'))
  16 - access("P"."CI_NAME"(+)="NP"."CONFIG_NAME" AND "P"."CLASS_ID"(+)="NP"."CLASS_ID" AND
              "P"."TABLE_FIELD_ID"(+)="NP"."TABLE_FIELD_ID")

Note
-----
  - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        10  recursive calls
          5  db block gets
        281  consistent gets
        28  physical reads
        412  redo size
        836  bytes sent via SQL*Net to client
      2292  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

修改执行计划后,性能恢复

3. 绑定SQL的执行计划

SQL> explain plan for
  2  MERGE /*+leading(np) no_merge(np) use_nl(np, p)*/ INTO BOSSWG.perf_last_data p
  3  USING (SELECT collect_time,
  4                c.class_id,
  5                config_name,
  6                table_field_id,
  7                decode(b.table_field_id, 191020, HOST_STATE) perf_value,
  8                field_name_cn perf_field_name_cn
  9          FROM (SELECT * /*+ index(t) */
10                  FROM BOSSWG.PERT_HOST_MONITOR
11                  WHERE ne_id = '74817660'
12                    and collect_time = to_date('07/20/2017 22:50:29', 'MM/DD/YYYY HH24:MI:SS')) a,
13                (select *
14                  from BOSSWG.data_table_fields d
15                  where d.table_name = upper('PERT_HOST_MONITOR')
16                    and d.field_name not in ('NE_ID',
17                                            'COLLECT_TIME',
18                                            'CREATE_TIME',
19                                            'CONFIG_NAME',
20                                            'CLASSNAME')
21                /*and d.field_type = 'NUMBER'*/
22                ) b,
23                (select class_id from BOSSWG.ci_base_element where instance_id = '74817660') c) np
24  ON (p.ci_name = np.config_name AND p.class_id = np.class_id AND p.table_field_id = np.table_field_id)
25  WHEN NOT MATCHED THEN
26    INSERT
27    VALUES
28      (np.config_name,
29      np.class_id,
30      np.table_field_id,
31      np.perf_field_name_cn,
32      np.perf_value,
33      np.collect_time,
34      null)
35  WHEN MATCHED THEN
36    UPDATE
37      SET p.collect_time = np.collect_time,
38          p.perf_value  = nvl(np.perf_value, p.perf_value);

Explained.

SQL> set linesize 180 pagesize 999
SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2979300314

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
|  0 | MERGE STATEMENT                          |                        | 31542 |    11M| 31851  (1)| 00:06:23 |      |      |
|  1 |  MERGE                                  | PERF_LAST_DATA          |      |      |            |          |      |      |
|  2 |  VIEW                                  |                        |      |      |            |          |      |      |
|  3 |    NESTED LOOPS OUTER                    |                        | 31542 |    11M| 31851  (1)| 00:06:23 |      |      |
|  4 |    VIEW                                |                        | 31542 |  9302K|  294  (0)| 00:00:04 |      |      |
|  5 |      MERGE JOIN CARTESIAN                |                        | 31542 |  6807K|  294  (0)| 00:00:04 |      |      |
|  6 |      NESTED LOOPS                      |                        |    12 |  708 |    6  (0)| 00:00:01 |      |      |
|  7 |        TABLE ACCESS BY INDEX ROWID      | CI_BASE_ELEMENT        |    1 |    11 |    3  (0)| 00:00:01 |      |      |
|*  8 |        INDEX UNIQUE SCAN                | PK_CI_BASE_ELEMENT      |    1 |      |    2  (0)| 00:00:01 |      |      |
|*  9 |        TABLE ACCESS BY INDEX ROWID      | DATA_TABLE_FIELDS      |    12 |  576 |    3  (0)| 00:00:01 |      |      |
|* 10 |        INDEX RANGE SCAN                | IDX_DATA_TABLE_NAME    |    13 |      |    1  (0)| 00:00:01 |      |      |
|  11 |      BUFFER SORT                        |                        |  2575 |  407K|  291  (0)| 00:00:04 |      |      |
|  12 |        PARTITION RANGE SINGLE            |                        |  2575 |  407K|    24  (0)| 00:00:01 |    7 |    7 |
|* 13 |        TABLE ACCESS BY LOCAL INDEX ROWID| PERT_HOST_MONITOR      |  2575 |  407K|    24  (0)| 00:00:01 |    7 |    7 |
|* 14 |          INDEX RANGE SCAN                | IDX_PERT_HOST_MONITOR_1 |  2042 |      |    24  (0)| 00:00:01 |    7 |    7 |
|  15 |    TABLE ACCESS BY INDEX ROWID          | PERF_LAST_DATA          |    1 |    68 |    2  (0)| 00:00:01 |      |      |
|* 16 |      INDEX UNIQUE SCAN                  | PK_PERF_LAST_DATA      |    1 |      |    1  (0)| 00:00:01 |      |      |
------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

  1 - MRG$1
  3 - SEL$F5BB74E1
  4 - SEL$C9CA1E20 / NP@SEL$1
  5 - SEL$C9CA1E20
  7 - SEL$C9CA1E20 / CI_BASE_ELEMENT@SEL$6
  8 - SEL$C9CA1E20 / CI_BASE_ELEMENT@SEL$6
  9 - SEL$C9CA1E20 / D@SEL$5
  10 - SEL$C9CA1E20 / D@SEL$5
  13 - SEL$C9CA1E20 / PERT_HOST_MONITOR@SEL$4
  14 - SEL$C9CA1E20 / PERT_HOST_MONITOR@SEL$4
  15 - SEL$F5BB74E1 / P@SEL$2
  16 - SEL$F5BB74E1 / P@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_MERGE_CARTESIAN(@"SEL$C9CA1E20" "PERT_HOST_MONITOR"@"SEL$4")
      USE_NL(@"SEL$C9CA1E20" "D"@"SEL$5")
      LEADING(@"SEL$C9CA1E20" "CI_BASE_ELEMENT"@"SEL$6" "D"@"SEL$5" "PERT_HOST_MONITOR"@"SEL$4")
      INDEX_RS_ASC(@"SEL$C9CA1E20" "PERT_HOST_MONITOR"@"SEL$4" ("PERT_HOST_MONITOR"."COLLECT_TIME"
              "PERT_HOST_MONITOR"."CONFIG_NAME"))
      INDEX_RS_ASC(@"SEL$C9CA1E20" "D"@"SEL$5" ("DATA_TABLE_FIELDS"."TABLE_NAME"))
      INDEX_RS_ASC(@"SEL$C9CA1E20" "CI_BASE_ELEMENT"@"SEL$6" ("CI_BASE_ELEMENT"."INSTANCE_ID"))
      USE_MERGE_CARTESIAN(@"SEL$06F95CF3" "PERT_HOST_MONITOR"@"SEL$8")
      USE_NL(@"SEL$06F95CF3" "D"@"SEL$9")
      LEADING(@"SEL$06F95CF3" "CI_BASE_ELEMENT"@"SEL$10" "D"@"SEL$9" "PERT_HOST_MONITOR"@"SEL$8")
      INDEX_RS_ASC(@"SEL$06F95CF3" "PERT_HOST_MONITOR"@"SEL$8" ("PERT_HOST_MONITOR"."COLLECT_TIME"
              "PERT_HOST_MONITOR"."CONFIG_NAME"))
      INDEX_RS_ASC(@"SEL$06F95CF3" "D"@"SEL$9" ("DATA_TABLE_FIELDS"."TABLE_NAME"))
      INDEX(@"SEL$06F95CF3" "CI_BASE_ELEMENT"@"SEL$10" ("CI_BASE_ELEMENT"."INSTANCE_ID"))
      USE_NL(@"SEL$F5BB74E1" "P"@"SEL$2")
      LEADING(@"SEL$F5BB74E1" "NP"@"SEL$1" "P"@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "P"@"SEL$2" ("PERF_LAST_DATA"."CI_NAME" "PERF_LAST_DATA"."CLASS_ID"
              "PERF_LAST_DATA"."TABLE_FIELD_ID"))
      NO_ACCESS(@"SEL$F5BB74E1" "NP"@"SEL$1")
      USE_NL(@"MRG$1" "P"@"MRG$1")
      USE_MERGE_CARTESIAN(@"MRG$1" "from$_subquery$_018"@"MRG$1")
      LEADING(@"MRG$1" "NP"@"MRG$1" "from$_subquery$_018"@"MRG$1" "P"@"MRG$1")
      FULL(@"MRG$1" "P"@"MRG$1")
      NO_ACCESS(@"MRG$1" "from$_subquery$_018"@"MRG$1")
      NO_ACCESS(@"MRG$1" "NP"@"MRG$1")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE_LEAF(@"MRG$1")
      MERGE(@"SEL$9")
      MERGE(@"SEL$8")
      MERGE(@"SEL$10")
      OUTLINE_LEAF(@"SEL$06F95CF3")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$6")
      MERGE(@"SEL$5")
      MERGE(@"SEL$4")
      OUTLINE_LEAF(@"SEL$C9CA1E20")
      ALL_ROWS
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

  8 - access("INSTANCE_ID"=74817660)
  9 - filter("D"."FIELD_NAME"<>'NE_ID' AND "D"."FIELD_NAME"<>'COLLECT_TIME' AND "D"."FIELD_NAME"<>'CREATE_TIME' AND
              "D"."FIELD_NAME"<>'CONFIG_NAME' AND "D"."FIELD_NAME"<>'CLASSNAME')
  10 - access("D"."TABLE_NAME"='PERT_HOST_MONITOR')
  13 - filter("NE_ID"=74817660)
  14 - access("COLLECT_TIME"=TO_DATE(' 2017-07-20 22:50:29', 'syyyy-mm-dd hh24:mi:ss'))
  16 - access("P"."CI_NAME"(+)="NP"."CONFIG_NAME" AND "P"."CLASS_ID"(+)="NP"."CLASS_ID" AND
              "P"."TABLE_FIELD_ID"(+)="NP"."TABLE_FIELD_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

  1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
  2 - "P"."PERF_VALUE"[NUMBER,22], "NP"."COLLECT_TIME"[DATE,7], "NP"."CLASS_ID"[NUMBER,22],
      "NP"."CONFIG_NAME"[VARCHAR2,250], "NP"."TABLE_FIELD_ID"[NUMBER,22], "NP"."PERF_VALUE"[NUMBER,22],
      "NP"."PERF_FIELD_NAME_CN"[VARCHAR2,250]
  3 - (#keys=0) "NP"."COLLECT_TIME"[DATE,7], "NP"."CLASS_ID"[NUMBER,22], "NP"."CONFIG_NAME"[VARCHAR2,250],
      "NP"."TABLE_FIELD_ID"[NUMBER,22], "NP"."PERF_VALUE"[NUMBER,22], "NP"."PERF_FIELD_NAME_CN"[VARCHAR2,250],
      "P".ROWID[ROWID,10], "P"."CI_NAME"[VARCHAR2,250], "P"."CLASS_ID"[NUMBER,22], "P"."TABLE_FIELD_ID"[NUMBER,22],
      "P"."PERF_FIELD_NAME_CN"[VARCHAR2,200], "P"."PERF_VALUE"[NUMBER,22], "P"."COLLECT_TIME"[DATE,7],
      "P"."PERF_STATE_VALUE"[VARCHAR2,50]
  4 - "NP"."COLLECT_TIME"[DATE,7], "NP"."CLASS_ID"[NUMBER,22], "NP"."CONFIG_NAME"[VARCHAR2,250],
      "NP"."TABLE_FIELD_ID"[NUMBER,22], "NP"."PERF_VALUE"[NUMBER,22], "NP"."PERF_FIELD_NAME_CN"[VARCHAR2,250]
  5 - (#keys=0) "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22], "CLASS_ID"[NUMBER,22], "D".ROWID[ROWID,10],
      "D"."TABLE_FIELD_ID"[NUMBER,22], "D"."TABLE_NAME"[VARCHAR2,50], "D"."FIELD_NAME"[VARCHAR2,50],
      "D"."FIELD_NAME_CN"[VARCHAR2,250], "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  6 - (#keys=0) "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22], "CLASS_ID"[NUMBER,22], "D".ROWID[ROWID,10],
      "D"."TABLE_FIELD_ID"[NUMBER,22], "D"."TABLE_NAME"[VARCHAR2,50], "D"."FIELD_NAME"[VARCHAR2,50],
      "D"."FIELD_NAME_CN"[VARCHAR2,250]
  7 - "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22], "CLASS_ID"[NUMBER,22]
  8 - "CI_BASE_ELEMENT".ROWID[ROWID,10], "INSTANCE_ID"[NUMBER,22]
  9 - "D".ROWID[ROWID,10], "D"."TABLE_FIELD_ID"[NUMBER,22], "D"."TABLE_NAME"[VARCHAR2,50], "D"."FIELD_NAME"[VARCHAR2,50],
      "D"."FIELD_NAME_CN"[VARCHAR2,250]
  10 - "D".ROWID[ROWID,10], "D"."TABLE_NAME"[VARCHAR2,50]
  11 - (#keys=0) "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  12 - "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  13 - "PERT_HOST_MONITOR".ROWID[ROWID,10], "NE_ID"[NUMBER,22], "COLLECT_TIME"[DATE,7],
      "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250], "PERT_HOST_MONITOR"."HOST_STATE"[NUMBER,22]
  14 - "PERT_HOST_MONITOR".ROWID[ROWID,10], "COLLECT_TIME"[DATE,7], "PERT_HOST_MONITOR"."CONFIG_NAME"[VARCHAR2,250]
  15 - "P".ROWID[ROWID,10], "P"."CI_NAME"[VARCHAR2,250], "P"."CLASS_ID"[NUMBER,22], "P"."TABLE_FIELD_ID"[NUMBER,22],
      "P"."PERF_FIELD_NAME_CN"[VARCHAR2,200], "P"."PERF_VALUE"[NUMBER,22], "P"."COLLECT_TIME"[DATE,7],
      "P"."PERF_STATE_VALUE"[VARCHAR2,50]
  16 - "P".ROWID[ROWID,10], "P"."CI_NAME"[VARCHAR2,250], "P"."CLASS_ID"[NUMBER,22], "P"."TABLE_FIELD_ID"[NUMBER,22]

Note
-----
  - dynamic sampling used for this statement (level=2)

153 rows selected.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> declare
  2    ar_hint_table    sys.dbms_debug_vc2coll;
  3    ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
  4    cl_sql_text      clob;
  5    i                pls_integer;
  6    v_sqlid          varchar2(64);
  7    vflag            pls_integer;
  8  begin
  9  
10  
11  -----------Fetch OutLine of Last explain plan SQL ,put into ar_profile_hints------------
12  
13  vflag :=0;
14  for c1 in 
15  ( select *from table(dbms_xplan.display(null,null,'ADVANCED')) ) 
16  loop
17      if( c1.PLAN_TABLE_OUTPUT like '%BEGIN_OUTLINE_DATA%' ) then vflag :=1;  end if;
18      if( vflag = 1 ) then
19                ar_profile_hints.extend;
20              ar_profile_hints(ar_profile_hints.count) := c1.PLAN_TABLE_OUTPUT;
21      end if;
22      if( c1.PLAN_TABLE_OUTPUT like '%END_OUTLINE_DATA%' ) then vflag :=0; end if;
23  end loop;
24  
25  
26  
27  ----------------Fetch Sql FullText By SQL_ID-------------
28    v_sqlid := '&sql_id';
29    select
30            SQL_FULLTEXT
31    into
32            cl_sql_text
33    from
34            -- replace with dba_hist_sqltext
35            -- if required for AWR based
36            -- execution
37            v$sql
38            -- sys.dba_hist_sqltext
39    where
40            sql_id = v_sqlid and rownum<2;
41  
42  
43  
44  
45  
46  -----------------------Create SqlProfile by sqltext and  ar_profile_hints ------------------ 
47    dbms_sqltune.import_sql_profile(
48      sql_text    => cl_sql_text
49    , profile    => ar_profile_hints
50    , name        => 'PROFILE_'||v_sqlid
51    -- use force_match => true
52    -- to use CURSOR_SHARING=SIMILAR
53    -- behaviour, i.e. match even with
54    -- differing literals
55    , force_match => false
56    );
57  end;
58  /
Enter value for sql_id: 111unfphab6v6
old  28:  v_sqlid := '&sql_id';
new  28:  v_sqlid := '111unfphab6v6';

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> set linesize 160
SQL> column sql_text format a60
SQL> select sql_id, hash_value,sql_text, address, version_count from v$sqlarea a
  2  where sql_id = '111unfphab6v6';

SQL_ID        HASH_VALUE SQL_TEXT                                                    ADDRESS          VERSION_COUNT
------------- ---------- ------------------------------------------------------------ ---------------- -------------
111unfphab6v6 1621465958 MERGE INTO perf_last_data p USING (SELECT collect_time,      000000067565C0A8            2
                            c.class_id,        config_name,        table_field_id, de
                        code( b.table_field_id,191020,HOST_STATE ) perf_value, field
                        _name_cn perf_field_name_cn  FROM (SELECT * /*+ index(t) */
                                    FROM PERT_HOST_MONITOR          WHERE ne_id=:1 an
                        d collect_time=:2) a,        (select *          from data_t
                        able_fields d          where d.table_name = upper('PERT_HOST
                        _MONITOR')            and d.field_name not in ('NE_ID','COLL
                        ECT_TIME','CREATE_TIME','CONFIG_NAME','CLASSNAME')
                          /*and d.field_type = 'NUMBER'*/) b,            (select cla
                        ss_id from ci_base_element where instance_id=:3) c ) np ON (
                        p.ci_name = np.config_name AND p.class_id = np.class_id AND
                        p.table_field_id = np.table_field_id) WHEN NOT MATCHED THEN
                          INSERT  VALUES    (np.config_name,      np.class_id,
                          np.table_field_id,      np.perf_field_name_cn,      np.per
                        f_value,      np.collect_time, null) WHEN MATCHED THEN  UPD
                        ATE      SET p.collect_time = np.collect


SQL> exec dbms_shared_pool.purge('000000067565C0A8,1621465958','C');

PL/SQL procedure successfully completed.

这个地方,如果不能purge,那需要将执行111unfphab6v6这个SQL的会话全部杀掉

4. 优化后效果

绑定后,主机CPU使用率下降到30左右

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
  r  b  swpd  free  buff  cache  si  so    bi    bo  in  cs us sy id wa st
  7  0      0 16835332 1105440 8731876    0    0    3  8487 42244 45601 42 11 47  0  0
  6  0      0 16840772 1105440 8731876    0    0    3  9621 41183 44889 38 10 52  0  0
  4  0      0 16840164 1105440 8731876    0    0    3  5994 40162 43994 38 10 53  0  0
14  0      0 16785496 1105440 8731912    0    0    3  5798 43824 43803 48 12 40  0  0
13  0      0 16780284 1105440 8731912    0    0    3  4831 38897 36680 51  9 40  0  0
15  0      0 16841036 1105440 8731904    0    0    3  5367 38953 39424 46  9 45  0  0
16  0      0 16832384 1105440 8731904    0    0    3  6056 42938 46381 39 12 50  0  0
  6  0      0 16840060 1105440 8731904    0    0    3  6006 39899 42826 37 10 53  0  0
  7  0      0 16840796 1105440 8731908    0    0    3  5139 39425 42246 38 10 53  0  0
  4  0      0 16843524 1105440 8731908    0    0    3  6695 42761 46070 38 11 52  0  0
  4  0      0 16844652 1105440 8731936    0    0    3  5631 41289 43703 38  9 52  0  0
12  0      0 16844552 1105440 8731936    0    0    3  5355 39232 42260 36  9 55  0  0
  7  0      0 16843972 1105440 8731936    0    0    3  5479 41498 44113 36 10 53  0  0
  7  0      0 16844024 1105440 8731936    0    0    3  4296 34691 37099 33  8 59  0  0
  7  0      0 16844680 1105440 8731940    0    0    3  5878 42471 46063 35 10 55  0  0
  7  0      0 16842632 1105440 8731964    0    0    3  5981 42165 45644 36 10 54  0  0
  7  0      0 16845348 1105440 8731968    0    0    3  6229 43912 47262 36 11 53  0  0
12  0      0 16845356 1105440 8731972    0    0    3  5727 41686 43961 37 11 52  0  0
12  0      0 16840720 1105440 8731972    0    0    3  5592 42041 44296 41 10 49  0  0
  8  0      0 16843192 1105440 8731976    0    0    3  5778 39224 41612 42  9 49  0  0

关于紫砂壶

感悟技术人生
此条目发表在Oracle性能问题分类目录,贴了标签。将固定链接加入收藏夹。