Tips and Tricks to Make Your Database Dance

Tips and Tricks to Make Your Database Dance Michael Abbey and Mike Corey Ntirety LLC Veritas Vision San Franciso CA April 25, 2005

Agenda • Commercial or home-grown • Space alerts • Performance bottlenecks • Environment concerns • Best practices

Commercial or home-grown • Consider • speed with which the technology changes • complexity of the technology • ability to monitor new features at the "flip of a switch" • Weigh cost factors • expense of initial acquisition and licencing • initial development and ongoing maintenance

Commercial or home-grown • Oracle's new features • Oracle7: cost-based optimizer • Oracle8: partitioning • Oracle9: RAC • Database 10g: ASM • Ongoing maturation of the cost-based optimizer • "Keeping up with the Sharmas" is more than half the work

Commercial or home-grown • SQL Server's new features • multiple instances on the same server • enhanced snapshot replication, transactional replication, and merge replication • failover and failback enhancements • Additional features of clustering provide opportunities (not to mention monitoring) • As the software changes, so must monitoring

Under the covers • Flavour of what Veritas is doing … more efficiently than home-grown • Concepts are vendor-independent • Jargon may differ • Areas needing manual supervision may differ • Same issues … different vendors

Space alerts • Fixed amounts of disk available to support your databases • Deficiencies, if not trapped by you, will lead to application downtime • Political issues with your SA if you consume and consume and consume … space • Some devices handle expansion—plan and do not be caught off-guard

1- Fixed amounts of disk • Intelligent use of existing space • Get a handle on the layout of your large objects • Track the fastest growing objects • Biggest problems with acquisition: • Exponential growth (what happened to all ny disk?) • Overhead at runtime when objects allowed to grab itty-bitty chunks of space

2 – Deficiencies => downtime • Application abends • User community gets cranky • Management gets on your case • You get crabby • You don’t do your best • Lose interest in your job • Pay little or no attention to what matters

3 – Political issues with your SA • You MUST get along with the important players in your organization • Liaise with your SA, then train Veritas to alert you after the 2 of you agree it’s important • A happy SA = happy applications • Easier to get a hold of • Tight integration with sysadmin leads to quick resolution of issues

4 – Plan for expansion • Do not be caught off-guard • How much bigger can you go • Do not rely on ability to expand as a crutch • Expandable space today may not be there tomorrow • Especially true in desktop (i.e., SQL Server) • Other applications coming online / sharing resources over and above just disk

Performance bottlenecks • Bad SQL • Overloaded devices • Storage-related bottlenecks • Physical vs. logical reads • Locks • CPU hogs • I/O nightmares

1 – Bad SQL • Zero in on resource-intensive SQL • Most frequent executors • Statements that access data and index information on multiple devices • Cartesian products • Explain SQL repetitively • Asses candidates for additional indexes

2 – Overloaded devices • Targets of unusual I/O activity • Hot data blocks • Where do they live • Are they on your fastest devices • Nature of activity … select insert update delete • Distribution metrics—who resides where • Object affinity—separate or shared devices??

3 – Storage related bottlenecks • Exorbidant extent size increases • 2 4 8 16 32 64 128 256 512 1024 2048 4096 8192 16384 32768 65536 131072 262144 524288 1048576 (20th) • Tons of allocated but unoccupied space • Objects with many many many many extents T r o u b l e

4 – Physical vs. logical reads • Are logical reads still cheaper • Faster and faster disk • Vendor imitations of raw devices • How much memory should be allocated to the database—what else does machine support • Leverage caching mechanisms • Are logical reads still cheaper ??

5 - Locks • Duration • Type • Blocking • Nature of the beast—tool cannot report false bottlenecks • Exclusive, share update, DML, DDL • Shared, update, intent, key-range O S

6 – CPU hogs • Metrics over extended time periods • Who are the repetitive culprits • Candidates for new indexes • Complete rewrite • Tool integration with O/S statistics • Sampling rate • Bottom line—applications do like and will consume lots of CPU—is that in itself a problem?

7 – I/O nightmares • I/O bound operations • Waiting and waiting and waiting to scoop data from disk • Where the biggest data volume is being requested • Size of a result set • Size of intermediary work objects • Change in behaviour over extended periods

Best practices • Define items that require an alert • Ensure the monitoring tool can trap their occurrence • Those it cannot, program yourself • Perl • PL/SQL • Transact SQL / imbedded @@ functions

Best practices • Tolerance for monitoring checks • Dictated by • User preference • Granularity of system clock • How long can you “afford” to be in error condition • Does it make sense from a licencing perspective

Best of breed 3 3 i for SQL Server i for Oracle

Contact information michael.abbey@ntirety.com michael.corey@ntirety.com

Tips and Tricks to Make Your Database Dance