We 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 CommentCategory: TUNING
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
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.
37 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 CommentA well-planned methodology is the key to success in performance tuning. Without a proper diagnosis of the issue any troubleshooting step taken will further delay the resolution. Example, a DBA can not just blindly start tracing/tuning a SQL/Sessions when all symptoms on careful analysis points towards issues with DIsk/Memory/Network etc.
2 CommentsHistogram helps Oracle Optimizer in generating a better execution plan specially when the data is highly skewed in the table. You can say a histogram is an optional special type of column statistic data that provides more detailed information about the actual data distribution in a table column.
Before going into Oracle Histograms details, let us understand what it Histogram in simple language.
2 CommentsOracle’s Cost based optimizer OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach. Choosing the right mode setting can improve many times the average performance of your queries. Optimizer is the mind of the Oracle engine which takes into account many factors like indexes, statistics etc and decide the optimum path to fetch the data and return back to the user session. Oracle provides some parameters which if tweaked can make Optimizer mode to go alternate ways which can be beneficial to your SQL queries. optimizer_mode database parameter is one such useful parameters.
2 CommentsJust like TKPROF, Trace Analyzer (TRCA) tool can help you analyze one or several SQL trace(s) generated by Event 10046 to produce an output diagnostics report which can be used to diagnose a poorly performing SQ statement. TRCA can produce output in two formats (html and text).
TRCA identifies expensive SQL, generates their explain plan and collects some other useful information like Cost-based Optimizer CBO statistics, metadata, configuration parameters, and similar elements that influence the query performance.
This post is part 4 of the below posts on trace and tkprof. Please refer other posts to read related information.
Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool