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