1 / 22

Using Snapshot monitor SQL Administrative Views for Performance Analysis on V10.1

Anthony Reina - Accelerated Value Specialist aereina@ca.ibm.com. Using Snapshot monitor SQL Administrative Views for Performance Analysis on V10.1. IBM Software Accelerated Value Program. The IBM Software Accelerated Value Program delivers a proactive, cost-reducing, and

vivian
Download Presentation

Using Snapshot monitor SQL Administrative Views for Performance Analysis on V10.1

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. Anthony Reina - Accelerated Value Specialist aereina@ca.ibm.com Using Snapshot monitor SQL Administrative Views for Performance Analysis on V10.1

  2. IBM Software Accelerated Value Program • The IBM Software Accelerated Value Program delivers a proactive, cost-reducing, and productivity enhancing advisory service. The program pairs you with an assigned team who build a foundational understanding of your overall environment. Through that understanding, the trusted partner works to facilitate faster deployment, lifecycle leadership, risk mitigation, and more by identifying ways to improve your environment, staff skill set, and processes. http://www-01.ibm.com/software/support/acceleratedvalue/ 2

  3. AGENDA • Introduction • Snapshot Administrative Views • Usage/Example

  4. Introduction Often times we encounter situations where increase in the workload against the database starts hindering overall database performance. Although this can be caused by the environment configurations, changes to the system, resource bottleneck, or simply increase in workload. It’s very possible queries running on the database are not running optimal as expected. Different methods for identifying long running sql’s… • DB2 Snapshots (ie. Application, Dynamic, etc…) • DB2 Diagnostics (ie. db2pd, db2fodc –perf/hang) • 3rd Party Monitoring Tools • DB2 Access Plans • Snapshot Administrative Views

  5. Snapshot Administrative Views • System Defined • Provide a primary, easy to use programmatic interface. • Includes a collection of… • Built-in views • Table functions • Procedures • Scalar functions for various DB2 task • Can be invoked from… • SQL Based application • DB2 Command Line • Command Script • DFT_MONT_STMT and DFT_MON_TIMESTAMP dbm cfg should be enabled.

  6. Snapshot Administrative Views • APPL_PERFORMANCE displays information about the percentage of rows selected by an application. • LONG_RUNNING_SQL returns SQL statements executed in the currently connected database. This can be used to verify long-running SQL statements in the database. • QUERY_PREP_COST returns a list of statements with information about the time required to prepare the statement. • TOP_DYNAMIC_SQL returns the top dynamic SQL statements sortable by number of executions, average execution time, number of sorts, or sorts per statement.

  7. Snapshot Administrative Views APPL_PERFORMANCE

  8. Snapshot Administrative Views LONG_RUNNING_SQL

  9. Snapshot Administrative Views QUERY_PREP_COST

  10. Snapshot Administrative Views TOP_DYNAMIC_SQL

  11. Example Scenario : Two set of workload is run from the environment identified as run_wkld1 and run_wkld2. DB2 DBA, would like to identify what are the queries being run from the two workload processand identify potential SQL problems.

  12. ExampleUse APPL_PERFORMANCE to see what applications are running…

  13. ExampleAPPL_PERFORMANCE view output The db2bp for AGENT_ID 75, 8, and 7 would more likely to be running SQL’s.

  14. ExampleUse LONG_RUNNING_SQL to identify longest running queries that are currently being executed…

  15. ExampleLONG_RUNNING_SQL view output 1st data collected AGENT_ID 8 and 7 is running for 1 min. and actual query is identified.

  16. ExampleLONG_RUNNING_SQL view output 2nd data collected AGENT_ID 8 and 7 is running for 3 min. and actual query is identified.

  17. ExampleUse QUERY_PREP_COST_TIME to determined how frequent a query is run as well as average execution time for each query…

  18. ExampleQUERY_PREP_COST view output Q1 Q2/ AGENT_ID 8 Q3 Q4 Q5/ AGENT_ID 7

  19. ExampleUse TOP_DYNAMIC_SQL to determine most frequently executed and longest-running SQL statements…

  20. ExampleTOP_DYNAMIC_SQL view output Q1 Q2/ AGENT_ID 8 Q3 Q4 Q5/ AGENT_ID 7

  21. Other Snapshot Admin Views • BP_HITRATIO returns bufferpool hit ratios, including total, data , XDA, ratio, and index hit ratio. • CONTAINER_UTILIZATION returns information about the table space containers and utilization rates. • LOG_UTILIZATION returns information for the currently connected database per partition. • Full list of Snapshot Admin Views

  22. Questions?

More Related