Tipi di tuning: tuning dell’architettura fisica tuning dell’istanza
SQL Tuning Tools of the Trade
-
Upload
carlos-sierra -
Category
Software
-
view
5.212 -
download
3
description
Transcript of SQL Tuning Tools of the Trade
![Page 1: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/1.jpg)
SQL Tuning Tools of the Trade
Carlos Sierra
![Page 2: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/2.jpg)
Enkitec (c) 2014 2
Carlos Sierra• SQLTXPLAIN + SQL Health-Check SQLHC +• Consultant/Developer/DBA/Design/+• Oracle Performance + SQL Tuning• Oracle Database Health-Check• Tools + Scripts• Speaker Question
Everything
![Page 3: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/3.jpg)
04/13/2023 Enkitec © 3
SQL Tuning Tools of the Trade• Stand-alone Scripts• SQLHC• SQLTXPLAIN
![Page 4: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/4.jpg)
04/13/2023 Enkitec © 4
Commonalities• Input one SQL statement– SQL_ID
• Output one or more diagnostics files– Used mainly for SQL Tuning
• Make use of some Oracle licensed features– Diagnostics and/or Tuning Packs
![Page 5: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/5.jpg)
04/13/2023 Enkitec © 5
Capabilities Compare
Stand-alone Scripts SQLHC SQLTXPLAIN
![Page 6: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/6.jpg)
04/13/2023 Enkitec © 6
Footprint Compare
Stand-alone Scripts SQLHCSQLTXPLAIN
![Page 7: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/7.jpg)
04/13/2023 Enkitec © 7
Stand-alone Scripts• Specialized diagnostics for SQL Tuning– planx.sql: Execution Plans from Memory and RAC– sqlmon.sql: SQL Monitor Report– sqlash.sql: Active Session History (ASH)– mystat.sql: Performance Statistics (counters)
• Install nothing on the database
![Page 8: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/8.jpg)
04/13/2023 Enkitec © 8
SQLHC• Provides most common diagnostics needed
for SQL Tuning• Installs nothing on the database
![Page 9: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/9.jpg)
04/13/2023 Enkitec © 9
SQLTXPLAIN• Provides about everything that is ever need
for SQL Tuning– Robust set of diagnostics reports– Automatic extraction of Test Case
• Installs two self-contained schemas
![Page 10: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/10.jpg)
04/13/2023 Enkitec © 10
![Page 11: SQL Tuning Tools of the Trade](https://reader035.fdocument.pub/reader035/viewer/2022062313/557b46f9d8b42a8c528b48c1/html5/thumbnails/11.jpg)
04/13/2023 Enkitec © 11
References• Stand-alone Scripts– carlos-sierra.net
• SQLHC– MOS 1366133.1
• SQLTXPLAIN– MOS 215187.1