Author: Osman DİNÇ
-
Why queries to the DBA_MVIEWS view are slow and Insights on SMON_SCN_TIME
The execution time for the “Select * from dba_mviews” query in a specific database is 45 seconds, which is unacceptable and requires improvement. The complexity of the dba_mviews view, particularly the inclusion of specific columns and full table scans of internal tables like SYS.SUMDELTA$ and SYS.SMON_SCN_TIME, contributes to this delay. Solutions suggested include excluding certain columns, creating a new view, or even indexing. Ultimately, optimizing refresh periods and managing data efficiently significantly reduced execution time, demonstrating the importance of these adjustments for performance enhancement.
-
Flashback Data Archiver (FBDA) Queries are not using Bind Variables
The excessive memory usage for SQL queries was discovered in a critical database, traced back to internal queries by the FBDA process. Hard parses, which are resource-intensive, were identified as the cause. The issue was detected through SQL plan and memory consumption analysis. A solution involving the use of bind variables and a workaround for the problem were proposed. A bug related to queries without bind variables was identified and it was revealed that a similar issue did not exist in another database using the flashback feature efficiently. The proposed workaround involved removing the quota clause from flashback data archives.
-
+ASM and +APX Instances are not registered with listener.
After upgrading to Oracle grid infrastructure 19c, +ASM and +APX instances were not registered with the listener and had a down OEM status. The issue was resolved by setting start_dependencies for asmgroup.
-
Parse Error Warnings in database alert.log file
SQL syntax errors and parse errors can impact database performance. The 12.2 release records failing SQLs in alert.log if called excessively, as seen with the “select dual” statement failing 100 times in 4 minutes. The default setting for recording parse errors in alert log is every 100 errors within a 60-minute period, but this can be adjusted.
-
Resolving CRS Startup Error: Fixing ASM Disk Group Credentials
The post discusses the challenges faced in restoring a mirrored disk failure, including errors with CRS and missing internal users. It outlines steps taken to resolve issues and successfully start CRS. Later, backup location changes are made.
-
ASM Disk Failure: Restoring OCR Config and amdu command
The post describes a recovery process after a Mirrored Disk Failure in Normal Redundancy Mode, causing CRS downtime. The team restored OCR config, changed VOTING disk location, and employed Oracle support documentation for ASM-based OCR restoration. The process involved reconfiguring and relocating files and resolving issues with missing disks, leading to successful recovery.
Osman’s DBlog 