8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_LIBCACHE
The DBMS_LIBCACHE package allows you to extract the SQL and PL/SQL from one Real Application Cluster (RAC) nodes library cache and compile it into the library cache of another node. Typically this is used to warm up the library cache before a failover or switchover. This package forms part of the Real Application Cluster Guard so it is not loaded by default, but if you have chosen to install the cluster management software the relevant files will be present under your ORACLE_HOME.
First we must create the necessary objects by running the "$ORACLE_HOME/pfs/admin/catlibc.sql" file.
$ ORACLE_SID=TSH2; export ORACLE_SID
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Nov 18 09:40:03 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @$ORACLE_HOME/pfs/admin/catlibc.sql
Package created.
Synonym dropped.
Synonym created.
Grant succeeded.
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
User dropped.
... Creating the parsing user and database link.
Below are the list of online tablespaces in this database.
Decide which tablespace you wish to use for the PARSER user.
TABLESPACE_NAME
------------------------------
UNDOTBS1
TEMP
CWMLITE
DRSYS
ODM
TOOLS
USERS
XDB
EXAMPLE
HTMLDB
10 rows selected.
Please enter the parsing users password and tablespaces.
Enter value for parser_password: parser
Enter value for default_tablespace: USERS
Enter value for temporary_tablespace: TEMP
User created.
Grant succeeded.
Enter value for parser_password: parser
Connected.
drop public database link libc_link
*
ERROR at line 1:
ORA-02024: database link not found
Please enter the parsing users TNS connect string.
Enter value for connect_string: TSH1
Database link created.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Next we create the DBMS_LIBCACHE package itself.
%> ORACLE_SID=TSH2; export ORACLE_SID
%> sqlplus /nolog
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Nov 18 09:58:05 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @$ORACLE_HOME/pfs/admin/dbmslibc.sql
Package created.
drop public synonym dbms_libcache
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Grant succeeded.
Grant succeeded.
Package body created.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
With the software installed on the RAC we can now warm up the library cache of the TSH2 node from the contents of the TSH1 node library cache.
Some examples of the DBMS_LIBCACHE usage are shown below.
-- Warm the library cache for all schemas.
EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK');
-- Warm the library cache for the specified schema.
EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK','MY_SCHEMA');
-- Warm the library cache for the specified schema with statement
-- having 10 or more executions.
EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK','MY_SCHEMA', 10);
-- Warm the library cache for the specified schema with statement
-- having 10 or more executions with a minimum cursor size of 2048.
EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK','MY_SCHEMA', 10, 2048);
The expected output when no statements are found in the remote library cache is shown below.
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK','MY_SCHEMA');
fetch_sql_headers: ORA-20008: No SQL statements found matching the input criteria.
compile_from_remote: ORA-20100: Exiting compile from remote
PL/SQL procedure successfully completed.
When statements are found in the remote library cache you would expect to see the following.
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK','MY_SCHEMA');
Total SQL statements to compile = 135
Total SQL statements compiled = 135
PL/SQL procedure successfully completed.
If some statements can't be processed for any reason you would expect output like this.
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_LIBCACHE.COMPILE_FROM_REMOTE('LIBC_LINK','MY_SCHEMA');
Total SQL statements to compile = 135
Parsing user cannot access the objects.
ORA-20111: Warning at cursor :44
Parsing user cannot access the objects.
ORA-20111: Warning at cursor :121
Total SQL statements compiled = 133
PL/SQL procedure successfully completed.
Although the DBMS_LIBCACHE was intended for use between two RAC nodes it is not restricted to RAC instances.
For more information see:
Hope this helps. Regards Tim...