已知SQL更好执行计划的调优

1.创建测试表及数据

CREATE TABLE T01(ID NUMBER,NAME VARCHAR2(20));
CREATE INDEX IDX_T01_ID ON T01(ID);
INSERT INTO T01 VALUES (1,'MONKEY');
INSERT INTO T01 VALUES (2,'MONKEY');
INSERT INTO T01 VALUES (3,'MONKEY');
INSERT INTO T01 VALUES (4,'MONKEY');
INSERT INTO T01 VALUES (5,'MONKEY');
COMMIT;

2.根据ID查询测试表并查看执行计划

SELECT * FROM MONKEY.T01 WHERE ID = 2;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'Advanced'))

3.自己加hint，生成更好的执行计划

SELECT /*+FULL(A)*/* FROM MONKEY.T01 A WHERE ID = 2;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'Advanced'))

4.提取对应的执行计划

/* Formatted on 2021-05-20 下午 02:51:45 (QP5 v5.163.1008.3004) */
SELECT    'q''['
       || REPLACE (EXTRACTVALUE (VALUE (d), '/hint'), '', '''')
       || ']'','
          AS outline_hints
  FROM XMLTABLE (
          '/*/outline_data/hint'
          PASSING (SELECT xmltype (other_xml) AS xmlval
                     FROM v$sql_plan
                    WHERE     sql_id = '4ym6mvwvuduha' ------sql_Id is which you get profile
                          AND child_number = 0
                          AND other_xml IS NOT NULL)) d;

在实践过程中，可能会出现隔得太久，执行计划已经被踢出sharepool的情况，此时就需要从DBA_HIST_SQL_PLAN抓取执行计划

SELECT    'q''['
       || REPLACE (EXTRACTVALUE (VALUE (d), '/hint'), '', '''')
       || ']'','
          AS outline_hints
  FROM XMLTABLE (
          '/*/outline_data/hint'
          PASSING (SELECT xmltype (other_xml) AS xmlval
                     FROM DBA_HIST_SQL_PLAN
                    WHERE     sql_id = '4x90hmraqdjf9' ------sql_Id is which you get profile
                          AND PLAN_HASH_VALUE = 3651047320     --这里需要用PLAN_HASH_VALUE
                          AND other_xml IS NOT NULL)) d;

5.用生成的执行计划替换原有的计划

/* Formatted on 2021-05-20 下午 02:56:44 (QP5 v5.163.1008.3004) */
DECLARE
   V_SQLID_1            V$SQL.SQL_ID%TYPE := '2a7npa11xs7st'; -------------sql_Id is which you want to change
   V_CHILDNO_1          V$SQL.CHILD_NUMBER%TYPE := 0;
   v_sqlprofile         SYS.sqlprof_attr;
   v_new_profile_name   VARCHAR2 (255);
   v_sql_text           V$SQL.SQL_FULLTEXT%TYPE;
BEGIN
   SELECT sql_fulltext
     INTO v_sql_text
     FROM v$sql
    WHERE sql_id = V_SQLID_1 AND child_number = V_CHILDNO_1;

   SELECT 'coe_' || DBMS_RANDOM.STRING ('A', 24)
     INTO v_new_profile_name
     FROM DUAL;

   DBMS_SQLTUNE.import_sql_profile (
      sql_text      => v_sql_text,
      name          => v_new_profile_name,
      profile       => sqlprof_attr (q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
                                     q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
                                     q'[DB_VERSION('11.2.0.4')]',
                                     q'[ALL_ROWS]',
                                     q'[OUTLINE_LEAF(@"SEL$1")]',
                                     q'[FULL(@"SEL$1" "A"@"SEL$1")]'),
      REPLACE       => TRUE,
      force_match   => TRUE);
END;
/

6.查看是否替换成功

SELECT A.EXECUTIONS,A.SQL_PROFILE FROM V$SQLAREA A WHERE A.SQL_ID='2a7npa11xs7st';

再次执行sql后，SQL_PROFILE栏位会有数据。

/*+
PUSH_PRED(X 2)
OUTLINE_LEAF()
*/

7.其他

删除sql profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');