Oracle SQL Profile

This article describes how to use SQL profile to bind the correct execution plan. 10gR2 is quite practical. Of course, baseline can be used for 11 GB,

Of course, I think both of them are quite good.

We may often encounter some big tables, such as the top T, wrong execution plan, such as full table scan, the system is basically in the Hang state, then the collection of statistical information may be slow at this time, even if the sampling ratio is small. Therefore, using profile and baseline is a good choice.

1. Create a test environment

SQL> create table test (n number );
Table created.

Declare
Begin
For I in 10000
Loop
Insert into test values (I );
Commit;
End loop;
End;
/
PL/SQL procedure successfully completed.

Create index test_idx on test (n );
Index created.

SQL> exec dbms_stats.gather_table_stats ('Leo ', 'test ');
PL/SQL procedure successfully completed.

Ii. Test SQL

Var v varchar2 (5 );
Exec: v: = 1;
Set autotrace on
SQL> select/* + no_index (test test_idx) */* from test where n =: v;
N
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 4 | 7 (0) | 00:00:01 |
| * 1 | table access full | TEST | 1 | 4 | 7 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("N" = TO_NUMBER (: V ))
Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
25 consistent gets
0 physical reads
0 redo size
415 bytes sent via SQL * Net to client
415 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

3. Use profile to fix and optimize SQL statements
Of course, the above is a full table scan, which is obviously not optimal. The following uses profile to fix and optimize SQL statements:

1. Create tuning task

SQL> declare
2 my_task_name VARCHAR2 (30 );
3 my_sqltext CLOB;
4 begin
5 my_sqltext: = 'select/* + no_index (test test_idx) */* from test where n =: V ';
6 my_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
7 SQL _text => my_sqltext,
8 user_name => 'Leo ', -- Username for whom the statement is to be tuned
9 scope => 'comphension ',
10 time_limit => 60,
11 task_name => 'my _ SQL _tuning_task_5 ',
12 description => 'Task to tune a query on a specified table ');
13 end;
14/
PL/SQL procedure successfully completed.

/* + As shown in the preceding figure, it is easier to use SQL text or SQL _id in the actual environment. The following describes how to use SQL _id */

1. Select SQL _id from v $ SQL where upper (SQL _text) like upper ('% select/* + no_index (test test_idx) % ');
2. SQL _id
3. ------
4. Brg4wn3kfzp34
5. SQL> declare
6. 2 my_task_name VARCHAR2 (30 );
7. 3 my_sqltext CLOB;
8. 4 my_sqlid varchar2 (50 );
9. 5 my_plan_hash_value varchar2 (50 );
10. 6 begin
11. 7 my_sqlid: = 'brg4wn3kfzp34 '; -- SQL _id of the preceding SQL statement
12. 8 my_plan_hash_value: = '000000'; -- The hash_value of the preceding SQL statement
13. 9 my_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
14. 10 SQL _id => my_sqlid,
15. 11 plan_hash_value => my_plan_hash_value,
16. 12 scope => 'comphension ',
17. 13 time_limit => 60,
18. 14 task_name => 'my _ SQL _tuning_task_5 ',
19. 15 description => 'Task to tune a query on a specified table ');
20. 16 end;
21. 17/

2. execute tuning task

SQL> begin
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'my _ SQL _tuning_task_5 ');
3 end;
4/
PL/SQL procedure successfully completed.

3. report tuning task

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('My _ SQL _tuning_task_5 ') from DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name: my_ SQL _tuning_task_5
Tuning Task Owner: SYS
Workload Type: Single SQL Statement
Execution Count: 2
Current Execution: EXEC_91
Execution Type: TUNE SQL
Scope: COMPREHENSIVE
Time Limit (seconds): 60
Completion Status: COMPLETED
Started at: 20:45:42
Completed at: 07/19/2012 20:45:43
-------------------------------------------------------------------------------
Schema Name: LEO
SQL ID: brg4wn3kfzp34
SQL Text: select/* + no_index (test test_idx) */* from test where n =: v
-------------------------------------------------------------------------------
Findings section (1 finding)
-------------------------------------------------------------------------------
1-SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 95.02%)
------------------------------------------
-Consider accepting the recommended SQL profile.
Execute dbms_sqltune.accept_ SQL _profile (task_name =>
'My _ SQL _tuning_task_5 ', task_owner => 'sys', replace => TRUE );
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
And measuring their respective execution statistics. A plan may have been
Only partially executed if the other cocould be run to completion in less time.
Original Plan With SQL Profile % Improved
---------------------------------------
Completion Status: COMPLETE
Elapsed Time (us): 642 168 73.83%
CPU Time (us): 1200 0 100%
User I/O Time (us): 0 0
Buffer Gets: 20 1 95%
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. The original plan was first executed to warm the buffer cache.
2. Statistics for original plan were averaged over next 9 executions.
3. The SQL profile plan was first executed to warm the buffer cache.
4. Statistics for the SQL profile plan were averaged over next 9 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1-Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 1 | 4 | 7 (0) | 00:00:01 |
| * 1 | table access full | TEST | 1 | 4 | 7 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("N" = TO_NUMBER (: V ))

2-Using SQL Profile
--------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-----------------------------------------------------------------------------
| 0 | select statement | 1 | 4 | 1 (0) | 00:00:01 |
| * 1 | index range scan | TEST_IDX | 1 | 4 | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("N" = TO_NUMBER (: V ))
-------------------------------------------------------------------------------

We can see the above information. Here we acctpt this profile:

1 2 Next Page