在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)
测试完成！