ASMFD - Troubleshooting ASMCMD-9513

 I was converting my Oracle ASM disk management from udev rules to ASM Filter Driver (ASMFD) and encountered the below errors which might happen during the conversion operation and this post might help you fix the issue as quickly as possible. 

You can read more on Oracle ASMFD, configuration and migrating to ASMFD using this 19c documentation

1. Wrong/incorrect afd_diskstring value

[root@ol819-rac1 ~]#
[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_label asmdisk1 /dev/sdb1 --migrate
No devices to be labeled.
ASMCMD-9513: ASM disk label set operation failed.

Since my disks are already pointing to /dev/oracleasm due to the udev rules, ASMFD config file picked up the afd_diskstring parameter picked up this value by default and hence the issue. Edit the /etc/oracleafd.conf config file and retry the operation

[root@ol819-rac1 ~]# cat /etc/oracleafd.conf
afd_diskstring='/dev/oracleasm/*'
[root@ol819-rac1 ~]# vi /etc/oracleafd.conf
[root@ol819-rac1 ~]# cat /etc/oracleafd.conf
afd_diskstring='/dev/sd*'
[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_label asm-disk1 /dev/sdb1 --migrate
Invalid label string : asm-disk1
ASMCMD-9513: ASM disk label set operation failed.

[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_label asmdisk1 /dev/sdb1 --migrate
[root@ol819-rac1 ~]#
[root@ol819-rac1 ~]# $ORACLE_HOME/bin/asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
ASMDISK1                    ENABLED   /dev/sdb1
[root@ol819-rac1 ~]# ls -al /dev/oracleafd/disks
total 4
drwxrwxr-x. 2 oracle dba      60 Jan 18 10:28 .
drwxrwx---. 3 oracle dba      80 Jan 18 09:45 ..
-rw-rw-r--. 1 oracle oinstall 10 Jan 18 10:28 ASMDISK1
[root@ol819-rac1 ~]# 

Note: You can see we get the Invalid label string when provided the disk name as asm-disk1. hyphen (-) is not allowed in the disk name. If needed we can use underscore (_). I left it without any special characters. 

2. Retry label after wrong path

I used a wrong path value during label operation by providing the pointer instead of actual disk path and tried to relabel again causing ASM-9513 

We can use the kfod command to check the label details of the ASM disk. 

[root@ol819-rac1 ~]# asmcmd afd_label asmdisk1 /dev/sdb1 --migrate
label name ASMDISK1 is already in use
ASMCMD-9513: ASM disk label set operation failed.

[root@ol819-rac1 ~]# kfod OP=DISKS status=TRUE disks=ASM label=TRUE dscvgroup=TRUE
--------------------------------------------------------------------------------
 Disk          Size Header    Path                                    Disk Group         Site      Failgroup           Disk-Label User     Group
================================================================================
   1:      25599 MB MEMBER    /dev/oracleasm/asm-disk1                 DATA                 #              #             ASMDISK1 oracle   dba
[root@ol819-rac1 ~]# 

In the above you can see the path is /dev/oacleasm/asm-disk1 with the Disk-label set to ASMDISK1. This might work now as I still have udev rules in place so the pointer path would work. Once I remove the udev rules, this path will not exist and would cause issues in identifying disks and my cluster won't come up as the disk contains the voting disk as well

[root@ol819-rac1 ~]# asmcmd afd_unlabel asmdisk1
disk AFD:ASMDISK1 is already provisioned for ASM
No devices to be unlabeled.
ASMCMD-9514: ASM disk label clear operation failed.

[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# afdtool -delete '/dev/oracleasm/asm-disk1'
disk /dev/oracleasm/asm-disk1 is already provisioned for ASM
No devices to be unlabeled.
[root@ol819-rac1 ~]# afdtool -delete -f '/dev/oracleasm/asm-disk1'
Unlabeled device ASMDISK1
[root@ol819-rac1 ~]# 
[root@ol819-rac1 ~]# kfod OP=DISKS status=TRUE disks=ASM label=TRUE dscvgroup=TRUE
--------------------------------------------------------------------------------
 Disk          Size Header    Path                                    Disk Group         Site      Failgroup           Disk-Label User     Group
================================================================================
   1:      25599 MB MEMBER    /dev/oracleasm/asm-disk1                 DATA                 #              #                    # oracle   dba
[root@ol819-rac1 ~]# 

afd_unlabel fails with ASMCMD-9514 stating disk already provisioned for ASM. This means afd_label command not only labels the disk but also includes it in the list of disks managed by AFD. So AFD does not allow altering the label for a disk that is currently being managed by AFD.

We can use afdtool command to delete the label in this case. 

After unlabeling the disk, we can see the kfod command has null value for Disk-label for the disk. 

We can then use afd_label command to label the disk using the proper path as shown in this post above.

References:

 

ASMCMD AFD_LABEL --RENAME FAILS (Doc ID 2884565.1)

ASMCMD-9513: Asmcmd Afd_label DATADG /dev/sdc1 --migrate (Doc ID 2308860.1)

Happy Troubleshooting...!!!

Linux Huge Pages and AUTO_ONLY option of Oracle 19c

HugePages is a feature integrated into the Linux kernel. A page is the basic unit of virtual memory, with the default page size being 4096 Bytes in the x86 architecture. Linux uses a mechanism in the CPU architecture called "Translation Lookaside Buffers" (TLB) to manage the mapping of virtual memory pages to actual physical memory addresses. So, when each time memory is accessed using the default page size, there is additional overhead as many TLB resources are consumed.

With the implementation of Huge Pages, we are grouping these small pages into a bigger chunk with consuming a single resource in TLB. Huge pages are of size 2MB (default) or 1GB (called as gigantic pages and can be configured). 

For Oracle Database, using HugePages reduces the operating system maintenance of page states and increases Translation Lookaside Buffer (TLB) hit ratio. 

Configuring Huge pages: 

There are a few conditions to be met before configuring huge pages. 

1. Linux kernel should support huge pages. 

2. Edit the memlock settings in /etc/security/limits.conf file. It is advisable to set the maximum locked memory limit to at least 90 percent of the current RAM when you enable HugePages memory. 

Make sure that you are running applications that requires Hugepages (if configured), as the locked memory can only be utilized by the application that requests them and they are not available for normal memory allocations. One advantage of using Hugepages is that the memory is pinned and will not be swapped out of memory providing better performance.

In my lab system, I have the memlock limits for oracle user set as below. Value is set in KB

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

The current settings can be verified by ulimit -l 

[oracle@linux-8 ~]$ ulimit -l
134217728
[oracle@linux-8 ~]$

3. Calculate the number of huge pages required. 

For Oracle database requirements, we can utilize the script provided by Oracle support via support note 401749.1. 

Script usage is as below. Make sure all the DBs are up and running before initiating the script as the calculation is done for all shared memory segments available when the script is run, no matter it is an Oracle RDBMS shared memory segment or not. 

SQL> sho parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 4G
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
[oracle@linux-8 ~]$ ls -lrt huge*
-rwxr-xr-x. 1 oracle oinstall 3485 Aug  1 10:34 hugepages_settings.sh
[oracle@linux-8 ~]$ ./hugepages_settings.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating the overall size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

Recommended setting: vm.nr_hugepages = 2050
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$

We can see the Recommended setting is to set vm.nr_hugepages = 2050 pages. This can be manually calculated by us if we know what exact memory we need for the oracle database SGA and other dependent applcations. 

In our case, I run only the Oracle database with sga_max_size of 4 GB. Default hugepage size is 2MB. So, to have 4GB memory of 2 MB page size we would need 4GB / 2MB = 4096 MB / 2 MB = 2048 pages + Minimum pages (here 1 page) + 1 = 2050 pages. 

If you have multiple DBs running in the system, we need to calculate the cumulative amount for all the DBs that needs to reserve hugepages. 

What if I don't set the memlock parameter in limits.conf file and try to start up the database? 

It's interesting we sometimes set the hugepages to the correct value as suggested by the Oracle provided script but miss to set the limits.conf file to mention which user can lock how much memory. In such cases, Oracle database would fail to start with the below error indicated in the alert log. 

**********************************************************************
2023-07-31T09:43:20.619135+05:30
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

2023-07-31T09:43:20.619242+05:30
 Per process system memlock (soft) limit = 64K
2023-07-31T09:43:20.619279+05:30
 Expected per process system memlock (soft) limit to lock
 instance MAX SHARED GLOBAL AREA (SGA) into memory: 4100M
2023-07-31T09:43:20.619346+05:30
 Available system pagesizes:
  4K, 2048K
2023-07-31T09:43:20.619412+05:30
 Supported system pagesize(s):
2023-07-31T09:43:20.619447+05:30
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2023-07-31T09:43:20.619510+05:30
     2048K                5            2050               0   ORA-27125
2023-07-31T09:43:20.619545+05:30
 Reason for not supporting certain system pagesizes:
2023-07-31T09:43:20.619603+05:30
  4K - Large pagesizes only
2023-07-31T09:43:20.619652+05:30
RECOMMENDATION:
2023-07-31T09:43:20.619688+05:30
 1. Configure system with expected number of pages for every
 supported system pagesize prior to the next instance restart operation.
2023-07-31T09:43:20.622629+05:30
 2. Increase per process memlock (soft) limit to at least 4100MB
 to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory
2023-07-31T09:43:20.622708+05:30
**********************************************************************
2023-07-31T09:43:20.622896+05:30
Oradism deallocated 5 large pages of size 2097152 bytes.
2023-07-31T09:43:20.622933+05:30
SGA: Realm creation failed
2023-07-31T10:04:04.780997+05:30
Starting ORACLE instance (normal) (OS id: 38918)
2023-07-31T10:04:04.784579+05:30
************************************************************
Instance SGA_TARGET = 4096 MB and SGA_MAX_SIZE = 4096 MB
************************************************************
2023-07-31T10:04:04.784879+05:30
****************************************************
 Sys-V shared memory will be used for creating SGA
 ****************************************************
2023-07-31T10:04:04.787059+05:30
DISM started, OS id=39038
2023-07-31T10:04:08.928252+05:30
ERROR: Failed to get available system pages to allocate memory
2023-07-31T10:04:08.929435+05:30
**********************************************************************
..
..

Init parameter use_large_pages = ONLY

With Oracle database version 11.2.0.3 and above we have database parameter use_large_pages = ONLY (Default parameter value is TRUE) which would make the database start up to fail if the expected number of huge pages are not available in the system. 

SQL> sho parameter use_large_pages

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
use_large_pages                      string      TRUE
SQL> alter system set use_large_pages=ONLY scope=spfile;

System altered.

SQL>exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
[oracle@linux-8 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB
[oracle@linux-8 ~]$ 

We can see Hugepages is not configured (HugePages_Total : 0). Now, let me configure hugepages less than 2050 pages and try to startup the database. 

[oracle@linux-8 ~]$ vi /etc/sysctl.conf
[oracle@linux-8 ~]$ sudo vi /etc/sysctl.conf
[sudo] password for oracle:
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$ cat /etc/sysctl.conf |grep huge
vm.nr_hugepages = 2000
[oracle@linux-8 ~]$ sudo sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 2000
[oracle@linux-8 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:    2000
HugePages_Free:     2000
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         4096000 kB
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 1 11:22:22 2023
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> startup force
ORA-27106: system pages not available to allocate memory
Additional information: 6183
Additional information: 1
SQL>

Alert log will register the below  

Starting ORACLE instance (normal) (OS id: 70058)
2023-08-01T11:22:35.736716+05:30
************************************************************
Instance SGA_TARGET = 4096 MB and SGA_MAX_SIZE = 4096 MB
************************************************************
2023-08-01T11:22:35.736896+05:30
****************************************************
 Sys-V shared memory will be used for creating SGA
 ****************************************************
2023-08-01T11:22:35.737250+05:30
ERROR: Failed to get available system pages to allocate memory
2023-08-01T11:22:35.737286+05:30
**********************************************************************
2023-08-01T11:22:35.737319+05:30
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

2023-08-01T11:22:35.737376+05:30
 Per process system memlock (soft) limit = 128G
2023-08-01T11:22:35.737410+05:30
 Expected per process system memlock (soft) limit to lock
 instance MAX SHARED GLOBAL AREA (SGA) into memory: 4100M
2023-08-01T11:22:35.737473+05:30
 Available system pagesizes:
  4K, 2048K
2023-08-01T11:22:35.737535+05:30
 Supported system pagesize(s):
2023-08-01T11:22:35.737572+05:30
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2023-08-01T11:22:35.737630+05:30
     2048K             2000            2050            1997   ORA-27102
2023-08-01T11:22:35.737663+05:30
 Reason for not supporting certain system pagesizes:
2023-08-01T11:22:35.737696+05:30
  4K - Large pagesizes only
2023-08-01T11:22:35.737728+05:30
RECOMMENDATION:
2023-08-01T11:22:35.737760+05:30
 1. Configure system with expected number of pages for every
 supported system pagesize prior to the next instance restart operation.
2023-08-01T11:22:35.741864+05:30
**********************************************************************
2023-08-01T11:22:35.741932+05:30
SGA: Realm creation failed
..

If I increase the huge pages to 2050 and try to start the database, the database will start allocating the expected hugepages. 

Init parameter use_large_pages = AUTO_ONLY

Starting Oracle database version 19c, we have a new option for use_large_pages = AUTO_ONLY which is the default value for 19c databases running on Exadata systems and can be configured for on premise databases as well. 

By setting the value to AUTO_ONLY, Oracle will request Linux system automatically on how many numbers of pages are required to start the database and if the server can provision the pages, the database will be started. If the request cannot be provisioned, database will fail to start. 

For demo purpose, I'll reset the hugepages to 0 and set the value use_large_pages = AUTO_ONLY using the pfile and try to start the database. 

[oracle@linux-8 ~]$ sudo vi /etc/sysctl.conf
[sudo] password for oracle:
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$ sudo sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 0
[oracle@linux-8 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$ vi /oracle/db/db19/dbs/initcdb19.ora
[oracle@linux-8 ~]$ grep large_pages /oracle/db/db19/dbs/initcdb19.ora
*.use_large_pages='AUTO_ONLY'
[oracle@linux-8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 1 11:37:59 2023
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=/oracle/db/db19/dbs/initcdb19.ora
ORACLE instance started.

Total System Global Area 4294963992 bytes
Fixed Size                  9143064 bytes
Variable Size            1056964608 bytes
Database Buffers         3221225472 bytes
Redo Buffers                7630848 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:    2050
HugePages_Free:        3
HugePages_Rsvd:        3
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         4198400 kB
[oracle@linux-8 ~]$

You can see Oracle dynamically requested Linux to allocate 2050 pages and once the request is provisioned the database is started without us needing to configure the number of huge pages required. 

Now if I try to start another instance with 100 MB sga_max_size, if the system can provision the requested pages the database will start.  

[oracle@linux-8 ~]$ vi /oracle/db/db19/dbs/initlpdemo.ora
[oracle@linux-8 ~]$ cat /oracle/db/db19/dbs/initlpdemo.ora | grep 'sga_max\|use_large'
*.sga_max_size=100m
*.use_large_pages='AUTO_ONLY'
[oracle@linux-8 ~]$
[oracle@linux-8 ~]$ . oraenv
ORACLE_SID = [oracle] ? lpdemo
ORACLE_HOME = [/home/oracle] ? /oracle/db/db19
The Oracle base has been set to /oracle/db
[oracle@linux-8 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 1 11:52:02 2023
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/oracle/db/db19/dbs/initlpdemo.ora
ORACLE instance started.

Total System Global Area  322960856 bytes
Fixed Size                  8895960 bytes
Variable Size             239075328 bytes
Database Buffers           67108864 bytes
Redo Buffers                7880704 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
[oracle@linux-8 ~]$ 
[oracle@linux-8 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:    2206
HugePages_Free:        6
HugePages_Rsvd:        6
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         4517888 kB
[oracle@linux-8 ~]$

It is not always guaranteed that the requested hugepages will be provisioned by Linux. If the system cannot allocate the required hugepages, database instance creation will fail registering insufficient number of hugepages in the alert log. 

2023-08-01T11:59:33.682674+05:30
 Available system pagesizes:
  4K, 2048K
2023-08-01T11:59:33.682755+05:30
 Supported system pagesize(s):
2023-08-01T11:59:33.682796+05:30
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2023-08-01T11:59:33.682866+05:30
     2048K              151             156             151   ORA-27102
2023-08-01T11:59:33.682899+05:30
 Reason for not supporting certain system pagesizes:
2023-08-01T11:59:33.682932+05:30
  4K - Large pagesizes only
2023-08-01T11:59:33.682964+05:30
RECOMMENDATION:
2023-08-01T11:59:33.683022+05:30
 1. Configure system with expected number of pages for every
 supported system pagesize prior to the next instance restart operation.
2023-08-01T11:59:33.697313+05:30
**********************************************************************
2023-08-01T11:59:33.697997+05:30
Oradism deallocated 151 large pages of size 2097152 bytes.
2023-08-01T11:59:33.698067+05:30
SGA: Realm creation failed

Advantages and implementations

Now as we understand how use_large_pages = AUTO_ONLY works, we need to understand where this setting can be implemented and what are the advantages

1. This setting doesn't require the prior calculation of no. of hugepages to be made with the scripts. 

2. When we add additional instances and if we intend to run those instances using hugepages, no changes necessary to system parameters are needed as the calculation and allocation of huge pages is dynamic. 

3. Can be used in development or test systems where instances are added and removed frequently. 

4. Dedicated single production database (though not recommended as a change in sga_max_size will have impact on how many pages are used and locked which might cause memory starvation if over provisioned by mistake). 

Quick note: Though the allocation of hugepages is dynamic, the pages are not released after the database is shut down and is shown under Hugepages_Free. This has an impact as this memory can't be used for normal memory allocation and is a waste of resource until next reboot. 

References:

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

Alert Log Recommendation - "Increase per process memlock (soft) limit to at least <n>MB to lock <n>% of SHARED GLOBAL AREA (SGA) pages into physical memory" (Doc ID 2049901.1)

5.2. Huge Pages and Transparent Huge Pages Red Hat Enterprise Linux 6 | Red Hat Customer Portal

What are Huge Pages and the advantages of using them? - Red Hat Customer Portal

Database Installation Guide for Linux (oracle.com)

Happy Huge Paging...!!!

Using Real time SQL Monitor in command line!

Query optimization or tuning is an interesting subject. Sometimes we would get complaints from application folks that their query is running slow in the timelines of running 4 hours against 1 or 2 hrs normal execution. During these times, we can't wait for the query to run to its completion only to see the query didn't run as expected. 

How can we find whether the query is running as expected? One of the simple ways is to run the explain plan to see whether the plan has changed from the existing one and is the join methods and filters are applied as expected and also whether the cost of the operation is reduced. But the problem here is Oracle can totally predict a wrong plan (due to missing stats for example) or with the adaptive plans can change its plan during run time as well. 

In order to know the details of the query execution we need to wait until the query completes. This is not welcome in all the situations as DBA can't wait for the query execution to complete before they decide on next course of investigation plan.

We can use DBMS_XPLAN.display_cursor or ?/rdbms/admin/utlxpls.sql or similar tools to get the explain plan of the query but for these to work to give the detailed statistics, the query should be run to its completion. Waiting for 2 or 3 hours just to check the execution details of the query seems not fun. 

Starting version 11g, Oracle introduced a feature called Real time SQL Monitoring. This is such a cool and useful feature where the stats can be seen in real time even the query is currently running. 

Requirements to use Real time SQL Monitor: 

1. Diagnostics and Tuning Pack license.
2. STATISTICS_LEVEL= 'TYPICAL' or 'ALL'

Methods to run Real time SQL Monitor: 

1. Oracle Enterprise Manager
2. SQL Developer
3. Command line. 

Conditions where SQL is monitored by default: 

1. When the SQL has consumed at least five seconds of the CPU or I/O time in a single execution
2. When the SQL execute in parallel
3. When the SQL has /*+ monitor */ hint

In this post, we are going to look into how to run Real time SQL Monitor using command line and view its results which will be useful to analyze the query execution

There are 2 packages via which sql can be monitored in real time. 

DBMS_SQLTUNE

DBMS_SQL_MONITOR

DBMS_SQLTUNE is a package that deals with tuning SQL on demand and is available from Oracle 11g onwards. The package deals with SQL profiles, creating and executing tuning tasks, tracing SQL, monitor SQL and reporting monitored SQL, etc.. We will look how to report real time SQL monitor using DBMS_SQLTUNE with an example. 

To report real time SQL, all we need is the sql_id along with the report type. By default, the report will be of text format but we have an option to get the report in nice and tidy html format. If the report needs to be generated only for a specific sql_plan_hash_value, then the value for this parameter can be provided. 

Usage example: 

DBMS_SQLTUNE

I'm now running a query with /*+monitor */ hint as this query takes less than 5 sec but I need to check the path the query has taken. 

Once the query has run or if it is a long running query we can check the v$sql_monitor view to see if the query has been captured for real time monitoring. We can then use the dbms_sqltune.report_sql_monitor procedure to report the real time statistics of the query. 

SQL> alter session set container=odb1;

Session altered.

SQL> set lines 200
SQL> select /*+ monitor */ * from oe.order_items join oe.orders on oe.order_items.order_id=oe.orders.order_id;

  ORDER_ID LINE_ITEM_ID PRODUCT_ID UNIT_PRICE   QUANTITY   ORDER_ID ORDER_DATE                     ORDER_MO CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
---------- ------------ ---------- ---------- ---------- ---------- ------------------------------ -------- ----------- ------------ ----------- ------------ ------------
      2354            1       3106         48         61       2354 14-JUL-08 05.18.23.234567 PM   direct           104            0       46257          155
      2354            2       3114       96.8         43       2354 14-JUL-08 05.18.23.234567 PM   direct           104            0       46257          155
      2354            3       3123         79         47       2354 14-JUL-08 05.18.23.234567 PM   direct           104            0       46257          155
      2354            4       3129         41         47       2354 14-JUL-08 05.18.23.234567 PM   direct           104            0       46257          155
      2354            5       3139         21         48       2354 14-JUL-08 05.18.23.234567 PM   direct           104            0       46257          155
...
...
...
      2458            2       3123         79        112       2458 16-AUG-07 02.34.12.234359 PM   direct           101            0     78279.6          153
      2458            3       3127      488.4        114       2458 16-AUG-07 02.34.12.234359 PM   direct           101            0     78279.6          153
      2458            4       3134         17        115       2458 16-AUG-07 02.34.12.234359 PM   direct           101            0     78279.6          153

665 rows selected.

SQL>
SQL> SET LINESIZE 300
SQL> COLUMN sql_text FORMAT A100
SQL> SELECT sql_id, status, sql_text FROM v$sql_monitor where sql_text like '%monitor%';

SQL_ID        STATUS              SQL_TEXT
------------- ------------------- ----------------------------------------------------------------------------------------------------
abf24z16pukw9 DONE (ALL ROWS)     select /*+ monitor */ * from oe.order_items join oe.orders on oe.order_items.order_id=oe.orders.orde
                                  r_id

SQL> SET LONG 1000000
SQL> SET FEEDBACK OFF head off
SQL> spool monitor_sql.html
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'abf24z16pukw9',type=> 'HTML') AS report FROM dual;


o/p will contain lots of html contents
as the report is generated as html.. 
...
...
...



SQL> spool off
SQL> 

We now have the report generated by name monitor_sql.html. We can view the report to see the real time execution plan statistics of the query. 

Since the query is now completed, we see the complete execution plan statistics and the completion of the query is 100%. This report is very useful when there is a long running query and if we need to investigate what's causing slowness. 

To showcase this, I just inserted 3 fold data into sales table but didn't gather statistics and ran a query without any filters. 
The first report generated shows below. 

The query runs for a very long time and the subsequent reports shows the progress of the query until it completes. Here, we can find that the query expected 919k rows from the sales table but the actual rows returned is 4M which is 4 folds higher than the expected value and hence we can easily say that the statistics is not proper for the table. 

DBMS_SQL_MONITOR

DBMS_SQL_MONITOR package provides information about Real-Time SQL Monitoring and Real-Time Database Operation Monitoring whereas DBMS_SQLTUNE is a comprehensive package consisting of all SQL related tuning capabilities and is an interface for SQL tuning on demand. 

Generating real time sql monitoring report using DBMS_SQL_MONITOR is similar to using DBMS_SQLTUNE and the report is also generated same as this. 

DBMS_SQL_MONITOR is introduced in 12c and we can now monitor SQL and PL/SQL calls containing SQL as well. 

Usage will be as below 

SQL> SET LONG 1000000
SQL> SET FEEDBACK OFF head off
SQL> spool monitor_sql.html
SQL> SELECT DBMS_SQL_MONITOR.report_sql_monitor(sql_id =>'1uhbtu4azdyqh',type=> 'HTML') AS report FROM dual;

SQL> spool off
SQL> 

Report generated will be similar to the one we saw before using DBMS_SQLTUNE with just the package name change. 

While SQL monitoring report of type => HTML gives a well formatted html report to analyze the query execution, we can generate ACTIVE report which would provide much better and additional information such as optimizer environment, parallel activity section for parallel queries, etc. Active report is my favorite go to tool for query tuning. 

So how would you generate the SQL monitor ACTIVE report? Use type => ACTIVE in the REPORT_SQL_MONITOR function as below. 

SQL> SET LONG 1000000  LONGCHUNKSIZE 1000000  LINESIZE 1000  PAGESIZE 0  TRIM ON  TRIMSPOOL ON  ECHO OFF  FEEDBACK OFF
SQL> spool monitor_sql.html
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'abf24z16pukw9',type=> 'ACTIVE') AS report FROM dual;
...
...
...

SQL> spool off

The report generated will be like below. 

We can see that the report is kind of interactive where we can change the execution plan to graphical mode if one wishes to understand better and we can toggle between various options to check the complete picture of where the query is executed like the Optimizer Environment, etc. Timeline section will keep updating if we generate the report frequently while the query is running. 

When the graphical mode is toggled, we get the easily understandable view of the execution plan

Hope this helps investigate the real time sql using command line. 

References: 

DBMS_SQLTUNE (oracle.com)
DBMS_SQL_MONITOR (oracle.com)
Monitoring SQL Statements with Real-Time SQL Monitoring (Doc ID 1380492.1)

Happy Tuning...!!! 

P.S: I'm not able to generate the real time reports on the Database version 23 Free and have raised a community discussion. Let's wait to hear on this.. 

Update: 

The community discussion has been answered by SQL Developer fame Jeff Smith. 
DIAGNOSTIC+TUNING pack is by default disabled and we need to enable it to generate the reports. 

Also, I have added the section of how to generate Active SQL monitor reports. 

Create Oracle database Flashback logs outside FRA!

 Many times, as a DBA we would be required to create database restore points so that if something messed up in the database be it due to a database upgrade or patching, application upgrade touching the database objects, etc., it would be easy to go back to the time when the operation was started without the need of restoring the database. 

To facilitate this easy feature, one would need to enable or create the restore point be it a normal restore point or a guaranteed restore point. I will always prefer the guaranteed restore point if I want to secure the database of unexpected mess as a roll back plan. The flashback logs of a normal restore point can be resued if there is a space pressure in the FRA or it is no longer needed to satisfy the flashback retention target and hence the recovery window might be limited. 

One downside of creating restore point is that Oracle logs another type of log file apart from the standard archive logs called the flashback logs. Until version 21c, if we enable restore point or to use flashback feature, Oracle will always use the Fast Recovery Area (FRA) space to create the flashback logs. FRA is a shared space for all the database restore and recovery related files such as archive log, control file, backup piece and also the flashback log. 

Below is an example of the FRA contents from a 19c database

You can see with the default settings, both archived logs and flashback log uses the same FRA and when there is a growth in flashback log, there is a possibility of database stalling due to insufficient space when either of the logs fills the FRA. 

In Oracle 23c, there are 2 new parameters introduced as below 

db_flashback_log_dest
db_flashback_log_dest_size

These 2 parameters define where the flashback logs are stored outside of the FRA making the management of flashback logs easier.

SQL> alter system set db_flashback_log_dest_size=4G scope=both;

System altered.

SQL> alter system set db_flashback_log_dest='/media/sf_Oracle/Linux-823' scope=both;

System altered.

SQL> sho parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest                string      /media/sf_Oracle/Linux-823
db_flashback_log_dest_size           big integer 4G
db_flashback_retention_target        integer     1440
SQL> 

Though we have enabled the new parameters to have a separate location for flashback logs, we still have to define FRA location and size before we start utilizing the new location for flashback logs. Otherwise, if you try to create restore point we will get the error as below. 

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL>
SQL> CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE;
CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'FREEPDB1_RP'.
ORA-38786: Recovery area is not enabled.


SQL> 

Now, let's enable FRA by allocating space and location and then check the contents before and after restore point creation. 

SQL> alter system set db_recovery_file_dest_size=4G;

System altered.

SQL> alter system set db_recovery_file_dest='/opt/oracle/FRA';

System altered.

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/FRA
db_recovery_file_dest_size           big integer 4G
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> CREATE RESTORE POINT freepdb1_rp FOR PLUGGABLE DATABASE FREEPDB1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

7 rows selected.

SQL> 

We can see that once the restore point is created, FLASHBACK LOG entry from v$flash_recovery_area_usage has disappeared. So how do we check the details of the flashback logs? We do have a new view GV$/V$FLASHBACK_LOG_DEST to check the details. 

SQL> select * from V$FLASHBACK_LOG_DEST;

NAME                                               SPACE_LIMIT SPACE_USED NUMBER_OF_FILES     CON_ID
-------------------------------------------------- ----------- ---------- --------------- ----------
/media/sf_Oracle/Linux-823                          4294967296  419430400               2          0

SQL> 

Okay, so what is the use case of having a location outside of FRA? 

We don't always create restore points (in turn flashback logs) and they are created as necessary. Most important times are before patching of application or database. FRA mostly is dedicated to a specific database and let's say we have multiple databases with varying work loads scheduled for patching or application upgrade involving multiple database. Managing FRA on each database could be cumbersome as few DB would easily fill up space but most database won't generate much logs. We can NFS mount a file system on multiple involved database servers and dedicate that location for flashback logs so that the space management can be done outside of the database easily as and when needed. 

From performance point of view, if we have a separate location (disk) for the flahsback logs, i/o performance can be improved as i/o will be distributed between archive logs and flashback logs as both has to be written for the database to function. 

References: 

OLTP and Core Database (oracle.com)

Happy Flashback logging...!!!