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 […]
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 […]
Alter Table Add Column with a separate Update or Alter Table Add Column with Default takes a long time for large tables. Why it takes long? The update (esp. on tables with many millions of rows) is the issue here. When a default is specified, all rows need to be updated. Updating large amount of rows will 1) […]
Theme Layout
Color Scheme