In an Oracle Database, the GC buffer busy acquire wait event occurs when a session tries to acquire a buffer from the buffer cache and…
Leave a CommentCategory: Tuning Basics
SQL Profile and SQL Baseline are both used to optimize the performance of SQL statements in Oracle databases. However, they work in slightly different ways:…
Leave a CommentPL/SQL lock timer wait event in Oracle occurs when a session is waiting for a lock held by another session to be released. This can…
Leave a CommentOracle 19c databases support several different types of indexes, each with its own specific use cases and characteristics. An index in an Oracle database is…
Leave a CommentWe have seen “enq: UL – contention” wait event a couple of times in our Oracle Database. It occurs when there is contention for an…
Leave a CommentThis 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
6 CommentsThe 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.
35 CommentsThe 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:
Leave a CommentThe 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.
Leave a CommentYou 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.
Leave a Comment