Database P

1
Database P T

• Manish Fatehpuria
• DBA
• Pepboys Manny Moe Jack

2
Topics for Discussion

• Database Performance
• Database Tuning
• Database Monitoring
• Application Tuning

3
DB Performance

• What is Performance ?
• What affects Performance ?

4
What is Performance

• Response Time
• Wait
• Contention
• Resources
• Throughput
• Single Transaction
• Server/Instance Wide
• System Wide

5
What Affects Performance

• DB Design
• Logical Physical
• Configuration Parameters
• Regular DB Maintenance
• Application Design
• Hardware
• CPU
• Disk
• Memory
• Network

6
Logical Database Design

• Data Modeling
• Entity Relationship
• Entity Types
• Normalization / Denormalization
• 3rd Normal Form
• No transitive dependencies (no non-key attribute
  is dependent on another non-key attribute).
• Adding redundant / derived columns
• Table splitting/collapsing

7
Physical Database Design

• Raw Partition Vs. File system
• Data and Index Device
• System Catalog Location
• Redo Log
• Archive Log
• Temp space for sorting in the database

8
Physical DB Design Cont.

• Type of Tablespace
• SMS/DMS (DB2)
• Locally/Globally managed (Oracle)
• No. of datafiles per tablespace
• Temp Space on Solid State Disk

9
DB Configuration Parameters

• Query Optimization Level (DB2)
• Rule Based Vs. Cost Based optimizer (Oracle)
• Degree of Parallelism
• Data Buffer Size
• Buffer Pool
• Prefetch
• SQL Buffer Size

10
Database Maintenance

• Update Statistics
• Reorg Tables/Indexes
• Run Checks for data/index corruption
• Delete/Archive Old Data

11
Application Design

• Simple and Efficient Queries
• Use Indexes
• Efficient Batch Size
• Commit Frequently
• No Explicit Locks Unless Required
• Use Stored Procedures/Functions/Triggers
• Do Basic Data Validation Upfront

12
Hardware

• Number Of Nodes
• EE Extended (DB2)
• EE with Parallel Option (Oracle)
• CPU
• Number
• Speed
• Disk
• Internal
• SAN

13
Hardware Cont.

• Memory
• Physical
• DB memory
• Network
• Protocol
• Bandwidth (ATM, Gigabit, 100 MegaBit)

14
Database Tuning

• Identification of the problem
• Specific Query Performance
• Overall Performance
• Particular Application
• Other..

15
Database Tuning / Troubleshooting

• Overnight increase in data
• Overnight addition of application/users
• Data corruption
• Missing Index
• Runaway Queries
• Space issues
• Hardware problems
• Network Change

16
Database Monitoring

• Snapshots (DB2)
• Show status of DB counters at given time
• Event Monitor
• Keep counter after the event
• Deadlock
• Connection
• Table Reads
• Other..

17
Database Monitoring Cont.

• System Counters (Oracle)
• v Tables
• Dynamic tables
• Keep counters from the last db restart
• UTLBSTAT/UTLESTAT
• Keep counter over time
• Display Various Database utilization statistics
• Statspack Package

18
Application Tuning

• Efficient Queries
• Explain Facility
• Plan Tables (Oracle)
• Explain Tables (DB2)
• Visual Explain
• Query Plan and cost statistics

19
Questions ?