DBSonar - Slice and Dice Performance Tuning and Management for IDS 7.x, 9.x and 10.x

2. DBSonar -Slice and Dice Performance Tuning and Management for IDS 7.x, 9.x and 10.x • Eric Lam • CTO/Cobrasonic

3. Difficulties in database performance tuning • Massive data with unknown definition • No relationship between DATA • Lack of Interpretation knowledge • No historical evidence information is kept • It happened, but when? Why? What’s the Impact? • Fighting among AP, DB, and HW teams • Human is not designed for routine checking…

4. Mission: A non-stop expertise providing tight linked information for browsing and discovering Collect  Integrate  Narrow Down  Root Cause • Provides analysis and suggestion about real-time/historical resource consumption • Cross-linking data from Net, OS, DB and applications • Performs slice and dice analysis as OLAP tool to bring out a clear picture of bottleneck • Tells what happened inside the user application itself • Continuously monitors database systems for potential problems and issues precautious alerts for you to solve these problems before they impact end-users. • Allowing system administrators to make informed decisions in handling exceptions or planning resources. • Make you an expert to work productively instead of routinely.

5. OS AP DBSonar Server Collecting, Integrating, Interpreting OS AP DBSonar Server Collecting, Integrating, Interpreting DBSonar —System Architecture DBSonar Client Interactive Analysis Tool Repository Alert Deliver Service

6. Server Switch Panel SQL Fatal Error Monitoring and Alert OS Performance Indicator Database Server Performance Trend DB Profile Network

7. Integrated Performance Analysis • Integrates OS, DB and Application • Top down analysis

8. The power of cross-linking • What if System Busy now???

9. Tracking Individual Busy Connection • A language independent DB application inspector • Easily pin-point the bottleneck instead of tracing the source code I don't want to trace someone's code!!!

10. SQL Statement Normalization • seleCt * from CUSTOMER where fname=‘Eric’ and age = 36; • Select * from cuStomer WHERE Fname=‘Brian’ AND age = 32 • select * from customer where fname=‘Spark’ and age =35 The ONLY way to do meaningful SQL tracing It does matter whatever “condition” have been changed in the where claust, or formatting, all will be normalized to be an identical one!

11. Cost-evaluation of problematic SQL statements Statements are not executed (even on IDS 7.x)

12. How the table is being used?

13. Check SQL against busy table • Evaluate the criteria, whether it choose the right path? • Whether additional index need to added or restructured? • Drill down to see SQL Analysis Information

14. Customizable Multilayer Trend-chart Display Events are connected based on point in time SQL at point in time can also be retrieved to check where the bottleneck came!

15. Real-Time & Historical Trend Chart • Real-Time System Monitoring Trend Chart • Disk I/O、Memory 、CPU 、SQL • Zoom in for Detail Utilization Trend Chart • Historical Utilization Trend Chart • OS – Disk I/O、Memory 、CPU 、SQL • Database – DBSpace I/O 、Buffer read/write 、Checkpoint … • Both for Hourly and Daily Trend Chart • The time-series based chart analysis brings the relationship between event and resource utilization • Service as source for capacity planning

16. Expert system for onconfig parameter optimization Effective Value Original Value Parameters Suggestion

17. Maintain a continuous service environment • Event Based Alert Subsystem (EMail, SMS) • When/Why/Who causes the issue? • Fast drill down approach to narrow down to the causing to ensure service continuous and recover fast • Log event and associated info for long term issue elimination

18. Instability causing Table Failure Dead Lock Database resource overflow Assertion Fail Chunk down Long Transaction Disk Full Backup Aborted Logical Log Full Unable to allocate memory

19. Facility for problem resolving Problem Resolve Quickly

20. Proactive Resource Monitoring

21. Display of the IDS Online Logs • Important Messages -> highlighted • Unimportant Messages -> filteredout

22. Analysis Reporting

23. Database Health Check Reports • Analysis Action Guide – System Tuning iteratively • Outstanding issues list both system and AP • Time series based historical trend charts integrate and form a clear picture among OS, DB and AP • Hardware Capacity Plan for Future

24. Database Performance Analysis Report (4)

25. Speed-up Your Business with Intelligent Administration DBSonar™ offers • Higher Overall System Availability • Optimized Hardware Utilization • Faster Application Response Time • Reduced System Administration Effort • Reduced Software Development Costs

26. DBSonar - Product Development Focus Performance Reliability IDS 7.x/9.x/10.x DisasterRecovery Security

27. Session TitleSession: ##### Eric Lam Cobrasonic Inc. eric.lam@cobrasonic.com

28. Product Plan • Deliver new Rule and Drill Down Tree • SNMP support • GUI enhancement, optimize in content aspect • SQL optimizer rewriter • Access shared memory instead of sysmaster • Report in PDF formant, support report customization • Support Save Directives • Generate update statistics SQL command • Tuning on Data Dictionary