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

Sql turning advise (sta)

SQL optimizer SQL Tuning Advisor (STA) is oracle's sql optimization subsidy tool.

In fact, there are two main ways to optimize sql:

One is to rewrite sql itself. Rewriting sql requires a good understanding of sql syntax and the execution of the database.

The second is this STA, which belongs to the DBMS_SQLTUNE package, and its main purpose is to create the correct index on the tables used by sql.

Prerequisites for using STA:

Make sure the optimizer is in CBO mode.

Show parameterOPTIMIZER_MODE

All_rows / * CBO,sql all return rows are run in a cost-based manner * /

First_rows / * CBO, using a combination of cost and heuristics, to find a way to return the first few lines as quickly as possible * /

First_rows_n / * CBO, all using the cost-based optimization method CBO, and return the first N rows of records as fast as possible * /

Choose / * if statistics are available, use CBO, otherwise use RBO*/

Rule / * RBO*/

Advisor permission is required to execute DBMS_SQLTUNE package for sql optimization:

Grant advisor toscott

The specific use of the tool is described in detail through a case:

1: create a case user and authorize

SQL > createuser lanniao identified by lanniao

The user has been created.

SQL > grantconnect,resource to lanniao

Authorization successful.

SQL > grantadvisor to lanniao

Authorization successful.

2: create a test table

SQL > createtable bigtab as select rownum as id,a.* from sys.all_objects a

The table has been created.

SQL > createtable smalltab as select rownum as id,a.* from sys.all_tables a

The table has been created.

Then run the following script several more times to add the data in the table:

SQL > insertinto bigtab select rownum as id,a.* fromsys.all_objects a

55637 rows were created.

SQL > insertinto bigtab select rownum as id,a.* fromsys.all_objects a

55637 rows were created.

SQL > insertinto bigtab select rownum as id,a.* fromsys.all_objects a

55637 rows were created.

SQL > commit

The submission is complete.

Here you create a large table and a small table, and there are no indexes, so execute a query:

SQL > conn / as sysdba

Connected.

SQL > grantdba to lanniao

Authorization successful.

SQL > conn lanniao/lanniao

Connected.

SQL > settiming on

SQL > setautot on

SQL > select count (*) from bigtab a, smalltab bwhere a.object_name=b.table_name

COUNT (*)

-

seven hundred and fifty two

Time spent: 00: 00: 00.32

Carry out the plan

Plan hashvalue: 3089226980

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

| | 0 | SELECT STATEMENT | | 1 | 34 | 966 (1) | 00:00:12 |

| | 1 | SORT AGGREGATE | | 1 | 34 |

| | * 2 | HASH JOIN | | 155k | 5152k | 966 (1) | 00:00:12 |

| | 3 | TABLE ACCESS FULL | SMALLTAB | 2542 | 43214 | 32 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | BIGTAB | 227K | 3778K | 932 (1) | 00:00:12 |

PredicateInformation (identified by operation id):

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

Note

-

-dynamic sampling used for this statement (level=2)

Statistical information

73 recursive calls

1 db block gets

3683 consistent gets

942 physical reads

132 redo size

535 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

As you can see above, when executing the above two queries, the two tables go to full table scan and hash join.

3: use STA to analyze sql

3.1: create an optimization task

Create the optimization task by calling the function CREATE_TUNING_TASK, and call the stored procedure EXECUTE_TUNING_TASK to perform the task:

SQL > setautot off

SQL > settiming off

SQL > declare

2 my_task_name varchar2 (30)

3 my_sqltext clob

4 begin

5 my_sqltext:='select count (*) from bigtab a minute smalltab b where

6 a. Objectroomnameroomb.tableroomname`

7 my_task_name:=dbms_sqltune.create_tuning_task (

8 sql_text = > my_sqltext

9 user_name = > 'LANNIAO'

10 scope = > 'COMPREHENSIVE'

11 time_limit = > 60

12 task_name = > 'tuning_sql_test'

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

14 dbms_sqltune.execute_tuning_task (task_name = > 'tuning_sql_test')

15 end

16 /

The PL/SQL process completed successfully.

Function CREATE_TUNING_TASK

Sql_text is a statement that needs to be optimized

User_name is the user through which the statement is executed, with an uppercase user name

Scope is the scope of optimization (limited or comprehensive)

Time limit of time_limit Optimization process

Task_name optimization task name

Description of the description optimization task.

3.2: perform optimization tasks

Perform the previously created optimization task by calling the dbms_sqltune.execute_tuning_task procedure.

SQL > execdbms_sqltune.execute_tuning_task ('tuning_sql_test')

The PL/SQL process completed successfully.

3.3: check the status of the optimization task

You can see the current status of the tuning task by looking at the user_advisor_tasks/dba_advisor_tasks view.

SQL > setlinesize 1000

SQL > SELECTtask_name,status FROM USER_ADVISOR_TASKS WHERE task_name = 'tuning_sql_test'

TASK_NAME STATUS

Tuning_sql_test COMPLETED

3.4: view optimization results

The result of the optimization task can be obtained through the dbms_sqltune.report_tning_task function.

SQL > set long999999

SQL > setserveroutput on size 999999

SQL > set line120

SQL > selectDBMS_SQLTUNE.REPORT_TUNING_TASK ('tuning_sql_test') from dual

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

GENERALINFORMATION SECTION

Tuning TaskName: tuning_sql_test

Tuning TaskOwner: LANNIAO

WorkloadType: Single SQL Statement

ExecutionCount: 2

CurrentExecution: EXEC_1056

Execution Type: TUNE SQL

Scope: COMPREHENSIVE

TimeLimit (seconds): 60

CompletionStatus: COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

Started at: 04/14/2014 16:42:18

Completedat: 04/14/2014 16:42:19

Schema Name:LANNIAO

SQL ID: 9n5grk4kh8ndq

SQL Text: select count (*) from bigtab a mai smalltab bwhere

A.object_name=b.table_name

FINDINGS SECTION (3 findings)

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

1-StatisticsFinding

-

The table "LANNIAO". "SMALLTAB" has not been parsed.

Recommendation

-

-consider collecting optimizer statistics for this table.

Execute dbms_stats.gather_table_stats (ownname= > 'LANNIAO', tabname = >

'SMALLTAB', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

Method_opt = > 'FOR ALL COLUMNSSIZE AUTO')

Rationale

-

In order to select a good execution plan, the optimizer needs the latest statistics for this table.

2-StatisticsFinding

-

The table "LANNIAO". "BIGTAB" has not been parsed.

Recommendation

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

-

-consider collecting optimizer statistics for this table.

Executedbms_stats.gather_table_stats (ownname = > 'LANNIAO', tabname = >

'BIGTAB', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE

Method_opt = > 'FOR ALL COLUMNSSIZE AUTO')

Rationale

-

In order to select a good execution plan, the optimizer needs the latest statistics for this table.

3-Index Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

The execution plan for this statement can be improved by creating one or more indexes.

Recommendation (estimated benefit: 90.48%)

-

-consider running access guides that can improve physical solution design or creating recommended indexes.

Create index LANNIAO.IDX$$_04050001 onLANNIAO.SMALLTAB ("TABLE_NAME")

-consider running access guides that can improve physical solution design or creating recommended indexes.

Create index LANNIAO.IDX$$_04050002 onLANNIAO.BIGTAB ("OBJECT_NAME")

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

Rationale

-

Creating a recommended index can significantly improve the execution plan of this statement. However, using a typical SQL workload transport

Line "visit Guide"

It may be preferable to a single statement. In this way, comprehensive index recommendations can be obtained, including computational index maintenance.

Overhead and additional space consumption.

EXPLAIN PLANSSECTION

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

1-Original

-

Plan hash value:3089226980

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

| | 0 | SELECT STATEMENT | | 1 | 34 | 966 (1) | 00:00:12 |

| | 1 | SORT AGGREGATE | | 1 | 34 |

| | * 2 | HASH JOIN | | 155k | 5152k | 966 (1) | 00:00:12 |

| | 3 | TABLE ACCESS FULL | SMALLTAB | 2542 | 43214 | 32 (0) | 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

| | 4 | TABLE ACCESS FULL | BIGTAB | 227K | 3778K | 932 (1) | 00:00:12 |

PredicateInformation (identified by operation id):

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

2-Using NewIndices

-

Plan hash value:2901183249

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

-

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

Ime |

-

| | 0 | SELECT STATEMENT | | 1 | 34 | 92 (4) | 0 |

0:00:0

2 |

| | 1 | SORT AGGREGATE | | 1 | 34 |

| |

| | * 2 | HASH JOIN | | 155k | 5152K | 92 (4) | 0 |

0:00:02 |

| | 3 | INDEX FAST FULL SCAN | IDX$$_04050001 | 2542 | 43214 | 12 (0) | 0 |

0:00:01 |

| | 4 | INDEX FAST FULL SCAN | IDX$$_04050002 | 227K | 3778K | 78 (2) | 0 |

0:00:0

1 |

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')

-

PredicateInformation (identified by operation id):

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

Take a look at this optimization recommendation report:

The first part is the basic information about this optimization task: such as task name, execution time, scope, statements involved, and so on.

The second part is about the problems found in this optimization task and the optimization suggestions given. The problem is described first: collecting statistics on the table and improving performance by building more indexes; then the specific content of the proposal: create an index on the field table_name of table smalltab, and create an index on the field object_name of table bigtab Finally, there are relevant considerations: although this optimization gives suggestions for creating an index, it is best to make an in-depth analysis through the SQL access advisor (SQL Access Advisor SAA) combined with the workload of the entire database, so that more reasonable suggestions can be given taking into account factors such as index maintenance and space consumption.

Finally, the report also gives the original query plan and the comparison of the query plan after the adoption of optimization recommendations. It can be seen that the value of COST has greatly decreased.

3.5: delete optimization tasks

You can delete existing optimization tasks by calling dbms_sqltuen.drop_tuning_task

SQL > execdbms_sqltune.drop_tuning_task ('tuning_sql_test')

3.6: optimize according to optimization recommendations

First of all, it is best not to optimize directly according to the recommendations given by the optimizer. Because operations such as indexing do not affect this statement, we are only here to verify the effectiveness of the optimization recommendations.

As recommended, create two indexes:

SQL > createindex smalltab_idx1 on smalltab (table_name)

The index has been created.

SQL > createindex bigtab_idx1 on bigtab (object_name)

The index has been created.

SQL > analyzetable smalltab compute statistics

The table has been analyzed.

SQL > analyzetable bigtab compute statistics

The table has been analyzed.

SQL > settiming on

SQL > setautot on

SQL > selectcount (*) from bigtab a, smalltab b where a.object_name=b.table_name

COUNT (*)

-

seven hundred and fifty two

Time spent: 00: 00: 00.05

Carry out the plan

Plan hash value:2594317117

-

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

Me |

-

| | 0 | SELECT STATEMENT | | 1 | 44 | 321 (2) | 00 |

: 00:04 |

| | 1 | SORT AGGREGATE | | 1 | 44 |

| |

| | * 2 | HASH JOIN | | 20689 | 888K | 321 (2) | 00 |

: 00:04 |

| | 3 | INDEX FAST FULL SCAN | SMALLTAB_IDX1 | 2869 | 54511 | 5 (0) | 00 |

: 00:01 |

| | 4 | INDEX FAST FULL SCAN | BIGTAB_IDX1 | 222k | 5433K | 313 (1) | 00 |

: 00:04 |

-

PredicateInformation (identified by operation id):

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

Statistical information

1 recursive calls

0 db block gets

1176 consistent gets

3 physical reads

0 redo size

535 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

As you can see, consistentgets is much lower than it was before optimization, and optimization recommendations do improve performance. Oracle10g makes optimization so easy.

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.