[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

[20230110]sql profile run standby database.txt

相關文章