Oracle SQL Profile Combat

Part I: Profile concept

Oracle database 10g uses a new approach called SQL configuration files to compensate for the drawbacks of storage profiles,

DBAs can use SQL Tuning Advisor (STA) or SQL Access Advisor (SAA) to identify SQL statements that can get better performance.

These statements can be saved in an SQL tuning set, a awr snapshot, or saved in the current library cache, once the adjustment is identified

selected, these consultants begin to analyze captured statements for better performance, and then generate specialized statement extensions (called

Do the SQL configuration file) and rewrite the SQL statements to get better performance during execution.

Like a storage profile, a SQL profile provides the ability to use a better execution plan (if the execution

Row is available, SQL profiles can be executed in phases like a storage profile, or restricted to a specific session

Row The SQL configuration file, but most important improvements are beyond the storage profile,

There are at least two aspects:

(a) Self-aligning capabilities ensure that the SQL profile provides the best execution plan

(b) Ability to check that the SQL configuration file is no longer valid (therefore ADDM recommends that a new SQL configuration file be generated)

SQL profile is valid for a type statement:

SELECT statement;

UPDATE statement;

Insert statement (valid only if the SELECT clause is used);

Delete statement;

Create statement (valid only if the SELECT clause is used);

The merge statement (valid only as an update and insert operation).

In addition, you must use SQL profile to have system permissions such as create any SQL profiles, DROP any SQL profiles, and alter any SQL profiles.

Part II: Profile use

---1. Prepare test environment

CREATE TABLE T1

(

SID number NOT NULL,

Sname VARCHAR2 (10)

)

Tablespace test;

--Loop Import data

Declare

MAXRECORDS constant int:=200000;

I int: = 1;

Begin

For I in 1..maxrecords loop

INSERT into T1 values (i, ' Ocpyang ');

End Loop;

Dbms_output.put_line (' Successful data entry! ');

Commit

End

/

CREATE index index_01 on T1 (SID);

exec dbms_stats.gather_table_stats (' SCOTT ', ' T1 ', cascade => True);

---2. Execute a query

var v varchar2 (5);

Exec:v: = 10;

Set Autot trace;

Select/*+ no_index (T1 index_01)/* from T1 where sid<=:v;

Execution plan

----------------------------------------------------------

Plan Hash value:3617692013

--------------------------------------------------------------------------

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

--------------------------------------------------------------------------

| 0 |      SELECT STATEMENT | |   10000 |   126k| 139 (3) | 00:00:02 |

|* 1 | TABLE ACCESS full| T1 |   10000 |   126k| 139 (3) | 00:00:02 |

--------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("SID" <=to_number (: V))

Statistical information

----------------------------------------------------------

5 Recursive calls

5 db block gets

513 consistent gets

0 physical Reads

496 Redo Size

723 Bytes sent via sql*net to client

519 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Ten rows processed

Set Autotrace off;