SQL profile
A SQL profile is a database object that contains auxiliary statistics specific to a SQL statement.
Conceptually, a SQL profile is to a SQL statement what object-level statistics are to a table or index. SQL profiles are created when a DBA invokes SQL Tuning Advisor. The advisor compares the optimizer estimate with values obtained by executing fragments of the statement on a data sample, When significant variances are found, SQL Tuning Advisor bundles corrective actions together in a SQL profile, and then recommends its acceptance. SQL profiles do not tie the optimizer to a specific plan or subplan. A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile is stored in an internal format in the data dictionary. The user interface is theDBA_SQL_PROFILESdictionary view. A SQL profile contains, among other statistics, a set of cardinality adjustments. SQL Profiles and Execution Plans The SQL profile contains supplemental statistics for the entire statement, not individual plans. The profile does not itself determine a specific plan. Internally, a SQL profile is implemented using hints that address different types of problems,These hints do not specify any particular plan. Rather, the hints correct errors in the optimizer estimation algorithm that lead to suboptimal plans. For example, a profile may use theTABLE_STATShint to set object statistics for tables when the statistics are missing or stale. Steps to tune Queries with help of SQL Profiles When SQL TUNING Advisor executed manually or automatically ,it may recommend to accept SQL PROFILES for particular SQL query.
Below mentioned is steps to get recommendation by SQL TUNING Advisor .
Step 1=> Creating Tuning Tasks for SQL_ID.
SQL> set long 1000000000
Col recommendations for a200
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '4fz1vtn0w8Aak',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 180,
task_name => 'tuning_task_for_4fz1vtn0w8aak',
description => 'Tuning task for statement 4fz1vtn0w8aak');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
PL/SQL procedure successfully completed.
Step 2=> Executing Tuning Tasks for SQL_ID.
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tuning_task_for_4fz1vtn0w8aak');
PL/SQL procedure successfully completed.
Step 3=> Get details recommendationa of Tuning Tasks for SQL_ID.
SET LONG 10000000;
SET PAGESIZE 100000000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('tuning_task_for_4fz1vtn0w8aak') AS recommendations FROM dual;
SET PAGESIZE 24
PL/SQL procedure successfully completed.
RECOMMENDATIONS
--------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_task_for_4fz1vtn0w8aak
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 180
Completion Status : COMPLETED
Started at : 01/16/2022 20:53:09
Completed at : 01/16/2022 20:53:10
-------------------------------------------------------------------------------
Schema Name: HR
SQL ID : 4fz1vtn0w8aak
SQL Text : select distinct DEPARTMENT_NAME from DEPT join EMPS using(DEP_ID)
where DEPT_NAME like '%ing' and SALARY<90000
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
RECOMMENDATIONS
-------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 33.33%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>'tuning_task_for_4fz1vtn0w8aak', task_owner => 'SYS', replace =>TRUE);
Step 4=> Accepting SQL_PROFILE recommendationa of Tuning Tasks for SQL_ID.
execute dbms_sqltune.accept_sql_profile(task_name =>'tuning_task_for_4fz1vtn0w8aak', task_owner => 'SYS', replace =>TRUE);
PL/SQL procedure successfully completed.
Step 5=> Checking Detail for SQL_PROFILE
SQL> select name,status,force_matching from dba_sql_profiles where task_id in ( select task_id from DBA_ADVISOR_TASKS where task_name ='tuning_task_for_4fz1vtn0w8aak');
NAME STATUS FOR
----------------------------------------------------------------------- ---
SYS_SQLPROF_017e63ab69140000 ENABLED NO
SQL>
Step 6=> Checking Detail for SQL_PROFILE while running Query again.
SQL> select * from table( dbms_xplan.display_cursor(format=>'rowstats last ') );
Plan hash value: 2473492969
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |
| 1 | HASH UNIQUE | | 1 | 1 | 0 |
| 2 | NESTED LOOPS | | 1 | 1 | 0 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 1 | 1 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |
|* 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 0 |
--------------------------------------------------------------------------------
Note
-----
- SQL profile SYS_SQLPROF_017e63ab69140000 used for this statement
- this is an adaptive plan
Step 7=> Dropping SQL_PROFILE.
SQL> begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_017e63ab69140000');
end;
/
PL/SQL procedure successfully completed.
Basic Operations with SQL PROFILES
1=> get list of sql profiles
SQL> SELECT NAME,type, SQL_TEXT, CATEGORY,TASK_ID ,TASK_EXEC_NAME,STATUS,CREATED FROM DBA_SQL_PROFILES;
NAME TYPE SQL_TEXT CATEGORY TASK_ID TASK_EXEC_NAME STATUS CREATED
------------------------------ ------- ---------------------------------------- -------------------- ---------- -------------
SYS_SQLPROF_017ee45b73920000 MANUAL select distinct DEPARTMENT_NAME from DEP DEFAULT 602 EXEC_643 DISABLED 10-FEB-22 09.28.25.692585 PM
ARTMENTS join EMPLOYEES using(DEPARTMENT
SQL>
2=> Disabling SQL Profile
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_017ee45b73920000',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
SQL> SELECT NAME,type, SQL_TEXT, CATEGORY,TASK_ID ,TASK_EXEC_NAME,STATUS,CREATED FROM DBA_SQL_PROFILES;
NAME TYPE SQL_TEXT CATEGORY TASK_ID TASK_EXEC_NAME STATUS CREATED
------------------------------ ------- ---------------------------------------- -------------------- ---------- -------------------
SYS_SQLPROF_017ee45b73920000 MANUAL select distinct DEPARTMENT_NAME from DEP DEFAULT 602 EXEC_643 DISABLED 10-FEB-22 09.28.25.692585 PM
ARTMENTS join EMPLOYEES using(DEPARTMENT
SQL>
3=> Check what's is in SQL PROFILE
SELECT CREATED, PROFILE_NAME, SQL_TEXT,
extractvalue(VALUE(hint), '.') AS hint
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p, TABLE(xmlsequence(extract(xmltype(h.comp_data), '/outline_data/hint'))) hint
WHERE p.name = h.profile_name
SQL>
CREATED PROFILE_NAME SQL_TEXT HINT
------------------------------ ------------------------------ -------------------------------------------------- --------------------------------------------------
10-FEB-22 09.28.25.692585 PM SYS_SQLPROF_017ee45b73920000 select distinct DEPARTMENT_NAME from DEPARTMENTS j OPT_ESTIMATE(@"SEL$58A6D7F6", TABLE, "EMPLOYEES"@"
oin EMPLOYEES using(DEPARTMENT SEL$1", SCALE_ROWS=0.0511682243)
10-FEB-22 09.28.25.692585 PM SYS_SQLPROF_017ee45b73920000 select distinct DEPARTMENT_NAME from DEPARTMENTS j OPT_ESTIMATE(@"SEL$58A6D7F6", TABLE, "DEPARTMENTS"
oin EMPLOYEES using(DEPARTMENT @"SEL$1", SCALE_ROWS=5.185185185)
10-FEB-22 09.28.25.692585 PM SYS_SQLPROF_017ee45b73920000 select distinct DEPARTMENT_NAME from DEPARTMENTS j OPT_ESTIMATE(@"SEL$2CBA5DDD", TABLE, "EMPLOYEES"@"
oin EMPLOYEES using(DEPARTMENT SEL$1", SCALE_ROWS=0.0511682243)
10-FEB-22 09.28.25.692585 PM SYS_SQLPROF_017ee45b73920000 select distinct DEPARTMENT_NAME from DEPARTMENTS j OPT_ESTIMATE(@"SEL$6AE97DF7", TABLE, "DEPARTMENTS"
oin EMPLOYEES using(DEPARTMENT @"SEL$1", SCALE_ROWS=5.185185185)
10-FEB-22 09.28.25.692585 PM SYS_SQLPROF_017ee45b73920000 select distinct DEPARTMENT_NAME from DEPARTMENTS j OPTIMIZER_FEATURES_ENABLE(default)
oin EMPLOYEES using(DEPARTMENT
SQL>
OPT_ESTIMATE
In simple cases where few stats missing or Histogram missing and even adaptive query is not used ,Optimizeer may guess wrong estimates for number of Rows returning from
specific operations as per explain plan operation-id as below.Most cases are likley for Join operations.
SQL> select * from table(dbms_xplan.display_cursor(format=>'+alias'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID 68vk4d2atahad, child number 0
-------------------------------------
select distinct DEPARTMENT_NAME from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID) where DEPARTMENT_NAME like '%ing' and SALARY>20000
Plan hash value: 2473492969
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | HASH UNIQUE | | 1 | 23 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 23 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 23 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 7 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
|* 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
4 - SEL$58A6D7F6 / EMPLOYEES@SEL$1
5 - SEL$58A6D7F6 / DEPARTMENTS@SEL$1
6 - SEL$58A6D7F6 / DEPARTMENTS@SEL$1
So SQL Profile provide an approach to optimizer a source in terms of packaged Hints which is OPT_ESTIMATE.
OPT_ESTIMATE tells optimizer that the selection need to be corrrected upto the estimated value which is mentioned in block as like below,
/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */
So the OPT_ESTIMATE hint,inform the query optimizer that some of its estimations are wrong, and by how much (SCALE_ROWS) or what to use instead (ROWS).
Below example tells optimizer to correct estimate by about 0.05 rows for TABLE selection operation
OPT_ESTIMATE(@"SEL$58A6D7F6", TABLE, "EMPLOYEES"@"SEL$1", SCALE_ROWS=0.0511682243)
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444