Oracle Sql Access advisor for sqlid using Sql tuing Set



Advantages for optimizing query performance and system efficiency.

Enhancing Performance: 
Manually running the SQL Tuning Advisor can identify query bottlenecks and suggest improvements. This could result in faster execution times and improved user experience.

Query Optimization: 
This tool analyzes query execution plans and proposes alternate solutions based on factors such as indexes, stats, and database settings.

Cost Savings: 
Poorly performing queries that use up too many resources can be identified and optimized, leading to savings through better hardware utilization.

Proactive Maintenance: 
Check for potential performance issues before they hit your production environment by regularly analyzing and tuning queries.

Learning Opportunities: 
Find out about query optimization techniques and best practices by studying the SQL Tuning Advisor’s recommendations.



1) Creating SQL Tuning Sets

SQL Tuning Sets (STS) are essential for storing SQL statements along with their execution context. 
These sets serve as input for SQL Access Advisor. Creating an STS involves defining the set and loading it with relevant SQL statements from your workload.

EXEC DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'atfz4c63at1k1_STS01', description => 'Tuning set for sales queries');




2) Loading SQL Tuning Sets

Populate the STS with SQL statements from your workload. This step ensures that the SQL Access Advisor has accurate and comprehensive data for analysis.


==> FROM SHARED SQL AREA

DECLARE
 CUR SYS_REFCURSOR;
 BEGIN
 OPEN CUR FOR
 SELECT VALUE(P) FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''atfz4c63at1k1''')) p;
 DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> 'atfz4c63at1k1_STS01', POPULATE_CURSOR=>CUR);
 CLOSE CUR;
 END;
 /


==> FROM AWR

DECLARE
 CUR SYS_REFCURSOR;
 BEGIN
 OPEN CUR FOR
 SELECT VALUE(P) FROM TABLE ( DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP=>1939, END_SNAP=>1940, BASIC_FILTER=> 'sql_id = ''atfz4c63at1k1''', ATTRIBUTE_LIST=>'ALL' )) p;
 DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> 'atfz4c63at1k1_STS01', POPULATE_CURSOR=>CUR);
 CLOSE CUR;
 END;
 /



SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME => 'atfz4c63at1k1_STS01'));



==> Below is not part of creating sql baseline (  do not perform this for sql access) 
 
DECLARE
 MY_PLANS PLS_INTEGER;
 BEGIN
 MY_PLANS := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(SQLSET_NAME=>'atfz4c63at1k1_STS01', BASIC_FILTER=> 'PLAN_HASH_VALUE= ''3811430562''' );
 END;
 /
 

select * from dba_sql_plan_baselines;
select * from dba_sqlset WHERE name='atfz4c63at1k1_STS01'



3) Executing SQL Access Advisor Tasks

Define and execute tasks to generate recommendations. 
This process involves creating a task, setting its parameters, linking it to an STS, and executing it.


DECLARE
  task_id NUMBER;
  task_name VARCHAR2(255);
BEGIN
  task_name := 'my_sql_optimization_task';
  DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', task_id, task_name);
  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'ANALYSIS_SCOPE', 'ALL');
  DBMS_ADVISOR.ADD_STS_REF(task_name, 'SH', 'atfz4c63at1k1_STS01');

   /* Set Task Parameters */
  dbms_advisor.set_task_parameter(task_name,'ANALYSIS_SCOPE','ALL');
  dbms_advisor.set_task_parameter(task_name,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
  dbms_advisor.set_task_parameter(task_name,'DEF_PARTITION_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(task_name,'TIME_LIMIT',10000);
  dbms_advisor.set_task_parameter(task_name,'MODE','LIMITED');
  dbms_advisor.set_task_parameter(task_name,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
  dbms_advisor.set_task_parameter(task_name,'DML_VOLATILITY','TRUE');
  dbms_advisor.set_task_parameter(task_name,'WORKLOAD_SCOPE','PARTIAL');
  dbms_advisor.set_task_parameter(task_name,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(task_name,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(task_name,'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(task_name,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(task_name,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);
  dbms_advisor.set_task_parameter(task_name,'CREATION_COST','TRUE');
  dbms_advisor.set_task_parameter(task_name,'JOURNALING','4');
  dbms_advisor.set_task_parameter(task_name,'DAYS_TO_EXPIRE','30');

  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/





SELECT rec_id, rank, benefit FROM USER_ADVISOR_RECOMMENDATIONS WHERE task_name = 'my_sql_optimization_task' ORDER BY rank;




Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more