Some Notes on Karen Morton’s #Webinar “Diagnosing SQL Performance Problems”


Some notes on Karen Morton‘s webinars on SQL Tuning sponsored by Embarcadero. This is about her first one: Diagnosing SQL Performance Problems

Posts About Webinars

Morton has the following posts about her first webinar:

Diagnosing SQL Performance Problems

Webinar is available via a form at Diagnosing SQL Performance Problems with Karen Morton.

Is The Problem Inside Or Outside The Database?

Remember to check the operating system statistics in the AWR report.

Statistics Collection

Points to remember:

  • Check for no statistics
  • Poor statistics collection—defaults are better in 11.2
  • “Collect too frequently”
  • “Collect too many histograms (or not enough)”
  • “Dynamic sampling disabled”

“Revert to previous statistics”

dbms_stats.restore_table_stats

Cardinality Feedback

Cardinality Feedback

Identified in AWR SQL reports, or specialised query to find unstable queries.

Parameters

opt_param hint

SQL Rewrite

Check for plan changes first. Find out why the plan changed. Need a longer AWR retention.

3 Most Common Rewrite Reasons

  • Query transformation problems
  • Datatype “oddities” or complex expressions
  • Procedural vs set-based approach
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s