ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql profile

sql profile

原创 Linux操作系统 作者:g644516804 时间:2012-05-03 11:24:13 0 删除 编辑

当sql 执行计划出错时,可以利用sql tuning profile功能使得sql走正确的执行计划

也可以从其他db上导出正确的执行计划 然后到其他db中

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Checked for relevance on 09-SEP-2010

Goal

The purpose of this document is to assist in moving an SQL Profile implemented on one database to another database.

Scope
DBAs and Support Analysts

Solution

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Managing SQL Profiles

For information on SQL Profiles see:

Document 271196.1 Automatic SQL Tuning - SQL Profiles

Steps to Create and Transfer Profile from One Database to Another

1. Create SQL Profile in SCOTT schema

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:

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 => 'Demo Task to tune a query');
 
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.

 

set lines 130
set autotrace on

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: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     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


Note: Even though no_index hint included, plan uses index as determined by profile
         Note in Plan information indicates "my_sql_profile" is used.

2. Creating a staging table to store the SQL Profiles

 

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

PL/SQL procedure successfully completed.


table_name => name of the table to store the SQL Profiles.
(table_name大写)
schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');


PL/SQL procedure successfully completed.


staging_table_name => name of the table to store the SQL Profiles.
profile_name => name of the SQL Profile to be packed.

Note: The table_name and schema_name are case-sensitive.

SQL> desc STAGE
 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

 

4. Export the Staging Table to the Target Database

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.

4a. Export from Source Database

my_linux_1:~> exp scott/tiger tables=STAGE

Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

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

 

4b. Import into Target Database

my_linux_1:~> imp scott/tiger tables=STAGE

Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                        "STAGE"          1 rows imported
Import terminated successfully with warnings.

 Or

Imp scott/trigger full=y file=expdat.dmp

5. Unpack the SQL Profiles

5a. Test before unpacking

SQL> set lines 130
SQL> set autotrace 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: 2872589290

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

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

   1 - filter("EMPNO"=7839)


Note: NO_INDEX hint honoured and FULL SCAN done on EMP

5b. Unpack Staging Table

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

PL/SQL procedure successfully completed.

 

6. Check the SQL Profile is enabled in Target Database

set lines 130
set autotrace on

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: 4066871323

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     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

 

References

NOTE:271196.1 - Automatic SQL Tuning - SQL Profiles.

 

 

 

Sql_tuning.sql

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqltext := 'select /*+ no_index(t2 t2_idx) */ * from t2 where object_id=7839';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,

user_name => 'LERRY',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'sql_tuning_task',

description => 'Demo Task to tune a query');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_tuning_task');

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'sql_tuning_task',

 name => 'my_sql_profile');

end;

 

 

or:

tuning_sqlid.sql:

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqlid := 'aidhgiehtd';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id =>my_sqlid,

user_name => 'LERRY',

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'sql_tuning_task',

description => 'Demo Task to tune a query');

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_tuning_task');

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'sql_tuning_task',

 name => 'my_sql_profile');

end;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24849178/viewspace-722611/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-04

  • 博文量
    104
  • 访问量
    229951