slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
David Scott, CSG International GOusers, March 9, 2012 PowerPoint Presentation
Download Presentation
David Scott, CSG International GOusers, March 9, 2012

Loading in 2 Seconds...

play fullscreen
1 / 35

David Scott, CSG International GOusers, March 9, 2012 - PowerPoint PPT Presentation

  • Uploaded on

Scalability: Super-Heroes and Super-Villains. David Scott, CSG International GOusers, March 9, 2012. Speaker Qualifications. Principal Consultant, CSG International Currently works with high-volume, RAC projects Metrics and analytics Data Warehousing

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'David Scott, CSG International GOusers, March 9, 2012' - channery

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Scalability: Super-Heroes

and Super-Villains

David Scott, CSG International

GOusers, March 9, 2012

speaker qualifications
Speaker Qualifications
  • Principal Consultant, CSG International
  • Currently works with high-volume, RAC projects
    • Metrics and analytics
    • Data Warehousing
  • Past president of GOusers (Georgia Oracle Users Group)
  • Has presented at OOW, IOUG, GOuser, and other conferences
  • Oracle DBA/developer since 1992
  • Oracle databases can scale to handle tremendous volumes of data, but some effort is involved to avoid the dastardly villains that would sabotage your success. This presentation will help DBAs avoid the villainous techniques and pitfalls in code and schema designs, as well as summon the super-heroes that can assist you in creating super-scaled data applications.
  • Identify the super-heroes of scalability - techniques and design choices that allow for system growth and performance improvement.
  • Identify the super-villains of scalability - the typical design and coding mistakes that inhibit efficiency and high-speed performance.
  • Identify opportunities within the system development lifecycle for improving scalability.
  • What is scalability?
    • Techniques and design choices that allow for system growth and performance improvement.
  • Why is scalability essential?
    • User expansion
    • The drive for lower TCO
    • 24*7*365 and other SLAs
    • SoA and proliferation of new uses
scalability targets
Scalability Targets
  • Number of users
  • Number of records/rows
  • Number of queries/transactions per …
  • Process time windows
  • Define your targets carefully!
tuning and scaling are related
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 help identify scalability inhibitors
    • Wait events
    • Profilers
    • Monitors
the flash
The Flash
  • In general, the faster something is, the more scalable it is.
  • Multiple fast CPUs
  • High-speed I/O and networks
  • Tuning and proper configuration
  • Tight, optimized code
  • Profiled operations
  • RAMsan and other specialized hardware
the incredible hulk
The Incredible Hulk
  • The more capable the system, the more scalable
    • AKA “The Hardware Option”
  • Exadata and other “Exa-stuff”
    • See
  • I/O capacity
  • Network bandwidth
  • GBs of RAM
carnac the magnificent
Carnac The Magnificent
  • If you can answer the question before it is asked, you can optimize your service levels.
  • Planning & Design
  • Materialized Views
  • Cubes and summary tables
  • Optimizer statistics
the invisible man
The Invisible Man
  • Processes and operations that impact users are often identified as non-scalable
  • Offload processes to other systems
  • Leverage background processing
  • Optimize batch windows
an army of ants
An Army of Ants
  • Small tasks are more scalable than large ones
    • How do you boil the ocean?
  • Partitioning
  • Parallel Query
  • ‘Divide and conquer’ designs
    • Exadata, Hadoop
  • Be careful of communications overhead
weapons in the war
Weapons in the War
  • SAN bandwidth
  • Partitioning
  • Parallel Query
  • Bitmap Join Index
  • PDML
  • Materialized Views
  • Powerful hardware
  • ATEP
  • IOTs
  • Temporary Tables
  • Registration Tables
  • Direct Path
  • Compression
  • Statistics

Find the ‘2nd right answer’

more firepower
More Firepower…
  • Grid Control, 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
  • Configuration & memory management
mr magoo unreasonable assumptions and vision
Mr. Magoo: Unreasonable Assumptions and Vision
  • Extrapolating results from smaller tests
  • Lack of experience with larger platform
  • Short testing windows
  • Inadequate staffing
  • The longevity of the temporary
    • Previous limitations still exist?
    • Previous methods still the best?
  • “Good enough for now…”
mr magoo page 2
Mr. Magoo, page 2
  • “Just add ____; everything will be fine”.
    • Disk / RAM / CPU
    • Job threads / Parallel Query
    • Exadata / RAC / The latest Oracle version
    • Magic pixie dust
  • Inadequate/Over-adequate SW Architecture
  • I/O saturation masks other bottlenecks
  • Creating test data
    • Extrapolation rarely, if ever, matches reality
the light at the end of the tunnel is an oncoming train
The light at the end of the tunnel…is an oncoming train.
  • “Just run it in parallel…”
    • PDML, INSERTS, indexing, summaries
  • CPU usage
    • The “Sears Tower” syndrome
  • All features/processes must scale
  • You don’t know what you don’t know!
    • Unless you’ve “been there, done that”
various coding villains
Various Coding Villains
  • Dr. Deadlock
    • Concurrency: blocking and locking
  • Lex Latcher
    • Large collisions on small objects
      • Sequences, headers
      • Writing to the same file from different processes
  • Captain Commit
    • Inside a loop, no less
  • And a host of others…
why the code doesn t scale
Why the Code Doesn’t Scale
  • Inadvertent blocking and locking
  • Ignores new code & database features
    • Can you rewrite the SQL?
  • Trigger happy?
  • Inefficient SQL & PL/SQL
    • Reliance on hints, bad hints
    • Ill-conceived scalar sub-queries
    • Function calls in SQL
    • Query generators: Beware!
    • Misuse of BULK operations
  • ‘Hacking’ the dictionary?
why the db doesn t scale
Why the DB Doesn’t Scale
  • Unnecessary I/O
    • Non-essential constraints
    • Data movement
  • Poor design, really awful design…
    • Incorrect indexing: Bitmaps & B-trees
  • Inadequate configuration
  • How robust is your tuning? Hints?
example update millions of rows
Example: 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 for LOTS more.
first steps preparing to scale
First Steps – Preparing to Scale
  • Re-read the original requirements
  • Identify current efficiencies & problems
  • Characterize your 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
basic architecture
Basic Architecture
  • Hardware choices
    • Specialized vs. commodity
    • Drive / SAN choice
      • Model IOPS requirements
    • RAM
  • Software choices
    • Build vs Buy
  • Network choices
    • Model throughput
service level agreements
Service Level Agreements
  • Data volume
  • Query response times
  • Batch performance
  • Backup and disaster recovery requirements
  • … and many others
  • These often define and interpret scalability within an organization
architectural options for database
Architectural Options for Database
  • Platform changes
    • Hadoop, NoSQL
    • “The Cloud”
      • Oracle and non-Oracle
  • RAC
  • Oracle “Exa-stuff”
    • Exadata, Exalytics
  • Leveraging your existing investment
development opportunities
Development Opportunities
  • Can your coding team scale???
    • Inventory your human resources
  • Think ahead
    • Code for scalability
    • Avoid maintenance and performance issues
  • Be consistent
    • Coding and naming standards
  • Multi-site issues
    • Sequences and site identifiers
testing for scalability
Testing for Scalability
  • Complexity at scale
    • Users
  • Data Volume
    • Is it possible to generate adequate test data?
  • Testing to a goal, testing to destruction
operations opportunities
Operations Opportunities
  • Process choreography
    • Do not start all batch processes at 0000 hours!
  • Think in terms of hourly tasks
  • Does ‘control and command’ have inherent conflict?
maintenance opportunities
Maintenance Opportunities
  • Re-factoring
  • What about bugs?
    • Mitigate these via Oracle Support and other resources
    • Architect to avoid the problems
  • Each system will have different challenges
  • Inevitable re-architecting of supporting systems
re architecture 101
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?
thank you for participating
Thank You for participating!
  • Please complete your evaluation form…
    • David ScottScalability: Super-heroes and Super-villains
  • To Contact, please email: