Oracle PRVTEMX_SQL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Undocumented
AUTHID DEFINER
Dependencies
ANYDATA DBMS_ASSERT PRVTEMX_ADMIN
DBA_ADVISOR_EXECUTIONS DBMS_REPORT PRVT_ADVISOR
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SCHEDULER PRVT_REPORT_TAGS
DBA_ADVISOR_OBJECTS DBMS_SESSION PRVT_SQLPA
DBA_ADVISOR_PARAMETERS DBMS_SQLPA SQL_BINDS
DBA_ADVISOR_TASKS DBMS_SQLTUNE SYS_IXMLAGG
DBA_RSRC_CONSUMER_GROUPS DBMS_STANDARD V$ADVISOR_PROGRESS
DBA_SQLSET DBMS_SYS_ERROR WRI$_REPT_SQLPI
DBA_SQLTUNE_BINDS DBMS_UTILITY XMLAGG
DBA_SQL_PROFILES DUAL XMLTYPE
DBMS_ADVISOR    
Documented No
First Available 12.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/prvtemp_sql.plb
Subprograms
 
ALTER_ANALYSIS_TASK_XML
Undocumented

Time invested in finding a valid action type did not produce one
prvtemx_sql.alter_analysis_task_xml(
task_id       IN NUMBER,
action_type   IN VARCHAR2,
show_sql_only IN NUMBER)
RETURN XMLTYPE;
SQL> SELECT task_id, description
  2  FROM dba_advisor_tasks
  3  WHERE execution_end IS NULL;

TASK_ID DESCRIPTION
---------- -----------------
2 Automatic SPM Evolve Task


SQL> SELECT prvtemx_sql.alter_analysis_task_xml(2, 'BASIC', 1)
  2  FROM dual;
ERROR:
ORA-20000: invalid action type
ORA-06512: at "SYS.PRVTEMX_SQL", line 650
ORA-06512: at "SYS.PRVTEMX_SQL", line 611
ORA-06512: at line 1
 
CREATE_ANALYSIS_TASK
Undocumented prvtemx_sql.creative_analysis_task(
task_name     IN VARCHAR2,
description   IN VARCHAR2,
sts_owner     IN VARCHAR2,
sts_name      IN VARCHAR2,
show_sql_only IN NUMBER)
RETURN XMLTYPE;
TBD
 
EXECUTE_QUICK_CHECK_TASK
Undocumented prvtemx_sql.executive_quick_check_task(
change_spec    IN VARCHAR2,
task_name      IN VARCHAR2,
consumer_group IN VARCHAR2);
TBD
 
REPORT_ANALYSIS_DEFAULTS_XML
Undocumented prvtemx_sql.report_analysis_defaults_xml RETURN XMLTYPE;
SQL> SELECT prvtemx_sql.report_analysis_defaults_xml
  2  FROM dual;

REPORT_ANALYSIS_DEFAULTS_XML
---------------------------------------------------------------------------------------------------------------------------------
<report db_version="12.2.0.1.0" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-18000" packs="2">
  <report_id><![CDATA[/orarep/sqlpa/default_params]]></report_id>
  <spa_defaults disable_multi_exec="FALSE" num_rows_to_fetch="ALL_ROWS" local_time_limit="UNUSED" total_time_limit="UNLIM
ITED" comparison_metric="UNUSED" workload_impact_threshold="1" sql_impact_threshold="1" execute_fulldml="FALSE" apply_cap
ture_compileenv="0"/>
</report>
 
REPORT_ANALYSIS_TASK_INFO_XML
Undocumented

TID = task_id
prvtemx_sql.report_analysis_task_info_xml(tid IN NUMBER)
RETURN XMLTYPE;
SQL> SELECT prvtemx_sql.report_analysis_task_info_xml(2)
  2  FROM dual;

PRVTEMX_SQL.REPORT_ANALYSIS_TASK_INFO_XML(2)
---------------------------------------------------------------------------------------------
<report db_version="18.0.0.0.0" cpu_cores="2" hyperthread="N" con_id="1" con_name="CDB$ROOT"
timezone_offset="0" packs="2">
  <report_id><![CDATA[/orarep/sqlpa/task_list]]></report_id>
  <execution_list/>
  <report_list/>
<task_info task_name="SYS_AUTO_SPM_EVOLVE_TASK" spa_type="UNUSED" quick_check_type="UNUSED"/>
</report>
 
REPORT_ANALYSIS_TASK_LIST_XML
Undocumented prvtemx_sql.report_analysis_task_list_xml(result_limit IN NUMBER)
RETURN XMLTYPE;
SQL> SELECT prvtemx_sql.report_analysis_task_list_xml(3)
  2  FROM dual;

PRVTEMX_SQL.REPORT_ANALYSIS_TASK_LIST_XML(3)
---------------------------------------------------------------------------------------------------------------------------------
<report db_version="12.2.0.1.0" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-18000" packs="2">
  <report_id><![CDATA[/orarep/sqlpa/task_list%3ftop%3d3]]></report_id>
  <task_list/>
  <session_user>SYS</session_user>
</report>
 
REPORT_QUICK_CHECK_DFLTS_XML
Undocumented prvtemx_sql.report_quick_check_dflts_xml RETURN XMLTYPE;
SQL> SELECT prvtemx_sql.report_quick_check_dflts_xml
  2  FROM dual;

REPORT_QUICK_CHECK_DFLTS_XML
---------------------------------------------------------------------------------------------------------------------------------
<report db_version="12.2.0.1.0" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-18000" packs="2">
  <report_id><![CDATA[/orarep/sqlpa/default_qc_params]]></report_id>
  <sts_list/>
  <cg_list>
    <cg name="BATCH_GROUP"/>
    <cg name="DEFAULT_CONSUMER_GROUP"/>
    <cg name="DSS_CRITICAL_GROUP"/>
    <cg name="DSS_GROUP"/>
    <cg name="ETL_GROUP"/>
    <cg name="INTERACTIVE_GROUP"/>
    <cg name="LOW_GROUP"/>
    <cg name="ORA$APPQOS_0"/>
    <cg name="ORA$APPQOS_1"/>
    <cg name="ORA$APPQOS_2"/>
    <cg name="ORA$APPQOS_3"/>
    <cg name="ORA$APPQOS_4"/>
    <cg name="ORA$APPQOS_5"/>
    <cg name="ORA$APPQOS_6"/>
    <cg name="ORA$APPQOS_7"/>
    <cg name="ORA$AUTOTASK"/>
    <cg name="SYS_GROUP"/>
  </cg_list>
</report>
 
REPORT_SET_ANALYSIS_DFLTS_XML
Undocumented

A DBMS_SQLPA analysis parameter was tried with the result shown
prvtemx_sql.report_set_analysis_dflts_xml(spa_defaults IN VARCHAR2);
SQL> exec prvtemx_sql.report_set_analysis_dflts_xml('<SQL PERCENTAGE="10">');
BEGIN prvtemx_sql.report_set_analysis_dflts_xml('<SQL PERCENTAGE="10">'); END;
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at "SYS.PRVTEMX_SQL", line 2107
ORA-06512: at line 1
 
REPORT_SQLSET_LIST_XML
Undocumented prvtemx_sql.report_sqlset_list_xml RETURN XMLTYPE;
SQL> SELECT prvtemx_sql.report_sqlset_list_xml
  2  FROM dual;

REPORT_SQLSET_LIST_XML
------------------------------------------------------------------
<report db_version="18.0.0.0.0" cpu_cores="2" hyperthread="N" con_id="1"
con_name="CDB$ROOT" timezone_offset="0" packs="2">
  <report_id><![CDATA[/orarep/sqlpa/default_params]]></report_id>
  <sts_list/>
  <default_params>
    <sqlset_owner>UNUSED</sqlset_owner>
    <sqlset_name>UNUSED</sqlset_name>
  </default_params>
</report>
 
SCHEDULE_ANALYSIS_COMPARE
Undocumented prvtemx_sql.schedule_analysis_compare(
task_id           IN NUMBER,
task_name         IN VARCHAR2,
execution_name    IN VARCHAR2,
description       IN VARCHAR2,
execution_name1   IN VARCHAR2,
execution_name2   IN VARCHAR2,
comparison_metric IN VARCHAR2,
start_date        IN TIMESTAMP WITH TIME ZONE,
show _sql_only    IN NUMBER)
RETURN XMLTYPE;
TBD
 
SCHEDULE_ANALYSIS_EXECUTION
Undocumented prvtemx_sql.schedule_analysis_execution(
task_id         IN NUMBER,
task_name       IN VARCHAR2,
exsecution_name IN VARCHAR2,
description     IN VARCHAR2,
execution_type  IN VARCHAR2,
start_date      IN TIMESTAMP WITH TIME ZONE,
show_sql_only   IN NUMBER)
RETURN XMLTYPE;
TBD
 
SCHEDULE_QUICK_CHECK_TASK
Undocumented prvtemx_sql.schedule_quick_check_task(
change_spec IN VARCHAR2,
task_name   IN VARCHAR2,
description IN VARCHAR2,
time_limit  IN VARCHAR2)
RETURN VARCHAR2;
TBD

Related Topics
Built-in Functions
Built-in Packages
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved