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

Today, I will talk to you about how to use ORACLE SQL PROFILE, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Sql profile is a new feature from ORACLE 10G and can be managed through OEM and DBMS_SQLTUNE.

Automatic sql tuning:

From the literal meaning, we can see that the SQL statement is optimized automatically. The optimizer may not generate an efficient execution plan because of the lack of some information, which may require manual intervention such as adding HINTS to make the optimizer make the right decision. But you are not allowed to modify CODE for packaged APPLICATION, so AUTOMATIC SQL TUNING can solve this problem through SQL PROFILE. First, you need to create a PROFILE for this SQL STATEMENT. Then SQL PROFILE solves the above generation of POOR EXECPLAN by collecting additional information such as sampling, local execution techniques. Finally, SQL PROFILE will produce a REPORT. Clearly show the advice given. For example, which fields need to establish INDEX, which TALBE need ANALYZE, and so on.

SQL PROFILE: the collected information is stored in the data dictionary. Let the optimizer create an efficient execution plan. It is important to note that as the amount of data increases and the INDEX is created. Maybe our fixed OUTLINE is no longer applicable. So it takes a while to re-REGENERATE SQL PROFILE.

SQL PROFILE valid range:

Select statements

Update statements

Insert statements (only with a select clause)

Delete statements

Create table statements (only with the as select clause)

Merge statements (the update or insert operations)

Declare

My_task_name varchar2 (30)

Mysqltext clob

Begin

Mysqltext:='select * from t where object_id=100'

My_task_name:=dbms_sqltune.create_tuning_task

(sql_text= > mysqltext

User_name= > 'SYSTEM'

Scope= > 'COMPREHENSIVE'

Task_name= > 'sql_tuning_test'

);

End

/

The whole process:

Conn system/admin

Connected.

SQL > create table t as select object_id,object_name from dba_objects

Table created.

SQL > set autotrace on

SQL > select * from t where object_id=100

OBJECT_ID

-

OBJECT_NAME

-

one hundred

ORA$BASE

Execution Plan

Plan hash value: 1601196873

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

| | 0 | SELECT STATEMENT | | 4 | 316 | 96 (2) | 00:00:02 |

| | * 1 | TABLE ACCESS FULL | T | 4 | 316 | 96 (2) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 100)

Note

-

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

Statistics

68 recursive calls

0 db block gets

429 consistent gets

345 physical reads

0 redo size

497 bytes sent via SQL*Net to client

415 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL > declare

My_task_name varchar2 (30)

Mysqltext clob

Begin

Mysqltext:='select * from t where object_id=100'

My_task_name:=dbms_sqltune.create_tuning_task

(sql_text= > mysqltext

User_name= > 'SYSTEM'

Scope= > 'COMPREHENSIVE'

Task_name= > 'sql_tuning_test'

);

End

/ 2 3 4 5 6 7 8 9 10 11 12 13

PL/SQL procedure successfully completed.

SQL > exec dbms_sqltune.execute_tuning_task ('sql_tuning_test')

PL/SQL procedure successfully completed.

SQL > SET LONG 999999

SQL > SET LINESIZE 100

SQL > set serveroutput on size 999999

SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('sql_tuning_test') FROM DUAL

DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL_TUNING_TEST')

GENERAL INFORMATION SECTION

Tuning Task Name: sql_tuning_test

Tuning Task Owner: SYSTEM

Workload Type: Single SQL Statement

Scope: COMPREHENSIVE

Time Limit (seconds): 1800

Completion Status: COMPLETED

Started at: 04/10/2012 19:27:31

Completed at: 04/10/2012 19:27:32

Schema Name: SYSTEM

SQL ID: 5314t67qk27hg

SQL Text: select * from t where object_id=100

FINDINGS SECTION (2 findings)

1-Statistics Finding

-

Table "SYSTEM". "T" was not analyzed

Recommendation

-

-Consider collecting optimizer statistics for this table.

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

'Turing, 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.

2-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: 96.86%)

-

-Consider running the Access Advisor to improve the physical schema design

Or creating the recommended index.

Create index SYSTEM.IDX$$_003C0001 on SYSTEM.T ("OBJECT_ID", "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: 1601196873

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

| | 0 | SELECT STATEMENT | | 4 | 316 | 96 (2) | 00:00:02 |

| | * 1 | TABLE ACCESS FULL | T | 4 | 316 | 96 (2) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 100)

2-Using New Indices

-

Plan hash value: 2426277634

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

| | 0 | SELECT STATEMENT | | 1 | 79 | 3 (0) | 00:00:01 |

| | * 1 | INDEX RANGE SCAN | IDX$$_003C0001 | 1 | 79 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("OBJECT_ID" = 100)

Execution Plan

Plan hash value: 1388734953

-

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

-

| | 0 | SELECT STATEMENT | | 1 | 2 (0) | 00:00:01 |

| | 1 | FAST DUAL | | 1 | 2 (0) | 00:00:01 |

-

Statistics

1596 recursive calls

970 db block gets

940 consistent gets

1 physical reads

572 redo size

22597 bytes sent via SQL*Net to client

14289 bytes received via SQL*Net from client

100 SQL*Net roundtrips to/from client

28 sorts (memory)

0 sorts (disk)

1 rows processed

Perform analysis: SQL > analyze table t compute statistics

Table analyzed.

SQL > create index SYSTEM.IDX$$_003C0001 on SYSTEM.T ("OBJECT_ID", "OBJECT_NAME")

Index created.

Delete Task:

Exec dbms_sqltune.drop_tuning_task ('sql_tuning_test')

Rebuild the task and perform it before you can view the new report again. This is because the records are put in the data dictionary, so it's the only way.

After reading the above, do you have any further understanding of how to use ORACLE SQL PROFILE? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.