RDBMS -- a Case Study: Suggestions for Performance Improvements, quick actions, utilizing the features for performance and the approach

Following is based on a real life performance problem, which was encountered on a very critical Oracle Database 19C Production System.

While the headings given below summarize how these performance problems should be approached from scratch, those written things under the headings provide case-specific details. Determining the current status of the environment, motivations in determining the direction of analysis, quick actions and suggestions for the future (Oracle specific).. Of course, the details are included in the analysis report. With this approach, we solve problems while ensuring customer satisfaction.. 

General Findings:

High parse

Lots of I/O -- due to several reporting activities

concurrency waits

Slow SQLs due to visiting very big tables.

High (almost at limits) OS load and CPU usage

Several active sessions and huge amount of queries (especially OLTP-type) 

Remote database queries and related network waits

Findings:

High number of sessions

High CPU usage

High Hard Parse

High Parse (Soft)

High number of SQL Executions

Low number but long CPU Bound SQL Execution

SQLs with high buffer get.. (there are also those with high execution and those with low execution)

Low Parse CPU to Parse Elapsed ratio

Medium-High DBlink wait

Medium-High Indexed Reading Wait

Fast I/O (ALL Flash I/O Subsystem)

High I/O count

Comments:

SQL Version count should be reduced. -> For mutexes

Hard Parse should be reduced. (Using Bind for Hard Parse, Dynamic SQL should be avoided, PLSQL should be used -- automatic cursor sharing)

Soft Parse should be reduced. (For Soft Parse, techniques such as simplifying queries, using indexes effectively, and reducing unnecessary joins can significantly improve performance.)

SGA should be increased to you. (780GB specified by Adviser. We have already recommended this.)

SQLs that perform high GET should be tuned so that they work with less data.

Motivation for further analysis:

Random Cursor S pin on X waits

High CPU usage ve Load

Random ORA-00600 errors

Low RU level

Possible Bugs

Lack of best practice development

Actions Taken:

RU upgrade

LOAD analysis

Platform Migration

Performance Analysis & Workshops

Error Diagnostics & Action Plans

Bug Fix- manual actions

Hang analysis & Support

Resource Management implementation tests

Recommendations for Improvements:

• Active Dataguard – Transferring the load from the source system to the secondary system with SQL/Report offloading (general benefit, I/O + Memory, less work to be done..)

• Continuous read/write data transfer to a secondary environment and SQL offloading with a tool such as Golden Gate (alternative to the above item)

• Abandoning the use of DB links. (Not very related to resources, but still noticeable and sometimes keeps queries waiting.)

• Partitioning – Sessions process with less data by partitioning operations based on queries. (This will reduce concurrency waits and reduce CPU usage.)

• In-Memory – Faster querying (will reduce concurrency waits and reduce CPU usage.)

• ILM (Information Lifecycle Management) implementation -- Data Archiving – Reducing and archiving data in the source system. (Less data, faster queries, less I/O and CPU usage due to concurrency)

• Oracle RAC – (Horizontal growth and node-based load distribution + many advantages.. -- Application side to be RAC Aware.)

• Reducing the number of parses – Mainly the work of the software developer. Increasing the number of parses in the current system, minimizing the hard parse and reaching the point of “Parse First Execute Many”. (More gain from CPU cycles)

• Testing of 23C / multi-tenant is mandatory – in terms of performance.. (it has many innovations and bug fixes. It may be beneficial indirectly.)

• What can be done to reduce the concurrency in memory.. (e.g., if there are cache buffer chains while reading the same part of the index, changing the index type.. reverse key index maybe.. Scenario-based actions) - low gain and challenging job.

• Hardware upgrade (last resort) – Compatible with licenses, but capacity on-demand. It will grow in place when necessary. Supported by virtualized structures that accept Oracle's capacity on-demand. Access to disks will still be physical.

• Cloud use can be considered (in all relevant contexts). It can be much more flexible and less costly.

• A fast cache in front of Oracle Database. (Fast reading and less work on DB. Maybe Redis. It needs to be supported with research.)

• Oracle Database Result Cache.. (It may seem contradictory depending on the situation, but it can be checked.)