Optimization for performance is normally focused on disk usage because this is by far the slowest component of any RDBMS.    Increasing the rate of reads and writes form disk dramatically increases the performance of most all database suites.  The standard answer to optimize performance in an Oracle 12c build is to use ASM (Automatic Storage Management), because oracle ASM automatically stripes data across all available disk groups.   This elevates the majority of problems with disk performance.   ASM automatically provides the following benefits over traditional storage configuration:

1. Striping
2. Mirroring
3. Online storage reconfiguration and dynamic rebalancing.
4. Managed file creation and deletion

If your organization is still use traditional storage options we should consider implement the following to optimize disk performance:

1. Stipe everything across every disk with RAID 1 mirror.
2. Move Archive Logs to Different Disks – Avoid DW and ARC process disk contention to improve performance.
3. Move Redo Logs to Separate Disk – High level of updates in most OLTP systems cause a lot of redo log writes. Thus avoid disk contention by moving to separate disks.
4. Continually monitor database performance to determine disk contention on various database functionality and redirect storage to avoid contention.

Larry Catt

OCP

SQL Performance Analyzer (SPA) allows you to evaluate a set of SQL statements against known workloads.  This set of SQL statements with execution statistics and execution context is known as an SQL Tuning set.    A single SQL Tuning set can be adjusted with updated SQL statements to test various evolutions of the same process.   Additionally, you can use cursor cache and AWR snapshots to track performance.  Using SQL TUNING SET enables you to:

1. Store SQL text and auxiliary info in single persistent database objects.
2. Capture Insert, update, delete, and select SQL statements in the SQL TUNING SET.
3. Load and merge content from different data sources like AWR and cursor cache.
4. Export SQL TUNING SET where SQL workload is captured and import into other systems.
5. Reuse SQL workload as input source for other advisors like SQL Tuning Advisor and SQL Access Advisor.

The Views and  package that relate to SPA are:

DBA_ADVISOR_TASKS – Info about SPA tasks created.

DBA_ADVISOR_EXECUTIONS – info about SPA tasks executed.

DBA_ADVISOR_FINDINGS – info about SPA finding, generate the following reports:

• Performance regressions problems.
• Changing structure of execution plan.
• Errors like non-existence of objects.
• Info messages.

DBA_ADVISOR_SQLPLANS – list of execution plans.

DBA_ADVISOR_SQLSTATS – list of SQWL compilations and execution statistics.

V$ADVISOR_PROGRESS – displays operation progress of SPA.

Functions

DBMS_SQLPA .CREATE_ANALYSIS_TASK – This function create analysis tasks for single SQL statements from SQL text, AWR, or cursor cache.  Also create analysis task from SQL Tuning set.

DBMS_SQLPA .EXECUTE_ANALYSIS_TASK – This function executes defined analysis tasks.

DBMS_SQLPA .REPORT_ANALYSIS_TASK – This function displays results of executed tasks.

Steps in Performing Performance Analysis:

1. Capture SQL workload to analyze and store it in a SQL tuning set.
2. If using test system it should be as close to production as possible and transfer SQL Tuning Set.
3. On test system create SPA task
4. Build pre-change SQL trails by executing the SQL statements store in the SQL tuning set.
5. Perform the system change.
6. Build post-change SQL trial by re-executing the SQL statements in SQL tuning set on the post-change test system.
7. Compare and analyze the pre-change and post-change versions of performance data and generate a report to identify the SLQ statements that have improved, remained unchanged, or regressed after system change.
8. Tune any regressed SQL statements that are identified.

Larry Catt

OCP

Tuning an application to run efficiently against any version of the Oracle RDBMS is a recursive process which requires continuous monitoring and adjustments.    There are multiple Oracle tools and process to aid in both the monitoring and adjustment process.    This article covers the basic process of Application Tuning.

1. Tune to a problem or performance level – Due not go about changing settings just to see what happens. Rather try to find a problem or bottle-neck in the database and tune to reduce the problem or performance slow down.
2. Gather Data/statistics – Use diagnostics tools to gather info about problem/slow down. Normally this tools will be related to gathering statistics based against a known baseline.
3. Make Discrete Changes – Make small exact changes and observe response. Try to make one change at a time, thus you will be able to determine what change helped solve the issue.
4. Stop – Stop making changes once the problem or bottle-neck has been addressed.
5. Monitor – Monitor the database and observe things that can be associated with performance problems:
   1. Long waits.
   2. Long held latches and locks.
   3. Long running processes.
   4. High CPU usage.
   5. High I/O
6. Create new baselines for performance with known workloads. These baselines can be used to truly determine that a perceived database issue is truly abnormal and needs to be addressed.

Larry Catt

OCP