The plan of an SQL can change, and the change is justified if it doesn’t degrade performance. Changes in the values of bind variables, change in statistics etc. can change the plan which is often an expected behavior. But now and then you may see a change that degrades performance. Below script compares the plans for SQL s from […]
Following is a brief account of some serious issues we had with DRM on a 3 node RAC system running 11g R2. We had intermittent spikes that were severe enough to disrupt service. Here is a section from a 10-minute ASH showing DRM related wait “gcs drm freeze in enter server mode.” Our first attempt was to disable DRM by setting _gc_policy_time=0. […]
RAC Wait event gc buffer busy acquire indicates that the session experiencing this event is waiting on another session on the same instance to release the block. Basically the waiting session is not able to pin a block, and will wait on this event until it can pin the block. (gc buffer busy release means […]
In the context of database processing, tuning can be defined as a set of techniques that helps the database to operate more efficiently–with higher throughput. Database tuning can be at the SQL statement level, or at the system level. SQL Tuning SQL Tuning is a set of techniques used to execute the SQL in the […]
When a SQL runs, the time it takes to run is basically come from the CPU time needed plus the time for waits. The following SQL looks into gv$active_session_history and finds the time the SQL spends on each wait event or CPU The Script — — — sqlwaits.sql — Parameters – none — — Purpose: […]
Package calls are very common. It will be helpful if we can find out the top SQLs from such a call The following script is written for that purpose. It basically looks into the AWR and pulls all SQLs that were run as part of a package call. Please note: The script assumes a package […]
The following SQL is a good example of scalar sub query tuning. The original query takes few hours to run. Please see the query and the plan below. SELECT SUM (FEE_PAID), MAX (INVOICE_DATE) FROM (SELECT STH.DATE_OF_TRAN AS INVOICE_DATE,STH.PENDING_SEQ_NO AS PENDING_TRAN_NUMBER, (SELECT FEE_AMT FROM FEE_TAB FT1 WHERE T_SEQ_NO = (SELECT MAX (T_SEQ_NO) FROM FEE_TAB FT2 WHERE P_SEQ_NO […]
It is useful to be able to see all SQLs that are running along with how long it has been running, and the number of parallel sessions opened by the session running it etc. The following script can do the job. Some of the columns are self explanatory. For other columns , a brief explanation is […]
Often, to evaluate a SQL, we need to examine how it performed in the past. Few things are important here: 1. Average elapsed time 2. Buffer gets per execution 3. Frequency of execution (how many times it executes on an hourly/daily basis) The following script can be used to generate this information from AWR. The script […]
How do we calculate the percentage improvement from tuning? Even though not always true, LIO (Logical IO/Buffer Gets) is an approximate measure of the work needed for a SQL. For example, if the LIO before tuning was 1 million, and after tuning it came down to 150K, the percentage gain can be calculated as given […]
Theme Layout
Color Scheme