[20230110]sql profile run standby database.txt

[20230110]sql profile run standby database.txt --//理论讲在standby database运行sql profile不可行的.因为要运行过程要写数据文件. --//我以前的做法在主库运行,然后在主库运用并接受sql profile的建议.这样备库一样可以应用sql profile建议. --//能否直接在备库运行sql profile呢?找到一些文档,测试看看: --//后记:节前做的测试,一直不成功,后来发现执行dbms_sqltune.execute_tuning_task的参数database_link_to的赋值一定要大写. --//一定可以通过,不知道为什么,不再探究. 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试: --//在主库建立用户SYS$UMF.在19c该用户实际上存在的. SYS@192.168.100.235:1521/orcl> select username,common,account_status from dba_users where username ='SYS$UMF'; USERNAME COM ACCOUNT_STATUS -------- --- -------------- SYS$UMF  YES LOCKED SYS@192.168.100.235:1521/orcl> alter user SYS$UMF identified by o1r2c3l4 account unlock ; User altered. SYS@192.168.100.235:1521/orcl> select username,common,account_status from dba_users where username ='SYS$UMF'; USERNAME COM ACCOUNT_STATUS -------- --- -------------- SYS$UMF  YES OPEN --//在主库建立db_link.连接主库自己: SYS@192.168.100.235:1521/orcl> create database link link_to_primary connect to "SYS$UMF" identified by "o1r2c3l4" using '192.168.100.235:1521/orcl'; Database link created. --//在备库运行验证db_link是否生效: SYS@192.168.100.237:1521/orcldg> select  db_unique_name from v$database@link_to_primary; DB_UNIQUE_NAME ------------------------------ orcl 3.测试sql Profile over dblink: --//在备库运行如下脚本sp1x.sql: --//修改原来sp1.sql脚本,dbms_sqltune.create_tuning_task 中加入参数database_link_to => 'LINK_TO_PRIMARY'以及 --//dbms_sqltune.execute_tuning_task( task_name=>'tuning &1' ,database_link_to => upper('&2') ); --//我的测试遇到一个古怪的问题,参数database_link_to赋值的变量一定要大写.不然运行无法通过!! --//做一个简单例子验证: SYS@192.168.100.237:1521/orcldg> @ cs lis alter session set current_schema=lis Session altered. SYS@192.168.100.237:1521/orcldg> select /*+ full(a) */ * from lis.LIS_CRIT_RESULT a where id=12752271; ... SYS@192.168.100.237:1521/orcldg> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2517908391 2yr2asfb18fx7            0      15271      1122455697  96143ba7  2023-02-09 10:34:41    16777219 SYS@192.168.100.237:1521/orcldg> @ sp1x 2yr2asfb18fx7 LINK_TO_PRIMARY PL/SQL procedure successfully completed. ============================================================================== tuning sql_id=2yr2asfb18fx7 : report ============================================================================== REPORT_TUNING_TASK ------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : tuning 2yr2asfb18fx7 Tuning Task Owner  : SYS Workload Type      : Single SQL Statement Scope              : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status  : COMPLETED Started at         : 02/09/2023 10:35:42 Completed at       : 02/09/2023 10:35:49 SQL Tuning at Standby TRUE ------------------------------------------------------------------------------- Schema Name: LIS SQL ID     : 2yr2asfb18fx7 SQL Text   : select /*+ full(a) */ * from lis.LIS_CRIT_RESULT a where              id=12752271 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) --------------------------------------------------------   A potentially better execution plan was found for this statement.   Recommendation (estimated benefit: 99.9%)   -----------------------------------------   - Consider accepting the recommended SQL profile.     execute dbms_sqltune.accept_sql_profile(task_name => 'tuning             2yr2asfb18fx7', task_owner => 'SYS', replace => TRUE);   Validation results   ------------------   The SQL profile was tested by executing both its plan and the original plan   and measuring their respective execution statistics. A plan may have been   only partially executed if the other could be run to completion in less time.                            Original Plan  With SQL Profile  % Improved                            -------------  ----------------  ----------   Completion Status:            COMPLETE          COMPLETE   Elapsed Time (s):             .025028            .00002      99.92 %   CPU Time (s):                 .013574            .00002      99.85 %   User I/O Time (s):                  0                 0   Buffer Gets:                     3042                 3       99.9 %   Physical Read Requests:             0                 0   Physical Write Requests:            0                 0   Physical Read Bytes:                0                 0   Physical Write Bytes:               0                 0   Rows Processed:                     1                 1   Fetches:                            1                 1   Executions:                         1                 1   Notes   -----   1. Statistics for the original plan were averaged over 10 executions.   2. Statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 1122455697 ------------------------------------------------------------------------------------- | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |                 |     1 |   231 |   581   (1)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| LIS_CRIT_RESULT |     1 |   231 |   581   (1)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ID"=12752271) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (3)) ---------------------------------------------------------------------------    0 -  STATEMENT          U -  IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block          U -  OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block    1 -  SEL$1 / A@SEL$1          U -  full(a) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS 2- Using SQL Profile -------------------- Plan hash value: 1272079374 ---------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                    |     1 |   231 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LIS_CRIT_RESULT    |     1 |   231 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | PK_LIS_CRIT_RESULT |     1 |       |     2   (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=12752271) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1)) ---------------------------------------------------------------------------    1 -  SEL$1 / A@SEL$1          U -  full(a) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS ------------------------------------------------------------------------------- ================================================================================================================================================= if finished,drop tuning task , run: execute dbms_sqltune.drop_tuning_task('tuning 2yr2asfb18fx7') if accept sql profile, run: execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 2yr2asfb18fx7', replace => TRUE ,name=>'tuning 2yr2asfb18fx7'); execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 2yr2asfb18fx7', replace => TRUE, name=>'tuning 2yr2asfb18fx7', FORCE_MATCH=>True) if drop or alter sql profile ,run : execute dbms_sqltune.drop_sql_profile(name => 'tuning 2yr2asfb18fx7') execute dbms_sqltune.alter_sql_profile(name => 'tuning 2yr2asfb18fx7',attribute_name=>'STATUS',value=>'DISABLED') ================================================================================================================================================= display SYS_AUTO_SQL_TUNING_TASK : report select dbms_sqltune.report_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK' ) report from dual; SYS@192.168.100.237:1521/orcldg> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 2yr2asfb18fx7', replace => TRUE, name=>'tuning 2yr2asfb18fx7', FORCE_MATCH=>True); PL/SQL procedure successfully completed. SYS@192.168.100.237:1521/orcldg> select /*+ full(a) */ * from lis.LIS_CRIT_RESULT a where id=12752270; no rows selected SYS@192.168.100.237:1521/orcldg> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  2x11z62jv16y3, child number 0 ------------------------------------- select /*+ full(a) */ * from lis.LIS_CRIT_RESULT a where id=12752270 Plan hash value: 1272079374 ----------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name               | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                    |        |       |     3 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LIS_CRIT_RESULT    |      1 |   231 |     3   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | PK_LIS_CRIT_RESULT |      1 |       |     2   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / A@SEL$1    2 - SEL$1 / A@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=12752270) Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 3 (U - Unused (1)) ---------------------------------------------------------------------------    0 -  STATEMENT            -  IGNORE_OPTIM_EMBEDDED_HINTS            -  OPTIMIZER_FEATURES_ENABLE(default)    1 -  SEL$1 / A@SEL$1          U -  full(a) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS Note -----    - SQL profile tuning 2yr2asfb18fx7 used for this statement    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 43 rows selected. --//OK通过. 4.附上sp1x.sql脚本: $ cat sp1x.sql set verify off set long 20000000 set longchunksize  20000000 column report_tuning_task format a300 declare   a varchar2(200); begin   a := dbms_sqltune.create_tuning_task(task_name=>'tuning &1',description=>'tuning sql_id=&1',scope=>dbms_sqltune.scope_comprehensive,time_limit=>1800,sql_id=>'&1',database_link_to => upper('&2')); --//  dbms_sqltune.execute_tuning_task( a );   dbms_sqltune.execute_tuning_task( task_name=>'tuning &1' ,database_link_to => upper('&2') ); end; / prompt prompt ================================================================================================================================================= prompt tuning sql_id=&1 : report prompt ================================================================================================================================================= select dbms_sqltune.report_tuning_task('tuning &1') report_tuning_task FROM dual; prompt ================================================================================================================================================= prompt if finished,drop tuning task , run: prompt execute dbms_sqltune.drop_tuning_task('tuning &1') prompt if accept sql profile, run: prompt execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE ,name=>'tuning &1');; prompt execute dbms_sqltune.accept_sql_profile(task_name => 'tuning &1', replace => TRUE, name=>'tuning &1', FORCE_MATCH=>True) prompt if drop or alter sql profile ,run : prompt execute dbms_sqltune.drop_sql_profile(name => 'tuning &1') prompt execute dbms_sqltune.alter_sql_profile(name => 'tuning &1',attribute_name=>'STATUS',value=>'DISABLED') prompt ================================================================================================================================================= prompt display SYS_AUTO_SQL_TUNING_TASK : report prompt select dbms_sqltune.report_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK' ) report from dual;; prompt set serveroutput off