CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SQLPA
Version 11.1
 
General
Note: Provides a capacity to help users predict the impact of system environment changes on the performance of a SQL workload. The interface lets users build and then compare two different versions of the workload performance, analyze the differences between the two versions, and unmask the SQL statements that might be impacted by the changes.
Source {ORACLE_HOME}/rdbms/admin/dbmsspa.sql
First Available 11.1
Dependencies
dba_analysis_task dbms_sqltune_util1 prvt_sqladv_infra
dbms_advisor dbms_sqltune_util2 sqlset_row
dbms_sqltune prvt_advisor sql_binds
dbms_sqltune_internal prvt_smgutil  
Security Model Execute is granted to PUBLIC
 
CANCEL_ANALYSIS_TASK
Cancels the currently executing task analysis of one or more SQL statements dbms_sqlpa.cancel_analysis_task(task_name IN VARCHAR2);
TBD
 
CREATE_ANALYSIS_TASK

Creates an advisor task to process and analyze one or more SQL statements

Overload 1
dbms_sqlpa.create_analysis_task(
sql_text       IN CLOB,
bind_list      IN sql_binds := NULL,
parsing_schema IN VARCHAR2 := NULL,
task_name      IN VARCHAR2 := NULL,
description    IN VARCHAR2 := NULL)
RETURN VARCHAR2;
variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);

-- Sql text format
EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-00'')');

-- Sql id format (cursor cache)
EXEC :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sql_id => 'ay1m3ssvtrh24');

-- Workload repository format
exec :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
begin_snap => 1, 
end_snap => 2, 
sql_id => 'ay1m3ssvtrh24');

-- Sql tuning set format (first we need to load an STS, then analyze it)
EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( -
sqlset_name => 'my_workload', -
order_by => 'BUFFER_GETS', -
description => 'process workload ordered by buffer gets');

Overload 2
dbms_sqlpa.create_analysis_task(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name       IN VARCHAR2 := NULL,
description     IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD

Overload 3
dbms_sqlpa.create_analysis_task(
begin_snap      IN NUMBER,
end_snap        IN NUMBER,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
task_name       IN VARCHAR2 := NULL,
description     IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD

Overload 4
dbms_sqlpa.create_analysis_task(
sqlset_name  IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
order_by     IN VARCHAR2 := NULL,
top_sql      IN VARCHAR2 := NULL,
task_name    IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
DROP_ANALYSIS_TASK
Drops a SQL analysis task dbms_sqlpa.drop_analysis_task(task_name IN VARCHAR2);
TBD
 
EXECUTE_ANALYSIS_TASK

Executes a previously created analysis task

Overload 1
dbms_sqlpa.execute_analysis_task(
task_name        IN VARCHAR2,
execution_type   IN VARCHAR2 := 'test execute',
execution_name   IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc   IN VARCHAR2 := NULL)
RETURN VARCHAR2;
-- reset and re-execute a task
exec dbms_sqlpa.reset_analysis_task(:sts_task);

-- re-execute the task
exec dbms_sqlpa.execute_analysis_task(:sts_task);

Overload 2
dbms_sqlpa.execute_analysis_task(
task_name        IN VARCHAR2,
execution_type   IN VARCHAR2 := 'test execute',
execution_name   IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc   IN VARCHAR2 := NULL);
TBD
 
INTERRUPT_ANALYSIS_TASK

Interrupts the currently executing analysis task
dbms_sqlpa.interrupt_analysis_task(task_name IN VARCHAR2);
-- Interrupt the task
exec dbms_sqlpa.interrupt_analysis_task(:conc_task);

-- Once a task is interrupted, we can elect to reset it, resume it, or check
-- out its results and then decide. For this example we will just resume.

exec dbms_sqlpa.resume_analysis_task(:conc_task);
 
REMOTE_PROCESS_SQL (new in 11.1.0.7)

Undocumented function for internal usage only
dbms_sqlpa.remote_process_sql(
sql_text       IN  CLOB,
parsing_schema IN  VARCHAR2,
bind_data      IN  RAW,
bind_list      IN  VARRAY,
action         IN  VARCHAR2,
time_limit     IN  NUMBER,
new_phv        OUT NUMBER,
buffer_gets    OUT NUMBER,
cpu_time       OUT NUMBER,
elapsed_time   OUT NUMBER,
disk_reads     OUT NUMBER,
disk_writes    OUT NUMBER,
rows_processed OUT NUMBER,
optimizer_cost OUT NUMBER,
parse_time     OUT NUMBER,
err_code       OUT NUMBER,
err_mesg       OUT VARCHAR2,
flags          IN  BINARY_INTEGER,
extra_res      OUT VARCHAR2);
TBD
 
REPORT_ANALYSIS_TASK

Displays the results of an analysis task
dbms_sqlpa.report_analysis_task(
task_name      IN VARCHAR2,
type           IN VARCHAR2 := 'text',
level          IN VARCHAR2 := 'typical',
section        IN VARCHAR2 := 'summary',
object_id      IN NUMBER := NULL,
top_sql        IN NUMBER := 100,
task_owner     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL))
RETURN CLOB;
-- Get the whole report for the single statement case.
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:stmt_task) from dual;

-- Show me the summary for the sts case.
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY')
FROM DUAL;

-- Show me the findings for the statement I'm interested in.
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;
 
RESET_ANALYSIS_TASK
Resets the currently executing analysis task to its initial state dbms_sqlpa.reset_analysis_task(task_name IN VARCHAR2);
TBD
 
RESUME_ANALYSIS_TASK
Resumes a previously interrupted analysis task that was created to process a SQL tuning set dbms_sqlpa.resume_analysis_task(
task_name    IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL);
TBD
 
RESET_ANALYSIS_DEFAULT_PARAMETER

Sets the SQL analysis task parameter default value

Overload 1
dbms_sqlpa.reset_analysis_default_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);
 
Parameter Description
APPLY_CAPTURED_COMPILENV Indicates whether the advisor could use the compilation environment captured with the SQL statements. The default is 0 (that is, NO).
BASIC_FILTER Basic filter for SQL tuning set
COMPARISON_METRIC Specify an expression of execution statistics to use in performance comparison (Example: buffer_gets, cpu_time + buffer_gets * 10)
DAYS_TO_EXPIRE Number of days until the task is deleted
DEFAULT_EXECUTION_TYPE The task will default to this type of execution when none is specified by the EXECUTE_ANALYSIS_TASK Function & Procedure.
EXECUTION_DAYS_TO_EXPIRE Number of days until the tasks's executions will be deleted (without deleting the task)
EXECUTION_NAME1 Name of the first task execution to analyze
EXECUTION_NAME2 Name of the second task execution to analyze
LOCAL_TIME_LIMIT Per-statement time out (seconds)
PLAN_FILTER Plan filter for SQL tuning set (see SELECT_SQLSET for possible values)
RANK_MEASURE1 First ranking measure for SQL tuning set
RANK_MEASURE2 Second possible ranking measure for SQL tuning set
RANK_MEASURE3 Third possible ranking measure for SQL tuning set
RESUME_FILTER A extra filter for SQL tuning sets besides BASIC_FILTER
SQL_IMPACT_THRESHOLD Threshold of a change impact on a SQL statement. Same as the previous parameter, but at the level of the SQL statement.
SQL_LIMIT Maximum number of SQL statements to tune
SQL_PERCENTAGE Percentage filter of SQL tuning set statements
TIME_LIMIT Global time out (seconds)

WORKLOAD_IMPACT_THRESHOLD

Threshold of a SQL statement impact on a workload. Statements which workload change impact is below the absolute value of this threshold will be ignored and not considered for improvement or regression.
TBD
Overload 2 dbms_sqlpa.reset_analysis_default_parameter(
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
 
SET_ANALYSIS_TASK_PARAMETER
Sets the SQL analysis task parameter value

Overload 1
dbms_sqlpa.set_analysis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN VARCHAR2);
TBD
Overload 2 dbms_sqlpa.set_analysis_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [77 users online]    © 2010 psoug.org