OracleSQL tuning

Literal SQL that should use bind variables

Groups statements that are identical apart from literals. Hundreds of copies of the same shape means hard parsing and shared pool churn; show the sample to the developers.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 160 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN signature     HEADING "FORCE MATCHING SIGNATURE" FORMAT 999999999999999999994COLUMN copies        FORMAT 999,9905COLUMN sample_sql_id HEADING "SAMPLE SQL_ID" FORMAT A136COLUMN sample_text   HEADING "SAMPLE TEXT"   FORMAT A70 TRUNCATE7 8SELECT * FROM (9  SELECT force_matching_signature AS signature,10         COUNT(*) AS copies,11         MIN(sql_id) AS sample_sql_id,12         SUBSTR(MIN(sql_text), 1, 70) AS sample_text13  FROM v$sqlarea14  WHERE force_matching_signature > 015  GROUP BY force_matching_signature16  HAVING COUNT(*) > 5017  ORDER BY copies DESC18) WHERE ROWNUM <= 15;

Save it as ora-no-binds.sql and run it with SQL> @ora-no-binds.

Open in denrepo

Helps with

• ORA-04031: unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")

More Oracle scripts: SQL tuning

• Execution plan from the cursor cachePrompts for a SQL_ID and prints every cached child plan. For actual row counts, run the statement with the GATHER_PLAN_STATISTICS hint and change…
• Full SQL text for a SQL_IDThe complete statement, not the first 1,000 characters. Reads the cursor cache; an optional AWR lookup for aged-out statements is included, commented…
• SQL running with more than one planStatements in the cursor cache with several plan hash values, showing the best and worst average time. A wide gap is the classic sign of plan…
• Plan history for a SQL_ID from AWREach AWR snapshot where the statement ran, with its plan hash value and average time. Shows exactly when a plan flipped and what it cost.
• Every historical plan for a SQL_IDPrints all plans AWR has captured for the statement, so you can compare a good plan with a bad one side by side.
• SQL plan baselines and SQL profilesWhat's pinning plans in this database. Baselines come with Enterprise Edition; SQL profiles are created by the SQL Tuning Advisor, which needs the…