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

[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方法来稳定执行计划方法简单实用。