oracle 10.2 for z/os and z/linux performance update
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/1.jpg)
Oracle 10.2 for z/OS and z/Linux
Performance Update
![Page 2: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/2.jpg)
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
![Page 3: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/3.jpg)
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.)
![Page 4: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/4.jpg)
Diagnostics
![Page 5: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/5.jpg)
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
![Page 6: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/6.jpg)
Statspack – Content
Environment Section
Load Profile
Instance Efficiency
Top 5 Timed Events
TOP SQL
I/O Statistics
![Page 7: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/7.jpg)
Oracle 10gAutomatic Workload Repository
(AWR)
![Page 8: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/8.jpg)
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
![Page 9: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/9.jpg)
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
![Page 10: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/10.jpg)
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• PlusPlus a lot of more Information
![Page 11: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/11.jpg)
DBA_HIST_xxx
Workload
RepositoryV$xxx
Statistics
SGA
• Base Statistics, Metrics, SQL-Statistics,Active Session History
• Automatic Snapshots (Default 1h)
• “Historic” Data (Default 7 days)
• Automatic Space Management
• “Light Weight-Capture”
MMON
Internal Clients
External Clients
Automatic Workload Repository (AWR)
-ADDM-Advisors-…
Tsp: SYSAUX$
-SQL*PLUS-EM-…
![Page 12: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/12.jpg)
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 identification using Δs in the kernel, by 6 dimensions:
• CPU• Elapsed• Parse• ...
![Page 13: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/13.jpg)
Active Session History (ASH)
Sampled history of v$session_wait
• 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)
![Page 14: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/14.jpg)
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
![Page 15: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/15.jpg)
DB Time
Query for Novels
Browse andRead
Reviews
Add item to
cart
Checkout using
‘one-click’
Active Session History (ASH)
![Page 16: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/16.jpg)
DB Time
Query for Novels
Browse andRead
Reviews
WAITING
Statedb file sequential readqa324jffritcf2137:38:26
EventSQL IDModuleSIDTime
CPUaferv5desfzs5Get review id2137:42:35
WAITING log file syncabngldf95f4deOne click2137:52:33
WAITING buffer busy waithk32pekfcbdfrAdd to cart2137:50:59
Add item to
cart
Checkout using
‘one-click’
Book by author
Active Session History (ASH)
![Page 17: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/17.jpg)
Active Session History - Examples
![Page 18: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/18.jpg)
Active Session History - Examples
![Page 19: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/19.jpg)
Active Session History - Examples
![Page 20: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/20.jpg)
Active Session History - Examples
![Page 21: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/21.jpg)
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
![Page 22: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/22.jpg)
Active Session History - Examples
Top User Events DB/Inst: PROD01/PROD011 (Feb 07 14:45 to 15:00)
Avg ActiveEvent Event Class % Activity Sessions----------------------------------- --------------- ---------- ----------enq: TT - contention Other 47.59 3.29CPU + Wait for CPU CPU 43.56 3.01enq: 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.80name|mode tablespace ID operation
"1414791174","14","16" 11.79
![Page 23: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/23.jpg)
Active Session History - ExamplesTop 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.42PROD_APP00 DFSPCC20 834/900 [ 93%] 0
1059,30273 13.42 CPU + Wait for CPU 13.40PROD_APP00 DFSPCC20 833/900 [ 93%] 0
1052,36644 12.98 enq: TT - contention 11.79PROD_APP00 733/900 [ 81%] 0
1055, 3522 12.87 enq: TT - contention 11.79PROD_APP00 733/900 [ 81%] 0
![Page 24: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/24.jpg)
Workload Repository Compare Report
![Page 25: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/25.jpg)
Workload Repository Compare Report
![Page 26: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/26.jpg)
Workload Repository Compare Report
![Page 27: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/27.jpg)
Oracle 10g Advisors
![Page 28: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/28.jpg)
Advisors : Overview
ADDM
SQL Tuning
SQL Access
Space
Mttr
Undo
Memory
PGA
SGA
Buffer Cache
Shared Pool
Segment
Obsolete if using SGA_TARGET
![Page 29: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/29.jpg)
• Self optimizing PGA• PGA_AGGREGATE_TARGET
• Self optimizing SGA• SGA_TARGET
sort
PGA Pool
sort
PGA Pool
SQL Cache
Online
Buffer Cache
Java Pool
SGA Pool
Batch
Java Pool
SQL Cache
Buffer Cache
SGA Pool
SGA_TARGET
![Page 30: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/30.jpg)
Advisors: Integration
Advisor Infrastructure
ADDM SQLTuning
SQLAccess
Segment …
AWR
UnifiedInterface
Integration
Common
Data Source
![Page 31: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/31.jpg)
Automatic DB Diagnostic Monitor (ADDM)
• Part of Diagnostic Pack• Automatic Diagnostic of
performance problems• Integrating all components
Intelligent Infrastructure
Application & SQLManagement
System ResourceManagement
SpaceManagement
Backup & RecoveryManagement
StorageManagement
Database Management
Proactive and effective tuning
![Page 32: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/32.jpg)
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
![Page 33: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/33.jpg)
SQL Tuning Advisor: Automatic SQL Tuning
I can do it for you !
SQL Tuning Advisor
DBAHigh-Load
SQL
ADDM
SQL
Workload
![Page 34: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/34.jpg)
Automatic SQL Tuning Overview
Add Missing Indexes
Modify SQL Constructs
Create a SQL Profile
Automatic Tuning Optimizer
SQL Structure Analysis
Access Path Analysis
SQL Profiling
Statistics Analysis
Gather Missing or Stale Statistics
DBA
SQL Tuning
RecommendationsSQL Tuning
Advisor
![Page 35: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/35.jpg)
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
![Page 36: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/36.jpg)
Miscellaneous
![Page 37: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/37.jpg)
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
![Page 38: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/38.jpg)
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
![Page 39: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/39.jpg)
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
![Page 40: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/40.jpg)
Oracle 9.2 / 10g on z/LinuxStorage Management
L
Linux Guest 1
Linux Guest 2
Linux Guest n
XSTOR
The Bar
Expanded Memory
![Page 41: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/41.jpg)
Oracle 9.2 / 10g on z/LinuxStorage Management
L
Linux Guest 1
Linux Guest 2
Linux Guest n
XSTOR
VM 5.2
![Page 42: Oracle 10.2 for z/OS and z/Linux Performance Update](https://reader035.vdocuments.us/reader035/viewer/2022062301/56813b85550346895da4af5f/html5/thumbnails/42.jpg)