Scaling to 100 Million Rows

1
Scaling to 100 Million Rows Per Day!

• David Scott
• GOusers

2
Speaker Qualifications

• Database Practice Manager at Intec Billing
• President of GOusers
• the Georgia Oracle Users Group
• Has presented at OOW, IOUG, GOuser, and other
  conferences
• Oracle DBA/developer since 1992

3
Abstract

• Have you ever heard that scaling an application
  is not simply a matter of adding more CPU, RAM,
  and disk storage? That is absolutely true. This
  presentation will share insights gained while
  expanding the processing capacity of an existing
  application to 100 million rows per day. Yes, it
  can be done!

4
Objectives

• Relate the experiences gained during scalability
  and performance testing of an application.
• Identify technical challenges associated with
  scaling applications.
• Identify chosen solutions to scalability
  challenges.

5
The Apps

• 1 from 6M/day to 100M/day
• Analytics and transactions (9iR2 gt 10gR1)
• 2 designed for 200M/day
• Data Warehousing (10gR2)
• 3 designed for 40M, observed peak at 120M/day
• Transaction processing (9iR2)
• And others

6
Prevalent Project Pitfalls

• Extrapolating results from smaller tests
• Lack of experience with larger platform
• Configuration and operation
• Short testing window
• Inadequate staffing
• The longevity of the temporary
• Previous limitations still exist?
• Previous methods still the best?
• Good enough

7
Tech Traps

• Just add ____ everything will be fine.
• Disk / RAM / CPU
• Job threads / Parallel Query
• RAC / The latest Oracle version
• Magic pixie dust
• Inadequate/Over-adequate SW Architecture
• I/O saturation masks other bottlenecks
• Creating test data

8
The light at the end of the tunnelis an
oncoming train.

• Just run it in parallel
• PDML, INSERTS, indexing, summaries
• CPU usage
• The Sears Tower syndrome
• All features/processes must scale
• You dont know what you dont know!
• Unless youve been there, done that

9
Oh, the Horror! Scalability Inhibitors

• UPDATE and DELETE statements
• Concurrency blocking and locking
• No partitioning / incorrect partitioning
• CURSOR FOR loop
• Incorrect indexing
• Nasty table designs
• Inadequate configuration
• And more

10
Why the Code Doesnt Scale

• Ignores new code database features
• Still using RULE?
• Can you rewrite the SQL?
• Trigger happy?
• Inefficient SQL PL/SQL
• Bad hints
• Ill-conceived scalar sub-queries
• Function calls in SQL
• Query generators Beware!

11
Why the DB Doesnt Scale

• Unnecessary I/O
• Non-essential constraints
• Data movement
• Poor design, really awful design
• Incorrect indexing Bitmaps B-trees
• Large collisions on small objects
• Sequences, headers
• Inadequate configuration
• How robust is your tuning? Hints?

12
Weapons in the War

• DESIGN!
• SAN bandwidth
• Oracle Partitioning
• Manual Partitioning
• Parallel Query
• Bitmap Join Index
• PDML
• Materialized Views

• NOLOGGING
• IOTs
• ATEP
• Temporary Tables
• Registration Tables
• Direct Path
• Compression
• Statistics

Find the 2nd right answer
13
More Firepower

• OEM, tracing, and tkprof absolutely essential
• Direct insert into table with different
  partitioning scheme
• Derive summaries from staging table
• Bind variables if appropriate
• Choreography for data and processes
• DBMS_STATS.SET_TABLE_STATS
• Configuration memory management

14
UPDATE Millions of Rows

• Just say NO
• Enough UNDO? ORA-1555? NOLOGGING?
• If millions, updates are usually a status or
  other limited domain value
• INSERT pk of original into status registration
  table
• Partitioning, subpartitioning can help
• Use GTT tables
• Read asktom.oracle.com for LOTS more.

15
What We Did Right

• SQL stored in a table
• VERY easy to research and tune
• Get expert help
• Installing, configuring, operating hardware
• Installing and tuning RAC
• Take time to test before users see it!
• Incremental scaling development (1,2,4,)
• Test with actual hardware environment
• Test with production volumes (and then some)
• Willing to re-write and re-architect some.

16
First Steps Preparing to Scale

• Re-read the original requirements
• Identify current efficiencies problems
• Characterize your new workload
• Find the low hanging fruit
• Identify the main challenges
• Start with a well-operating implementation
• Establish milestones interim targets
• For processes and data, not just the project

17
A Few More Startup Tips

• Test at scale
• Know your DATA and functional requirements
• Know the necessary performance RESULT
• Design for results at scale
• Tradeoff academic correctness
• Let each piece do what it does best
• Use the right tools. If you have a hammer
• Study!
• Know relevant features, pros and cons, tuning

18
Scalability Targets

• Number of users
• Number of records/rows
• Number of queries/transactions
• Process time windows
• Define your targets carefully

19
Tuning and Scaling are Related

• A well-tuned system is a candidate for scaling
  (and the converse is also true)
• Low waits
• Efficient use of db structures
• Balance I/O for bandwidth and time
• Tuning tools identify scalability inhibitors
• Wait events
• Profilers
• Monitors

20
Bugs? YMMV.

• Mitigating Factors
• Oracle Support and other resources
• Architect to avoid the problems
• Experiences
• Materialized View problems
• ALL_SUMDELTA
• ASM ORA-04031
• Etc
• Each system will have different challenges

21
Working with Support

• Create a proper test case to identify the problem
• Prove it with a script
• Reduce the script to the essentials
• Identify the business impact of the problem
• Helps assign priority
• Establish value and time frame

22
Bolt-On vs. Built-In

• Configuration Fixes
• Some Indexes
• Tuning
• Compression
• etc.
• Some dramatic improvements
• Worth the investment
• Best for short-term and interim goals

• Re-coding
• Partition operations
• PDML
• Temp tables
• etc.
• Most worthwhile improvements
• Longer timeframes
• Least side effects

23
Re-Architecture 101

• Investigate Profile code process architecture
• Is the old way still the best way?
• What is the anticipated workload?
• Evaluate Choose proper targets for rework
• Find bottlenecks and blind spots, current and
  future
• Create Find the second right answer
• Do your homework, and be creative!
• Experiment-ate Test to destruction
• Or at least, to the allocated time window
• Does it perform/scale?

24
What about RAC?

• If it scales well on SMP, it will (probably)
  scale well on RAC.
• Our experience was that it scaled even better.
• And if not
• RAC amplifies the GOOD and BAD (TKyte)
• Like a guitar amplifier (DScott)

25
You Gotta See This!

• Effective Oracle by Design (Osborne ORACLE Press
  Series) by Thomas Kyte
• Oracle Documentation Data Warehousing Guide,
  Performance Tuning Guide
• http//askstom.oracle.com
• Oracle Wait Interface A Practical Guide to
  Performance Diagnostics Tuning (Osborne ORACLE
  Press Series) by Richmond Shee, Kirtikumar
  Deshpande, and K. Gopalakrishnan
• Expert Oracle Database Architecture 9i and 10g
  Programming Techniques and Solutions by Thomas
  Kyte

26
Super-Heroes of Scalability
27
The Flash

• Tuning
• Multiple fast CPUs
• Proper Config

28
The Incredible Hulk

• I/O Bandwidth
• Lotsa RAM
• 64-bit CPU

29
Carnac The Magnificent

• Planning Design
• Materialized Views
• Summary tables
• Statistics

30
The Invisible Man

• Background processing
• Batch windows
• FIRST_ROWS

31
An Army of Ants

• Partitioning,
• PQ
• Divide and conquer designs

32
Questions?
33
Thank You for participating!

• Please complete your evaluation form
• David ScottScaling to 100 Million Rows Per
  Day!
• To Contact, please email David.Scott_at_intecbilling
  .com