Environment: Oracle 9.2.0.7 EE, 32bit, Windows 2003 Enterprise
Server.
Problem: Transaction (bulk load) hangs and getting below error in the alert.log
Thread 1 advanced to log sequence 1234
Current log# 1 seq# 1234 mem# 0: D:\ORACLE\ORADATA\LOG1.ORA
Wed Sep 25 21:06:06 2007
Failure to extend rollback segment 19 because of 1000 conditionFULL status of
rollback segment 19 set.
Wed Sep 25 21:06:20 2007
SMON: FULL status of rollback segment 19 cleared.
I have seen various reasons and scenarios (like insufficient disk space for RBS/UNDO tablespace/datafiles to grow, or hitting maxsize limitation for datafiles, max extents reached, ORA-1555 the famous "snapshot too old" error, etc) causing failure of a rollback (undo) segment. But this is the first time I came across a RBS failure which is slightly different the usual.
The database is in AUTOMATIC undo management mode
NAME TYPE VALUE
----------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
Solution: After trying various options, attempts and looking the alert log message under the microscope, I came to know that the reason for the error is ORA-01000 (as the error in the alert.log says "because of 1000", so DBAs are expected to assume it is ORA-01000 error, go figure!). Here is what Oracle documentation say about this error:
ORA-01000 maximum open cursors exceeded
Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of
cursors per user.
Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.
After changing the OPEN_CURSORS parameter (from 300) to 2000 and restarting the database, the bulk load went fine.
Did anyone had this problem? welcome to share your thoughts..
Wednesday, September 26, 2007
Failure to Extend Rollback Segment
Thursday, March 1, 2007
Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines
Environment: GIS database environment, ESRI's ArcSDE 9.0 running on Oracle 9.2.0.7 database on a 32 bit Windows 2003 Server operating system.
Table of Contents
Introduction
Rollback and De-Installation of Patches
Patch Install Verification
Part 1 - Install DST Patches in Oracle 9.2.0.7 Database Server
1. Software and Utility Requirement
2. DST Patch Download from Oracle Metalink
3. Shutdown and Reboot
4. Patch Install
4.1. Patch # 5548107 [Interim Patch for Bug: 5548107]
4.2. Patch # 5654905 [Critical patch update 9.2.0.7 Patch 15]
4.2.2.1. Patch Database(s)
4.3. Patch # 5047902 [Oracle JVM TimeZone - Interim Patch for bug 5047902]
5. Final Testing
Part 2 - Install DST Patches in Oracle 9.2.0.7 Client Machines
| Server | Oracle Product Installed |
| ORADBTEST | Database Server |
| ORADBPROD | Database Server |
| WEBTEST | Client |
| WEBPROD | Client |
| APPTEST | Client |
| APPPROD | Client |
Rollback and De-Installation of Patches: It’s safe to backup entire database server (including ORACLE_HOME and Database files) before patches are installed for the purpose of restoring the database.
However, there is a second option available, rollback scripts are created for each patch install when applied using Oracle OPatch Utility, hence a patch (or a patch-set) can be rolled back using them. During the patch install, rollback scripts are automatically created at "%ORACLE_HOME%\.patch_storage\<patch_id>\" folder in the name "rollback_<patch_id>.cmd". Patch De-Installation instructions are available at the bottom of each respective README.txt (or README.html if available) files.
There was no requirement for patch rollback when installed, hence steps for rolling back a patch is not included in this document.
Part 1 - Install DST Patches in Oracle 9.2.0.7 Database Server
1. Software and Utility Requirement: Oracle DST Patches are applied using an Oracle Patch utility called OPatch. It requires Perl 5.005_03 or higher and JDK versions of 1.3.1 or higher.
Note: Java, OPatch and Perl that were already present in Database Server may not include all the required files for DST patches. Hence download and reinstall all the three once again to successfully install the DST patches.
1.1. JAVA: Download and install JAVA from below Sun website and set PATH accordingly:-
J2SDK - http://java.sun.com/j2se/1.4.2/download.html
| C:\j2sdk1.4.2_13\bin>java -version java version "1.4.2_13" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_13-b06) Java HotSpot(TM) Client VM (build 1.4.2_13-b06, mixed mode |
In Database Server, it is installed in "C:\j2sdk1.4.2_13", include its "bin" folder in "Path" environment variable [refer section 1.4].
1.2. Perl - Download Perl using Oracle Interim Patch # 2417994 and install it as per instructions provided in the README.txt file.
Log into "metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "2417994" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". Download the 9.2.0.1 release, downloaded file name would be "p2417872_9201_WINNT.zip". [Or use below link to download]
| C:\perl\perl\5.00503\bin\MSWin32-x86>perl -version This is perl, version 5.005_03 built for MSWin32-x86 Copyright 1987-1999, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5.0 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page. |
In Database Server, Perl is installed in "C:\perl", include its "..\bin\MSWin32-x86" folder in "Path" environment variable [refer section 1.4].
1.3. OPatch - In Database Server, OPatch is installed in "D:\oracle\ora92\OPatch" folder. [If already present, move/backup the files present in the OPatch folder and reinstall new OPatch in the same folder]. Include its "D:\oracle\ora92\OPatch" folder in "Path" environment variable [refer section 1.4].
| set ORACLE_HOME=d:\oracle\ora92 D:\>opatch version Oracle Interim Patch Installer version 1.0.0.0.52 OPatch Version: 1.0.0.0.52 OPatch returns with error code = 0 |
Download Oracle OPatch using Bug # 2417872, and install it as per instructions provided in the README.txt file.
"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "2617419" as patch number, Select "Microsoft windows Server 2003" as platform and click "Go". Download the 10.1.0.2 release, downloaded file name would be "p2617419_10102_GENERIC.zip". [Or use below link to download].
http://updates.oracle.com/ARULink/Download/process_form/p2617419_10102_GENERIC.zip?aru=5939446&file_id=9596770&patch_file=p2617419_10102_GENERIC.zip
Reference: Oracle9i Data Server Interim Patch Installation (OPatch) Note: 189489.1
1.4. Path environment variable: Once Path is set for all the above 3, it would look as follows:-
| echo %Path% D:\oracle\ora92\jre\1.4.2\bin\client;D:\oracle\ora92\jre\1.4.2\bin; D:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin; C:\Program Files\Oracle\jre\1.1.8\bin;C:\WINDOWS\system32; C:\WINDOWS;C:\WINDOWS\System32\Wbem; C:\Program Files\Microsoft SQL Server\80\Tools\BINN; C:\esri_sde\ArcSDE\ora9iexe\bin;c:\program files\netiq\Common\Bin; C:\j2sdk1.4.2_13\bin;D:\oracle\ora92\OPatch; C:\perl\perl\5.00503\bin\MSWin32-x86; |
2. DST Patch Download from Oracle Metalink:-
2.1. Interim Patch for Bug: 5548107
"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "5548107" as patch number, Select "Microsoft windows Server 2003" as platform and click "Go". Download the 9.2.0.7 release, downloaded file name would be "p5548107_92070_GENERIC.zip". [Or use below link to download].
2.2. Oracle 9.2.0.7 Patch 15 on Windows 32 bit # 5654905
"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "5654905" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". The downloaded file name would be "p5654905_92070_WINNT.zip". [Or use below link to download].
2.3. Oracle JVM Patch # 5047902
"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" provide "5047902" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". Download the 9.2.0.7 release, downloaded file name would be "p5047902_92070_GENERIC.zip". [Or use below link to download].
Unzip all the above three zip files (patches) in the appropriate folders and following is the folder structure created in Database Server:
Example:
Folder called "D:\5548107" for patch 5548107
Folder called "D:\5654905" for patch 5654905
Folder called "D:\5047902" for patch 5047902
Read the README.txt present in the respective unzipped folder for further information about the patches.
3.1. Manual Services - Make all the ArcSDE and Oracle Services "Startup Type" as "Manual" if they are "Automatic" [Note down the "Startup Type" of these services prior to the change, will need to reverse it back to the original startup type ("Automatic") after the patch install].
3.2. Shutdown ArcSDE Service(s):
| C:\>set ORACLE_SID=TESTORCL C:\>set SDEHOME=C:\esri_sde\ArcSDE\ora9iexe C:\>sdemon -o shutdown -i esri_sde -p <password> -N ArcSDE Instance esri_sde on TESTORCL is Shutdown! |
3.3. Shutdown Oracle Databases: [From command line sqlplus.exe, not the windows sqlplusw.exe]
| SQL> conn sys/ Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. |
3.4. Stop all Oracle* services:
| C:\>net stop OracleServiceTESTORCL The OracleServiceTESTORCL service is stopping. The OracleServiceTESTORCL service was stopped successfully. C:\>net stop OracleOra92TNSListener |
3.5. Rename bin and reboot server
- Rename the "bin" folder in "D:\oracle\ora92" to some other name (Example: "bin_save") and reboot the server.
- Once the server comes back up, rename the "bin_save" folder back to "bin" again
4. Patch Install
4.1. Patch # 5548107[Interim Patch for Bug: 5548107]. Patch the Oracle Home (Run only once for an Oracle Home):
| -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\>set ORACLE_HOME=D:\ORACLE\ORA92 D:\>cd 5548107 D:\5548107>opatch lsinventory Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_16-02-35.log" Result: Installed Patch List: ===================== 1) Patch 5548107 applied on Wed Feb 28 15:28:38 EST 2007 [ Base Bug(s): 5548107 ] OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\5548107>opatch apply Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\5548107\Apply_5548107_02-28-2007_16-03-22.log" Subset patches: 5548107, The fixes for Patch 5548107, are included in the patch currently being installed (5548107). OPatch will roll back the subset patch(es) and install the new patch (5548107). Backing up comps.xml ... OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = d:\oracle\ora92) Is this system ready for updating? Please respond Y|N > Y Rolling back patch 5548107... Creating log file quot;D:\oracle\ora92\.patch_storage\5548107\RollBack_5548107_02-28 -2007_16-03-39.log" Rolling back with all-node mode. OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Oracle instances have been shut down, proceeding with auto-rollback. Removing patch 5548107... Restoring copied files... Warning: Cannot restore the file "d:\oracle\ora92\rdbms\admin\utltzuv2.sql" as OPatch can't find backup file, so it is backed up as quot;d:\oracle\ora92\rdbms\admin\utltzuv2.sql_removed_5548107" Updating inventory... Back to applying patch 5548107... Backing up 2nd copy of comps.xml ... Applying patch 5548107... Patching jar files... Patching copy files... Inventory is good and does not have any dangling patches. Updating inventory... Verifying patch... Backing up comps.xml ... OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- |
Optional: Patch 5548107 will create a file called "utltzuv2.sql" in the "ORACLE_HOME\rdbms\admin" folder, this script is to check if TIMESTAMP WITH TIME ZONE data is stored in the database. Refer to Metalink document "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note: 396670.1" for further details.
4.2. Patch # 5654905 [Critical patch update 9.2.0.7 Patch 15]
4.2.1. Patch the Oracle Home (Run once for each Oracle Home):
| -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\>cd D:\5654905 set ORACLE_HOME=D:\ORACLE\ORA92 D:\5654905>opatch lsinventory Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_16-19- 55.log" Result: Installed Patch List: ===================== 1) Patch 5548107 applied on Wed Feb 28 16:04:19 EST 2007 [ Base Bug(s): 5548107 ] OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\5654905>opatch apply Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\5654905\Apply_5654905_02-28-20 07_16-21-12.log" Backing up comps.xml ... OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = d:\oracle\ora92) Is this system ready for updating? Please respond Y|N > Y Executing the Apply pre-patch script (D:\5654905\custom\scripts\pre.bat)... Applying patch 5654905... Patching jar files... Patching copy files... Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\30" Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\90" Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\101" Creating file to hold list of directories that were mkdir'ed: "D:\oracle\ora92\.patch_storage\5654905\opatch_dirs_created.lst" Inventory is good and does not have any dangling patches. Updating inventory... Verifying patch... Backing up comps.xml ... ***************************************************************** ** ATTENTION ** ** ** ** Please note that the Security Patch Installation (Patch Deinstallation) is ** ** not complete until all the Post Installation (Post Deinstallation) ** ** instructions noted in the Readme accompanying this patch, have been ** ** successfully completed. ** ** ** ***************************************************************** ***************************************************************** Executing the Apply post-patch script (D:\5654905\custom\scripts\post.bat)... OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\5654905>cscript //nologo remove_demo.js ORACLE_HOME is set to D:\ORACLE\ORA92 This script will remove the Vulnerable OHS Demos %ORACLE_HOME%/Apache/Apache/fcgi-bin/echo* Please note that you will NOT be able to restore these demos after removing, if you may want to restore these demos, please manually backup all the files under %ORACLE_HOME%/Apache/Apache/fcgi-bin/ directory, and then rerun remove_demo.js Continue to remove the Vulnerable OHS Demos? Please respond Y|[N] => Y Removing the Vulnerable OHS Demos... Patch Installation Script Completed -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- |
4.2.2. Patch the Databases (run for each database if multiple instances running)
4.2.2.1. Database: TESTORCL
C:\>net start OracleOra92TNSListener |
Shutdown and Restart TESTORCL database, and start ArcSDE Service esri_sde
SQL> shutdown immediate; |
Testing: Using ArcCatalog, connect to Vector (esri_sde) and see if the connection succeeds and able to view the layers. Tested and it worked.
4.3. Patch # 5047902 [Oracle JVM TimeZone - Interim Patch for bug 5047902]
4.3.1. Shutdown all ArcSDE Services and Oracle Databases [as described in section 3.2 through 3.4].
4.3.2. Move file "orajox9java_util.dll" from "D:\oracle\ora92\javavm\admin" to some other (backup) folder and ensure that the file is backed up and not present in "ORACLE_HOME\javavm\admin" folder.
4.3.3. Patch the Oracle Home (Run only once for an Oracle Home):
D:\5654905>cd ../5047902 |
4.3.4. Startup Oracle Databases and respective ArcSDE Services [use section 4.2.2.1. for startup command usage] and below Create JAVA in each databases.
D:\oracle\ora92\rdbms\admin>sqlplus "sys/<password>@TESTORCL as sysdba" |
4.3.5. Make ArcSDE and Oracle Services "Startup Type" to "Automatic" if they were originally "Automatic" (refer section 3.1).
5. Final Testing: Shutdown ArcSDE Services and Oracle Databases, reboot the machine, once the machine comes up, startup Oracle Database and ArcSDE Services and connect it from ArcCatalog and other front end applications.
Part 2 - Install DST Patches in Oracle 9.2.0.7 Client Machines
1. Backup/move (create a folder, example "old_28Feb2007") the existing "timezlrg.dat" and "timezone.dat" files present in "ORACLE_HOME\oracore\zoneinfo" to the new folder ("old_28Feb2007").
2. Locate the new "timezlrg.dat", "timezone.dat" and "readme.txt" in the "...5654905\files\oracore\zoneinfo" folder where Patch 5654905 is unzipped (or copy it from the DB server where the patch has already been installed) and copy it to "ORACLE_HOME\oracore\zoneinfo".
| Author: | Babu Rangasamy |
| Title: | GIS and Database Consultant |
| Created Date: | March 1, 2007 |
| Contact: | Babu.Rangasamy[at]gmail.com |
Thursday, February 8, 2007
Impact of DST 2007 (Day Light Saving) on Oracle Databases
Daylight Saving Time (DST) - USA - 2007
Introduction: As per the Energy Policy Act of 2005, DST will start from 2 a.m. on the 2nd Sunday in March and end at 2 a.m. on First Sunday in November [Earlier it used to start at 2 a.m. 1st Sunday in April and end at 2 a.m. Last Sunday in October].
Spring Forward: On 2nd Sunday in March, clock will leap ahead from 1:59 a.m. to 3:00 a.m.
Fall Back: On 1st Sunday in November, clock will move back from 1:59 a.m. to 1:00 a.m.
Countries Affected: Canada and Bermuda are making similar changes to be consistent with USA time changes. Computers and applications running in other countries (who don't follow DST) having associated with computers and applications running in the DST implemented counties could also be affected.
Operating System: Once the required patch is applied, the system clock will automatically adjust its system time at the beginning and end of DST. Any dependent applications running on the OS will obtain the system time from the system clock.
Microsoft Daylight Saving Time: Help and Support Center
How it affects the Oracle Database?: Time zone and DST are used in Oracle database at the below two areas. Hence 2 patches are available, 1 for Time zone files and other for OJVM.
[1] Time Zone: Applicable only for Oracle 9.x and 10.x. There are two datatypes TIMESTAMP WITH LOCAL TIME ZONE [TSLTZ] and TIMESTAMP WITH TIME ZONE [TSTZ] and a function TZ_OFFSET which obtains their time-zone information from Oracle's time-zone file which comes as a part of Oracle database installation.
[1a] Database Server Side:
If your database uses any of the above (datatypes or/and functions) and uses the affected time zones, then patch is required, otherwise patch is not necessary. A script is available to assess the use of time zone in the database.
[1b] Client Side: If all the below conditions are met, then time zone file install is required:-
- connects to the affected database
- time zone data type used by client
- client uses the affected time zone
Refer Below Metalink documents:-
- Effects on client and middle-tier of applying time zone patches on the Oracle Database Note:396426.1
- Workarounds when Database time zone patches are not available for your patchset Note:396387.1
[2] Oracle Java Virtual Machine (OJVM) uses time-zone information which is stored in its database. Need to install the patch only on the database side if OJVM is installed, applicable for Oracle database versions from 8.1.7.4 to 10.2.0.3.
Here is a typical (9.2) query to check whether OJVM is installed in your database:-
| column owner format a10 SELECT object_type, owner, status, COUNT(*) FROM dba_objects WHERE object_type LIKE '%JAVA%' GROUP BY object_type,owner,status; OBJECT_TYPE OWNER STATUS COUNT(*) ------------------ ---------- ------- ---------- JAVA DATA SYS VALID 288 JAVA CLASS SYS VALID 9080 JAVA CLASS SYS INVALID 1 JAVA CLASS ORDSYS VALID 870 JAVA CLASS ORDSYS INVALID 12 JAVA SOURCE SYS VALID 8 JAVA SOURCE ORDSYS VALID 8 JAVA RESOURCE SYS VALID 180 JAVA RESOURCE ORDSYS VALID 16 9 rows selected |
Refer to Metalink Note:397770.1 [How to Correctly Check if the Oracle JVM is Installed in the Database].
[3] Here are the things that you got to do:-
[3a] Backup your client and server.
[3b] Run re_tz_views.sql [Refer to Metalink Note:412971.1 - SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a
Database]
| @cre_tz_views.sql spool cre_tz_views_output.txt select * from TZ$TSLTZ_TAB_COLS; spool off drop function TZ$NAMED_TSTZ_VW_COLS_FN ; |
| DBTIMEZONE ---------- -04:00 USAGE_TYPE VALUE DESCRIPTION --------------------------- ---------- ------------------------------------------------------------------------ DBTIMEZONE -04:00 Database Time Zone, if this is an 'offset' then not affected by DST NAMED_TSTZ_TABLE_USE_NONSYS 0 Number of TSTZ columns using named time zones in tables not owned by SYS NAMED_TSTZ_TABLE_USE_SYS 0 Number of TSTZ columns using named time zones in tables owned by SYS NAMED_TSTZ_VIEW_USE_NONSYS 0 Number of TSTZ columns using named time zones in views not owned by SYS NAMED_TSTZ_VIEW_USE_SYS 0 Number of TSTZ columns using named time zones in views owned by SYS TSLTZ_TABLE_USE 0 Number of TSLTZ columns used in tables TSLTZ_VIEW_USE 0 Number of TSLTZ columns used in views TZ_ARGUMENTS 0 Number of PL/SQL objects with Time Zone arguments OWNER NAME TYPE LINE TEXT --------------- ------------------------------ ------------ ---------- ------------------------------------------------------------------------------------------- BABU TESTXSP_STORE_DDL PROCEDURE 25 updatedate=systimestamp,RESOURCE_TYPE=IN_RESOURCE_TYPE BABU TESTXSP_STORE_DML PROCEDURE 39 IN_BABU_SERVICE,IN_SERVICE_TYPE,IN_ONLINK,IN_CONTENT_TYPE,systimestamp, IN_RESOURCE_TYPE ); BABU CREATE_ONLINE_DOCUMENT PROCEDURE 13 insert into TEST_TAB (ACTTIME) values (systimestamp); BABU_RW B_UPDATE_APPL_ALERT_UPDATE TRIGGER 5 SELECT SYSTIMESTAMP INTO :NEW.LAST_UPDATE_STARTED FROM dual; BABU_ADMIN B_INSERT_SERVICE_TXT TRIGGER 13 SELECT SYSTIMESTAMP INTO :NEW.TIME_STAMP FROM dual; BABU_ADMIN B_UPDT_WEB_SERVICES_TXT TRIGGER 2 select sysdate into :new.timestamp from dual; BABU_ADMIN B_INSRT_WEB_SERVICES_TXT TRIGGER 2 select sysdate into :new.timestamp from dual; BABU_ADMIN PKG_SERVICE PACKAGE BODY 18 || 'TO_TIMESTAMP(''' || start_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')' || ' AND ' BABU_ADMIN PKG_SERVICE PACKAGE BODY 19 || 'TO_TIMESTAMP(''' || end_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')' |
[3c] Identify the correct "utltzuv2.sql" [this script only checks stored TIMESTAMP WITH TIME ZONE data in the database, but NOT the TIMESTAMP WITH LOCAL TIME ZONE data], download it [Refer Table 1 in Metalink Note:359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database] and run the "utltzuv2.sql" to check the if TSTZ datatype is used in any tables in the database.
For 9.2.0.7, download Patch and install patch 5548107 (also to get the utltzuv2.sql script).
Refer to "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note:396670.1"
| SQL> @utltzuv2.sql You current timezone version is 1! Query sys.sys_tzuv2_temptab table to see if any TIMEZONE data is affected by version 3 transition rules PL/SQL procedure successfully completed. |
[3d] Save TSTZ data as varchar2.
[3e] Apply patch [Refer Table 2 and 3 in Metalink Note:359145.1
- Impact of 2007 USA daylight saving changes on the Oracle database]
[For 9.2.0.7 DB TimeZone, use Patch bundle 15 (bug 5654905) or later]
[For 9.2.0.7 OJVM, use JVM Timezone Patch 5047902]
[3f] Restore back to TSTZ datatype
[3g] Apply fixes to the clients and middle-tiers.
[3h] Work on already existing TSLTZ data.
[3i] Apply patch for OJVM.
References:
1. Impact of 2007 USA daylight saving changes on the Oracle database - Note:359145.1
2. How to Determine Whether Time Zone Changes Will Affect Your Database - Note:406410.1
3. SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a Database - Note:412971.1
4. USA 2007 Daylight Saving Time (DST) Compliance for Oracle Server Technologies Products Note:397281.1
5. Timestamps & time zones - Frequently Asked Questions Note:340512.1
Click here for a step-by-step documentation on "Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines"
| Author: | Babu Rangasamy |
| Title: | GIS and Database Consultant |
| Created Date: | February 8, 2007 |
| Contact: | Babu.Rangasamy[at]gmail.com |
Thursday, December 7, 2006
Oracle Shared Server (Erstwhile Multi-Threaded Server) and ORA-4031 Error on Large Pool
Basic MTS Configuration:- Include the following 2 parameters in the init.ora:-
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=db_server_ip_address))(DISPATCHERS=2)"
SHARED_SERVERS = 20
Note:
DISPATCHERS (Required to set): Oracle recommends a ratio of 1 DISPATCHER for every 1000 connections,
here I have allocated 2 DISPATCHERS to be on the safer side and it can handle up to 2000 connections.
SHARED_SERVERS (Optional): Database server uses 1 shared server for every 20 connections, so 20 will support 200 sessions, which is fair enough for this database requirement.
Troubleshooting: ORA-4031 ErrorsReceived the below ORA-4031 Error at the client side:-ORA-4031: unable to allocate %s bytes of
shared memory ("%s","%s","%s","%s")Noticed following error in the Alert.log of a dedicated server (non
multi-threaded server), where a job runs and used a database link, which is
connect to a multi-threaded server:-ORA-04031: unable to allocate 64 bytes of
shared memory ("large pool","unknown object","session heap","trigger condition
node")No errors noted in the Alert.log of MTS server, database functioning
normal, accepting user connections, but following were noted in the trace files
(SID_s001_2076.trc) of user_dump_dest:-Dump file f:\oracle\admin\<sid>\udump\<sid>_s000_2056.trc
Thu Nov 09 19:35:43 2006
ORACLE V9.2.0.6.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: <sid>
Redo thread mounted by this instance: 1
Oracle process number: 10
Windows thread id: 2056, image: ORACLE.EXE
*** 2006-11-09 19:35:43.048
*** SESSION ID:(16.4) 2006-11-09 19:35:43.001
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=11/09/2006 19:35:41
Allocation request for: sort key
Heap: 58B34B04, size: 8192
******************************************************
HEAP DUMP heap name="large pool" desc=02170098
extent sz=0xfc4 alt=100 het=32767 rec=9 flg=-126 opc=0
parent=00000000 owner=00000000 nex=00000000 xsz=0x0
******************************************************
HEAP DUMP heap name="sort subheap" desc=58B34B04
extent sz=0x2024 alt=32767 het=32767 rec=0 flg=2 opc=2
parent=55F9F3AC owner=00000000 nex=00000000 xsz=0x1
Subheap has 1871424 bytes of memory allocated
====================
Process State Object
====================
----------------------------------------
SO: 5A59A1A4, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=10, calls cur/top: 5A705014/5A705014, flag: (40) MS SERVER
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 139
last post received-location: kmcmbf: not KMCVCFTOS
last process to post me: 5a59f358 1 128
last post sent: 0 0 146
last post sent-location: kmcpdp
last process posted by me: 5a59f358 1 128
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 5A5F8F10
O/S info: user: SYSTEM, term: <server_host_name>, ospid: 2056
OSD pid info: Windows thread id: 2056, image: ORACLE.EXE
=========================
User Session State Object
=========================
----------------------------------------
SO: 5A6068B4, type: 4, owner: 5BAE7D58, flag: INIT/-/-/0x00
(session) trans: 00000000, creator: 5BAE7D58, flag: (1000e5) USR/- BSY/-/-/-/-/-
DID: 0001-000B-00000008, short-term DID: 0000-0000-00000000
txn branch: 00000000
oct: 3, prv: 0, sql: 67908120, psql: 67908120, user: 50/RTT
O/S info: user: <OS User>, term: <Client_Hostname>, ospid: 2892:2560, machine:
<domain>\<hostname>
program: <program_name>
application name: <app name>, hash value=0
last wait for 'SQL*Net message to client' blocking sess=0x0 seq=24275 wait_time=6
driver id=4d545300, #bytes=1, =0
temporary object counter: 0
...No current library cache object being loaded
===========================
Current Instatiation Object
===========================
-------------------------------------
INSTANTIATION OBJECT: object=576F3410
type="cursor"[2] lock=659E4EF0 handle=67908120 body=00000000 level=0
flags=FST[60] executions=0
cursor name:
SQL Query...............
child pin: 5bd992b0, child lock: 5bdd363c, parent lock: 659e4ef0
xscflg: 80110476, parent handle: 67908120, xscfl2: 7a00009
Dumping Literal Information
Bind Pos: 8, Bind Len: 21, Bind Val:
nxt: 4.0x00000660 nxt: 3.0x00000470 nxt: 2.0x000007d0 nxt: 1.0x00000304
Cursor frame allocation dump:
frm: -------- Comment -------- Size Seg Off
bhp size: 172/560
whp size: 1887880/1895664
Dump of CURRENT WORK HEAP:
Since database is functioning normal and accepting user connections, its not
so critical to find an immediate solution, but the above heap in the trace file
causes some concern. Based on the Oracle's METALINK document
ORA-4031 from LARGE POOL when running with SHARED SERVER Configuration in 9i,
(ID: 223153.1), increased LARGE_POOL_SIZE from 70M to 300M, decreased
SHARED_POOL_SIZE & SHARED_POOL_RESERVED_SIZE from 200M & 20M to 150M & 15M
respectively. After the changes, init.ora will look like below:-SGA_MAX_SIZE=1400M
PGA_AGGREGATE_TARGET=600M
DB_16K_CACHE_SIZE=400M
DB_CACHE_SIZE=400M
LARGE_POOL_SIZE=300M
SHARED_POOL_SIZE=150M
SHARED_POOL_RESERVED_SIZE=15M
JAVA_POOL_SIZE=40M
HASH_AREA_SIZE=4M
SORT_AREA_RETAINED_SIZE=2M
SORT_AREA_SIZE=2M
DB_BLOCK_SIZE=8192
DB_FILE_MULTIBLOCK_READ_COUNT=16
DISPATCHERS='(ADDRESS=(PROTOCOL=TCP)(HOST=db_server_ip_address))(DISPATCHERS=2)'
LOG_BUFFER=1M
OPEN_CURSORS=300
QUERY_REWRITE_ENABLED='FALSE'
SESSION_CACHED_CURSORS=50
SESSIONS=340
PROCESSES=400
SHARED_SERVERS=20About LARGE_POOL: It is a part of SGA, doesn't use LRU list, not a part of SHARED_POOL. Used by
Oracle Shared Server (MTS) for UGA and RMAN if used. If LARGE_POOL is not set, then MTS will use memory from SHARED_POOL.
Use V$SGASTAT, V$SGA_DYNAMIC_COMPONENTS and V$SGA to find info about SGA and run the below query to check the summary of SGA:-
SELECT * FROM v$sgastat
WHERE pool ='large pool';
SELECT NVL(POOL,NAME) POOL_NAME,
ROUND(SUM(BYTES)/1024/1024,2) MBYTES
FROM V$SGASTAT GROUP BY NVL(POOL,NAME) ORDER BY 2 DESC;
COLUMN component FORMAT A15
SELECT component,
current_size/1024/1024 CURRENT_SIZE_MB,
granule_size/1024/1024 GRANULE_SIZE_MB
FROM v$sga_dynamic_components;
SELECT name,
ROUND((value)/1024/1024,2) MBYTES
FROM v$sga ORDER BY 2 DESC;
Note: Requires logon to access Oracle Metalink documents.Reference: Configuration
1)
Oracle9i Database Administrator's Guide - Configuring Oracle for the Shared Server - Chapter 5
2)
Net Services Administrator's Guide - Chapter 14 - Configuring Shared Server
3) Oracle Metalink Subject:
Multi-Threaded Server (MTS) Diagnostics, Doc ID: 1005259.6
Reference: Troubleshooting and Tuning
4) Oracle9i Database Performance Tuning Guide -
Chapter 14 - Memory Configuration and Use - Tuning the Large Pool and Shared
Pool for the Shared Server Architecture
5) Oracle9i Database Performance Tuning Guide -
Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool
6) Oracle Metalink Subject:
ORA-4031 from LARGE POOL when running with SHARED SERVER Configuration in 9i,
Doc ID: 223153.1
7) Oracle Metalink Subject:
Diagnosing and Resolving Error ORA-04031, Doc ID:146599.1
8) Oracle Metalink Subject:
Fundamentals of the Large Pool, Doc ID:62140.1
9) Oracle Metalink Subject:
ORA-4031 AND ORA-600 [1113] USING MTS AND LARGE_POOL_SIZE, Doc ID:1058003.6
Tuesday, December 5, 2006
Caching Objects in Shared Pool
This document is about KEEPing frequently used database objects like PROCEDURES, PACKAGES, SEQUENCES, TRIGGERS and CURSORS in the SHARED_POOL of SGA. Caching/KEEPing frequently
used objects in SHARED POOL will help improve the database performance.
Tested Environment: Oracle 9.2, Windows 2000 Server.
Connect to the database as sys:-
SQL> connect sys/**** as sysdba
Step#1: Create a table sp_obj_tab to hold the objects that need to be cached:-
SQL> CREATE TABLE sys.sp_obj_tab (
objownr VARCHAR2(64),
objname VARCHAR2(1000),
objtype CHAR(1),
listdte DATE,
keepdte DATE,
remarks CHAR(30));
Step#2: Take a look at script shared_pool_mgmt.SQL and run it, which will create a package called shared_pool_mgmt:-
SQL> @shared_pool_mgmt.SQL
Step#3: Create the below triggers which runs during database startup and shutdown:-
SQL> CREATE OR REPLACE TRIGGER db_shutdown_sp_obj_list
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.SHARED_POOL_MGMT.list_obj_table;
END;
/
SQL> CREATE OR REPLACE TRIGGER db_startup_sp_obj_keep
AFTER STARTUP ON DATABASE
BEGIN
sys.SHARED_POOL_MGMT.keep_obj_startup;
END;
/
Step#4: Schedule a dbms job that runs at regular intervals, the below once runs at 5 pm every day:-
SQL> VARIABLE JOBNO NUMBER;
SQL> BEGIN
DBMS_JOB.SUBMIT(JOB=>:JOBNO,
WHAT=>'shared_pool_mgmt.keep_obj_periodic;',
NEXT_DATE=>TRUNC(SYSDATE)+1+17/24,
INTERVAL=>'TRUNC(SYSDATE)+1+17/24');
COMMIT;
END;
/
SQL> PRINT JOBNO;
Below is the script, mentioned in Step#2, copy and paste it in a file called shared_pool_mgmt.SQL :-
CREATE OR REPLACE PACKAGE shared_pool_mgmt AS
---------------------------------------------------
---- Name: SHARED_POOL_MGMT
---- Schema: SYS
---- Created by: BabuMani
---- Created on: Dec-05-2006
-- Description: This package helps database
-- administrators to KEEP frequently used database
-- objects like PROCEDURES, PACKAGES, SEQUENCES,
-- TRIGGERS and CURSORS in the SHARED_POOL of SGA.
---------------------------------------------------
PROCEDURE list_obj_table;
-- PROCEDURE LIST_OBJ_TABLE: Identifies the top 10
-- objects based on various system resource
-- consuming factors and writes them in a table
-- called SP_OBJ_TAB. This table is populated
-- during database shutdown. This procedure is run
-- using database shutdown trigger (BEFORE
-- SHUTDOWN ON DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_startup;
-- PROCEDURE KEEP_OBJ_STARTUP: Reads the objects
-- listed in table SP_OBJ_TAB during database
-- startup and KEEP them (except CURSORS)in
-- SHARED_POOL. This procedure is run using
-- database startup trigger (AFTER STARTUP ON
-- DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_periodic;
-- PROCEDURE KEEP_OBJ_PERIODIC: It refreshes the
-- list of objects in table SP_OBJ_TAB and KEEP
-- them (including CURSORS) in SHARED_POOL.
-- Suggested to run this procedure at scheduled
-- intervals.
---------------------------------------------------
END shared_pool_mgmt;
/
CREATE OR REPLACE PACKAGE BODY shared_pool_mgmt AS
PROCEDURE list_obj_table IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM sys.sp_obj_tab;
COMMIT;
INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
SELECT DISTINCT owner,name,DECODE(type,'TRIGGER','R','P'),SYSDATE,'Listed to Keep in Shared Pool'
FROM v$db_object_cache
WHERE type IN ('PACKAGE','PROCEDURE','PACKAGE BODY','TRIGGER')
AND loads >= 1 AND executions > 0
UNION
SELECT DISTINCT owner,name,'Q',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$db_object_cache
WHERE type = 'SEQUENCE'
AND loads >= 1 ;
INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY executions DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY loads DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY buffer_gets DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY disk_reads DESC)
WHERE ROWNUM<=10
UNION
SELECT * FROM
(SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
FROM v$sqlarea
WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
AND loads > 0
AND executions > 0
ORDER BY sharable_mem DESC)
WHERE ROWNUM<=10;
COMMIT;
END list_obj_table;
PROCEDURE keep_obj_startup IS
PRAGMA AUTONOMOUS_TRANSACTION;
own VARCHAR2(64);
nam VARCHAR2(1000);
typ VARCHAR2(1);
CURSOR cur1 IS
SELECT objownr, objname, objtype FROM SYS.sp_obj_tab WHERE objtype<>'C';
BEGIN
OPEN cur1;
LOOP
FETCH cur1 into own, nam, typ;
EXIT WHEN cur1%NOTFOUND;
SYS.DBMS_SHARED_POOL.KEEP(''|| own || '.' || nam || '',''||typ||'');
UPDATE sys.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
COMMIT;
END LOOP;
CLOSE cur1;
END keep_obj_startup;
PROCEDURE keep_obj_periodic IS
PRAGMA AUTONOMOUS_TRANSACTION;
own VARCHAR2(64);
nam VARCHAR2(1000);
typ VARCHAR2(1);
CURSOR cur2 IS
SELECT objownr, objname, objtype FROM SYS.sp_obj_tab;
BEGIN
shared_pool_mgmt.list_obj_table;
OPEN cur2;
LOOP
FETCH cur2 INTO own, nam, typ;
EXIT WHEN cur2%NOTFOUND;
IF typ='C' THEN
SYS.dbms_shared_pool.keep(''|| own ||'',''||typ||'');
ELSE
SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '',''||typ||'');
END IF;
UPDATE SYS.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
COMMIT;
END LOOP;
CLOSE cur2;
END keep_obj_periodic;
END shared_pool_mgmt;
/
Below are some Handy-Dandy queries:-
COLUMN pool FORMAT A25
SELECT pool||(' -- Total') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool
UNION
SELECT pool||(' --- Free') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE name='free memory' AND pool IS NOT NULL GROUP BY pool;
SELECT type,COUNT(*) COUNT FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;
SELECT DECODE(objtype,'R','Trigger','P','Package','C','Cursor','Q','Sequence',objtype) OBJTYPE, COUNT(*) COUNT FROM sp_obj_tab GROUP BY objtype ORDER BY 2 ;
COMPUTE SUM LABEL 'TOTAL ' OF COUNT ON REPORT
COMPUTE SUM LABEL 'TOTAL ' OF SHARABLE_MEM_K ON REPORT
BREAK ON REPORT
SELECT type,COUNT(*) COUNT, round(sum(SHARABLE_MEM)/1024) SHARABLE_MEM_K FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;
Library Cache Hit Ratio:
COMPUTE SUM LABEL 'TOTAL' OF PINHITS ON REPORT
COMPUTE SUM LABEL 'TOTAL' OF PINS ON REPORT
BREAK ON REPORT
SELECT namespace
, pins
, pinhits
, reloads
, invalidations
FROM v$librarycache
ORDER BY namespace;
CLEAR COMPUTE
CLEAR BREAK
SELECT sum(pinhits) pinhits,
sum(pins) pins,
round((sum(pinhits)/sum(pins))*100,2) LIBRARY_CACHE_HIT_RATIO
FROM v$librarycache
ORDER BY namespace;
Dictionary Cache (Row Cache) Hit Ratio:
column parameter format a25
column RATIO format 999.9
column UPDATES format 999,999,999
SELECT parameter
, SUM(gets) gets
, SUM(getmisses) getmisses
, 100*SUM(gets - getmisses) / SUM(gets) RATIO
, SUM(modifications) UPDATES
FROM v$rowcache
WHERE gets > 0
GROUP BY parameter;
SELECT ROUND((SUM(gets - getmisses - fixed)) / SUM(gets)*100,2) "ROW CACHE" FROM v$rowcache;
Reference:
Oracle9i Database Performance Tuning Guide and Reference - Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool