Appendix B. A Summary of Oracle Packages Used for Performance Tuning

In this book we have used various Oracle tools and packages to investigate and solve performance issues.

Note

There are also many tools in the market, made by third-party software vendors, which can help DBAs and developers in many fields, from coding PL/SQL packages and procedures to analyzing data warehouses or tuning the database I/O.

In this appendix we present a brief summary of these packages, providing a small reference from which we can start when we want to solve a performance problem. The packages are presented in alphabetical order.

DBMS_ADDM

This package provides procedures to manage Oracle Automatic Database Diagnostic Monitor.

Procedures

The most relevant procedures are:

• ANALYZE_DB : creates an ADDM task to analyze the database and execute it

• ANALYZE_INST : creates an ADDM task for analyzing in instance analysis mode and executes it

• GET_REPORT: retrieves the default text report of an executed ADDM task

DBMS_ADVISOR

This package helps in managing the Advisors, a set of expert systems that identify and help resolve performance problems related to various database server components.

Procedures

The most relevant procedures are:

• SET_DEFAULT_TASK_PARAMETER : sets the default values for task parameters

• QUICK_TUNE : performs an analysis on a single SQL statement

• EXECUTE_TASK : executes the specified task

DBMS_JOB

Schedules and manages jobs in the database job queue.

Note

Oracle recommends using the DBMS_SCHEDULER package.

Procedures

The most relevant procedures are:

• SUBMIT: submits a new job to the job queue

• RUN: forces a specified job to run

• NEXT_DATE: alters the next execution time for a specified job

• BROKEN: deletes a job execution

• REMOVE: removes the specified job from the job queue

DBMS_LOB

This package provides procedures to work with BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs.

Procedures

The most relevant procedures are:

• GET_LENGTH: gets the length of the LOB value

• FILEOPEN : opens a file

• LOADFROMFILE : loads LOB data from a file

• APPEND : appends the contents of a source LOB to a destination LOB

• OPEN : opens an LOB

• READ : reads data from the LOB starting at the specified offset

• WRITE : writes data to the LOB from a specified offset

• CLOSE : closes a previously opened LOB

DBMS_MVIEW

This package helps the management of Materialized Views, refreshes them and helps understanding the capabilities for materialized views and potential materialized views.

Procedures

The most relevant procedures are:

• EXPLAIN_MVIEW : explains what is possible with a materialized view or potential materialized view

• EXPLAIN_REWRITE : explains why a query failed to rewrite or why the optimizer chose to rewrite a query with a particular materialized view(s)

• REFRESH : refreshes one or more materialized views

• REFRESH_ALL_MVIEWS : refreshes all the materialized views

DBMS_OUTLN

This package contains the functional interface to manage stored outlines.

To use this package the EXECUTE_CATALOG_ROLE role is needed. There is also a public synonym OUTLN_PKG.

Procedures

The most relevant procedures are:

• CLEAR_USED : clears the outline "used" flag

• DROP_BY_CAT : drops outlines which belong to a specific category

• UPDATE_BY_CAT : updates the category of outlines to a new category

• DROP_UNUSED: drops outlines never applied in the compilation of a SQL statement

DBMS_OUTLN_EDIT

This package contains the functional interface to manage stored outlines.

The public role has execute privileges on DBMS_OUTLN_EDIT, which is defined with invoker's rights.

Procedures

The most relevant procedures are:

• CREATE_EDIT_TABLES : creates outline editing tables in calling a user's schema; beginning from Oracle 10g, you will not need to use this procedure because the outline editing tables are part—as temporary tables—of the SYSTEM schema

• REFRESH_PRIVATE_OUTLINE : refreshes the in-memory copy of the outline, synchronizing its data with the edits made to the outline hints

• DROP_EDIT_TABLES : drops the outline editing tables from the calling user's schema

DBMS_SHARED_POOL

This package allows access to information about sizes of the objects stored in the shared pool and marks them for keeping or not-keeping.

Procedures

The most relevant procedures are:

• KEEP: keeps an object in the shared pool, so it isn't subject to aging

• UNKEEP : unkeeps an object from the shared pool

• PURGE: purges the object

• SIZES: shows objects in the shared pool larger than the specified size

DBMS_SPACE

This package enables the analysis of segment growth and space requirements.

Procedures

The most relevant procedures are:

• CREATE_TABLE_COST : determines the size of a table

• CREATE_INDEX_COST : determines the size of an index

• FREE_BLOCKS : returns information about free blocks in an object

• SPACE_USAGE : returns information about free blocks in a segment managed by automatic space management

DBMS_SPM

This package provides an interface to manipulate plan history and SQL plan baselines.

Procedures

The most relevant procedures are:

• LOAD_PLANS_FROM_CURSOR_CACHE : loads one or more plans from the cursor cache for a SQL statement

• LOAD_PLANS_FROM_SQLSET : loads plans stored in a SQL tuning set into SQL plan baselines

• EVOLVE_SQL_PLAN_BASELINE : evolves SQL plan baselines associated with one or more SQL statements, changing them to accepted if they are found to be better than the SQL plan baseline performance and if the user asks such action

• DROP_SQL_PLAN_BASELINE : drops a single plan or all the plans associated with a SQL statement

DBMS_SQL

This package provides an interface to use dynamic SQL to parse both DML and DDL statements using PL/SQL.

Procedures

The most relevant procedures are:

• EXECUTE : executes a cursor

• OPEN_CURSOR : returns the cursor ID number of the new cursor

• PARSE: parses the given statement

• BIND_VARIABLE : binds a given value to a given variable

• CLOSE_CURSOR : closes a given cursor and frees associated memory

DBMS_SQLTUNE

This package provides an interface to tune SQL statements.

Procedures

The most relevant procedures related to the SQL tuning set are:

• CREATE_SQLSET : creates a SQL tuning set object in the database

• DROP_SQLSET : drops a SQL tuning set if not active

• SELECT_SQLSET : collects SQL statements from an existing SQL tuning set

• LOAD_SQLSET: populates the SQL tuning set with a set of selected SQL statements

• SELECT_CURSOR_CACHE : collects SQL statements from the cursor cache

The most relevant procedures to manage SQL tuning tasks are:

• CREATE_TUNING_TASK : creates a tuning of a single statement or tuning set

• EXECUTE_TUNING_TASK : executes a previously created tuning task

• REPORT_TUNING_TASK : displays the results of a tuning task

• INTERRUPT_TUNING_TASK : interrupts the currently executing tuning task

• RESUME_TUNING_TASK : resumes a previously interrupted tuning task

DBMS_STATS

This package allows you to view and modify optimizer statistics.

Procedures

The most relevant procedures are:

• GATHER_SCHEMA_STATS : gathers optimizer statistics for a schema class

• GATHER_DATABASE_STATS: gathers optimizer statistics for a database class

• GATHER_TABLE_STATS: gathers table statistics

• GATHER_INDEX_STATS: gathers index statistics

• CREATE_STAT_TABLE: creates the user statistics table

• DROP_STAT_TABLE : drops the user statistics table

• EXPORT_SCHEMA_STATS : exports schema statistics to a user statistics table

• IMPORT_SCHEMA_STATS : import schema statistics from a user statistics table

DBMS_UTILITY

This package provides various utility subprograms.

Procedures

The most relevant procedures are:

• ANALYZE_SCHEMA : analyzes all the tables, indexes, and clusters in a schema

• ANALYZE_DATABASE : analyzes all the tables, indexes, and clusters in a database

• GET_TIME : returns the current time in hundredths of a second

DBMS_WORKLOAD_REPOSITORY

This package allows management of Workload Repository.

Procedures

The most relevant procedures are:

• CREATE_SNAPSHOT : creates a manual snapshot

• MODIFY_SNAPSHOT_SETTINGS : modifies the snapshot settings

• CREATE_BASELINE : creates a single baseline