SQL Tuning Set操作

最新推荐文章于 2021-01-19 08:32:37 发布

xiaoxiao树

最新推荐文章于 2021-01-19 08:32:37 发布

阅读量1.1k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

73 篇文章 0 订阅

订阅专栏

a SQL Tuning Set

About SQL Tuning Sets
1.A set of SQL statements

2.Associated execution context
such as user schema,
application module name and action,
list of bind values,
and the environment for SQL compilation of the cursor

3.Associated basic execution statistics
such as elapsed time,
CPU time, buffer gets,
disk reads,
rows processed,
cursor fetches,
the number of executions,
the number of complete executions,
optimizer cost,
and the command type

4.Associated execution plans and row source statistics for each SQL statement (optional)

The database stores SQL tuning sets in a database-provided schema.

User Interfaces for SQL Tuning Sets
   Oracle Enterprise Manager Cloud Control (Cloud Control) or
    the DBMS_SQLTUNE package
to manage SQL tuning sets

Creating a SQL Tuning Set
1.Create a new STS.
2.Load the STS with SQL statements and associated metadata.
3.Optionally, display the contents of the STS.
4.Optionally, update or delete the contents of the STS.
5.Create a tuning task with the STS as input.
6.Optionally, transport the STS to another database.
7.Drop the STS when finished.

Creating a SQL Tuning Set

To create an STS:

    1.Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.CREATE_SQLSET procedure.
    For example, execute the following PL/SQL program:
    BEGIN
      DBMS_SQLTUNE.CREATE_SQLSET (
        sqlset_name => 'SQLT_WKLD_STS'
    ,   description => 'STS to store SQL from the private SQL area'
    );
    END;

    2.Optionally, confirm that the STS was created.
    The following example queries the status of all SQL tuning sets owned by the current user:
    COLUMN NAME FORMAT a20
    COLUMN COUNT FORMAT 99999
    COLUMN DESCRIPTION FORMAT a30
    SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION
    FROM   USER_SQLSET;
    NAME                 SQLCNT DESCRIPTION
    -------------------- ------ ------------------------------
    SQLT_WKLD_STS             2 SQL Cache


Loading a SQL Tuning Set

To load an STS:
   1. Connect SQL*Plus to the database as a user with the appropriate privileges.

   2. Run the DBMS_SQLTUNE.LOAD_SQLSET procedure.

    For example, execute the following PL/SQL program to populate a SQL tuning set with all cursor cache statements that belong to the sh schema:

    DECLARE
      c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
    BEGIN
     OPEN c_sqlarea_cursor FOR
       SELECT VALUE(p)
       FROM   TABLE(
                DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
                ' module = ''SQLT_WKLD'' AND parsing_schema_name = ''SH'' ')
              ) p;
    -- load the tuning set
      DBMS_SQLTUNE.LOAD_SQLSET (
        sqlset_name     => 'SQLT_WKLD_STS'
    ,   populate_cursor => c_sqlarea_cursor
    );
    END;

    DECLARE
      c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
    BEGIN
     OPEN c_sqlarea_cursor FOR       SELECT VALUE(p)       FROM   TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(' module = ''SQLT_WKLD'' AND parsing_schema_name = ''SYS'' ')) p;
    -- load the tuning set
      DBMS_SQLTUNE.LOAD_SQLSET (
        sqlset_name     => 'SQLT_WKLD_STS'
    ,   populate_cursor => c_sqlarea_cursor
    );
    END;

SELECT *     FROM   TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(' module = ''SQLT_WKLD'' AND parsing_schema_name = ''SH'' ' ))

Displaying the Contents of a SQL Tuning Set
After an STS has been created and populated, execute the DBMS_SQLTUNE.SELECT_SQLSET function to read the contents of the STS,
optionally using filtering criteria.
You select the output of SELECT_SQLSET using a PL/SQL pipelined table function, which accepts a collection of rows as input.
You invoke the table function as the operand of the table operator in the FROM list of a SELECT statement.

To display the contents of an STS:
1.Connect SQL*Plus to the database with the appropriate privileges, and then query the STS contents using the TABLE function.

    For example, execute the following query:

    COLUMN SQL_TEXT FORMAT a30
    COLUMN SCH FORMAT a3
    COLUMN ELAPSED FORMAT 999999999

    SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
           ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
    FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) );

    Sample output appears below:

    SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS
    ------------- --- ------------------------------ ---------- -----------
    79f8shn041a1f SH select * from sales where quan    8373148       24016
                      tity_sold < 5 union select * f
                      rom sales where quantity_sold
                      > 500

    2cqsw036j5u7r SH select promo_name, count(*) c     3557373         309
                      from promotions p, sales s whe
                      re s.promo_id = p.promo_id and
                       p.promo_category = 'internet'
                       group by p.promo_name order b
                      y c desc

    fudq5z56g642p SH select sum(quantity_sold) from    4787891       12118
                       sales s, products p where s.p
                      rod_id = p.prod_id and s.amoun
                      t_sold > 20000 and p.prod_name
                       = 'Linen Big Shirt'

    bzmnj0nbvmz8t SH select * from sales where amou     442355       15281
                      nt_sold = 4

    Optionally, filter the results based on user-specific criteria.

    The following example displays statements with a disk reads to buffer gets ratio greater than or equal to 50%:

    COLUMN SQL_TEXT FORMAT a30
    COLUMN SCH FORMAT a3
    COLUMN BUF_GETS FORMAT 99999999
    COLUMN DISK_READS FORMAT 99999999
    COLUMN %_DISK FORMAT 9999.99
    SELECT sql_id, parsing_schema_name as "SCH", sql_text,
           buffer_gets as "B_GETS",
           disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK"
    FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET(
                'SQLT_WKLD_STS',
                '(disk_reads/buffer_gets) >= 0.50' ) );

    Sample output appears below:

    SQL_ID        SCH SQL_TEXT                       B_GETS DISK_READS %_DISK
    ------------- --- ------------------------------ ------ ---------- -------
    79f8shn041a1f SH select * from sales where quan 24016      17287 71.98
                      tity_sold < 5 union select * f
                      rom sales where quantity_sold
                      > 500

    fudq5z56g642p SH select sum(quantity_sold) from 12118       6355 52.44
                       sales s, products p where s.p
                      rod_id = p.prod_id and s.amoun
                      t_sold > 20000 and p.prod_name
                       = 'Linen Big Shirt'



2.Optionally, filter the results based on user-specific criteria.

    The following example displays statements with a disk reads to buffer gets ratio greater than or equal to 50%:

    COLUMN SQL_TEXT FORMAT a30
    COLUMN SCH FORMAT a3
    COLUMN BUF_GETS FORMAT 99999999
    COLUMN DISK_READS FORMAT 99999999
    COLUMN %_DISK FORMAT 9999.99
    SELECT sql_id, parsing_schema_name as "SCH", sql_text,
           buffer_gets as "B_GETS",
           disk_reads, ROUND(disk_reads/buffer_gets*100,2) "%_DISK"
    FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET(
                'SQLT_WKLD_STS',
                '(disk_reads/buffer_gets) >= 0.50' ) );

    Sample output appears below:

    SQL_ID        SCH SQL_TEXT                       B_GETS DISK_READS %_DISK
    ------------- --- ------------------------------ ------ ---------- -------
    79f8shn041a1f SH select * from sales where quan 24016      17287 71.98
                      tity_sold < 5 union select * f
                      rom sales where quantity_sold
                      > 500

    fudq5z56g642p SH select sum(quantity_sold) from 12118       6355 52.44
                       sales s, products p where s.p
                      rod_id = p.prod_id and s.amoun
                      t_sold > 20000 and p.prod_name
                       = 'Linen Big Shirt'



Modifying a SQL Tuning Set
To modify the contents of an STS:

    1.Connect SQL*Plus to the database with the appropriate privileges, and then optionally query the STS contents using the TABLE function.

    For example, execute the following query:

    SELECT SQL_ID, ELAPSED_TIME, FETCHES, EXECUTIONS
    FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQLT_WKLD_STS'));

    Sample output appears below:

    SQL_ID        ELAPSED_TIME    FETCHES EXECUTIONS
    ------------- ------------ ---------- ----------
    2cqsw036j5u7r      3407459          2          1
    79f8shn041a1f      9453965      61258          1
    bzmnj0nbvmz8t       401869          1          1
    fudq5z56g642p      5300264          1          1

    2.Delete SQL statements based on user-specified criteria.

    Use the basic_filter predicate to filter the SQL from the STS defined on attributes of the SQLSET_ROW. The following example deletes all statements in the STS with fetch counts over 100:

    BEGIN
      DBMS_SQLTUNE.DELETE_SQLSET (
          sqlset_name => 'SQLT_WKLD_STS'
    ,     basic_filter => 'fetches > 100'
    );
    END;
    /

   3. Set attribute values for SQL statements.

    The following example sets the priority of statement 2cqsw036j5u7r to 1:

    BEGIN
      DBMS_SQLTUNE.UPDATE_SQLSET (
          sqlset_name     => 'SQLT_WKLD_STS'
    ,     sql_id          => '2cqsw036j5u7r'
    ,     attribute_name => 'PRIORITY'
    ,     attribute_value => 1
    );
    END;
    /

    4.Optionally, query the STS to confirm that the intended modifications were made.

    For example, execute the following query:

    SELECT SQL_ID, ELAPSED_TIME, FETCHES, EXECUTIONS, PRIORITY
    FROM   TABLE(DBMS_SQLTUNE.SELECT_SQLSET('SQLT_WKLD_STS'));

    Sample output appears below:

    SQL_ID        ELAPSED_TIME    FETCHES EXECUTIONS   PRIORITY
    ------------- ------------ ---------- ---------- ----------
    2cqsw036j5u7r      3407459          2          1          1
    bzmnj0nbvmz8t       401869          1          1
    fudq5z56g642p      5300264          1          1

Transporting a SQL Tuning Set
Transporting SQL Tuning Sets with DBMS_SQLTUNE

To transport an STS:

    1.Connect SQL*Plus to the production database with administrator privileges.

    Use the CREATE_STGTAB_SQLSET procedure to create a staging table to hold the exported SQL tuning sets.

    The following example creates my_11g_staging_table in the dba1 schema and specifies the format of the staging table as 11.2:

    BEGIN
      DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
        table_name => 'my_10g_staging_table'
    ,   schema_name => 'dba1'
    ,   db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
    );
    END;
    /

    2.Use the PACK_STGTAB_SQLSET procedure to populate the staging table with SQL tuning sets.

    The following example populates dba1.my_11g_staging_table with the STS my_sts owned by hr:

    BEGIN
      DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
        sqlset_name         => 'sqlt_wkld_sts'
    ,   sqlset_owner        => 'sh'
    ,   staging_table_name => 'my_11g_staging_table'
    ,   staging_schema_owner => 'dba1'
    ,   db_version          => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
    );
    END;
    /

    3.Use Oracle Data Pump to export the contents of the statistics table.

    For example, run the expdp command at the operating system prompt:

    expdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=sts.dmp TABLES=my_11g_staging_table

    4.Transfer the dump file to the test database host.

    5.Log in to the test host as an administrator, and then use Oracle Data Pump to import the contents of the statistics table.

    For example, run the impdp command at the operating system prompt:

    impdp dba1 DIRECTORY=dpump_dir1 DUMPFILE=sts.dmp TABLES=my_11g_staging_table

    6.On the test database, execute the UNPACK_STGTAB_SQLSET procedure to copy the SQL tuning sets from the staging table into the database.

    The following example shows how to unpack the SQL tuning sets:

    BEGIN
      DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
        sqlset_name        => '%'
    ,   replace            => true
    ,   staging_table_name => 'my_11g_staging_table');
    END;
    /

Dropping a SQL Tuning Set

To drop an STS:

   1. Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.DROP_SQLSET procedure.

    For example, execute the following PL/SQL program:

    BEGIN
      DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'SQLT_WKLD_STS' );
    END;
    /

   2. Optionally, confirm that the STS was deleted.

    The following example counts the number of SQL tuning sets named SQLT_WKLD_STS owned by the current user (sample output included):

    SELECT COUNT(*)
    FROM   USER_SQLSET
    WHERE NAME = 'SQLT_WKLD_STS';

      COUNT(*)
    ----------
             0