[20151201]備份遷移sql profile.txt
[20151201]備份遷移sql profile.txt
--在生產系統使用 sql profile 來穩定計劃，需要將這些內容移植到測試環境。自己做一個測試：
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table tx as select rownum id ,'test' name from dual connect by level<=1e5;
Table created.
SCOTT@book> create unique index pk_tx on tx(id);
Index created.
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'tx',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> variable x number;
SCOTT@book> exec :x := 42;
PL/SQL procedure successfully completed.
SCOTT@book> select /*+ full(tx) */ * from tx where id = :x ;
ID NAME
---------- --------------------
42 test
SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4rgrzpar16sv2, child number 0
-------------------------------------
select /*+ full(tx) */ * from tx where id = :x
Plan hash value: 40191160
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 61 (100)| |
|* 1 | TABLE ACCESS FULL| TX | 179 | 3401 | 61 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
2.建立sql profile：
--參考連結: [20141119]使用指令碼完成sql_profile工作 http://blog.itpub.net/267265/viewspace-1340660/
SCOTT@book> @ &r/sp1 4rgrzpar16sv2
PL/SQL procedure successfully completed.
SCOTT@book> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 4rgrzpar16sv2', replace => TRUE, name=>'tuning 4rgrzpar16sv2', FORCE_MATCH=>True);
PL/SQL procedure successfully completed.
SCOTT@book> select /*+ full(tx) */ * from tx where id = :x ;
ID NAME
---------- --------------------
42 test
SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4rgrzpar16sv2, child number 0
-------------------------------------
select /*+ full(tx) */ * from tx where id = :x
Plan hash value: 1336555843
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TX@SEL$1
2 - SEL$1 / TX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL profile tuning 4rgrzpar16sv2 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
37 rows selected.
--可以發現現在執行計劃已經發生了改變，使用索引。
3.如何儲存：
--檢查發現可以使用包dbms_sqltune完成。
SCOTT@book> @&r/desc_proc sys dbms_sqltune %_stgtab_sqlprof%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE D
----- ------------- ---------------------- -------- -------------------- --------------- --------- --------------- -
SYS DBMS_SQLTUNE CREATE_STGTAB_SQLPROF 1 TABLE_NAME VARCHAR2 IN VARCHAR2 N
2 SCHEMA_NAME VARCHAR2 IN VARCHAR2 Y
3 TABLESPACE_NAME VARCHAR2 IN VARCHAR2 Y
PACK_STGTAB_SQLPROF 1 PROFILE_NAME VARCHAR2 IN VARCHAR2 Y
2 PROFILE_CATEGORY VARCHAR2 IN VARCHAR2 Y
3 STAGING_TABLE_NAME VARCHAR2 IN VARCHAR2 N
4 STAGING_SCHEMA_OWNER VARCHAR2 IN VARCHAR2 Y
REMAP_STGTAB_SQLPROF 1 OLD_PROFILE_NAME VARCHAR2 IN VARCHAR2 N
2 NEW_PROFILE_NAME VARCHAR2 IN VARCHAR2 Y
3 NEW_PROFILE_CATEGORY VARCHAR2 IN VARCHAR2 Y
4 STAGING_TABLE_NAME VARCHAR2 IN VARCHAR2 N
5 STAGING_SCHEMA_OWNER VARCHAR2 IN VARCHAR2 Y
UNPACK_STGTAB_SQLPROF 1 PROFILE_NAME VARCHAR2 IN VARCHAR2 Y
2 PROFILE_CATEGORY VARCHAR2 IN VARCHAR2 Y
3 REPLACE PL/SQL BOOLEAN IN PL/SQL BOOLEAN N
4 STAGING_TABLE_NAME VARCHAR2 IN VARCHAR2 N
5 STAGING_SCHEMA_OWNER VARCHAR2 IN VARCHAR2 Y
17 rows selected.
SCOTT@book> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(TABLE_NAME=>'t_sql_profile',SCHEMA_NAME=>user);
PL/SQL procedure successfully completed.
--這樣建立了t_sql_profile表。
SCOTT@book> select count(*) from t_sql_profile ;
COUNT(*)
----------
0
SCOTT@book> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from t_sql_profile ;
COUNT(*)
----------
1
SCOTT@book> @ &r/pt2 'select * from t_sql_profile'
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ---------- --------------- ----------------------------------------------------------------------------------------------------
1 1 VERSION 3
2 SIGNATURE 12082737585315772852
3 SQL_HANDLE SQL_a7ae81d11701f9b4
4 OBJ_NAME tuning 4rgrzpar16sv2
5 OBJ_TYPE SQL_PROFILE
6 PLAN_ID 0
7 SQL_TEXT select /*+ full(tx) */ * from tx where id = :x
8 CREATOR SCOTT
9 ORIGIN MANUAL
10 DB_VERSION 11.2.0.4.0
11 CREATED 01-DEC-15 08.12.52.000000 AM
12 LAST_MODIFIED 01-DEC-15 08.12.52.000000 AM
13 STATUS 1
14 CATEGORY DEFAULT
15 SQLFLAGS 1
16 TASK_ID 360
17 TASK_EXEC_NAME EXEC_363
18 TASK_OBJ_ID 1
19 TASK_FND_ID 1
20 TASK_REC_ID 1
21 INUSE_FEATURES 1
22 COMP_DATA <outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM
22 rows selected.
4.測試匯入:
--先刪除sql profile，單機環境先刪除在匯入。
SCOTT@book> execute dbms_sqltune.drop_sql_profile(name => 'tuning 4rgrzpar16sv2')
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/spext 4rgrzpar16sv2
no rows selected
--匯入:
SCOTT@book> exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UNPACK_STGTAB_SQLPROF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--引數REPLACE必須賦值。
SCOTT@book> exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE=>false,STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/spext 4rgrzpar16sv2
HINT NAME
----------------------------------------- ------------------------------
OPTIMIZER_FEATURES_ENABLE(default) tuning 4rgrzpar16sv2
IGNORE_OPTIM_EMBEDDED_HINTS tuning 4rgrzpar16sv2
--附上抽取hint指令碼:
$ cat spext.sql
/* Formatted on 2015/4/10 17:03:49 (QP5 v5.252.13127.32867) */
column hint format a200
column name format a30
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name
FROM SYS.sqlobj$data od
,SYS.sqlobj$ so
,TABLE
(
XMLSEQUENCE
(
EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
)
) h
WHERE so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1')
AND so.signature = od.signature
AND so.CATEGORY = od.CATEGORY
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
總結：
1.建立表exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(TABLE_NAME=>'t_sql_profile',SCHEMA_NAME=>user);
2.匯入表 exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
2a.使用exp/expdp匯出表。
3.匯出exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE=>false,STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1850190/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL Server 備份遷移策略SQLServer
- MySQL備份遷移之mydumperMySql
- dnf資料庫備份&遷移資料庫
- 遷移後帶庫備份問題
- 資料庫的冷備份遷移資料庫
- SVN倉庫備份和遷移基本操作
- mysql 備份與遷移 資料同步方法MySql
- MySQL 遷移表空間，備份單表MySql
- MongoDB 資料遷移 備份 匯入（自用）MongoDB
- 遷移後帶庫備份問題（二）
- Cacti資料備份與遷移 （轉載）
- 【備份恢復】行遷移與行連結
- svn版本升級遷移和異地備份
- 透過rman全庫備份遷移資料庫資料庫
- Jira/Confluence的備份、恢復和遷移
- 用 RMAN 備份異機恢復 遷移資料
- 12c跨平臺完成PDB的備份遷移
- RMAN備份恢復典型案例——跨平臺遷移pdb
- Docker容器中的備份、恢復、遷移、匯入、匯出Docker
- Velero：備份、遷移Kubernetes叢集資源和PV
- [Sqlite] 資料遷移備份--從低版本到高版本SQLite
- Mac硬碟備份遷移資料工具：Carbon Copy Cloner 簡稱CCCMac硬碟
- docker 容器操作、應用部署、mysql，redis，nginx、遷移與備份、DockerfileDockerMySqlRedisNginx
- Domino從UNIX平臺到windows平臺的遷移及備份Windows
- K8s叢集備份還原與遷移利器-VeleroK8S
- 遷移 SQL Server 到 Azure SQL 實戰SQLServer
- SQL 備份指令碼SQL指令碼
- SQL Server 備份策略SQLServer
- SQL Server 冷備份SQLServer
- sql檔案備份SQL
- 在Ubuntu 14.04上備份，還原和遷移MongoDB資料庫UbuntuMongoDB資料庫
- mongodb資料庫備份與恢復（資料庫資料遷移）MongoDB資料庫
- 快速實現本地資料備份與FTP遠端資料遷移FTP
- 移除遷移遺留的無法訪問的RMAN過期備份
- 轉：VMware遷移的真實教訓：為什麼備份如此重要
- SQL Server資料庫遷移SQLServer資料庫
- 伺服器遷移備忘錄伺服器
- 【SQL】【遷移】寫了一個簡單的sequence遷移指令碼SQL指令碼