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

Optimize it through case study-- SQL Profile

What is SQL Profile (summary)

SQL Profile plays an important role in performance optimization.

This is how SQL Profile is described in MOS:

SQL Profile is a new feature in 10g and is managed by Oracle Enterprise Manager as part of the automatic SQL tuning process. Except for OEM,SQL Profile, it can be managed through the DBMS_SQLTUNE package.

The query optimizer sometimes misestimates a SQL statement and produces a poor execution plan because of a lack of sufficient information. Automatic SQL tuning solves this problem through SQL profiling, and the auto-tuning optimizer generates a summary of the SQL statement, called SQL Profile. It consists of some auxiliary statistics for this statement, confirmed by sampling and local execution techniques, and adjusts the estimates in the execution plan if necessary. In SQL profiling, the auto-tuning optimizer can also set the appropriate optimizer parameters through the execution history of a SQL statement, such as changing the OPTIMIZER_MODE parameter from ALL_ROWS to FIRST_ROWS.

In other words, a SQL profile is an object that contains information that helps the query optimizer find an efficient execution plan for a particular SQL statement. This information includes the execution environment, object statistics, and corrections to the evaluation of the query optimizer. One of its biggest advantages is that it affects the decision of the query optimizer without modifying the SQL statement and the session execution environment. (art of Oracle performance diagnosis)

SQL Profile does not contain information about a single execution plan, and it is important to note that SQL Profile does not fix the execution plan of an SQL statement. As the data in the table grows or the index is created or deleted, the execution plan that uses the same SQL Profile may change, and the information stored in the SQL Profile will continue to work. However, after a long time, its information may become obsolete and need to be regenerated.

The scope of SQL Profile is controlled by the CATEGORY attribute, which determines which user sessions can apply this profile. You can view this property from the CATEGORY field in DBA_SQL_PROFILES. By default, all profiles are created as DEFAULT categories, which means that this profile can be used by all user sessions with the SQLTUNE_CATEGORY initialization parameter DEFAULT. You can modify this property, such as changing it to SCO, so that only user sessions with the SQLTUNE_GATEGORY parameter SCO can use it. With this feature, you can test a SQL Profile in a restricted environment.

16:42:03 SYS@ prod > desc dba_sql_profiles Name Null? Type NAME NOT NULL VARCHAR2 (30) CATEGORY NOT NULL VARCHAR2 (30) SIGNATURE NOT NULL NUMBER SQL_TEXT NOT NULL CLOB CREATED NOT NULL TIMESTAMP (6) LAST_MODIFIED TIMESTAMP (6) DESCRIPTION VARCHAR2 (500) TYPE VARCHAR2 (7) STATUS VARCHAR2 (8) FORCE_MATCHING VARCHAR2 (3) TASK_ID NUMBER TASK_EXEC_NAME VARCHAR2 (30) TASK_OBJ_ID NUMBER TASK_FND_ ID NUMBER TASK_REC_ID NUMBER 16:50:43 SYS@ prod > select name CATEGORY,sql_text,status from dba_sql_profiles No rows selected

SQL Profile can be used in expressions such as SELECT; UPDATE; INSERT (where the SELECT clause is included), DELETE; CREATE TABLE (where the SELECT clause is included), and MERGE (the UPDATE or INSERT operation).

II. Management of SQL Profile

The steps for Oracle to execute the SQL statement are as follows:

1. The user sends the SQL statement to be executed to the SQL engine

2. The SQL engine requires the query optimizer to provide an execution plan

3. Query optimization obtains system statistics, object statistics of objects referenced by SQL statements, SQL profiles, and initialization parameters that make up the execution environment.

4. The query optimizer parses the SQL statement and generates an execution plan

5. Pass the execution plan to the SQL engine

6. SQL engine executes SQL statements

SQL Profile can be managed by OEM or manually through the DBMS_SQLTUNE package.

(1) the steps for using OEM are as follows:

1. On the Performance page, click Top Activity. The Top Activity page appears

two。 Under Top SQL, click the SQL ID link of the SQL expression that is using SQL Profile, and a SQL Details page will appear

3. Click the Plan Control tab, and a list of SQL profile will be displayed under SQL Profiles and Outlines.

4. To select the SQL Profile you want to manage, you can do the following: enable or disable, remove

5. A confirmation page will appear. Click Yes to continue and No to cancel

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.