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

First, create test data.

SQL > conn scott/oracle

Connected.

SQL > create table obj as select * from dba_objects

Table created.

SQL > create table ind as select * from dba_indexes

Table created.

SQL > insert into obj select * from obj

86965 rows created.

SQL > insert into obj select * from obj

173930 rows created.

SQL > insert into obj select * from obj

347860 rows created.

SQL > insert into obj select * from obj

695720 rows created.

SQL > commit

2

Commit complete.

SQL > insert into ind select * from ind

5069 rows created.

SQL > insert into ind select * from ind

10138 rows created.

SQL > insert into ind select * from ind

20276 rows created.

SQL > commit

Commit complete.

two。 Then make a joint query on the two tables, obj and ind, and view their execution plan through autotrace:

SQL > set timing on

SQL > set autot trace

SQL > set line 160

SQL > select count (*) from obj o, ind i where o.object_name=i.index_name

Elapsed: 00:00:00.23

Execution Plan

Plan hash value: 380737209

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

| | 0 | SELECT STATEMENT | | 1 | 83 | | 11272 (1) | 00:02:16 |

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

| | * 2 | HASH JOIN | | 13m | 1086m | 1416K | 11272 (1) | 00:02:16 |

| | 3 | TABLE ACCESS FULL | IND | 49775 | 826k | | 378 (0) | 00:00:05 |

| | 4 | TABLE ACCESS FULL | OBJ | 1456K | 91m | | 5413 (1) | 00:01:05 |

Predicate Information (identified by operation id):

2-access ("O". "OBJECT_NAME" = "I". "INDEX_NAME")

Note

-

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

Statistics

0 recursive calls

0 db block gets

21308 consistent gets

0 physical reads

0 redo size

528 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

By executing the plan, you can clearly see the full table scan and hash join of the two tables when executing the federated query of the above two tables.

3 View the sql_id of the sql statement

SQL > set autot off

SQL > set timing off

SQL > set line 160

SQL > col sql_text for A65

Select sql_id, sql_text, optimizer_mode, plan_hash_value, child_number from v$sql where sql_text like 'select count (*) from obj o, ind i where o.objectroomnametelli indexingname% alternate SQL > SQL >

SQL_ID SQL_TEXT OPTIMIZER_ PLAN_HASH_VALUE CHILD_NUMBER

-

8xwgyq8mkv86x select count (*) from obj o, ind i where o.object_name=i ALL_ROWS 380737209 0

.index _ name

Second, use SQL Tuning Advisor (STA) for optimization.

1 create optimization task

Create an optimization task by calling the function DBMS_SQLTUNE.CREATE_TUNING_TASK

Call the stored procedure DBMS_SQLTUNE.EXECUTE_TUNING_TASK to perform this task:

SQL > DECLARE

A_tuning_task VARCHAR2 (30)

BEGIN

A_tuning_task: = dbms_sqltune.create_tuning_task (sql_id = > '8xwgyq8mkv86x'

Task_name = > 'sql_profile_test_SQLID')

Dbms_sqltune.execute_tuning_task (a_tuning_task)

END

/

PL/SQL procedure successfully completed.

2 perform optimization tasks

SQL > conn / as sysdba

Connected.

SQL > grant advisor to scott

Grant succeeded.

SQL > conn scott/oracle

Connected.

SQL > exec dbms_sqltune.execute_tuning_task ('sql_profile_test_SQLID')

PL/SQL procedure successfully completed.

3 check the status of the optimization task

You can view the current status of the optimization task by viewing the user_advisor_tasks/dba_advisor_tasks view, and COMPLETED indicates that it is complete

SQL > SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name = 'sql_profile_test_SQLID'

TASK_NAME STATUS

Sql_profile_test_SQLID COMPLETED

4 View the optimization results

Set long 999999

Set serveroutput on size 999999

Set line 160

Select DBMS_SQLTUNE.REPORT_TUNING_TASK ('sql_profile_test_SQLID') from dual

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

GENERAL INFORMATION SECTION

Tuning Task Name: sql_profile_test_SQLID

Tuning Task Owner: SCOTT

Workload Type: Single SQL Statement

Execution Count: 2

Current Execution: EXEC_314

Execution Type: TUNE SQL

Scope: COMPREHENSIVE

Time Limit (seconds): 1800

Completion Status: COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

Started at: 04/12/2020 18:23:49

Completed at: 04/12/2020 18:23:49

Schema Name: SCOTT

SQL ID: 8xwgyq8mkv86x

SQL Text: select count (*) from obj o, ind i where

O.object_name=i.index_name

FINDINGS SECTION (2 findings)

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

1-Statistics Finding

-

Table "SCOTT". "IND" was not analyzed.

Recommendation

-

-Consider collecting optimizer statistics for this table.

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

'IND', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

Method_opt = > 'FOR ALL COLUMNS SIZE AUTO')

Rationale

-

The optimizer requires up-to-date statistics for the table in order to

Select a good execution plan.

2-Statistics Finding

-

Table "SCOTT". "OBJ" was not analyzed.

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

Recommendation

-

-Consider collecting optimizer statistics for this table.

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

'OBJ', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE

Method_opt = > 'FOR ALL COLUMNS SIZE AUTO')

Rationale

-

The optimizer requires up-to-date statistics for the table in order to

Select a good execution plan.

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

EXPLAIN PLANS SECTION

1-Original

-

Plan hash value: 380737209

-

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

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

| |

-

| 0 | SELECT STATEMENT | | 1 | 83 | | 11272 (1) | 00:02:

16 |

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

| |

| * 2 | HASH JOIN | | 13m | 1086m | 1416K | 11272 (1) | 00:02:

16 |

| 3 | TABLE ACCESS FULL | IND | 49775 | 826k | | 378K (0) | 00:00:

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

05 |

| 4 | TABLE ACCESS FULL | OBJ | 1456K | 91m | | 5413 (1) | 00:01:

05 |

-

Predicate Information (identified by operation id):

2-access ("O". "OBJECT_NAME" = "I". "INDEX_NAME")

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_PROFILE_TEST_SQLID')

Implement optimization recommendations

SQL > execute dbms_stats.gather_table_stats (ownname = > 'SCOTT', tabname = >' OBJ', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt = > 'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL > execute dbms_stats.gather_table_stats (ownname = > 'SCOTT', tabname = >' IND', estimate_percent = > DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt = > 'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

5 delete optimization task

By calling dbms_sqltuen.drop_tuning_task, you can delete existing optimization tasks and release resources.

SQL > exec dbms_sqltune.drop_tuning_task ('sql_profile_test_SQLID')

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.