DBMS_ADDM DBMS_ADVANCED_REWRITE

DBMS_ADDM

SELECT DBMS_ADDM.compare_databases (

         base_begin_snap_id => 1962,

         base_end_snap_id   => 1964,

         comp_begin_snap_id => 1970,

         comp_end_snap_id   => 1972,

         report_type        => 'HTML') AS report

FROM   dual;

SELECT DBMS_ADDM.compare_instances (

         base_instance_id   => 1,

         base_begin_snap_id => 1962,

         base_end_snap_id   => 1964,

         comp_instance_id   => 1,

         comp_begin_snap_id => 1970,

         comp_end_snap_id   => 1972,

         report_type        => 'HTML') AS report

FROM   dual;

ANALYZE_DB procedure does analysis of the whole database for the period between the two specified snapshots

DECLARE

  l_task_name VARCHAR2(30) := '1783_1785_addm_db';

BEGIN

  DBMS_ADDM.analyze_db (

    task_name      => l_task_name,

    begin_snapshot => 1783,

    end_snapshot   => 1785);

END;

ANALYZE_INST procedure does analysis of a specific instance for the period between the two specified snapshots

DECLARE

  l_task_name VARCHAR2(30) := '1783_1785_addm_inst';

BEGIN

  DBMS_ADDM.analyze_inst (

    task_name       => l_task_name,

    begin_snapshot  => 1783,

    end_snapshot    => 1785,

    instance_number => 1);

END;

DELETE Procedure : This procedure deletes an already created ADDM task

BEGIN

  DBMS_ADDM.DELETE ('my_partial_analysis_mode_task');

END

GET_REPORT Function: This function retrieves the default text report of an executed ADDM task.

DBMS_ADDM.GET_REPORT (

   task_name           IN VARCHAR2)

  RETURN CLOB;

DBMS_ADVANCED_REWRITE

DBMS_ADVANCED_REWRITE contains interfaces for advanced query rewrite users. Using this package, you can create, drop, and maintain functional equivalence declarations for query rewrite.

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO User;

GRANT CREATE MATERIALIZED VIEW TO User;

ALTER_REWRITE_EQUIVALENCE Procedure

This table list the all the package subprograms in alphabetical order.

Syntax

DBMS_ADVANCED_REWRITE.ALTER_REWRITE_EQUIVALENCE (

   name            VARCHAR2,

   rewrite_mode    VARCHAR2);

DECLARE_REWRITE_EQUIVALENCE Procedures

This procedure creates a declaration indicating that source_stmt is functionally equivalent to destination_stmt for as long as the equivalence declaration remains enabled, and that destination_stmt is more favorable in terms of performance.

Syntax

DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

   name                 VARCHAR2,

   source_stmt          VARCHAR2,

   destination_stmt     VARCHAR2,

   validate             BOOLEAN    := TRUE,

   rewrite_mode         VARCHAR2   := 'TEXT_MATCH');

rewrite_mode

The following modes are supported, in increasing order of power: disabled: Query rewrite does not use the equivalence declaration. Use this mode to temporarily disable use of the rewrite equivalence declaration. text_match: Query rewrite uses the equivalence declaration only in its text match modes. This mode is useful for simple transformations. general: Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. However, query rewrite makes no attempt to rewrite the specified destination_query. recursive: Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. Moreover, query rewrite further attempts to rewrite the specified destination_query for further performance enhancements whenever it uses the equivalence declaration.

DROP_REWRITE_EQUIVALENCE Procedure

This procedure drops the specified rewrite equivalence declaration.

Syntax

DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE (

   name        VARCHAR2);

VALIDATE_REWRITE_EQUIVALENCE Procedure

This procedure validates the specified rewrite equivalence declaration

Syntax

DBMS_ADVANCED_REWRITE.VALIDATE_REWRITE_EQUIVALENCE (

   name         VARCHAR2);

name

A name for the equivalence declaration to validate. The name can be of the form owner.name, where owner complies with the rules for a schema name, and name compiles with the rules for a table name. Alternatively, a simple name that complies with the rules for a table name can be specified. In this case, the rewrite equivalence is validated in the current schema. The invoker must have sufficient privileges to execute both the source_stmt and destination_stmt of the specified equivalence declaration.