Create Oracle SQL Profile For Tuning


1024×768


Normal
14
false
false
false
IT
X-NONE
X-NONE


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Tabella normale”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans.

Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query.

If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile.

The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

 

Here’s the set of SQL statement you can use to trace the execution time of ACTIVE running SQL query that you wish to tune.

 

— Get The SQL_ID From Active Session SQL

SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = ‘ACTIVE’

AND a.spid = ‘11696’

ORDER BY a.spid, c.piece

 

— Trace SQL Query Execution Time Using SQL ID

SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,

(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,

buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text

FROM v$sql s  WHERE s.sql_id=’4n01r8z5hgfru’

 

— Append the /* + gather_table_statistics */ hint into SQL Statement and execute

SELECT /* + gather_table_statistics */ sysdate … (SQL Statement)

 

Note: The execution OF SQL statement WITH hint /* + gather_table_statistics */ will subsequently provide the detail plan execution TIME IN the NEXT query below.

 — Get The Detail Explain Plan Using SQL ID

SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))

 

— Get The History Of Explain Plan Execution Using SQL ID In AWR report

SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr(‘dtdqt19kfv6yx’))

 

— Use Longops To Check The Estimation Runtime

SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,

last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,

elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,

round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME

FROM v$session_longops

WHERE sofar<>totalwork

AND time_remaining <> ‘0’

 

Creating a SQL Profile Using DBMS_SQLTUNE

 

SQL Profiles (commonly known as the SQL Tuning Advisor) were introduced in Oracle 10g. The feature tunes queries by gathering information about data distribution, relations between the columns and joined tables and more useful optimizer information.

 

It provides recommendations which, when implemented are associated to the query and are used by the optimizer at parse time.

 

The following will create a SQL Profile:

 

SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => ‘FULL QUERY TEXT’,

profile => sqlprof_attr(‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’),

name => ‘PROFILE NAME’, force_match => TRUE/FALSE);

 

‘FULL QUERY TEXT’ –  The value can be obtained from the the SQL_FULLTEXT column in table GV$SQLAREA or DBA_HIST_SQLTEXT.

 

SQL> SELECT SQL_FULLTEXT FROM GV$SQLAREA WHERE sql_id = ‘4n01r8z5hgfru’

 

SQL> SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE sql_id = ‘4n01r8z5hgfru’

 

‘HINT SPECIFICATION WITH FULL OBJECT ALIASES The hint specification can be obtained from the table DBA_HIST_SQL_PLAN. For example:

 

SQL> SELECT extractvalue(VALUE(d), ‘/hint’) AS outline_hints

FROM xmltable(‘/*/outline_data/hint’

passing (SELECT xmltype(other_xml) AS xmlval

FROM dba_hist_sql_plan WHERE sql_id = ‘4n01r8z5hgfru’ AND plan_hash_value = ‘82930460’

AND other_xml IS NOT NULL)) d;

 

Or you can get it from DBMS_XPLAN.DISPLAY_AWR function as below.

 

SQL> SELECT plan_table_output FROM TABLE

(dbms_xplan.display_awr(‘SQL_ID’,’PLAN_HASH’,NULL,’OUTLINE’))

You can also generate the Trace File 10053 and look for the hint specification between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA. Download SQLTXPLAIN.sql from Oracle Metalink and run it to get Trace File 10053.

 

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3’)

      OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)

      OPT_PARAM(‘_fast_full_scan_enabled’ ‘false’)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$335DD26A”)

      MERGE(@”SEL$3″)

      OUTLINE_LEAF(@”SEL$7286615E”)

      MERGE(@”SEL$5″)

      OUTLINE_LEAF(@”SEL$1″)

      ……

    END_OUTLINE_DATA

  */

 

FORCE_MATCH is really the main reason for using SQL Profiles, when set to TRUE it will ignore literals with exact queries and implement the profile on them (just like what cursor_sharing=force does to the entire DB). For Example: –

 

When force match is set to TRUE, a.segment1 = 1234 will become a.segment1 = :b1

 

To create a SQL Profile a user must have the following: ADVISOR role, create any sql profile privilege, alter any sql profile privilege, drop any sql profile privilege and execute priviliege on DBMS_SQLTUNE.

 

SQL> GRANT EXECUTE ON SYS.DBMS_SQLTUNE TO <user>;

SQL> GRANT ADVISOR TO <user>;

SQL> GRANT CREATE ANY SQL PROFILE TO <user>;

SQL> GRANT ALTER ANY SQL PROFILE TO <user>;

SQL> GRANT DROP ANY SQL PROFILE TO <user>;

 

List of example to create Oracle SQL Profile:

 

SQL> DBMS_SQLTUNE.IMPORT_SQL_PROFILE

(

   sql_text => ‘select * from emp’,

   profile => sqlprof_attr(‘ALL_ROWS’,’IGNORE_OPTIM_EMBEDDED_HINTS’, …… ),

   category => ‘DEFAULT’,

   name => ‘change_emp’,

   force_match => TRUE

);

 

DECLARE

cl_sql_text CLOB;

BEGIN

— SELECT sql_fulltext INTO cl_sql_text from gv$sqlarea where sql_id=’4n01r8z5hgfru’;

SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = ‘4n01r8z5hgfru’;

 

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

sql_text => cl_sql_text,

profile => sqlprof_attr(‘HINT SPECIFICATION WITH FULL OBJECT ALIASES’),

name => ‘PROFILE NAME’,

force_match => TRUE);

END;

/

 

DECLARE

cl_sql_text CLOB;

hint_spec sys.sqlprof_attr;

BEGIN

–SELECT sql_fulltext INTO cl_sql_text FROM gv$sqlarea WHERE sql_id=’gtwyx63711jp1′;

SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = ‘gtwyx63711jp1’;

 

SELECT

extractvalue(VALUE(d), ‘/hint’) AS outline_hints

BULK COLLECT

INTO

hint_spec

FROM

xmltable(‘/*/outline_data/hint’

passing (

SELECT

xmltype(other_xml) AS xmlval

FROM

dba_hist_sql_plan

WHERE

sql_id = ‘gtwyx63711jp1’

AND plan_hash_value = ‘82930460’

AND other_xml IS NOT NULL

)

) d;

 

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(

sql_text => cl_sql_text,

profile => hint_spec,

name => ‘PROFILE NAME’,

force_match => TRUE);

END;

/

 

Note: You may use the table v$sql_plan if there is no outline hints available in dba_hist_sql_plan.

 

Once you have finish creating the Oracle SQL Profile, check on the database system for the new SQL Profile.

 

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

 

SQL> SELECT sql_attr.attr_val outline_hints

FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr

WHERE sql_profiles.signature = sql_attr.signature

AND sql_profiles.name = ‘PROFILE NAME’

ORDER BY sql_attr.attr# ASC;

 

Dropping a SQL Profile Using DBMS_SQLTUNE

 

You can drop a SQL Profile with the DROP_SQL_PROFILE procedure. For example:

 

BEGIN

  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => ‘PROFILE NAME’);

END;

/

 

In this example, my_sql_profile is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE is accepted.

Potrebbero interessarti anche...