V-Views Built Upon X-Views
- The V$ views are built upon the X$ views; Oracle provides V$ views to capture information about the overall database status.
- There are many public-domain scripts that can be used to get a snapshot of Oracle performance.
- Using an alert script, the savvy DBA can monitor thousands of Oracle databases
- It is common to see identical results come from different coding techniques.
- The V$SYSSTAT view lists the following statistics; they include data buffer hit ratio, the number of disk sorts and the number of continued row fetches.
Querying the X$ structures
One of the most hidden areas of the Oracle database is the X$ structures.
The X$ structures are the innermost tables that are used to build the common v$ views that we use to query the data dictionary. Here is one way of naming these X$ structures.
These internal structures change between Oracle releases, and this list is from Oracle 8.1.5.
x$ksmfs kernel services, memory fixed SGA
x$ksmfsv kernel services, memory fixed SGA vectors
x$ksmjs kernel services, memory java_pool summary
x$ksmlru kernel services, memory LRU
x$ksmls kernel services, memory large_pool summary
x$ksmmem kernel services, memory
x$ksmpp kernel services, memory process pool
x$ksmsd kernel services, memory SGA definition
x$ksmsp kernel services, memory shared pool
x$ksmspr kernel services, memory shared pool reserved
x$ksmss kernel services, memory shared_pool summary
x$ksmup kernel services, memory user pool
x$ksqst kernel services, enqueue status
x$ksulop kernel services, user long operation
x$ksulv kernel services, user locale value
x$ksupr kernel services, user process
Methods To Increase SGA Limits
In a typical 32-bit Linux kernel, one can create an SGA of up to 1.7GB size. Using a Linux Hugemem kernel enables the creation of an SGA of upto 3.6GB size. To go beyond 3.6GB on a 32-bit kernel, you must use the VLM feature.
The following are the methods to increase SGA limits on a 32-bit computer:
Red Hat Enterprise Linux 4 and Oracle Enterprise Linux 4 include a new kernel known as the Hugemem kernel. The Hugemem kernel feature is also called a 4GB-4GB Split Kernel as it supports a 4GB per process user space (versus 3GB for the other kernels), and a 4GB direct kernel space. Using this kernel enables RHEL 4/OEL 4 to run on systems with up to 64GB of main memory. The Hugemem kernel is required to use all the memory in system configurations containing more than 16GB of memory. The Hugemem kernel can run configurations with less memory. A classic 32-bit 4GB virtual address space is split 3GB for user processes and 1GB for the kernel. The new scheme (4GB/4GB) permits 4GB of virtual address space for the kernel and almost 4GB for each user process. Due to this scheme with hugemem kernel, 3.6GB of SGA can be created without using the indirect data buffer method.
Red Hat Enterprise Linux 5/ Oracle Enterprise Linux 5 on 32-bit does not have the hugemem kernel. It supports only the 3GB user process/ 1GB kernel split. It has a PAE kernel that supports systems with more than
4GB of RAM and reliably upto 16GB. Since this has a 3GB/1GB kernel split, the system may run out of lowmem if the system's load consumes lots of lowmem. There is no equivalent kernel for hugemem in Enterprise Linux 5 and one is recommended to either use Enterprise Linux 4 with hugemem or go for 64-bit.
The Hugemem kernel on large computers ensures better stability as compared to the performance overhead of address space switching.
Run the following command to determine if you are using the Hugemem kernel:
$ uname -r
Hugemem Kernel with Very Large Memory
If you use only Hugemem kernels on 32-bit systems, then the SGA size can be increased but not significantly.