ݺߣ

ݺߣShare a Scribd company logo

Tune my Database! Mehr Oracle SQL Performance über SQLHC - Yann Neuhaus, Nicolas Jardot, dbi services

•
•
dbi services
dbi services

This document discusses SQL Health Check (SQLHC), a free script from Oracle that analyzes the execution environment of a SQL statement. SQLHC checks settings like the cost-based optimizer, object statistics, instance parameters, and execution plans. It generates diagnostic reports without requiring installation or creating database objects. The document provides instructions for running SQLHC and an example of analyzing a SQL statement from a human resources application.

1 of 18
Downloaded 11 times
dbi services SQL Health Check Nicolas Jardot Consultant Mobile +41 79 835 02 95 nicolas.jardot@dbi-services.com www.dbi-services.com Yann Neuhaus CEO – Region Manager Basel Area Mobile +41 79 963 27 20 yann.neuhaus@dbi-services.com www.dbi-services.com 1 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 2 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Introduction  Many parameters may influence SQL executions  CBO settings, Objects statistics,…  Tuning requires to check and analyze these parameters  Cloud Control allows to monitor SQLs to find which one is the more consuming 3 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Introduction – Highlight SQLHC  is a script from Oracle  Download at My Oracle Support, note 1366133.1  dbi services advices to regularly check if new version is available  creates a report on an SQL statement  focus only on one statement  is available for free  SQHC is not dependent of Tuning/Diagnostic Pack license  does not require installation 4 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Introduction – Highlight 5 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 6 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Concept How SQLHC script works?  Quickly get a report of the execution environment  Require SYS account or DBA role  Can be run locally of from distant client  Does not create objects in the database 7 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Concept What is checked?  CBO settings  Objects statistics  Instance parameters  Execution plans 8 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Concept 9 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Concept 10 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Concept  Instance parameters 11 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Concept 12 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 13 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Usage  Run script from SQL Plus SQL> @sqlhc.sql [ T | D | N ] sql_id  License level  T: Tuning Pack  D: Diagnostic Pack  N: None  The output contains several files *_1_health_check.html *_2_diagnostics.html *_3_execution_plans.html [*_4_sql_detail.html] [*_5_sql_monitor.zip] [*_6_10053_trace_from_cursor.trc] 14 www.dbi-services.com 08.11.2012 © dbi services
Edition Based Redefinition Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 15 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Case study  Human Resource Application 16 www.dbi-services.com 08.11.2012 © dbi services
SQL Health Check Case study - Methodology  Full table scan on both tables EMPLOYEES and JOBS  Existing index IDX_JOB_TITLE on column present in the where clause not used  Why index is not used?  Bad/missing statistics or histograms  Instance parameters  Bad selectivity  …  Let’s focus on SQLHC report 17 www.dbi-services.com 08.11.2012 © dbi services
dbi services Any Questions? Please Do Ask! Nicolas Jardot Consultant Mobile +41 79 835 02 95 nicolas.jardot@dbi-services.com www.dbi-services.com Yann Neuhaus CEO – Region Manager Basel Area Mobile +41 79 963 27 20 yann.neuhaus@dbi-services.com www.dbi-services.com „We look forward to working with you!“ 20 www.dbi-services.com 08.11.2012 © dbi services

More Related Content

Tune my Database! Mehr Oracle SQL Performance über SQLHC - Yann Neuhaus, Nicolas Jardot, dbi services

  • 1. dbi services SQL Health Check Nicolas Jardot Consultant Mobile +41 79 835 02 95 nicolas.jardot@dbi-services.com www.dbi-services.com Yann Neuhaus CEO – Region Manager Basel Area Mobile +41 79 963 27 20 yann.neuhaus@dbi-services.com www.dbi-services.com 1 www.dbi-services.com 08.11.2012 © dbi services
  • 2. SQL Health Check Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 2 www.dbi-services.com 08.11.2012 © dbi services
  • 3. SQL Health Check Introduction  Many parameters may influence SQL executions  CBO settings, Objects statistics,…  Tuning requires to check and analyze these parameters  Cloud Control allows to monitor SQLs to find which one is the more consuming 3 www.dbi-services.com 08.11.2012 © dbi services
  • 4. SQL Health Check Introduction – Highlight SQLHC  is a script from Oracle  Download at My Oracle Support, note 1366133.1  dbi services advices to regularly check if new version is available  creates a report on an SQL statement  focus only on one statement  is available for free  SQHC is not dependent of Tuning/Diagnostic Pack license  does not require installation 4 www.dbi-services.com 08.11.2012 © dbi services
  • 5. SQL Health Check Introduction – Highlight 5 www.dbi-services.com 08.11.2012 © dbi services
  • 6. SQL Health Check Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 6 www.dbi-services.com 08.11.2012 © dbi services
  • 7. SQL Health Check Concept How SQLHC script works?  Quickly get a report of the execution environment  Require SYS account or DBA role  Can be run locally of from distant client  Does not create objects in the database 7 www.dbi-services.com 08.11.2012 © dbi services
  • 8. SQL Health Check Concept What is checked?  CBO settings  Objects statistics  Instance parameters  Execution plans 8 www.dbi-services.com 08.11.2012 © dbi services
  • 9. SQL Health Check Concept 9 www.dbi-services.com 08.11.2012 © dbi services
  • 10. SQL Health Check Concept 10 www.dbi-services.com 08.11.2012 © dbi services
  • 11. SQL Health Check Concept  Instance parameters 11 www.dbi-services.com 08.11.2012 © dbi services
  • 12. SQL Health Check Concept 12 www.dbi-services.com 08.11.2012 © dbi services
  • 13. SQL Health Check Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 13 www.dbi-services.com 08.11.2012 © dbi services
  • 14. SQL Health Check Usage  Run script from SQL Plus SQL> @sqlhc.sql [ T | D | N ] sql_id  License level  T: Tuning Pack  D: Diagnostic Pack  N: None  The output contains several files *_1_health_check.html *_2_diagnostics.html *_3_execution_plans.html [*_4_sql_detail.html] [*_5_sql_monitor.zip] [*_6_10053_trace_from_cursor.trc] 14 www.dbi-services.com 08.11.2012 © dbi services
  • 15. Edition Based Redefinition Agenda Agenda i. Introduction ii. Concept iii. Usage iv. Case study 15 www.dbi-services.com 08.11.2012 © dbi services
  • 16. SQL Health Check Case study  Human Resource Application 16 www.dbi-services.com 08.11.2012 © dbi services
  • 17. SQL Health Check Case study - Methodology  Full table scan on both tables EMPLOYEES and JOBS  Existing index IDX_JOB_TITLE on column present in the where clause not used  Why index is not used?  Bad/missing statistics or histograms  Instance parameters  Bad selectivity  …  Let’s focus on SQLHC report 17 www.dbi-services.com 08.11.2012 © dbi services
  • 18. dbi services Any Questions? Please Do Ask! Nicolas Jardot Consultant Mobile +41 79 835 02 95 nicolas.jardot@dbi-services.com www.dbi-services.com Yann Neuhaus CEO – Region Manager Basel Area Mobile +41 79 963 27 20 yann.neuhaus@dbi-services.com www.dbi-services.com „We look forward to working with you!“ 20 www.dbi-services.com 08.11.2012 © dbi services