oracle database 10g performance …...oracle database 10g performance troubleshooting: it’s...
Post on 27-Jun-2020
41 Views
Preview:
TRANSCRIPT
Oracle Database 10g Performance Troubleshooting: It’s Automatic!
Prabhaker GongloorSr. Product Manager, Oracle Corporation
Arup Nanda Manager, DB Systems, Starwood Hotels
Load Data 6%
Ongoing System Management55%
Software Maintenance6%
Where DBA’s spend their time
Install 6%Create & Configure
12 %
Source: IOUG 2001 DBA Survey
Load Data 6%
Ongoing System Management55%
Software Maintenance6%
Where DBA’s spend their time
Install 6%Create & Configure
12 %
Source: IOUG 2001 DBA Survey
Ongoing System Management
55% of DBA’s time is spent in ongoing management, monitoring, and tuning
#1 Cause:Performance Diagnosis & TroubleshootingResource Tuning
Source: IOUG 2001 DBA Survey
Traditional Performance Tuning Methodology
Performance and Workload Data Capture– System Statistics– Wait Information, SQL Statistics, etc.
Analysis – What types of operations DB is spending most time on?– Which resources is the DB bottlenecked & What is causing it ? – What can be done to resolve the problem?
Problem Resolution – If multiple problems identified, which is most critical?– How much performance gain expected if solution implemented?
Traditional Performance Tuning
Performance and Workload Data Capture– System Statistics, Wait Information, SQL Statistics, etc.
Analysis– What types of operations database is spending most time
on?– Which resources is the database bottlenecked on?– What is causing these bottlenecks?– What can be done to resolve the problem?
Problem Resolution– If multiple problems identified, which is most critical?– How much performance gain expected if is solution
implemented?Oracle Database 10g and Diagnostics Pack Automates All Steps
and Addresses All Issues & Challenges!
Intelligent Infrastructure Overview
Automatic Workload Repository – “Data Warehouse” of Database– Code instrumentation
Automatic Maintenance Tasks– Pre-packaged, resource
controlledServer-generated Alerts
– Push vs. Pull, Just-in-time, Out-of-the-box
Advisory Infrastructure– Integrated, uniformity– ADDM, SQL Tuning and other
Advisors
Intelligent Infrastructure
Application & SQLManagement
System ResourceManagement
SpaceManagement
Backup & RecoveryManagement
StorageManagement
Database Manageme
ntAutomatic Workload Repository
Automatic Maintenance Task Infrastructure
Server-generated Alert Infrastructure
Advisory Infrastructure
Intelligent InfrastructureAWR: Automatic Workload Repository
– Stores different classes of data: - BASE STATISTICS , SQL STATISTICS , METRICS, ASH- STATSPACK++
Active Session History (ASH)– Intelligent Sampling, Efficient– Enables Fine-grain System/User Level Historical drill down
Enhanced Wait Model – Wait Events Categorized Based On Solution Area
Time Model Automatic Tracking of Operation Times, DB Time, Correlation of Benefit
across ComponentsOS Statistics: CPU, MemoryAdvisory Infrastructure
– ADDM, SQL Tuning and Other Advisors
Automatic Workload Repository (AWR)
Built-in Workload and Performance Statistics Repository Within DatabasePart of Oracle Database 10g Intelligent Self-Management Infrastructure Automatically Captures Workload Data
• Every 60 minutes, or manually, saves data for 7 days by default• Resides in Newly Introduced SYSAUX Tablespace• Server Automatically Manages Space Requirements
• Old Data is automatically purged• Stores different classes of data:
– BASE STATISTICS e.g. physical reads– SQL STATISTICS e.g. disk reads (per sql stmt)– METRICS e.g. physical reads / sec– ACTIVE SESSION HISTORY
Intelligent Infrastructure: New Base Statistics Extensive code instrumentation
Time Model (v$sys_time_model)– Automatic Tracking of Operation Times– DB time– Connection Management (logon, logoff)– Parse (hard, soft, failed,..)– SQL, PLSQL and Java execution times
Wait Model (v$system_event)– Wait Events Categorized Based On Solution Area– 800+ different wait events =>12 wait classes (Appln, Concurrency..)
OS Stats (v$osstat)– CPU + Memory
SQL statement statistics– Wait class: PLSQL, Java, etc time– Sampled bind values (v$sql_bind_capture)– Efficient Top SQL identification using ∆s in the kernel, by dimensions
PLSQL Exec
Conn MgmtParse
Java Exec
SQL Exec
DB time
Checkout using ‘one-click’
User Response Time
DB Time BrowserAPPSServer
WANLAN LANAPPSServer
Browser WAN
DB time
DB Time
Query for Melanie Craft
Novels
Browse andRead
Reviews
Add item to
cart
Checkout using
‘one-click’
Intelligent Infrastructure: 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 Active Sessions (v$session_wait)
Active Session History (ASH)
DB Time
Query for Melanie Craft
Novels
Browse andRead
Reviews
Add item to
cart
Checkout using
‘one-click’
Active Session History (ASH)
DB Time
Query for Melanie Craft
Novels
Browse andRead
Reviews
Add item to
cart
Checkout using
‘one-click’
WAITING
Statedb file sequential readqa324jffritcf2137:38:26
EventSQL IDModule
Book by author
SIDTime
CPUaferv5desfzs5Get review id2137:42:35
WAITING log file syncabngldf95f4deOne click2137:52:33
7:50:59 213 Add to cart hk32pekfcbdfr WAITING buffer busy wait
Automatic Database Diagnostic Monitor (ADDM)
Self-Diagnostic Engine In the DatabaseIntegrate all components togetherAutomatically provides database-wide performance diagnostic, including RACReal-time results using the Time ModelProvides impact and benefit analysis, non problem areasProvides Information vs. raw dataRuns proactively out of the box, reactively when required
Intelligent Infrastructure
Application & SQLManagement
System ResourceManagement
SpaceManagement
Backup & RecoveryManagement
StorageManagement
Database Management
ADDM Architecture
Automatic Diagnostic Engine
Snapshots inAutomatic Workload
Repository
Self-Diagnostic Engine inside DB
Top Down Diagnosis Using AWR Snapshots & Time-Wait ModelThroughput centric - Focus on reducing time ‘DB time’Classification Tree - based on decades of Oracle performance tuning expertiseReal-time results
– Runs proactively, reactive when required
Pinpoints root cause– Distinguishes symptoms
from the root causeReports non-problem areas
– E.g. I/O is not a problem
High-load SQL
IO / CPU issues RAC issues
System Resource
Advice
Network + DB config
Advice
SQLAdvisor
Oracle Database 10g Self-Management Infrastructure
Automatic, Efficient, LightweightMost customer deployments experience overall performance improvement
– Validated with real world customers for large, busy databases in production
Minimal Overhead
Real-World Customer Case Studies
Dell Inc.Eurostar EMEA Rollups
– Business Critical: Supports Finance, Services, Telestats units– 3-Node Red Hat 3.0 Linux Cluster, each node: 4 CPUs, 8 GB RAM; 1
TB DB, Oracle DB Release: 10.1.0.3 – Global Operations, 24x7 Availability– Challenges:
Performance Slowdown during Peak Business ProcessingWhat caused it and how can it be fixed ?
– SolutionADDM automatically identified bottlenecks on the systemUsing EM Drill down in real-time, Dell identified that Statistics are gathered in an overlapped windowChanged the Maintenance Window appropriately and peak processing throughput improvedOther cases where ADDM/SQL Tuning Advisor improved performance significantly
Qualcomm Inc.Centauri Application
– Monitoring DB across the Enterprise– Migrated DB from 8.1.7.4 to Oracle Database 10g RAC– Performance degraded after DB upgrade– Challenges:
What is causing performance degradation?How can it be fixed?
– Solution:ADDM automatically identified that a single SQL statement was consuming most of system resourcesSQL Tuning Advisor recommended an index to make the statement more efficient
ADDM identified the problem…
…the offending SQL statement. Also, recommended running SQL Tuning Advisor as the solution.
SQL Tuning Advisor immediately identified the missing index…
Qualcomm Study Contd:
Qualcomm Case Study: TOP SQL tuning details
Qualcomm Case Study: TOP SQL tuning: Index Creation details
Oklahoma State University (OSU)
Meta directory, E-Mail Provisioning System– Junior DBA managing the system after senior staff left– 2 Node RAC Cluster, Database Release 10.1.0.2– Challenges
How do you diagnose performance bottlenecks with little experience and training?
– SolutionDBA relies on EM, ADDM, AWR About 6 cases where DB performance improved by following ADDM recommendations
- SQL Profile, Indexes, Redo Log Buffer Resize- EM Monitoring
Bug DB: Oracle Internal Case Study
Business Critical System for Tracking Bugs Used Worldwide– 100 GB DB, 33000 Users, 64-bit HP-UX 2 Node RAC Cluster, Each
node 8 CPUs– Numerous cases of ADDM Automatically Diagnosing Performance
Problems Correctly
High Load SQL Statements: Recommended SQL profiles, IndexUnaccounted Jobs causing periodic performance slowdown
Tuning Net Result: CPU Usage of the System now below 70%, was close to 100 % for the last 18 months
Bug DB: Internal Case Study
Periodic Slow Down Problem – Periodic Slow down in Performance during peak
processing time– What caused the slowdown and how can it be resolved
Solution– ADDM automatically detected SQL statements causing
significant impact on the system– Using EM screens, DBA drilled down and identified Users
running 5 concurrent copies of a Program– DBA checked with IT staff and disabled the job– Performance is more predictable and has improved by 20%
Performance Management: Starwood Hotels Case Study
Arup NandaManager, Database Systems
Starwood Hotels
Background
Starwood Hotels – parent of chains St Regis, W, Westin, Sheraton, Four Points and The Luxury Collection.About 728 properties around the world … and growing.Global Data Center in US.All major databases in Oracle – 9i through 10gDBA Group in White Plains, NY
Issues
Growing Databases – In Number– In Size– In Complexity
Number of DBAs has been pretty steadyResult– Lack of Attention to Database Management – Mostly Firefights
Challenges
Proactive Management– Learn about Problems before Users Know Them– Problems are Always Preventable and Prevented
Reactive Approach to Database ManagementDBA Experience is Diverse – Junior to Advanced
Addressing the Issues
Perfect Utopia – A Tool that Sees Problems before they Materialize
Even More Perfect Utopia– It Solves them as well!
Common Tools in the Market – DBA Rejection due to In-Command-Line-We-Trust– Graphical Representation; No Value Addition– Usually Resource Hungry– Generic Advice
Enter Oracle 10g
AWR – Automatic Workload RepositoryADDM – Automatic Database Diagnostic MonitorEM – Enterprise Manager
Typical Problem Diagnosis
This is how a problem is diagnosed:– Identify the Key Metrics– Identify the Normal Steady State Metrics– Explain the Deviation
Identify the Key Metrics
STATSPACK used to be a key data collector – What happened “then”Wait Events – What is happening “now”Historical Statspack Reports – What “normally” should happen
Why Not STATSPACK
Limited Statistics CollectionNo Metrics; Only StatisticsTells What Happened; DBA Needed to Estimate Next StepsInconvenient Comparison with Historical RepositoryMaintenance – Purge, Historical Trail
Why not Statistics
Time
Tim
e W
aite
d fo
r E
nque
ue
T1 T2 T3
Indications of Trend
Problem with Stats
Statistics are Snapshots at Point-in-TimeMetrics are Trends derived from StatisticsStatistics are Meaningless; Metrics are Important
Problem with MetricsTi
me
Wai
ted
for
Enqu
eue
T1
T2
T3
Indications of Trend
Session
Tim
e W
aite
d
Histograms
select wait_time_milli, wait_countfrom v$event_histogramwhere event = 'db file sequential read'
WAIT_TIME_MILLI WAIT_COUNT--------------- ----------
1 1415022 134224 117908 13008
16 3823732 1432464 2699
128 1619256 233512 96
1024 322048 04096 1
New In Oracle Database 10g: Gives the Exact Number of Waits for the Time Period. Example: The DB waited 96 times for waits
spanning between 512 and 1024 ms
Available Histograms
V$EVENT_HISTOGRAM– For each event such as “db file sequential read”
V$FILE_HISTOGRAM– For Single Block Reads
V$TEMP_HISTOGRAM– For Temp Segment Single Block Reads
Problem with Metrics …DB CPUDB timeJava execution elapsed timePL/SQL compilation elapsed timePL/SQL execution elapsed timebackground cpu timebackground elapsed timeconnection management call elapsed timefailed parse (out of shared memory)failed parse elapsed timehard parse (bind mismatch) elapsed timehard parse (sharing criteria) elapsed timehard parse elapsed timeinbound PL/SQL rpc elapsed timeparse time elapsedsequence load elapsed timesql execute elapsed time
Elapsed Time
Elapsed Time
Time Model
New in Oracle Database 10g. These provide the elapsed time in various components such as CPU, Hard Parse, etc.
V$SYS_TIME_MODEL– Time Model for the Overall Database
V$SESS_TIME_MODEL– Time Model for a Specific Session
AWR
AutomaticConfigurableCollects Usage Statistics As WellPartitioned for Easier ManagementSource of Data for ADDMPowers Self-ManagementCollection Can be On-Demand As Well
ADDM
Analyzes the Data Collected by AWRAutomaticBuilt into the DB Kernel, not a Separate ToolTunes based on workloadAutomates The Complex Task Of Problem Detection And Root Cause IdentificationProvides RecommendationsAnalysis Can be On-Demand As Well
Key Differentiators –vs- Tools
Automatic Key Data CollectionHolistic – OS, RAC, Undo, etc.Automatic Performance DiagnosisRecommendations Based on:
– Data Collected – Not Generic– Expert Advise – Not Trivial
Analysis and Recommendations Stored for Future Reference – No Need to Regenerate Recommendations.
Enterprise Manager
Allows a Graphical Front End to ADDM and AWR.Very Intuitive and Easy
– DBAs of All Levels Can UseRecommendations Can be Implemented by Button ClickingExpanded to Cover a lot of Functionality
Workflow
AWRCollectsStatistics
ADDMAnalyzes Data and
Recommends
ViewingThrough
EM
DB
calls
Case Study
A Live Demonstration on How ADDM, AWR and EM are being used to solve typical performance problems
– Row Lock Waits– Block Contention– Inefficient SQL
Practical Considerations
Consider Accepting SQL ProfilesSQL Profile Vs. Outlines
– Outlines freeze execution plan, SQL profiles don’t– SQL Profiles are Data Dependent, Outlines aren’t
ADDM does not Replace YouAdditional Tuning Avenues Should be Explored
– Use Hash Partitioned Indexes to Reduce Buffer Busy Waits in Index Segments
– Freelist Groups Can’t Be Modified
How it Benefited Us
Proactive Monitoring Becomes EasyReactive Performance Tuning Becomes Easier with Excellent HistoryJunior DBAs can Successfully Address a Large Number of IssuesEven Experienced DBAs can Get a Boost by Reduction in the Performance Diagnosis Time
AWR, ADDM and I
I am most excited about :– Proactive Self-diagnosis– Available Information Hitherto Impossible– Time Model for Granular Time Stats– Event Histograms– OS Statistics– Pre-computed Metrics
But, it does not Replace “You”, the DBAIt’s Truly a Helper, not just a GUI Tool Overall Assessment – Two Thumbs Up !!
Conclusion
Automates management of performance issues for the Oracle Database 10g
– Automatic Performance Diagnosis– Guided problem resolution– Graphical, intuitive and easy to use – “Point & Click”
Adds significant business value– Eliminates Fire drills – Enables higher QoS– Enhances DBA’s quality of life and productivity – Makes available more resources to focus on strategic
initiatives
Next StepsRecommended Parallel Sessions
– The Self-Managing Database: Where Technology Meets the Customer: Tue 12/7/04 2:00 PM - Room 304
– The Art of Effortless Administration: Oracle Database 10g DBA Best Practices: Tue 12/7/04 3:30 PM - Room 304
– Oracle Database 10g SQL Optimization: Wed 12/8/04 3:00 PM - Room 304– Oracle Database 10g Space and Undo Management: Do's and Don'ts: Thu 12/9/04 1:00
PM - Room 302 – The Self-Healing Database: Oracle's Strategies and Directions for Automating Trouble
Diagnostic: Tue 12/7/04 10:30 AM - Room 302
Related Demos/Exhibits– Oracle Database 10g Manageability in Oracle DEMOgrounds
See Your Business in Our Software– Visit Oracle Direct in the Oracle DEMO grounds for a personalized
proposal
Related Web Sites For More Information– http://otn.oracle.com/products/manageability/database
Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S
Case Study
top related