 |
|
|
dba_advisor_sqla_wk_stmts and
dba_advisor_sqlw_stmts The Oracle documentation contains details about the wealth of parameters that can be configured for SQLAccess Advisor analysis. These parameters range from resource limits to choices about where new indexes and materialized views may be placed:
PARAMETER_NAME PARAMETER_VALUE PARAMETER_ I ---------------------- -------------------------------- ---------- - ACTION_LIST UNUSED STRINGLIST N COMMENTED_FILTER_LIST /* OPT_DYN_SAMP */,/* DS_SVC */ STRINGLIST N CREATION_COST TRUE STRING N DAYS_TO_EXPIRE 30 NUMBER N DEF_EM_TEMPLATE SQLACCESS_EMTASK STRING N DEF_INDEX_OWNER UNUSED STRING N DEF_INDEX_TABLESPACE UNUSED STRING N DEF_MVIEW_OWNER UNUSED STRING N DEF_MVIEW_TABLESPACE UNUSED STRING N DEF_MVLOG_TABLESPACE UNUSED STRING N DML_VOLATILITY TRUE STRING N EM_DATA UNUSED STRING N END_SNAPSHOT UNUSED NUMBER N END_TIME UNUSED UNKNOWN N EVALUATION_ONLY FALSE STRING N EXECUTION_TYPE FULL STRING N FAST_REFRESH FALSE STRING N INDEX_NAME_TEMPLATE <TABLE>_IDX$$_<TASK_ID><SEQ> STRING N INSTANCE UNUSED NUMBER N INVALID_TABLE_LIST UNUSED TABLELIST N JOURNALING 4 NUMBER N MODE COMPREHENSIVE STRING N MODULE_LIST UNUSED STRINGLIST N MVIEW_NAME_TEMPLATE MV$$_<TASK_ID><SEQ> STRING N ORDER_LIST PRIORITY,OPTIMIZER_COST STRINGLIST N REFRESH_MODE ON_DEMAND STRING N REFRESH_TIME UNLIMITED NUMBER N REPORT_DATE_FORMAT DD/MM/YYYY HH24:MI STRING N REPORT_SECTIONS ALL STRING N SQL_LIMIT UNLIMITED NUMBER N START_SNAPSHOT UNUSED NUMBER N START_TIME UNUSED UNKNOWN N STORAGE_CHANGE UNLIMITED NUMBER N TARGET_OBJECTS UNUSED STRINGLIST N TIME_LIMIT UNLIMITED NUMBER N USERNAME_LIST UNUSED STRINGLIST N VALID_TABLE_LIST UNUSED TABLELIST N WORKLOAD_SCOPE PARTIAL STRING N
For example, the following call script sets the parameter mode to the value COMPREHENSIVE, we are directing the advisor to perform a detailed analysis:
SQL>exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1’, ‘MODE’, ' COMPREHENSIVE ');
In another option, the DBA can configure parameters for the SQL workload. To retrieve that parameter list for the workload created above, use the query below.
SELECT p.parameter_name, p.parameter_value, p.parameter_type FROM dba_advisor_sqlw_parameters p WHERE p.owner = 'DABR' AND p.workload_name = 'SQL_WRKLD_1';
The output for this query, showing all workload parameters, looks like the following:
PARAMETER_NAME PARAMETER_VALUE PARAMETER_ ---------------------- -------------------------------- ---------- ACTION_LIST UNUSED STRINGLIST COMMENTED_FILTER_LIST /* OPT_DYN_SAMP */,/* DS_SVC */ STRINGLIST DAYS_TO_EXPIRE 30 NUMBER DEF_DATA_SOURCE UNUSED STRING DEF_EM_TEMPLATE SQLACCESS_EMWKLD STRING END_SNAPSHOT UNUSED NUMBER END_TIME UNUSED UNKNOWN INSTANCE UNUSED NUMBER INVALID_TABLE_LIST UNUSED TABLELIST JOURNALING 4 NUMBER MODE COMPREHENSIVE STRING MODULE_LIST UNUSED STRINGLIST ORDER_LIST PRIORITY,OPTIMIZER_COST STRINGLIST REPORT_DATE_FORMAT DD/MM/YYYY HH24:MI STRING REPORT_SECTIONS ALL STRING SQL_LIMIT UNLIMITED NUMBER START_SNAPSHOT UNUSED NUMBER START_TIME UNUSED UNKNOWN TARGET_OBJECTS UNUSED STRINGLIST TIME_LIMIT UNLIMITED NUMBER USERNAME_LIST UNUSED STRINGLIST VALID_TABLE_LIST UNUSED TABLELIST
This shows that the dbms_advisor.set_sqlwkld_parameter procedure can be used to set all values for the required parameters. Remember, in-depth descriptions for every parameter are available in the Oracle documentation.
After configuring all necessary parameters, the DBA is ready to start the SQLAccess Advisor tool to search for recommendations. The dbms_advisor.execute_task is used to proceed to workload analysis:
exec dbms_advisor.execute_task (‘SQL_ACC_TASK1’);
During the analysis phase of work, the progress of the execution can be monitored using the dba_advisor_log view:
SELECT pct_completion_time FROM dba_advisor_log WHERE task_name = ‘SQL_ACC_TASK1’;
Once the analysis has completed successfully, the DBA can proceed to review and evaluate the recommendations and then implement the changes necessary for improving performance.
To view the recommendations summary portion of the SQLAccess Advisor analysis report, the query below can be used:
SELECT a.rec_id, a.precost, a.postcost, (a.precost -a.postcost )*100/a.precost "Percent Benefit", s.sql_text FROM dba_advisor_sqla_wk_stmts a, dba_advisor_sqlw_stmts s WHERE a.task_name = 'SQL_ACC_TASK1' AND a.workload_name = 'SQL_WRKLD_1' AND a.sql_id = s.sql_id;
The sample output of the above script might look like:
REC_ID PRECOST POSTCOST Percent Benefit SQL_TEXT ------ ------- -------- --------------- --------------------------------------------------- 3 3 2 33,3333333 select b.*,a.* from spv_alert_def a,spv_baselines b where a.dbid = :dbid and a.i
2 16 6 62,5 SELECT NVL(MAX(BEGIN_TIME), TO_DATE('01011900', 'DDMMYYYY')) FROM STATS$U 0 12 12 0 INSERT INTO STATS$SNAPSHOT ( SNAP_ID, DBID, INSTANCE_NUMBER , SNAP_TIME, 4 2 2 0 SELECT 1 FROM STATS$DATABASE_INSTANCE WHERE STARTUP_TIME = :B3 AND DBID 1 2 2 0 SELECT NVL(:B4 , :B3 ) , NVL(:B2 , :B1 ) FROM STATS$STATSPACK_PARAMETER 1 4 4 0 SELECT NVL(:B21 , SESSION_ID) , NVL(:B20 , SNAP_LEVEL) , NVL(:B19 , UCOM
It is important that the DBA know that the precost and postcost columns are expressed in terms of optimizer cost, and they show the estimated SQL cost before and after the recommended changes. In the above listing, it is clear that optimizer cost was reduced by one from three to two.
The following query returns the particular actions that are suggested in order for the DBA to implement each recommendation:
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command FROM dba_advisor_actions WHERE task_name = 'SQL_ACC_TASK1' ORDER BY rec_id, action_id;
The output of the above script typically looks like this sample:
REC_ID ACTION_ID COMMAND ---------- ---------- ---------------- 1 5 RETAIN INDEX 2 2 CREATE INDEX 3 1 CREATE INDEX 3 3 RETAIN INDEX 4 4 RETAIN INDEX
The final step is the generation of a SQL script that implements changes according to the automatically generated recommendations. The dbms_advisor.get_task_script should be used to produce the script. Keep in mind that the following parameters have influence on the resulting script: mview_name_templat, index_name_templat, def_index_owner, def_mview_owner, def_mview_tablespace, and def_index_tablespace . The SQL commands below can be used to save the SQL script to the SQL file:
CREATE DIRECTORY ADVISOR_RESULTS AS 'd:\temp\’; GRANT READ ON DIRECTORY ADVISOR_RESULTS TO PUBLIC; GRANT WRITE ON DIRECTORY ADVISOR_RESULTS TO PUBLIC; exec dbms_advisor.create_file (dbms_advisor.get_task_script ('SQL_ACC_TASK1'), - 'ADVISOR_RESULTS', 'advscript.sql');
The following is a sample result of the SQL script produced by the SQLAccess Advisor :
< dbms_advisor_mark.sql
set feedback 1 set linesize 80 set trimspool on set tab off set pagesize 60
whenever sqlerror CONTINUE
CREATE BITMAP INDEX "SPV"."_IDX$$_11A40001" ON "SPV"."SPV_BASELINES" ("AWR") COMPUTE STATISTICS;
CREATE INDEX "PERFSTAT"."_IDX$$_11A40002" ON "PERFSTAT"."STATS$UNDOSTAT" ("DBID","INSTANCE_NUMBER") COMPUTE STATISTICS;
/* RETAIN INDEX "SPV"."SPV_ALERT_DEF_PK" */
/* RETAIN INDEX "PERFSTAT"."STATS$DATABASE_INSTANCE_PK" */
/* RETAIN INDEX "PERFSTAT"."STATS$STATSPACK_PARAMETER_PK" */
whenever sqlerror EXIT SQL.SQLCODE
begin dbms_advisor.mark_recommendation('SQL_ACC_TASK1',1,'IMPLEMENTED'); dbms_advisor.mark_recommendation('SQL_ACC_TASK1',2,'IMPLEMENTED'); dbms_advisor.mark_recommendation('SQL_ACC_TASK1',3,'IMPLEMENTED'); dbms_advisor.mark_recommendation('SQL_ACC_TASK1',4,'IMPLEMENTED'); end; /
As we noted, the comprehensive analysis sessions can be very time consuming, but Oracle10g also offers quick analysis features that are less stressful on your production database.
|
|
|
