This post will cover the SQL Tuning Health Check script basics and how you can use it to collect the key information regarding a poorly performing SQL. SQLHC helps you on focusing on specific SQL and checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance.
Unlike SQLT, this tool SQLHC do not require any code to be installed in the database to provide recommendation. SQLHC uses the SQL_ID of a statement that has already been executed and is in memory to generate the report so it do not actually execute the SQL all lover again. SQLHC works 10g and above. SQLHC is also RAC aware
Block corruptions are one of the common source of database outages. A database block is corrupted when its content has changed from what Oracle Database expects. Correcting block corruptions can prove to be a lengthy and costly exercise and requires good expertise 0n the DBA part.
Block Corruption can happen at Logical level or Physical level. Also, such corruption can impact a single block or huge number of blocks in the database.
The Oracle’s Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. The report generated by AWR is a big report and it can take years of experience to actually understand all aspects of this report. In this post we will try to explain some important sections of AWR, significance of those sections and also some important tips. Please note that explaining all sections of AWR will not be possible so we will stick to some of the most frequently used sections.
Note that this is not comprehensive information and goal is to help in giving an overview of few key sections to Junior DBAs as a primer and to encourage them to build further the knowledge in related fields.
OEM 13c (13.2) has many new features which make this version more useful than the previous OEM versions. If you are moving from OEM 12c to OEM 13c first change you will find will be that the appearance and look and feel of application is change a lot. In the starting you may feel little overwhelmed as you may be working for long in OEM 13c and may have got accustomed to it.
Some of the noteworthy new features of OEM 13.2 are mentioned below:
1. ENHANCED SUPPORT FOR VIRTUALIZATION SOFTWARES
Oracle VM enables virtual machines to be deployed, managed, and moved throughout Oracle’s public and private cloud infrastructure. Oracle Enterprise Manager 13c can manage Oracle VM technologies efficiently. The Enterprise Manager for Oracle Virtualization (VT) Plug-in has been extended to work seamlessly with Oracle’s latest server virtualization product, Oracle VM Release 3.4
OEM tool has improved a lot in last decade. Oracle has added so many new features to it and fixed many software bugs. In coming years OEM will prove more important as the OEM capabilities are constantly increasing and the increasing trend of Hybrid setups (cloud and non-cloud) requires some monitoring tool which successfully monitors all kind of setups.
This post will describes some noteworthy capabilities of OEM and what all you can achieve by using OEM with your technological setups.
Oracle Enterprise Manager is Oracle’s solution to manage/monitor various database/applications which may be running on Cloud infrastructure or on-premise. OEM 13c is the latest version at the time of writing this post. In this post we will discuss about the architectural components which are required by Oracle Enterprise Manager.
Below is simple architectural representations of an OEM 13c setup.
The active session history (ASH) sampler performs the sampling. ASH samples the current state of all active sessions. The database collects this data into memory, where you can access it with a V$ view.
Using ASH enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information.
ASH also contains execution plan information for each captured SQL statement. You can use this information to identify which part of SQL execution contributed most to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:
The Automatic Workload Repository (AWR) automatically persists the cumulative and delta values for most of the statistics at all levels except the session level.
This process is repeated on a regular time period and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period.
The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database.
You can utilize Oracle Database dynamic performance views to query important tuning related parameters like memory utilization, Disk I/O, Disk structure etc. These dynamic performance views are special views that are continuously updated while a database is open and in use.
Note that the actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects.
In our EBS system, we found that SYSADMIN login was slow. It was taking around 4-5 minutes to login and when inside the EBS application it was taking 2-3 minutes every time we were navigating in the self-service pages. This issue was not occurring for normal EBS users. We thought of applying the trace to SYSADMIN user to find the root cause of it.