Using Oracle 10g SQL Optimizer (STA) Optimization statement

2010-10-23  来源:本站原创  分类:Database  人气:126 

SQL optimizer (SQL Tuning Advisor STA) is introduced in Oracle10g optimization tool to help DBA, which is characterized by simple, smart, DBA value of the call function can give a poor performance, statements of the optimization results. Here's what it's used.

STA must be guaranteed with a CBO optimizer mode.
First, the use of STA optimization statement

Problem statement for the collection, the other by Oracle10g automation tools: database automatic diagnostic monitoring tools (ADDM). Its use can refer to my another article "Oracle10g Automatic Database Diagnostic Monitor Tool (ADDM) Guide."

Here we briefly explain the problem to find how to optimize a statement. As mentioned earlier said, STA is very simple to use (as long as you call the stored procedure, can use this tool), three steps to complete a statement tuning.
Create test environment:
SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a. * FROM dba_objects a;

Table created.
SQL> create table smalltab as select rownum as "id", a. * FROM dba_tables a;
Table created.
SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);

Table altered.
SQL> DECLARE
n NUMBER;
BEGIN
FOR n IN 1 .. 100
LOOP
INSERT INTO bigtab SELECT rownum as "id", a. * FROM dba_objects a;
COMMIT;
END LOOP;
END;
/

PL / SQL procedure successfully completed.

This creates a large table and a small table and no index, execute the following query:
SQL> set timing on
SQL> set autot on
SQL> select count (*) from bigtab a, smalltab b where a.object_name = b.table_name;

COUNT (*)
----------
135000

Elapsed: 00:00:05.59

Execution Plan
-------------------------------------------------- --------
Plan hash value: 3089226980

-------------------------------------------------- ------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-------------------------------------------------- ------------------------------

| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2) | 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| * 2 | HASH JOIN | | 155K | 5462K | 3550 (2) | 00:00:43 |
| 3 | TABLE ACCESS FULL | SMALLTAB | 1223 | 22014 | 11 (0) | 00:00:01 |
| 4 | TABLE ACCESS FULL | BIGTAB | 1205K | 20M | 3526 (1) | 00:00:43 |

-------------------------------------------------- ------------------------------

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

2 - access ("A". "OBJECT_NAME" = "B". "TABLE_NAME")

Statistics
-------------------------------------------------- --------
0 recursive calls
0 db block gets
16013 consistent gets
14491 physical reads
0 redo size
412 bytes sent via SQL * Net to client
385 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The implementation of this statement can be seen poor performance: 16013 consistent gets.
The first step: Create and implement optimization tasks

Created by calling the function CREATE_TUNING_TASK optimization tasks, call the stored procedure EXECUTE_TUNING_TASK perform this task:
SQL> set autot off
SQL> set timing off
SQL> DECLARE
2 my_task_name VARCHAR2 (30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext: = 'select count (*) from bigtab a, smalltab b where a.object_name = b.table_name';
6 my_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
7 sql_text => my_sqltext,
8 user_name => 'DEMO',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_sql_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'tuning_sql_test');
15 END;
16 /

PL / SQL procedure successfully completed.

Function CREATE_TUNING_TASK, sql_text is need to optimize the statement, user_name is the statement by which users perform, scope is to optimize the scope (limited or comprehensive), time_limit optimization time limit, task_name optimization task name, description optimization task description.

You can view USER_ADVISOR_LOG and USER_ADVISOR_LOG to see created for optimization tasks.
SQL> select task_name, status from USER_ADVISOR_LOG where task_name = 'tuning_sql_
test ';

TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
Step Two: View Optimization Results

Optimal results can be viewed through the function.
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('tuning_sql_test') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')
-------------------------------------------------- --------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------- -----------------------------
Tuning Task Name: tuning_sql_test
Tuning Task Owner: DEMO
Scope: COMPREHENSIVE
Time Limit (seconds): 60
Completion Status: COMPLETED
Started at: 11/30/2005 13:16:43
Completed at: 11/30/2005 13:16:44
Number of Index Findings: 1

Schema Name: DEMO
SQL ID: 6p64dnnsqf9pm
SQL Text: select count (*) from bigtab a, smalltab b where
a.object_name = b.table_name

-------------------------------------------------- -----------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------- -----------------------------

1 - Index Finding (see explain plans section below)

The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX $ $ _06C50001 on DEMO.SMALLTAB ('TABLE_NAME');

- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX $ $ _06C50002 on DEMO.BIGTAB ('OBJECT_NAME');

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

EXPLAIN PLANS SECTION
-------------------------------------------------- -----------------------------

1 - Original
-----------
Plan hash value: 3089226980

-------------------------------------------------- ------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-------------------------------------------------- ------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2) | 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| * 2 | HASH JOIN | | 155K | 5462K | 3550 (2) | 00:00:43 |
| 3 | TABLE ACCESS FULL | SMALLTAB | 1223 | 22014 | 11 (0) | 00:00:01 |
| 4 | TABLE ACCESS FULL | BIGTAB | 1205K | 20M | 3526 (1) | 00:00:43 |

-------------------------------------------------- ------------------------------

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

2 - access ("A". "OBJECT_NAME" = "B". "TABLE_NAME")

2 - Using New Indices
--------------------
Plan hash value: 494801882

-------------------------------------------------- ---------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- ---------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 1108 (3) | 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| * 2 | HASH JOIN | | 155K | 5462K | 1108 (3) | 00:00:14 |
| 3 | INDEX FAST FULL SCAN | IDX $ $ _06C50001 | 1223 | 22014 | 3 (0) | 00:00:01 |
| 4 | INDEX FAST FULL SCAN | IDX $ $ _06C50002 | 1205K | 20M | 1093 (2) | 00:00:14 |
-------------------------------------------------- ---------------------------------------

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

2 - access ("A". "OBJECT_NAME" = "B". "TABLE_NAME")

-------------------------------------------------- -----------------------------

Look at this optimization report:

The first part is about the basic information about the optimization of the task: such as task name, execution time, scope, related to the statement and so on.

The second part is on the optimization of the problems found by the task and the given optimization. Gives the first description of the problem before: You can build more of the lead to improve performance; then proposed specific content: a field in the table table_name smalltab create an index on the table bigtab create index on the field object_name; final are related note: Although the optimization of the recommendations given to create the index, but the best proposal through SQL access device (SQL Access Advisor SAA) with the workload of the entire database to in-depth analysis, as a index maintenance can be given consideration and space consumption and other factors more reasonable proposal.

Finally, the report also shows the original query plan, and the optimized query plan after the proposed comparison. COST can see the value dropped significantly.

The third step: According to the optimization optimization

First like to say that it is best not to direct the advice given by the optimizer direct optimization. Because of this operation as the construction of the index is not this one statement. Second, it can use sql profile optimization of a statement or be optimized for certain sessions (the next chapter will give the sql profile how to use.) We are here only to test the effect of optimization.

In accordance with the proposal to create two indexes:
SQL> create index smalltab_idx1 on smalltab (table_name);

Index created.

SQL> create index bigtab_idx1 on bigtab (object_name);

Index created.

SQL> analyze table smalltab compute statistics;

Table analyzed.

SQL> analyze table bigtab compute statistics;

Table analyzed.

SQL> set timing on
SQL> set autot on
SQL> select count (*) from bigtab a, smalltab b where a.object_name = b.table_name;

COUNT (*)
----------
135000

Elapsed: 00:00:01.09

Execution Plan
-------------------------------------------------- --------
Plan hash value: 2594317117

-------------------------------------------------- --------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-------------------------------------------------- --------------------------------------

| 0 | SELECT STATEMENT | | 1 | 36 | 1119 (3) | 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| * 2 | HASH JOIN | | 155K | 5463K | 1119 (3) | 00:00:14 |
| 3 | INDEX FAST FULL SCAN | SMALLTAB_IDX1 | 1223 | 22014 | 3 (0) | 00:00:01 |
| 4 | INDEX FAST FULL SCAN | BIGTAB_IDX1 | 1205K | 20M | 1104 (2) | 00:00:14 |

-------------------------------------------------- --------------------------------------

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

2 - access ("A". "OBJECT_NAME" = "B". "TABLE_NAME")

Statistics
-------------------------------------------------- --------
332 recursive calls
0 db block gets
4999 consistent gets
1 physical reads
0 redo size
412 bytes sent via SQL * Net to client
385 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
12 sorts (memory)
0 sorts (disk)
1 rows processed

It can be seen, consistent gets dropped dramatically than before, the optimization does improve performance.

Oracle10g for optimization has become so simple.
Second, the use of sql profile storage optimization strategy

Optimize the use of the statement after the STA, STA will analyze statements by using the best optimization strategy, and gives the optimized query plan. You can follow the advice given STA rewrite statement. However, in some cases, you may not rewrite the statement (such as in a production environment, your statement is another in a package). This time we can use sql profile, will be stored in the profile in the optimization strategy, Oracle building this statement in the query plan, they will not use the existing statistical data, and profile of the strategy used to generate a new query plan.

Part I: profile of use

SQL Profile for the type of statement about effective:

SELECT statement;

UPDATE statement;

INSERT statements (only valid when using the SELECT clause);

DELETE statement;

CREATE statement (only valid when using the SELECT clause);

MERGE statement (UPDATE, and INSERT operations only as valid.)

In addition, there must also be using the SQL Profile CREATE ANY SQL PROFILE, DROP ANY SQL PROFILE and ALTER ANY SQL PROFILE other system privileges.

Is an example of it:
The first step: assign permissions to users
SQL> conn sys / sys as sysdba
Connected.

SQL> GRANT CREATE ANY SQL PROFILE TO DEMO;

Grant succeeded.

SQL> GRANT DROP ANY SQL PROFILE TO DEMO;

Grant succeeded.

SQL> GRANT ALTER ANY SQL PROFILE TO DEMO;

Grant succeeded.

SQL> conn demo / demo
Connected.

SQL> create index smalltab_idx1 on smalltab (table_name);

Index created.

SQL> analyze table smalltab compute statistics;

Table analyzed.

SQL> set autot on
SQL> select / * + no_index (smalltab smalltab_idx1) * / count (*) from smalltab where ta
ble_name = 'TAB $';

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

Execution Plan
-------------------------------------------------- --------
Plan hash value: 2298554444

-------------------------------------------------- -----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- -----------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| * 2 | TABLE ACCESS FULL | SMALLTAB | 1 | 18 | 11 (0) | 00:00:01 |
-------------------------------------------------- -----------------------------

The second step, create, perform optimization tasks
SQL> DECLARE
2 my_task_name VARCHAR2 (30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext: = 'select / * + no_index (smalltab smalltab_idx1) * / count (*) from
smalltab where table_name =''TAB $''';
6 my_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
7 sql_text => my_sqltext,
8 user_name => 'DEMO',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'sql_profile_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'sql_profile_test');
15 END;
16 /

PL / SQL procedure successfully completed.

Step Three: View Optimization Tips
SQL> set autot off
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('sql_profile_test') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- --------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------- -----------------------------
Tuning Task Name: sql_profile_test
Tuning Task Owner: DEMO
Scope: COMPREHENSIVE
Time Limit (seconds): 60
Completion Status: COMPLETED
Started at: 11/29/2005 14:52:09
Completed at: 11/29/2005 14:52:09
Number of SQL Profile Findings: 1

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- -----------------------------------------
-------------------------------------------------- --------------------------------------
Schema Name: DEMO
SQL ID: 3kta54ycuqccb
SQL Text: select / * + no_index (smalltab smalltab_idx1) * / count (*) from
smalltab where table_name = 'TAB $'

-------------------------------------------------- -----------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------- -----------------------------

1 - SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- ------------------------------
-------------------------------------------------- -------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 90.94%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile (task_name => 'sql_profile_test',
replace => TRUE);

-------------------------------------------------- -----------------------------
EXPLAIN PLANS SECTION

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- ------------------------------
-------------------------------------------------- -------------------------------------------------

1 - Original With Adjusted Cost
------------------------------
Plan hash value: 2298554444

-------------------------------------------------- -----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- -----------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- --------------------------------------------------
| * 2 | TABLE ACCESS FULL | SMALLTAB | 1 | 18 | 11 (0) | 00:00:01 |
-------------------------------------------------- -----------------------------

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

2 - filter ("TABLE_NAME" = 'TAB $')

2 - Using SQL Profile
--------------------
Plan hash value: 2664476518

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- --------------------------------------------------
-------------------------------------------------- --------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time
|
-------------------------------------------------- ---------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| * 2 | INDEX RANGE SCAN | SMALLTAB_IDX1 | 1 | 18 | 1 (0) | 00:00:01 |
-------------------------------------------------- ---------------------------------

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

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST')
-------------------------------------------------- --------------------------------------------------

2 - access ("TABLE_NAME" = 'TAB $')

-------------------------------------------------- -----------------------------

Here you can see that in the optimization given a new query plan. Now, we have decided to accept this proposal, and does not rewrite the statement.

The third step: receiving profile
SQL> DECLARE
2 my_sqlprofile_name VARCHAR2 (30);
3 BEGIN
4 my_sqlprofile_name: = DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => 'sql_profile_test',
6 name => 'my_sql_profile');
7 END;
8 /

PL / SQL procedure successfully completed.

Here the package DBMS_SQLTUNE with another function: ACCEPT_SQL_PROFILE. The parameter task_name optimization tips that we create the name of the task, name is the profile name can be any valid name. Besides this function, there are other functions, the following is the prototype of this function:
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name IN VARCHAR2,
object_id IN NUMBER: = NULL,
name IN VARCHAR2: = NULL,
description IN VARCHAR2: = NULL,
category IN VARCHAR2: = NULL;
task_owner IN VARCHAR2: = NULL,
replace IN BOOLEAN: = FALSE,
force_match IN BOOLEAN: = FALSE)
RETURN VARCHAR2;

Description is the description of profile information; task_owner optimization task is the owner; replace is TRUE, if the profile already exists to replace it; force_match to TURE, it said that mandatory match with the statement that the mandatory use bind variables, and system parameter is set to FORCE when cursor_sharing similar to FALSE, and when cursor_sharing set to EXACT, similar to that exact match.

A special mention is the category this parameter, you can set this parameter to use this session to develop a specific profile. In 10g, each session has a new parameter SQLTUNE_CATEGORY, his default is DEFAULT. And we call this function, if this parameter is not specified, then its value is DEFAULT, and if we give the profile of other CATEGORY specify a value, such as FOR_TUNING, then only the session parameters for FOR_TUNING SQLTUNE_CATEGORY only when Use this porfile. This parameter is useful why it? Imagine such an environment: a production system you are tuning a statement by STA, STA optimization has been given, but you can not rush the implementation of the recommendations it gives (after all, it's just the machine Well, not completely trust) , you can create a special CATEGORY of the profile, and then develop your own session SQLTUNE_CATEGORY for this special CATEGORY, it can either look at the practical effect of optimization without affecting the production environment.

You can also view DBA_SQL_PROFILES to see has been created profile.
Step Four: View the profile of the effect of
SQL> select / * + no_index (smalltab smalltab_idx1) * / count (*) from smalltab where ta
ble_name = 'TAB $';

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

Execution Plan
-------------------------------------------------- --------
Plan hash value: 2664476518

-------------------------------------------------- ---------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- ---------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| * 2 | INDEX RANGE SCAN | SMALLTAB_IDX1 | 1 | 18 | 1 (0) | 00:00:01 |
-------------------------------------------------- ---------------------------------

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

2 - access ("TABLE_NAME" = 'TAB $')

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

You can see, the statement profile data used to create a new query plan. There is also in the query plan and some additional information, that this statement is used 'my_sql_profile' the profile, rather than the object to generate the above statistics the query plan.

Part II: profile of the control

The control of the profile is relatively simple: modify, and delete. Package DBMS_SQLTUNE provides two stored procedures to accomplish these two operations: ALTER_SQL_PROFILE and DROP_SQL_PROFILE.

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE (name => 'my_sql_profile');

END;

/
1. Profile changes

ALTER_SQL_PROFILE prototype is:
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name IN VARCHAR2,
attribute_name IN VARCHAR2,
value IN VARCHAR2);

Where, name is the profile name; attribute_name is the name of the attribute to modify; value is the modified value. For example, the need to 'my_sql_profile' failure, you can modify the STATUS attribute to DISABLED:
SQL> BEGIN
2 DBMS_SQLTUNE.ALTER_SQL_PROFILE (
3 name => 'my_sql_profile',
4 attribute_name => 'STATUS',
5 value => 'DISABLED');
6 END;
7 /

PL / SQL procedure successfully completed.

SQL>
SQL> set autot on exp
SQL> select / * + no_index (smalltab smalltab_idx1) * / count (*) from smalltab where ta
ble_name = 'TAB $';

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

Execution Plan
-------------------------------------------------- --------
Plan hash value: 2298554444

-------------------------------------------------- -----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------- -----------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| * 2 | TABLE ACCESS FULL | SMALLTAB | 1 | 18 | 11 (0) | 00:00:01 |
-------------------------------------------------- -----------------------------

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

2 - filter ("TABLE_NAME" = 'TAB $')

2. Delete profile

DROP_SQL_PROFILE prototype is:
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name IN VARCHAR2,
ignore IN BOOLEAN: = FALSE);

Where, name is the profile name, ignore the TRUE, when the specified profile does not exist without an error.

3. Confirm whether a statement has the appropriate profile

Of course, we also want to confirm whether a statement has formed a profile, see if it is necessary for tuning. Then they can use SQLTEXT_TO_SIGNATURE function:
SQL> set serveroutput on
SQL> declare
2 v_signature number;
3 begin
4 v_signature: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (
5 sql_text => 'select / * + no_index (smalltab smalltab_idx1) * / count (*) from smalltab where table_name =''TAB $''',
6 force_match => FALSE);
7 if v_signature is null then
8 DBMS_OUTPUT.put_line ('no such sql text in profile');
9 else
10 DBMS_OUTPUT.put_line ('The sql text is in profile');
11 end if;
12 end;
13 /
The sql text is in profile

PL / SQL procedure successfully completed.

Which, sql_text is the need to detect the content; force_match ACCEPT_SQL_PROFILE interpretation and the corresponding parameters are the same.
Part III: profile of the dump and transplantation

In some circumstances, such as the production environment is very high security requirements, can not directly optimize the production environment, only the production environment in a consistent environment for the optimization of the image, then, want to optimize the results of implementation to the production environment to go, how to do it? DBMS_SQLTUNE package provides several other functions are used to export the profile data to the table, and then the data table can then migrate to other environments, the following describes how to use them.
The first step: create a stored table

First create a stored procedure using a table stored profile:
SQL> begin
2 DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
3 table_name => 'PROFILE_STGTAB',
4 schema_name => 'DEMO',
5 tablespace_name => 'EDGARDEMO');
6 end;
7 /

PL / SQL procedure successfully completed.

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

Table_name is the need to create the storage table's name, schema_name the name of its own schema, tablespace_name is the table space belongs.

In addition, note that this stored procedure actually does is create table operation, which is the DDL operation, so do not call it within a transaction. Use this stored procedure requires CREATE ANY TABLE privileges.
Step two: the profile stored in the data into a table

Profile using a stored procedure can be built in the data storage goes just storage table:
SQL> BEGIN
2 DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
3 profile_name => '%',
4 profile_category => 'DEFAULT',
5 staging_table_name => 'PROFILE_STGTAB',
6 staging_schema_owner => 'DEMO');
7 END;
8 /

PL / SQL procedure successfully completed.

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> select profile_name, category, sql_text from PROFILE_STGTAB;

PROFILE_NAME CATEGORY SQL_TEXT
------------------------------ -------------------- -------------------------------------------------- -----------------
my_sql_profile DEFAULT select / * + no_index (smalltab smalltab_idx1) * / count (*)
frommalltab where table_name = 'TAB $'

profile_name is the need to store the profile name (case sensitive), the default is '%', that is, all under the CATEGORY profile; profile_category the category in which to store the profile name (case sensitive), the default is DEFAULT; staging_table_name is to use store profile data in the table name (case sensitive); staging_schema_owner is the table belongs schema. Some call this function requires CREATE ANY SQL PROFILE system privilege, and to have SELECT permissions on the storage table.

To note that: call the stored procedure will execute COMMIT, so pay attention to the impact of the previous transaction.

In addition, you can modify the stored procedure to store the information in the table:
SQL> begin
2 DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF (
3 old_profile_name => 'my_sql_profile',
4 new_profile_name => 'new_sql_profile',
5 new_profile_category => 'DEV',
6 staging_table_name => 'PROFILE_STGTAB',
7 staging_schema_owner => 'DEMO');
8 end;
9 /

PL / SQL procedure successfully completed.

Old_profile_name is a need to modify the stored profile in the storage table's name (case sensitive); new_profile_name is the need to modify the new name, the default is NULL, not change the name; new_profile_category is the need to change the name to the new directory, the default is NULL, neither change; staging_table_name is the need to modify the storage table name (case sensitive); staging_schema_owner is stored in the table belongs schema.

Of course, call this function requires the UPDATE privilege on the stored table.
The third step: Import the table from the profile data storage

The profile of the data into a storage table, we can store the data in the table will lead to other databases to go:
SQL> create table STGTAB as select * from PROFILE_STGTAB @ EDGAR;

Table created.

And then export the data to profile:
SQL> begin
2 DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
3 profile_name => 'new_sql_profile',
4 profile_category => 'DEV',
5 replace => FALSE,
6 staging_table_name => 'STGTAB',
7 staging_schema_owner => 'DEMO');
8 end;
9 /

PL / SQL procedure successfully completed.

SQL> select name, category, sql_text from DBA_SQL_PROFILES
2;

NAME CATEGORY SQL_TEXT
------------------------------ -------------------- ---------- ---------------------------------------- ------------
new_sql_profile DEV select / * + no_index (smalltab smalltab_idx1) * / count (*)
from smalltab where table_name = 'TAB $'

You can see, profile has been successfully imported. Call the stored procedure requires some CREATE ANY SQL PROFILE system privilege. And the meaning of the parameters is the same as the previous function.
Added: store data in the table and storage table delete to delete.

In fact, the store table data stored in the table delete and delete and normal operation is the same table, use DELETE and DROP on it:
SQL> delete from STGTAB;

1 row deleted.

SQL> drop table STGTAB;

Table dropped.
Third, the summary

As the article mentioned at the beginning, this tool lets statement makes it really easy tuning, DBA can use the shortest possible time, the best way to optimize the suggestions given, and the safest way to debug optimized results.

In addition, STA also a concern for the data warehouse environment is very useful tool down: SQL Tuning Set. Alone we will introduce it with an article.

Of: fuyuncat
Source: www.HelloDBA.com

相关文章
  • Using Oracle 10g SQL Optimizer (STA) Optimization statement 2010-10-23

    SQL optimizer (SQL Tuning Advisor STA) is introduced in Oracle10g optimization tool to help DBA, which is characterized by simple, smart, DBA value of the call function can give a poor performance, statements of the optimization results. Here's what

  • Oracle 10g SQL optimizer (STA) Optimization statement 2011-04-14

    SQL optimizer (SQL Tuning Advisor STA) is introduced in Oracle10g DBA with the help of optimization tools, it is characterized by simple, smart, DBA values ​​need to call function can give a poor performance of the statement of the optimization resul

  • Database index Oracle 10g SQL Performance Tuning - use the function index to address the role of general index of problem does not occur 2010-03-18

    Transfer from: http://www.javaeye.com/articles/2516 As the Oracle 10g SQL Performance Tuning - use the index to improve the performance of the database select statement as an example, for the test app_user table, 3 million records, the select * from

  • Experience of Oracle 10g sql tuning advisor 2010-11-26

    Is free, bored, suddenly saw a document Oralce 10g sql tuning adviser, which lists the many benefits of the new features: Quote - Determining stale or missing statistics - Determining better execution plan - Detecting better access paths and objects

  • Oracle 10g SQL Fundamentals I (Study Notes Chapters 1-3) 2011-10-08

    First Chapters distinct like 'S%' S Starts with '_A%' The second letter is A Bind variables select ename from &haha; select &&hehe from scott.emp whre &hehe.2000 order by &hehe Multiple substitution, the sentence , Appear multiple time

  • Oracle 10g SQL Fundamentals I (Study Notes Chapter II, para 10-11) 2011-10-16

    Create other objects Chapter ( View, index , Sequences, synonyms ) View of the advantages of : 1. Simplify queries 2. You can restrict access to data 3. Provide data independence 4. The same data can be displayed using different views Create a view :

  • Oracle 10g SQL Fundamentals II (study notes two Section 1-2) 2011-10-17

    Chapter user access Create a user create user user identified by passwd; Authorize grant privilege to user; grant create session,create table,create sequence,create view to scott; Create a role create role manager; grant create table,create view to m

  • Oracle 10g SQL Fundamentals II (study notes two Section 3-4) 2011-10-17

    Chapter III data collection Copies data from a table insert into sales(id,name,salary,commission_pct) select employee_id,last_name,salary from employees where job_id like '%REP%'; Use a subquery as the Insert destination INSERT INTO (SELECT employee_

  • Oracle 10g SQL Fundamentals II (study notes two chapters 5-6) 2011-10-17

    Chapter fifth time zones management data TIME_ZONE The session parameter value A relative value : ALTER SESSION SET TIME_ZONE = '-05:00'; Database time zone : ALTER SESSION SET TIME_ZONE = dbtimezone; The operating system time zone : ALTER SESSION SE

  • Oracle 10g SQL Fundamentals I (Study Notes Chapter II, para 4-6) 2011-10-08

    The fourth section : Grouping function : select [column,]group_function(column),... from table where condition group by column order by column; select avg(salary) ,max(salary),min(salary),sum(salary) from employees where job_id where '%REP%'; select

  • Oracle 10g SQL Fundamentals I (study notes two chapters 7-8) 2011-10-16

    Seventh chapter using a variety of operations Union/UNION ALL/MINUS/INTERSECT union The use of : Duplicate result removal of query select employee_id,job_id from employees union select employee_id,job_id from job_history; union all The use of :( Incl

  • Oracle 10g SQL Fundamentals II (study notes two chapters 7-8) 2011-10-17

    Chapter seventh layered search Syntax : SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ; For example, : SELECT employee_id, last_name, job_id, manager_id FROM employees START

  • How to maintain the stability of Oracle database SQL performance 2010-07-24

    Customer experience with SQL performance instability, sudden deterioration of performance caused serious problems. For large-scale systems that, SQL performance unstable and sometimes sudden deterioration, which is frequently encountered problems. Th

  • Oracle 10G Basic Operation (1) 2010-03-15

    C: Documents and SettingsAdministrator> sqlplus SQL * Plus: Release 9.0.1.0.1 - Production on Monday February 16 17:26:46 2009 (C) Copyright 2001 Oracle Corporation. All rights reserved. Please enter a user name: / as sysdba Connect to: Oracle9i Ente

  • Comparative Functional oracle and sql server 2011-01-05

    Oracle Database 10g compared the competitive advantage of SQL Server 2005 2005.11.7 officially released by Microsoft in SQL Server 2005. By comparing Oracle Database 10g and SQL Server 2005 features and performance, we give the competitive advantage

  • ORACLE and SQL syntax differences summarized 2010-03-04

    Comparison of data types Type name Oracle SQLServer Compare Character data types CHAR CHAR are fixed-length character data, but the maximum degree of oracle which 2kb, SQLServer which the maximum length of 8kb Variable-length character data type VARC

  • oracle 10g Regular Expressions 2010-10-07

    Quote oracle regular expressions (10g only available) oracle regular expression (regular expression) briefly present, the regular expression has a lot of software is widely used, including * nix (Linux, Unix, etc.), HP and other operating systems, PH

  • ORACLE database SQL statement optimization techniques 2010-03-15

    ORACLE database SQL statement optimization techniques: IN operator IN written with the advantages of SQL is easier to write and clear easy to understand, is more suitable for modern software development style. However, the SQL performance with IN is

  • optimization oracle 10g 2011-06-04

    I. Optimization aspects to be considered 1 program their own designs; 2. SQL statement optimization: 1) through v $ librarycache about hit rate, This is referred to below Shared Pool has a relationship to avoid duplication of parsing the SQL statemen

  • Oracle's optimizer (Optimizer) (CBO optimization) to share 2011-09-01

    Oracle's optimizer (Optimizer) (CBO optimization) Before running a SQL Oracle, first analyze the statement execution plan, then the execution plan to execute. Analysis of statement execution plan by the optimizer work (Optimizer) to complete. Differe