Oracle 18cR3 PRVTEMX

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	REPORT_ANALYSIS_TASK_INFO_XML	REPORT_SQLSET_LIST_XML
CREATE_ANALYSIS_TASK	REPORT_ANALYSIS_TASK_LIST_XML	SCHEDULE_ANALYSIS_COMPARE
EXECUTE_QUICK_CHECK_TASK	REPORT_QUICK_CHECK_DFLTS_XML	SCHEDULE_ANALYSIS_EXECUTION
REPORT_ANALYSIS_DEFAULTS_XML	REPORT_SET_ANALYSIS_DFLTS_XML	SCHEDULE_QUICK_CHECK_TASK

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