在不同的資料庫內移植SQL PROFILE優化的SQL資訊

[@more@]

在scott使用者下建立一個SQL PROFILE:

SQL> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified table');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task');
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task',
name => 'my_sql_profile'
);
END;
/

PL/SQL procedure successfully completed.
現在SQL PROFILE已經建立並且對於SQL的建議優化也已經完成。

傳輸SQL PROFILE

i)建立一個用於儲存SQL PROFILE的表

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'TEST',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.
table_name =>是用於儲存SQL PROFILE的表

schema_name=>用於指定table_name在哪個scheme下建立

ii)將SQL PROFILE資訊轉儲入以上建立的表內：

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'TEST',profile_name=>'my_sql_profile');

PL/SQL procedure successfully completed.

staging_table_name => 指定以上建立的table_name

profile_name=>將要傳輸的SQL PROFILE name

SQL> desc test
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
PROFILE_NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
SIGNATURE NUMBER
SQL_TEXT CLOB
DESCRIPTION VARCHAR2(500)
TYPE VARCHAR2(9)
STATUS VARCHAR2(8)
BOOLEAN_FLAGS NUMBER
ATTRIBUTES SQLPROF_ATTR
VERSION NUMBER
SPARE1 CLOB
SPARE2 BLOB

SQL> select count(*) from test;

COUNT(*)
----------
1

SQL> set autot on
SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7839)

Note
-----
- SQL profile "my_sql_profile" used for this statement

iii)通過EXPORT/IMPORT匯出這個表

[oracle@localhost oracle]$ exp scott/tiger tables=test

Export: Release 10.2.0.3.0 - Production on Fri Sep 7 01:18:06 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TEST 1 rows exported
Export terminated successfully without warnings.

iv)在沒有SQL PROFILE的情況下，執行同樣的查詢在另一個資料庫裡

SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 4 | 348 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMPNO"=7839)

v)匯入以上剛匯出的DMP檔案

[oracle@localhost oracle]$ imp scott/tiger file=expdat.dmp full=y

Import: Release 10.2.0.3.0 - Production on Fri Sep 7 01:24:15 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "TEST" 1 rows imported
Import terminated successfully without warnings.

vi)接下來將匯入的SQL PROFILE資訊繫結到對應的SQL PROFILE內

SQL> conn scott/tiger /* connect to scott schema where the staging table is imported and then unpack */

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST');

PL/SQL procedure successfully completed.

vii)驗證SQL PROFILE的有效性

SQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPNO"=7839)

測試完成！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10130206/viewspace-1035333/，如需轉載，請註明出處，否則將追究法律責任。