[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