Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to create SQL Profile by hand". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Create SQL profile by hand

For the 10G version of ORACLE, you can obtain the hint used by SQL Profile by looking at sys.sqlprof$ and sys.sqlprof$attr, but the base tables of these two data dictionaries are no longer valid after 11g. You need to check the hint used by SQL Profile by viewing sys.sqlobj$data and sys.sqlobj$.

Let's move on to the previous section and take a look at the hint used by the SQL Profile created with SQL Tuning Advisor. (11G version)

SQL > SELECT extractValue (value (h),'.') AS hint

2 FROM sys.sqlobj$data od, sys.sqlobj$ so

3 table (xmlsequence (extract (xmltype (od.comp_data),'/ outline_data/hint')) h

4 WHERE so.name = 'SYS_SQLPROF_01479094feeb0003'

5 AND so.signature = od.signature

6 AND so.category = od.category

7 AND so.obj_type = od.obj_type

8 AND so.plan_id = od.plan_id

Hint

OPT_ESTIMATE (@ "SEL$1", TABLE, "TEST" @ "SEL$1", SCALE_ROWS=0.004)

OPT_ESTIMATE (@ "SEL$1", INDEX_SCAN, "TEST" @ "SEL$1", "T_IND", SCALE_ROWS=0.004)

OPTIMIZER_FEATURES_ENABLE (default)

These hint are not the hint we use every day, and most of them start with OPT_ESTIMATE. For example, OPT_ESTIMATE (@ "SEL$1", TABLE, "TEST" @ "SEL$1", SCALE_ROWS=0.004) represents a modification of the cardinality returned by the table test after predicate filtering to the cardinality of the original evaluation multiplied by 0.004, which is reduced by 250x: the cardinality has shrunk from 25000 to 100. Following the OPT_ESTIMATE hint, the reduced cardinality is very accurate, and because OPT_ESTIMATE tells the optimizer very accurate cardinality information, the optimizer chooses index scan when evaluating the execution plan again.

As we can see, SQL Profile does not explicitly tell the optimizer to use index scanning, but just tells it how to correct the optimizer's original evaluation to get better cardinality information. However, over time, these prompts may become obsolete and eventually no longer valid, so SQL that uses SQL Profile may also encounter changes in the execution plan, failing to lock the execution plan. How to use SQL Profile to lock down the execution plan is described later in this chapter.

What hint might be included in n Note:SQL Profile? Here is an explanation for some of the common hint in SQL Profile.

1) OPT_ESTIMATE (@ SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)

Returns the cardinality of the table 10 times the estimate

2) OPT_ESTIMATE (@ SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1)

Returns the cardinality of the estimated index of 1/10

3) OPT_ESTIMATE (@ SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1), SCALE_ROWS=4.2)

When test1,test2 does join, it returns 4.2 times the estimated cardinality.

4) TABLE_STATS ("HR", "EMPLOYEES", scale, blocks=10, rows=107)

Provide statistics for tables, such as rows and blocks

5) COLUMN_STATS ("HR", "EMPLOYEES", "EMPLOYEE_ID", scale,length=3 DISTINCT=107 nulls=0 min=100 max=207)

Provide statistics for the columns on the table: null, maximum, minimum, etc.

6) INDEX_STATS ("HR", "EMPLOYEES", "EMP_IDX", scale, blocks=5, rows=107)

Provide statistics for indexes, such as number of index blocks, number of index entries

7) ALL_ROWS

Set the optimizer mode to ALL_ROWS

8) IGNORE_OPTIM_EMBEDDED_hintS

Ignore hint embedded in SQL

Although ORACLE officially only offers to create SQL Profile through SQL Tuning Advisor, some ORACLE enthusiasts have slowly discovered the underlying mechanism of SQL Tuning Advisor and discovered that SQL Tuning Advisor actually created SQL Profile by calling the import_sql_profile of the dbms_sqltune package. Through the import_sql_profile process, you can create the desired SQL Profile for any SQL. Let's see how import_sql_profile works.

PROCEDURE IMPORT_SQL_PROFILE

Argument Name Type In/Out Default?

SQL_TEXT CLOB IN

PROFILE SQLPROF_ATTR IN

NAME VARCHAR2 IN DEFAULT

DESCRIPTION VARCHAR2 IN DEFAULT

CATEGORY VARCHAR2 IN DEFAULT

VALIDATE BOOLEAN IN DEFAULT

REPLACE BOOLEAN IN DEFAULT

FORCE_MATCH BOOLEAN IN DEFAULT

To create a SQL Profile using IMPORT_SQL_PROFILE, you need to provide some parameters. SQL_TEXT refers to the text of the SQL statement. We can get the complete text information of the SQL statement from the sql_fulltext of v$sqlarea. PROFILE refers to the hint collection that needs to be bound for the SQL text. Name is the name of SQL Profile, DESCRIPTION is the description of SQL Profile, and CATEGORY is the class information to which SQL Profile belongs. The default is default,VALIDATE represents whether the SQL Profile created is valid. The default is true, and REPLACE represents whether to replace the previously existing SQL Profile,FORCE_MATCH represents how to standardize the text to generate signatures. The default is false. The meaning of FORCE_MATCH is explained in detail in the section on text standardization and signature in this chapter. Let's create a SQL Profile by hand:

SQL > exec dbms_sqltune.drop_sql_profile ('profile_c37q7z5qjnwwf_dwrose')

PL/SQL procedure successfully completed.

SQL > declare

2 l_profile_name varchar2 (30)

3 cl_sql_text clob

4 begin

5 select sql_fulltext

6 into cl_sql_text

7 from v$sqlarea

8 where sql_id = 'c37q7z5qjnwwf'

nine

10 select 'profile_' | |' c37q7z5qjnwwf' | |'_ dwrose'

11 into l_profile_name

12 from dual

13 dbms_sqltune.import_sql_profile (sql_text = > cl_sql_text)

14 profile = >

15 sqlprof_attr ('INDEX_RS_ASC (TEST T_IND)')

16 category = >''

17 name = > l_profile_name

18 force_match = > FALSE)

19 end

20 /

PL/SQL procedure successfully completed.

We first delete the SQL Profile created by SQL Tuning Advisor through the drop_sql_profile process of the dbms_sqltune package, and then manually create a SQL Profile through import_sql_profile, and we use our common hint INDEX_RS_ASC (TEST T_IND) instead of SQL Profile's default hint that starts with OPT_ESTIMATE. The above code has successfully created a SQL Profile, let's see if using regular hint will work.

SQL > select count (name) from test where status='Inactive'

COUNT (NAME)

-

one hundred

1 row selected.

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID c37q7z5qjnwwf, child number 1

-

Select count (name) from test where status='Inactive'

Plan hash value: 1950795681

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

| | 0 | SELECT STATEMENT | 51 (100) | | |

| | 1 | SORT AGGREGATE | | 1 | 21 | |

| | * 2 | TABLE ACCESS FULL | TEST | 25000 | 512K | 51 (2) | 00:00:01 |

Note

-

-SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement

SQL > SELECT extractValue (value (h),'.') AS hint

2 FROM sys.sqlobj$data od, sys.sqlobj$ so

3 table (xmlsequence (extract (xmltype (od.comp_data),'/ outline_data/hint')) h

4 WHERE so.name = 'profile_c37q7z5qjnwwf_dwrose'

5 AND so.signature = od.signature

6 AND so.category = od.category

7 AND so.obj_type = od.obj_type

8 AND so.plan_id = od.plan_id

Hint

INDEX_RS_ASC (TEST T_IND)

Although the output Note section of the execution plan shows that SQL Profile has been used, the execution plan has not been changed as we expected, it is still a full table scan, and looking at the base table storing the hint also shows that the index scanned hint has been bound to this SQL, so what's the problem?

This is because SQL Profile is very picky about hint. Hint accepted in SQL Profile needs to provide Query Block Name (hint for initializing parameter classes does not need to provide Query Block Name), otherwise the optimizer will ignore these hint. Let's reset the Hints of SQL Profile and add Query Block Name to the Hints. (refer to the Query Block Name section of this chapter for Query Block Name related knowledge)

SQL > declare

2 l_profile_name varchar2 (30)

3 cl_sql_text clob

4 begin

5 select sql_fulltext

6 into cl_sql_text

7 from v$sqlarea

8 where sql_id = 'c37q7z5qjnwwf'

nine

10 select 'profile_' | |' c37q7z5qjnwwf' | |'_ dwrose'

11 into l_profile_name

12 from dual

13 dbms_sqltune.import_sql_profile (sql_text = > cl_sql_text)

14 profile = >

15 sqlprof_attr ('INDEX_RS_ASC (@ SEL$1 TEST@SEL$1 T_IND)')

16 category = >''

17 name = > l_profile_name

18 force_match = > FALSE)

19 end

20 /

PL/SQL procedure successfully completed.

SQL > select count (name) from test where status='Inactive'

COUNT (NAME)

-

one hundred

1 row selected.

SQL > select * from table (dbms_xplan.display_cursor)

PLAN_TABLE_OUTPUT

SQL_ID c37q7z5qjnwwf, child number 1

-

Select count (name) from test where status='Inactive'

Plan hash value: 4130896540

-

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

-

| | 0 | SELECT STATEMENT | 218,100 | | |

| | 1 | SORT AGGREGATE | | 1 | 21 | |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEST | 25000 | 512K | 218 (1) | 00:00:03 |

| | * 3 | INDEX RANGE SCAN | T_IND | 25000 | | 63 (0) | 00:00:01 |

-

Note

-

-SQL Profile profile_c37q7z5qjnwwf_dwrose used for this statement

This time the hint works. The Note part of the output of the execution plan can know that the created SQL Profile has worked, and the execution plan has been scanned by the index. It seems that SQL Profile can accept regular hint, but these hint should contain Query Block Name. If SQL Profile finds that the specified hint is invalid, it will simply ignore these hint, will not report any errors, and will not do any verification. Now that regular hint works on SQL Profile, we can also use SQL Profile to lock down the execution plan. From the output of the execution plan above, you can also see that because we used the regular hint, the cardinality information of the execution plan was not corrected, but the execution plan was forced to be modified to index scan through the violent hint of index_rs_asc.

This is the end of how to create SQL Profile by hand. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.