Oracle Data Dictionary Conclusion

The statements you've provided remain valid for Oracle Database 19c, with some additional context:

1. V$ Views Built Upon X$ Views: The V$ views, also known as dynamic performance views, are indeed built upon the underlying X$ tables. These V$ views provide information about the current state of the database, facilitating monitoring and performance analysis.
2. Public-Domain Scripts for Oracle Performance Snapshots: Numerous publicly available scripts can be utilized to capture snapshots of Oracle performance metrics. These scripts often query V$ views to extract relevant performance data.
3. Alert Scripts for Monitoring Multiple Databases: Experienced DBAs frequently employ alert scripts to monitor numerous Oracle databases efficiently. These scripts can automate the process of checking database health and performance, enabling the management of large numbers of databases.
4. Identical Results from Different Coding Techniques: It's common to achieve the same results using various coding methods in Oracle. For instance, different SQL queries or PL/SQL approaches can yield the same dataset, though their performance characteristics may vary.
5. Statistics Listed in V$SYSSTAT View: The V$SYSSTAT view provides a comprehensive list of system statistics, including metrics such as the data buffer hit ratio, the number of disk sorts, and the number of continued row fetches. These statistics are crucial for assessing database performance and are accessible through this view.

In summary, the statements you've mentioned are applicable to Oracle Database 19c, with the provided explanations offering additional clarity.

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.
Kernel Services

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:
• Hugemem Kernel:
  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.

To modernize these statements for Oracle 19c running on Ubuntu 20.04 LTS x64, you would adapt them to reflect the current kernel architectures and configurations available on Ubuntu and Oracle 19c's compatibility. Here's the updated version:

1. Ubuntu 20.04 x64 and Kernel Memory Management
   • Ubuntu 20.04 LTS x64 uses a 64-bit Linux kernel that supports high memory addressing without the need for a separate hugemem kernel.
   • Modern 64-bit kernels handle memory management efficiently, providing stable and scalable performance for systems with more than 4 GB of RAM.
2. Memory Limits and Usage on Ubuntu 20.04
   • The 64-bit architecture supports vast address spaces, eliminating the 3GB user process/1GB kernel split limitation found in older 32-bit systems.
   • Systems running Ubuntu 20.04 can utilize terabytes of RAM depending on hardware capabilities and kernel configurations, removing the need for PAE (Physical Address Extension) or hugemem-like solutions.
3. Low Memory (Lowmem) Concerns
   • Lowmem-related issues have largely been mitigated in 64-bit environments.
   • For memory-intensive workloads, it is recommended to configure HugePages in Oracle 19c.
   • HugePages reduce Translation Lookaside Buffer (TLB) overhead and improve database performance by allocating large memory pages for the Oracle SGA (System Global Area).
4. Recommendations for Oracle 19c on Ubuntu 20.04
   • Use a 64-bit Ubuntu kernel for maximum memory utilization and stability.
   • Enable and configure HugePages for optimal Oracle database performance when working with large memory footprints.
   • Verify kernel configurations to ensure the system is optimized for database workloads.
5. Checking HugePages Configuration
   • To determine if HugePages are enabled and configured on your system, run the following command:

   • grep Huge /proc/meminfo

   • This will display the HugePages settings, including the number of HugePages reserved and free.
   • For optimal Oracle 19c performance, ensure sufficient HugePages are allocated based on your SGA size.
6. Monitoring Kernel and Memory Usage
   • Use free -h or vmstat to monitor memory usage on the system.
   • These tools provide insights into how memory is being utilized, ensuring your database instance has sufficient resources.

By transitioning to a 64-bit architecture and modern Ubuntu kernel, the need for legacy solutions like hugemem is eliminated, and performance is significantly enhanced for Oracle 19c workloads.

$ uname -r
2.6.9-5.0.3.ELhugemem

We looked closely at using the alert monitor against the V$ views, and creating a script that can be used to alert the DBA to pending performance problems.
Now you should be able to:

1. Identify the main V$ views that monitor database performance
2. Identify the main DBA views that can be interrogated for the status of the database
3. Know the primary table used to monitor disk sorts, memory usage and SGA events
4. Identify the critical events that warrant attention, and the queries to alert the condition
5. Describe a method to notify the DBA about a pending performance problem

About Static Data Dictionary Views Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:

1. An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
2. A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
3. A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views. The columns of the ALL_, DBA_, and USER_ views corresponding to a single data dictionary table are usually nearly identical. Therefore, these views are described in full only once in this chapter, at their first occurrence alphabetically, and are listed without full descriptions at their other occurrences.

Here are some terms used in this module that you may choose to review:

1. crontab: A crontab in Unix is a file that contains a list of commands that are to be executed periodically. The crontab file is managed by the cron daemon, which is a program that runs in the background and checks the crontab file for new or changed entries.
2. V$ views: V$ views, or dynamic performance views, are a set of views in Oracle that provide information about the performance of the database instance. They are prefixed with the letter "V$".
3. X$views: X$ views, or dynamic system views, are a set of views in Oracle that provide information about the internal structures of the database. They are prefixed with the letter "X$".

The following sections describe how to create, edit, display, and remove crontab files, as well as how to control access to them.

• Inside a crontab File The cron daemon schedules system tasks according to commands found within each crontab file. A crontab file consists of commands, one command per line, that will be executed at regular intervals. The beginning of each line contains date and time information that tells the cron daemon when to execute the command. For example, a crontab file named root is supplied during SunOS software installation. The file's contents include these command lines:
  

  10 3 * * * /usr/sbin/logadm (1)
  15 3 * * 0 /usr/lib/fs/nfs/nfsfind (2)
  1 2 * * * [ -x /usr/sbin/rtc ] && /usr/sbin/rtc -c > /dev/null 2>&1 (3)
  30 3 * * * [ -x /usr/lib/gss/gsscred_clean ] && /usr/lib/gss/gsscred_clean (4)
  

  
  The following describes the output for each of these command lines:
  1. The first line runs the logadm command at 3:10 a.m. every day.
  2. The second line executes the nfsfind script every Sunday at 3:15 a.m.
  3. The third line runs a script that checks for daylight savings time (and make corrections, if necessary) at 2:10 a.m. daily.
  If there is no RTC time zone, nor an /etc/rtc_config file, this entry does nothing.

Before we go on to discuss Oracle table partitioning, click the Quiz link below to complete a final quiz for this module.
DBA Performance Tools - Quiz
The next module will look at tuning with Oracle8 table partitioning.