36 DBMS_AUTO_MV
DBMS_AUTO_MV
contains subprograms for
configuring automatic materialized views.
This chapter contains the following topics:
36.1 Using DBMS_AUTO_MV
The DBMS_AUTO_MV
package contains functions and procedures to manage automatic materialized views.
This package is owned by SYS
, so EXECUTE
package privilege is required by non-SYS
users. Grant EXECUTE
privilege on the package to the users.
36.2 Summary of DBMS_AUTO_MV Subprograms
This table lists the DBMS_AUTO_MV
package subprograms and
briefly describes them.
Table 36-1 DBMS_AUTO_MV Package Subprograms
Subprogram | Description |
---|---|
CONFIGURE Procedure |
Enables, disables, and configures the various parameters of the automatic materialized view feature. |
DROP_AUTO_MVS Procedure |
Drops automatic materialized views that were created. This routine can only be executed by DBA. |
RECOMMEND Function |
This function allows a user to manually generate automatic materialized view recommendations for SQL statements in a given SQL tuning set |
REFRESH Procedure |
This procedure allows a user to manually refresh all stale automatic materialized views in the system unconditionally. |
REPORT_ACTIVITY Function |
This function generates a report on the automatic materialized view activities and usage for a specified time duration. The report can be generated in text, HTML, or XML formats as specified by the argument type. |
REPORT_LAST_ACTIVITY Function |
This function generates a report on the most recent automatic materialized view activities and usage. The report can be generated in text, HTML, or XML formats as specified by the argument type. |
36.2.1 CONFIGURE Procedure
This procedure enables, disables, and configures the various parameters of the automatic materialized view feature.
Syntax
DBMS_AUTO_MV.CONFIGURE ( parameter IN VARCHAR2, value IN VARCHAR2, allow IN BOOLEAN DEFAULT TRUE);
Parameters
Table 36-2 CONFIGURE Procedure Parameters
Parameter | Description |
---|---|
|
The name of the parameter to be modified. Parameter names are not case sensitive. |
|
The value of the specified parameter. |
|
This parameter allows or disallows various schemas or app
modules. It is applicable only for the |
Parameters
Table 36-3 CONFIGURE Parameters Names
Parameter | Description |
---|---|
|
Enables, disables, or engages report-only mode. Possible values are:
|
|
Activates and deactivated automatic maintenance of materialized views.
|
|
Specifies the amount of space budget available for implementing automatic materialized views. The total space value is the sum of currently space used by all user tables (i.e. not system tables). The calculation does not include user access structures (like indexes or materialized views). Possible values are:
The default budget is |
|
Specifies the tablespace to place automatic materialized views. Possible values are:
If the value is changed dynamically, it takes effect the next time automatic materialized views recommendations are implemented. |
|
Specifies the temporary tablespace while creating or refreshing automatic materialized views. Possible values are:
If the value is changed dynamically, it takes effect the next time recommendations are implemented. |
|
Specifies the number of days automatic materialized views exists without being utilized by a query. When the expiry period is reached, the materialized view is dropped.
|
|
Specifies the maximum number of days to retain analysis and recommendation history.
The history of analysis and verification is retained in the |
|
Specifies the maximum number of hours to make recommendations.
|
|
Specifies the minimum time in seconds for a query to be considered for automatic materialized views recommendation. Queries below this threshold are not considered for recommendations.
|
|
Specifies the schemas to include or exclude from creation of automatic materialized views. Possible values are:
If both the lists (the inclusion list and the exclusion list) contain at least one schema, then all the schemas can use automatic materialized views, except the schemas listed in the exclusion list. |
|
Specifies application modules to include or exclude from creation of automatic materialized views. Possible values are:
Initially, the inclusion list and the exclusion list are empty and we can create automatic materialized views under all application modules when automatic automatic materialized views are enabled for a database. |
|
Specifies the maximum number of days to retain the verification history.
|
|
Specifies the maximum number of days to retain history of automatic materialized view maintenance.
|
Example
begin dbms_auto_mv.configure (‘AUTO_MV_SPACE_BUDGET’, ‘50%’); end; begin dbms_auto_mv.configure (‘AUTO_MV_SCHEMA’, ‘SH’, FALSE); dbms_auto_mv.configure (‘AUTO_MV_SCHEMA’, ‘SCOTT’); end; /
36.2.2 DROP_AUTO_MVS Procedure
This procedure drops automatic materialized views that were created. This routine can only be executed by DBA.
Syntax
DBMS_AUTO_MV.DROP_AUTO_MVS ( owner IN VARCHAR2, mv_name IN VARCHAR2, allow_recreate IN BOOLEAN DEFAULT FALSE);
Parameters
Table 36-4 DROP_AUTO_MVS Procedure Parameters
Parameter | Description |
---|---|
|
Specifies the name of the owner of the automatic materialized views. If If Dropped automatic materialized views are not recreated automatically by the system as default. |
|
The name of the automatic materialized views. |
|
Enables or disables the automatic creation of dropped automatic materialized views.
|
Examples
begin dbms_auto_mv.DROP_AUTO_MVS (‘SH’); end; / begin dbms_auto_mv.DROP_AUTO_MVS (‘SH’, ‘AUTO_MV$$_G2MKPB9SA1FB7’); end; /
36.2.3 RECOMMEND Function
This function allows a user to manually generate automatic materialized view recommendations for SQL statements in a given SQL tuning set
Syntax
DBMS_AUTO_MV.RECOMMEND (
sts_owner IN VARCHAR2 DEFAULT 'SYS',
sts_name IN VARCHAR2 DEFAULT 'SYS_AUTO_STS',
workload_start_time IN TIMESTAMP DEFAULT NULL,
workload_end_time IN TIMESTAMP DEFAULT NULL,
automv_mode IN VARCHAR2 DEFAULT 'REPORT ONLY')
RETURN VARCHAR2;
Parameters
Table 36-5 RECOMMEND Function Parameters
Parameter | Description |
---|---|
|
The name of the owner of the SQL tuning set. The default value is
|
|
The name of the SQL tuning set. The default value is
|
|
The start time of the workload window. The value |
|
The end time of the workload window. |
|
When |
Return Value
The execution name (execution_name
) to be used in
DBA_AUTO_MV%
catalog views.
Examples
In the following examples, the default SQL tuning set, SYS_AUTO_STS
is used. Make sure that SYS_AUTO_STS
contains the required workload to
generate the automatic materialized views.
Example 1: Generate and report recommendations using
SYS_AUTO_STS
for the past 24 hours. Note that the default behavior of
this function is REPORT ONLY
, so no automatic materialized view will be
implemented.
var exec_name varchar2(200);
begin
:exec_name := dbms_auto_mv.recommend();
end;
/
SELECT * FROM DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS
WHERE exec_name = :exec_name;
Example 2: Generate and publish recommendations using SYS_AUTO_STS
for the past 24 hours.
var exec_name varchar2(200);
begin
:exec_name := dbms_auto_mv.recommend(automv_mode=>'IMPLEMENT');
end;
/
36.2.4 REFRESH Procedure
This procedure allows a user to manually refresh all stale automatic materialized views in the system unconditionally.
Syntax
DBMS_AUTO_MV.REFRESH ();
Example
begin dbms_auto_mv.refresh(); end; /
36.2.5 REPORT_ACTIVITY Function
This function generates a report on the automatic materialized view activities and usage for a specified time duration. The report can be generated in text, HTML, or XML formats as specified by the argument type.
Syntax
DBMS_AUTO_MV.REPORT_ACTIVITY (
activity_start IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP -1,
activity_end IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
type IN VARCHAR2 DEFAULT ‘TEXT’,
section IN VARCHAR2 DEFAULT ‘ALL’,
level IN VARCHAR2 DEFAULT ‘TYPICAL’)
RETURN CLOB;
Parameters
Table 36-6 REPORT_ACTIVITY Function Parameters
Parameter | Description |
---|---|
activity_start |
The start time for report generation. |
activity_end |
The end time for report generation. |
|
The format type in which the report needs to be generated. The
possible values are:
The default value is |
|
The section can be a combination of the following:
The default value is You can generate a specific combination of report by using
|
|
The level can be either The default value is |
Return Value
This functions returns the report as a CLOB
.
Examples
Example 1: The following call to REPORT_ACTIVITY()
generates an HTML output for all the automatic materialized view activities:
select dbms_auto_mv.report_activity(type => ‘HTML') from dual;
36.2.6 REPORT_LAST_ACTIVITY Function
This function generates a report on the most recent automatic materialized view activities and usage. The report can be generated in text, HTML, or XML formats as specified by the argument type.
Syntax
DBMS_AUTO_MV.REPORT_LAST_ACTIVITY (
type IN VARCHAR2 DEFAULT ‘TEXT’,
section IN VARCHAR2 DEFAULT ‘ALL’,
level IN VARCHAR2 DEFAULT ‘TYPICAL’)
RETURN CLOB;
Parameters
Table 36-7 REPORT_LAST_ACTIVITY Function Parameters
Parameter | Description |
---|---|
|
The format type in which the report needs to be generated. The
possible values are:
The default value is |
|
The section can be a combination of the following:
The default value is You can generate a specific combination of report by using
|
|
The level can be either The default value is |
Return Value
This functions returns the report as a CLOB
.
Examples
Example 1: The following call to
REPORT_LAST_ACTIVITY()
generates a TEXT
output:
select dbms_auto_mv.report_last_activity('TEXT', 'ALL', ‘TYPICAL’) from dual;
Example 2: The following call to REPORT_LAST_ACTIVITY()
generates an XML output:
select dbms_auto_mv.report_last_activity(‘XML', 'ALL', 'TYPICAL') from dual;
Example 3: The following call to REPORT_LAST_ACTIVITY() generates an HTML output:
select dbms_auto_mv.report_last_activity(‘HTML', 'ALL', 'TYPICAL') from dual;