1 / 26

Informix Performance Tuning with DBSonar

Informix Performance Tuning with DBSonar. Young Tan Director, Product Marketing Cobrasonic Software Inc. Aug. 2006 Platform:. Company Background. Cobrasonic Software Inc. is: Independent Software Vendor for IBM/Informix IBM Taiwan Software 1 Tier Reseller

nico
Download Presentation

Informix Performance Tuning with DBSonar

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Informix Performance Tuning with DBSonar Young Tan Director, Product Marketing Cobrasonic Software Inc. Aug. 2006 Platform:

  2. Company Background • Cobrasonic Software Inc. is: • Independent Software Vendor for IBM/Informix • IBM Taiwan Software 1 Tier Reseller • IBM Taiwan Informix Service & Support Partner • International Informix User Group Golden Sponsor • 2006 Q2 - awarded a contract worth US$5.5 million by Chunghwa Telecom Co. Ltd., for providing an IBM Informix Enterprise License and consulting services.

  3. Cobrasonic Partners International U. S. A. U. S. A. Southeast Asia China

  4. IT Executive Dilemma • Reality with RDBMS and Database • Black Box - Informix or any RDBMS • Tens of thousands – SQL Executions daily • Few SQL bottlenecks affect the overall performance • Suggestions from Vendors • Buy larger server with more CPUs? • Upgrade to faster hard disk? • Upgrade to newer OS version? • On-Site consultant?

  5. What deserves my attentions ? For resource intensive performance tuning, What deserves my attentions in performance tuning? SQL statements with High Frequency SQL statements with High Cost SQL statements with Sequential Scan The most influential SQL statements !!!

  6. DBSonar: #1 Informix Application Tuning Tool • DBSonar: • ‘The ultimate diagnostic and performance • tuning tool for IBM Informix databases and applications’ • Apply the SQL Skeleton and Convergence Tech. • Capture the most influential SQL statements

  7. DBSonar: #1 Informix Application Tuning Tool An expert system Configurable Real time Monitoring Repository DB to contain historical data Online Performance Analysis Periodic Performance Analysis Report Features Light cost by watching shared memory Patent-Pending SQL Convergence Tech Java-based GUI client

  8. DBSonar Shared Memory Informix VP DBSonar Process Informix Shared Memory Attached by Informix VP Informix VP Informix VP Informix VP Informix VP Informix VP Informix VP Informix VP Attach to Shared Memory and Take a snap shot every 60 sec

  9. DBSonar Architecture Informix VP DBSonar Virtual Process Informix Shared Memory Attached by Informix VP Informix VP Informix VP Informix VP Informix VP Informix VP Informix VP DBSonar Repository Database Informix VP DBSonar VP passes SM data in separate Repository Database

  10. DBSonar —GUI Presentation Server Switch Panel SQL Fatal Error Monitoring and Alert OS Performance Indicator Database Server Performance Trend DB Profile Network

  11. DBSonar - Proactive Resource Monitoring

  12. DBSonar Online - Drill Down to DB Connections Discovery by drill down

  13. DBSonar Online - Drill Down by Session ID Discovery by ID Association Association by Session ID Session ID 1239501 Session ID 1239501

  14. Trend – Get back to last bottleneck 8:00AM this morning?

  15. Trend – SQL at Point in Time 8:00AM this morning? The exact SQL statement running at 8:00am

  16. Trend – Historical data 24 hours to 30 days

  17. DBSonar —Performance Analysis Report

  18. SQL:Individual Query Freq and Cost Meaningless data for performance when Frequency == 1

  19. SQL – Typical Informix 4GL with variable DECLARE c_select CURSOR FOR SELECT name, age FROM customer FOR UPDATE LET upd_datate = “UPDATE customer SET (name, age) =(?,?” “WHERE CIRRENT OF c_select” PREPARE p_update FROM upd_stmt FOREACH c_select INTO name_variable, age_variable --- modify the variable EXECUTE p_update USING name_variable, age_variable END FOREACH One SELECT statement by 4GL = 100 UPDATEs for DBA

  20. SQL:Normalization Normalize the spelling and remove strings and constants

  21. SQL:Convergence and Skeleton Converge normalized SQL into SQL Skeleton Frequency by SQL Skeleton is the soul of DBSonar Performance Tuning SQL Skeleton

  22. The solid evidence between DEV & DBA The number of times that is caught by snap shot

  23. DBSonar SQL Tool Select ..from Select ..from Select ..from Sorted by skeleton Sorted into groups Select ..from Select ..from Select ..from Select ..from Sampling Filtering Converging Categorizing Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from 100,000 SQLs 100 SQL Skeletons

  24. SQL 80/20 Rule Select ..from Select ..from Catch the Fewer SQL (20%-) stmts with the most Influence (80%+) Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from Select ..from 80% weight 20%

  25. Summary Performance Tuning Methodology SQL Skeleton & Convergence Technology Identify – The most influential SQL Resolve – SQL Bottlenecks Periodic Performance Analysis Report Guidance – 80/20 Rule Critical stuff first

More Related