Shulou(Shulou.com)06/01 Report--

This article will explain in detail the analysis of the use of STA for you. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Using the SQL Tuning Advisor,DBA provided by Oracle 10g, it is easy to optimize SQL according to the comments and suggestions given by STA, and everything has finally become so simple.

SQL > set timing on

SQL > set autot on

SQL > select count (*) from bigtab a little tab b

2 where a.object_name=b.table_name

COUNT (*)

-

191597

Time spent: 00: 00: 27.53

Carry out the plan

Plan hash value: 3089226980

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

| | 0 | SELECT STATEMENT | | 1 | 83 | 16418 (3) | 00:03:18 |

| | 1 | SORT AGGREGATE | | 1 | 83 |

| | * 2 | HASH JOIN | | 1928K | 152m | 16418 (3) | 00:03:18 |

| | 3 | TABLE ACCESS FULL | SMALLTAB | 1594 | 27098 | 12 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | BIGTAB | 5465K | 343m | 16311 (2) | 00:03:16 |

Predicate Information (identified by operation id):

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

Note

-

-dynamic sampling used for this statement

Statistical information

446 recursive calls

0 db block gets

73581 consistent gets

73386 physical reads

0 redo size

414 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

Now we can create and execute our SQL tuning task

DECLARE

My_task_name VARCHAR2 (30)

My_sqltext CLOB

BEGIN

My_sqltext: = 'select count (*) from bigtab a, smalltab b where a. Object _ name _ name _ name

My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (

Sql_text = > my_sqltext

User_name = > 'SYS'

Scope = > 'COMPREHENSIVE'

Time_limit = > 60

Task_name = > 'oracle_ace_task'

Description = > 'Task to tune a query on a specified table')

DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name = > 'oracle_ace_task')

END

Or we can use a procedure to generate

Create or replace procedure sql_tuning (my_sqltext in clob,schema_name in varchar2)

Is

My_task_name varchar2 (30)

Begin

My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (

Sql_text = > my_sqltext

User_name = > schema_name

Scope = > 'COMPREHENSIVE'

Time_limit = > 60

Task_name = > 'oracle_ace_task'

Description = > 'Task to tune a query on a specified table')

DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name = > 'oracle_ace_task')

End

Exec sql_tuning ('select count (*) from bigtab a, smalltab b where a.objectroomnameaccounb.tableroomnamewriting pageSys')

In the function CREATE_TUNING_TASK,sql_text is the statement that needs to be optimized, user_name is the user through which the statement is executed, scope is the optimization scope (limited or comprehensive), the time limit of the time_limit optimization process, the name of the task_name optimization task, and the description optimization task description.

You can view the optimization tasks created through the views USER_ADVISOR_LOG and USER_ADVISOR_LOG.

SQL > select task_name, status from USER_ADVISOR_LOG where task_name='oracle_ace_task'

TASK_NAME STATUS

Oracle_ace_task COMPLETED

Finally, let's generate the report of tuning.

SQL > set long 10000

SQL > set longchunksize 1000

SQL > set linesize 100

SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('tuning_sql_test') from DUAL

/

PL/SQL procedure successfully completed.

This is the end of the analysis on the use of STA. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.