[20241013]记录一条sql语句优化.txt

--//昨天别人想通过dbms_shared_pool.keep减少硬分析，我昨晚看了其sql语句，实际上该语句与以前生产系统的语句类似。
--//正好在21c下可以重复这个现象，做一个优化分析：

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

$ cat a1.txt
                SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
                E.table_name as table_ref, f.column_name as column_ref,
                C.table_name
        FROM ALL_CONS_COLUMNS C
        inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
        left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
        left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
        WHERE C.OWNER = 'SCOTT'
           and C.table_name = 'EMP'
           and D.constraint_type <> 'P'
        order by d.constraint_name, c.position;
--//与我生产系统类似，仅仅传入的参数我修改一下，这类语句往往是某个程序递归执行的，实际的执行计划超级复杂。

2.测试：
--//执行多次。
SCOTT@book01p> set timing on
SCOTT@book01p> column TABLE_REF format a20
SCOTT@book01p> column COLUMN_REF format a20
SCOTT@book01p> @ a1.txt
C COLUMN_NAME                      POSITION R_CONSTRAINT_NAME              TABLE_REF            COLUMN_REF           TABLE_NAME
- ------------------------------ ---------- ------------------------------ -------------------- -------------------- ------------------------------
R DEPTNO                                  1 PK_DEPT                        DEPT                 DEPTNO               EMP
Elapsed: 00:00:02.02

SCOTT@book01p> @ a1.txt
C COLUMN_NAME                      POSITION R_CONSTRAINT_NAME              TABLE_REF            COLUMN_REF           TABLE_NAME
- ------------------------------ ---------- ------------------------------ -------------------- -------------------- ------------------------------
R DEPTNO                                  1 PK_DEPT                        DEPT                 DEPTNO               EMP
Elapsed: 00:00:02.05
--//每次执行需要2秒。

SCOTT@book01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1379538955 49hn4n193n60b            0       6155       754505174  523a180b  2024-10-13 12:41:27    16777232

SYS@book> @ cr_s
PL/SQL procedure successfully completed.
--//执行dbms_workload_repository.create_snapshot()建立awr报表，不然sqlhh查询不到结果。

SYS@book> @ sqlhh 49hn4n193n60b 1
time unit : millisecond
BEGIN_INTERVAL_TIME    INST_ID SQL_ID        PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC  AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC
------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- ---------------
2024-10-13 10:58:28          1 49hn4n193n60b       754505174         13            1794            1789           1.0          3119              0               0         0.0               0               0               0

SYS@book> @ gunshare 49hn4n193n60b
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp
REASON_NOT_SHARED                CURSORS    SQL_IDS
----------------------------- ---------- ----------
OPTIMIZER_MISMATCH                    13          1
--//执行13次，每次都产生子光标并且都不共享，实际上每次都是1次硬分析，而执行计划很复杂，这样分析时间很长就很正常。
--//这才是对方遇到的问题本质，通过dbms_shared_pool.keep自然不起作用，实际上这是执行计划adaptive导致的问题,执行计划
--//note部分提示 this is an adaptive plan,以前11g没有这个特性，自然没有这个问题。

SYS@book> @ ashtop event,time_model_name   sql_id='49hn4n193n60b' &day
    Total                                                                                             Distinct Distinct    Distinct
  Seconds     AAS %This   EVENT           TIME_MODEL_NAME   FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- --------------- ----------------- ------------------- ------------------- ---------- -------- -----------
       34      .0  100% |                 PARSE HARD_PARSE  2024-10-13 11:36:11 2024-10-13 12:41:29          1       34          34
--//time_model_name模型也提示主要消耗在分析上。只要sql profile稳定执行计划就ok了。

SYS@book> @ spsw 49hn4n193n60b 0 49hn4n193n60b 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 49hn4n193n60b')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 49hn4n193n60b',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
--//感觉应该在pdb下执行。
SYS@book> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 49hn4n193n60b')
PL/SQL procedure successfully completed.
--//确实如此!!重复执行查看执行计划note部分没有提示使用sql profile。

--//改在pdb下执行：
SYS@book01p> @ spsw 49hn4n193n60b 0 49hn4n193n60b 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 49hn4n193n60b')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 49hn4n193n60b',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================

SCOTT@book01p> @ a1.txt
C COLUMN_NAME                      POSITION R_CONSTRAINT_NAME              TABLE_REF            COLUMN_REF           TABLE_NAME
- ------------------------------ ---------- ------------------------------ -------------------- -------------------- ------------------------------
R DEPTNO                                  1 PK_DEPT                        DEPT                 DEPTNO               EMP
Elapsed: 00:00:00.37

SCOTT@book01p> @ a1.txt
C COLUMN_NAME                      POSITION R_CONSTRAINT_NAME              TABLE_REF            COLUMN_REF           TABLE_NAME
- ------------------------------ ---------- ------------------------------ -------------------- -------------------- ------------------------------
R DEPTNO                                  1 PK_DEPT                        DEPT                 DEPTNO               EMP
Elapsed: 00:00:00.10
--//现在很快返回，问题解决。
--//这种情况使用sql profile方法来稳定执行计划方法简单实用。