1 / 42

Oracle 10.2 for z/OS and z/Linux Performance Update

Oracle 10.2 for z/OS and z/Linux Performance Update. Agenda. Introduction Tuning – Why Diagnosis Statspack Oracle 10g Automatic Workload Repository(AWR) Oracle 10.2 Features AWR - Enhancements ASH Workload Repository compare Report Oracle 10g Advisors Miscellaneous z/OS - WLM VM.

malha
Télécharger la présentation

Oracle 10.2 for z/OS and z/Linux Performance Update

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. Oracle 10.2 for z/OS and z/Linux Performance Update

  2. Agenda • Introduction • Tuning – Why • Diagnosis • Statspack • Oracle 10g Automatic Workload Repository(AWR) • Oracle 10.2 Features • AWR - Enhancements • ASH • Workload Repository compare Report • Oracle 10g Advisors • Miscellaneous • z/OS - WLM • VM

  3. Why do we need to tune ? • Users report „bad“ response times because of • CPU Time + Wait Time • Poor performing queries • SQL-Tuning • „bad“ database parameters • Bottlenecks in „system“ (Operating system, WLM, IO/Subsystem etc.)

  4. Diagnostics

  5. Statspack – a short overview • spcreate.sql - installs Statspack (run only once) • statspack.snap - data capture (procedure) • spreport.sql - reporting • spdoc.txt - user documentation • sppurge.sql - delete Statspack data • spdrop.sql - drop Statspack

  6. Statspack – Content • Environment Section • Load Profile • Instance Efficiency • Top 5 Timed Events • TOP SQL • I/O Statistics

  7. Oracle 10gAutomatic Workload Repository (AWR)

  8. Automatic Workload Repository (AWR) • Automatically collects database instance statistics • An “automated” STATSPACK with less overhead and enhanced functionality • On by default in Oracle Database 10g • Stores data in SYSAUX tablespace • Provides DBA_HIST (historical) views

  9. Automatic Workload Repository (AWR) • Automatically collects database instance statistics • Licensed in the Diagnostics Packs • Captures statistical data • Used by • AWR-Reports • Oracle database advisors • self-management features • Coordinated across RAC instances

  10. Automatic Workload Repository (AWR) • Text and HTML Version available • Reports can be generated / viewed by • OEM • Scripts • awrrpt.sql • awrrpti.sql • ashrpt.sql (10.2) • awrddrpt.sql(10.2) • Contains information comparable to Statspack • Plus a lot of more Information

  11. Automatic Workload Repository (AWR) • Base Statistics, Metrics, SQL-Statistics,Active Session History • Automatic Snapshots (Default 1h) • “Historic” Data (Default 7 days) • Automatic Space Management • “Light Weight-Capture” DBA_HIST_xxx V$xxx MMON Statistics Workload Repository SGA Tsp: SYSAUX$ • SQL*PLUS • EM • … • ADDM • Advisors • … Internal Clients External Clients

  12. Oracle 10g SQL Statistics • SQL_id – unique hash value • SQL statement statistics • Wait class time • PLSQL time • Java time • Sampled bind values (v$sql_bind_capture) • Default=900 • Efficient top SQL identificationusing Δs in the kernel, by 6 dimensions: • CPU • Elapsed • Parse • ...

  13. Active Session History (ASH) • Samples active sessions every second into memory (v$active_session_history) • Direct access to kernel structures • Selected samples flushed to AWR • Data captured includes: • SID • SQL ID • Program, Module, Action • Wait event# • Object, File, Block • actual wait time (if captured while waiting) Sampled history of v$session_wait

  14. Active Session History • Part of AWR • Helps to analyze • Short term problems (minute history) • Isolation of the cause by SQL_ID, SESSION_ID, MODULE etc. • Blocking Sessions (Enqueue, buffer busy wait) • Called by • ASH Report (ashrpt.sql or Enterprise Manager) • Hang Analyze

  15. Add item to cart Checkout using ‘one-click’ Query for Novels Browse and Read Reviews DB Time Active Session History (ASH)

  16. Add item to cart Checkout using ‘one-click’ Query for Novels Browse and Read Reviews DB Time Active Session History (ASH) Time SID Module SQL ID State Event Book by author 7:38:26 213 qa324jffritcf WAITING db file sequential read 7:42:35 213 Get review id aferv5desfzs5 CPU 7:50:59 213 Add to cart hk32pekfcbdfr WAITING buffer busy wait 7:52:33 213 One click abngldf95f4de WAITING log file sync

  17. Active Session History - Examples

  18. Active Session History - Examples

  19. Active Session History - Examples

  20. Active Session History - Examples

  21. Active Session History - Examples ASH Report For PROD01/PROD011 DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ PROD01 2608917897 PROD011 1 10.2.0.2.0 YES prodx005 CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size ---- ------------------ ------------------ ------------------ ------------------ 4 8,192M (100%) 6,224M (76.0%) 1,899M (23.2%) 8.0M (0.1%) Analysis Begin Time: 07-Feb-07 14:45:21 Analysis End Time: 07-Feb-07 15:00:21 Elapsed Time: 15.0 (mins) Sample Count: 6,215 Average Active Sessions: 6.91 Avg. Active Session per CPU: 1.73 Report Target: None specified

  22. Active Session History - Examples Top User Events DB/Inst: PROD01/PROD011 (Feb 07 14:45 to 15:00) Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- enq: TT - contention Other 47.59 3.29 CPU + Wait for CPU CPU 43.56 3.01 enq: HW - contention Configuration 4.39 0.30 ------------------------------------------------------------- Top Background Events DB/Inst: PROD01/PROD011 (Feb 07 14:45 to 15:00) No data exists for this section of the report. ------------------------------------------------------------- Top Event P1/P2/P3 Values DB/Inst: PROD01/PROD011 (Feb 07 14:45 to 15:00) Event % Event P1 Value, P2 Value, P3 Value % Activity ------------------------------ ------- ----------------------------- ---------- Parameter 1 Parameter 2 Parameter 3 -------------------------- -------------------------- -------------------------- enq: TT - contention 47.59 "1414791172","14","16" 35.80 name|mode tablespace ID operation "1414791174","14","16" 11.79

  23. Active Session History - Examples Top Sessions DB/Inst: PROD01/PROD011 (Feb 07 14:45 to 15:00) -> '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event -> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics. Sid, Serial# % Activity Event % Event --------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 1067,10368 13.44 CPU + Wait for CPU 13.42 PROD_APP00 DFSPCC20 834/900 [ 93%] 0 1059,30273 13.42 CPU + Wait for CPU 13.40 PROD_APP00 DFSPCC20 833/900 [ 93%] 0 1052,36644 12.98 enq: TT - contention 11.79 PROD_APP00 733/900 [ 81%] 0 1055, 3522 12.87 enq: TT - contention 11.79 PROD_APP00 733/900 [ 81%] 0

  24. Workload Repository Compare Report

  25. Workload Repository Compare Report

  26. Workload Repository Compare Report

  27. Oracle 10g Advisors

  28. Advisors : Overview SQL Tuning Segment SQL Access Mttr ADDM Space Undo PGA Buffer Cache Memory SGA Shared Pool Obsolete if using SGA_TARGET

  29. Buffer Cache sort sort PGA Pool PGA Pool SGA_TARGET • Self optimizing PGA • PGA_AGGREGATE_TARGET • Self optimizing SGA • SGA_TARGET Online Batch SGA Pool SGA Pool Buffer Cache SQL Cache SQL Cache Java Pool Java Pool

  30. Integration SQLTuning SQLAccess … ADDM Segment Advisor Infrastructure UnifiedInterface AWR Common Data Source Advisors: Integration

  31. Automatic DB Diagnostic Monitor (ADDM) • Part of Diagnostic Pack • Automatic Diagnostic of performance problems • Integrating all components Application & SQL Management Storage Management System Resource Management Space Management Backup & Recovery Management Database Management Intelligent Infrastructure Proactive and effective tuning

  32. ADDM - Overview • Database-wide performance diagnostics, including RAC • Tuning Metric: Reduce DB-Time • Throughput centric, top down • Root Causes with impacts • Differentiation of causes and symptoms • Actionable, detailed recommendations with benefit • Runs automatically every hour (AWR-Snap) or manually • May recommend calling other advisors

  33. SQL Tuning Advisor: Automatic SQL Tuning I can do it for you ! ADDM DBA High-LoadSQL SQL Workload SQL Tuning Advisor

  34. Automatic SQL Tuning Overview SQL Tuning Recommendations Automatic Tuning Optimizer SQL Tuning Advisor Gather Missing or Stale Statistics Statistics Analysis SQL Profiling Create a SQL Profile DBA Add Missing Indexes Access Path Analysis SQL Structure Analysis Modify SQL Constructs

  35. Automatic Tuning Optimizer (ATO) • It is the query optimizer running in tuning mode • Uses same plan generation process but performs additional steps that require lot more time • It performs verification steps • To validate statistics and its own estimates • Uses dynamic sampling and partial executions • It performs exploratory steps • To investigate the use of new indexes that could provide significant speed-up • To analyze SQL constructs that led to expensive plan operators

  36. Miscellaneous

  37. z/OS WLM • Everything works fine without peaks (e.g.CPU 30%) • Common Problems we had with WLM(during peak periods) • The „Everything is important syndrom“ • User didn´t classify any discretionary goals • Everything had the same importance • Enclave(Sess) with response time goals • Enclave goes to last period (which was discretionary) shortly after Logon • No default service class for OSDI • Mistake in classification rules will result in SYSOTHER being used – discretionary goal

  38. Oracle 10g on z/Linux • Async-I/O • - Process passes I/O-request to OS • - work is processed in parallel • - OS sends an interrupt when the work is finished • • Direct-I/O • - Unix Filesystem Buffer Cache is not used • - no double buffering

  39. Oracle 10g on z/Linux • Async I/O changes on init.ora. • Raw devices • - DISK_ASYNCH_IO = TRUE • Filesystem • - FILESYSTEMIO_OPTIONS=ASYNC • - FILESYSTEMIO_OPTIONS=SETALL • Async I/O + Direct I/O

  40. Oracle 9.2 / 10g on z/LinuxStorage Management L Linux Guest 1 The Bar Linux Guest 2 Linux Guest n XSTOR Expanded Memory

  41. Oracle 9.2 / 10g on z/LinuxStorage Management L Linux Guest 1 Linux Guest 2 VM 5.2 Linux Guest n XSTOR

More Related