Real Application Clusters Automatic Storage Management Performance Enhancements Results Caches Optimizer Enhancements Ti

1. Grid and OLTP • Real Application Clusters • Automatic Storage Management • Performance Enhancements • Results Caches • Optimizer Enhancements • TimesTen

2. Real Application Clusters

3. RAC Optimized Cache Fusion protocols • Improved performance for read-intensive workloads • improves any read from disk (not cache) whether short random reads or large table scans • Throughput improved up to 70% for internal read-only benchmark

4. Database Storage RAC Other Enhancements • Fine tune performance, scaling, failover, management • Seamless integration with XA and Microsoft Transaction Server • Faster and more robust handling of instance crashes and hangs • Most of this also in 10.2.0.3 • Runtime Connection Load Balancing works with OCI Session Pools

5. Database Control 11gTiled Instance Charts

6. ADDM for RAC Database-Level ADDM 11g • Performance expert in a box • Identify the most “Globally Significant” performance problems for the entire RAC cluster database • Database-wide analysis of: • Global cache interconnect issues • Global resource contention, e.g. IO bandwidth, hot blocks • Globally high-load SQL • Skew in instance response times • Runs proactively every hour when taking AWR snapshots (default) Self-Diagnostic Engine Instance-Level ADDM Inst 1 Inst 2 Inst 3 AWR 2 AWR 3 AWR 1

7. <Insert Picture Here> Automatic Storage Management

8. 1 2 Disk again accessible:Only need to resync modified extents ASM Fast Mirror Resync • Fraction of time to establish redundancy • Only changed blocks are resync’ed • Benefits: • Fast recovery from transient failures • Enables pro-active maintenance Failure time < DISK_REPAIR_TIME

9. ASM Preferred Mirror Read • Allow local mirror read operations • Eliminate network latencies in extended clusters Site A Site B Extended Cluster S P ASM_PREFERRED_READ_FAILURE_GROUPS=DATA.SITEB ASM_PREFERRED_READ_FAILURE_GROUPS=DATA.SITEA SELECT preferred_read FROM v$asm_disk; SELECT * FROM v$asm_disk_iostat;

10. ASM Rolling Upgrades and Patches • Maximizes database availability in a cluster • How it works: • Place cluster in ‘Rolling Migration’ mode • Bring down ASM on a cluster node • Upgrade or patch software • Re-start ASM • Stop ‘Rolling Migration’ mode after upgrading all nodes

11. ASM Scalability and Performance Enhancements • 63 disk groups • 10,000 ASM disks • 4 petabyte per ASM disk • 40 exabyte of storage • 1 million files per disk group • Maximum file size: • External redundancy: 140 PB • Normal redundancy: 42 PB • High redundancy: 15 PB • Variable size extents • grows automatically with file size • Benefits • Increase ASM file size • Reduce memory utilization in SGA • 100% automatic

12. Multiple Allocation Unit Size support • Allocation Unit (AU) selected at disk group creation time and may be 1,2,4,8,16,32,64 MB • Works with variable extent sizing • Set Oracle MAX_IO_SIZE = AU size • default MAX_IO_SIZE is 1MB • MAX_IO_SIZE determines default AU size • Striping • coarse stripe size = 1 AU • fine stripe size = 128KB • Higher performance for large sequential I/O (DW) • Better leverage of Hardware RAID read-ahead

13. SYSASM Privilege • SYSASM role to manage ASM instances separates duty of DBAs and storage administrators • SYSDBA will be deprecated for ASM management: • Oracle Database 11g Release 1 behaves as in 10g • In future releases SYSDBA privileges restricted in ASM instances SQL> CONNECT / AS SYSASM SQL> CREATE USER ossysasmusername IDENTIFIED by passwd; SQL> GRANT SYSASM TO ossysasmusername; SQL> CONNECT ossysasmusername / passwd AS SYSASM; SQL> DROP USER ossysasmusername;

14. md_restore md_backup repair lsdsk cp ASMCMD Extensions User created directoriesTemplatesDisk group compatibilityDisk group nameDisk names and failure groups $ asmcmd help Copy files RepairBad Blocks List ASM disks

15. Results Caches

16. Results CachesAgenda • Server Results Cache • SQL Query Results Cache • PL/SQL Function Cache • OCI Consistent Client Cache

17. <Insert Picture Here> SQL Query Result Cache

18. Data Warehouse Workload • Analyze data across large data sets • reporting • forecasting – trend analysis • data mining • Use parallel execution for good performance • Result • very IO intensive workload – direct reads from disk • memory is less important • mostly execution memory

19. Data Warehouse Query Example select p.prod_category , sum(s.amount_sold) revenue from products p , sales s where s.prod_id = p.prod_id and s.time_id between to_date('01-JAN-2006','dd-MON-yyyy')‏ and to_date('31-DEC-2006','dd-MON-yyyy')‏ group by rollup (p.prod_category)‏ • accesses very many rows • returns few rows

20. Data Warehouse ConfigurationSizing • Critical success factors • IO throughput • number of physical disks • number of channels to disks • CPU power • Everything else follows • Storage capacity (500GB – 1TB common) - use surplus for high availability and ILM • Memory capacity (4GB/CPU is “standard”) - use surplus for... RESULT CACHE

21. query 1executes result iscached join query 2 uses cachedresult transparently cachedresult join Group by Group by join join Group by Table 4 join join Table 1 Table 1 join Table 2 Table 2 Table 3 Table 3 Table 5 Table 5 SQL Query Result CacheBenefits • Caches results of queries, query blocks, or pl/sql function calls • Read consistency is enforced • DML/DDL against dependent database objects invalidates cache • Bind variables parameterize cached result with variable values

22. SQL Query Result CacheEnabling • result_cache_mode initialization parameter • MANUAL, use hints to populate and use • FORCE, queries will use cache without hint • result_cache_max_size initialization parameter • default is dependent on other memory settings (0.25% of memory_target or 0.5% of sga_target or 1% of shared_pool_size) • 0 disables result cache • never >75% of shared pool (built-in restriction) • /*+ RESULT_CACHE */ hint in queries

23. SQL Query Result Cache Example • Use RESULT_CACHE hint select /*+ RESULT_CACHE */ p.prod_category , sum(s.amount_sold) revenue from products p , sales s where s.prod_id = p.prod_id and s.time_id between to_date('01-JAN-2006','dd-MON-yyyy')‏ and to_date('31-DEC-2006','dd-MON-yyyy')‏ group by rollup (p.prod_category)‏

24. SQL Query Result Cache Example • Execution plan fragment ------------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | RESULT CACHE | fz6cm4jbpcwh48wcyk60m7qypu | | 2 | SORT GROUP BY ROLLUP | | |* 3 | HASH JOIN | | | 4 | PARTITION RANGE ITERATOR| | |* 5 | TABLE ACCESS FULL | SALES | | 6 | VIEW | index$_join$_001 | |* 7 | HASH JOIN | | | 8 | INDEX FAST FULL SCAN | PRODUCTS_PK | | 9 | INDEX FAST FULL SCAN | PRODUCTS_PROD_CAT_IX | ------------------------------------------------------------------

25. SQL Query Result CacheOpportunity • Depends... based on • query repetitiveness • query execution times • DML activity (cache invalidation frequency)‏ • Remember data warehouse workload • query may run 30 minutes • query may return 5 rows • query served from result cache would take split second

26. SQL Query Result Cache Restrictions • Result cache is disabled for queries containing • temporary or dictionary tables • non-deterministic PL/SQL functions • sequence CURRVAL and NEXTVAL • SQL functions current_date, sysdate, sys_guid, etc. • Result cache for distributed queries • set result_cache_remote_expiration > 0 • 0 means distributed queries are not cached • default is 0 • DML/DDL on remote database will not expire cached results

27. SQL Query Result CacheCaveats • Result cache does not automatically release memory • Grows until maximum size is reached • DBMS_RESULT_CACHE.FLUSH purges memory • Bind variables • Cached result is parameterized with variable values • Cached results can only be found for same variable values • Cached result will not be built if • Query is built on a non-current version of data (read consistency enforcement)‏ • Current session has outstanding transaction on table(s) in query • Flashback queries can be cached

28. SQL Query Result CacheInternal Benchmark • Retail customer data (~50 GB)‏ • Concurrent users submitting queries randomly • executive dashboard application with 12 heavy analytical queries • Cache results only at in-line view level • 12 queries run in random, different order • 4 queries benefiting from the cache • measure average, total response time for all users

29. <Insert Picture Here> PL/SQL Function Cache

30. PL/SQL Function CacheThe challenge • Calculate a complex derived metric – like the ratio of the highest median income grouped by state to the lowest median income grouped by state over the whole population • We need a PL/SQL function • Like SQL Result Cache, the data changes fairly slowly (say every hour) but the query is repeated fairly often (say every second)

31. PL/SQL Function CacheThe challenge function f2 return t1%rowtype is ... begin select a, m into r1.a, r1.b from ...; select a, m into r2.a, r2.b from ...; r.a := r1.a + r2.a; r.b := r1.b + r2.b; return r; end f2; • ~ 2,000 milliseconds for each new call

32. PL/SQL Function Cache function f2 return t1%rowtype result_cache relies_on(t1, t2) is ... begin select a, m into r1.a, r1.b from ...; select a, m into r2.a, r2.b from ...; r.a := r1.a + r2.a; r.b := r1.b + r2.b; return r; end f2; • ~ 0 milliseconds for each new call

33. SQL Query Result Cache & PL/SQL Function Cache • Both are cross-session and RAC interoperable • set result_cache_max_size > 0 on all instances, or = 0 on all instances • memory pool is instance-specific • Both build on the same infrastructure • same Result_Cache_Max_Size,… initialization parameters • same DBMS_Result_Cache management package • same v$Result_Cache_* performance views

34. <Insert Picture Here> OCI Consistent Client Cache

35. Application Caching Challenge • Applications often implement custom caching of data • significant development effort • synchronization may not be possible or come at performance cost • not transparent to applications • takes development focus out of business logic into infrastructure

36. Consistent Caching Application Server Database OCI Consistent Client Cachelike cache fusion between server and client • Caches query results on client • Targeted at repetitive queries against read-mostly, read-only data

37. OCI Consistent Client CacheBenefits • Frees application developers from building a shared per-process result cache shared by all sessions that is consistent. • Extends server-side query caching to client side memory • leverages cheaper client-side memory • each application has it’s working set cached locally • Ensures better performance by eliminating round trips to the server • Improves server scalability by saving server resources • Transparently maintains cache consistency with server side changes • Consistency mechanism works with RAC

38. OCI Consistent Client CacheHow does it work? • Leverages a combination of unique Oracle technologies: • Oracle's snapshot based Read Consistency • Database Change Notification technology • OCI Layer enhanced to lookup internal cache for all queries with /*+ result_cache */ • Consistency maintained by sending IN-BAND notifications on every roundtrip to server • in a relatively busy client, cache keeps sliding forward by catching up with the DB • in a relatively idle client, cache can trail behind DB no more than CACHE_LAG secs • Query results cached in OCI client memory • per-process cache shared across multiple sessions/threads

39. OCI Consistent Client Cache Enabling • Works with all OCI-based drivers • Including JDBC OCI, OCCI, ODP.Net, PHP, ODBC • Activated with server or client parameter • Server • CLIENT_RESULT_CACHE_SIZE (default 0, cache disabled) • CLIENT_RESULT_CACHE_LAG (optional, 3000ms default) • Client (set in sqlnet.ora) • OCI_RESULT_CACHE_MAX_SIZE (optional) • OCI_RESULT_CACHE_MAX_RSET_SIZE (optional) • OCI_RESULT_CACHE_MAX_RSET_ROWS(optional) • Applications explicitly tag queries with SQL hint • select /*+ result_cache */ id, name from products; • Enable statement caching in Drivers/layers such as JDBC, ODP.Net etc  or OCI Statement Caching can be used

40. OCI Consistent Client Cache Caveats • Some restrictions • views • VPD • DBlinks • Generally, not recommended for clients with extraneous channels • e.g. IPC between clients to notify of changes, since second process would expect to see update

41. OCI Consistent Client CacheUsage Guidelines • Look for candidate queries in AWR • frequent queries in Top SQL by CPU/Elapsed time • identify candidate queries on read-only/read-mostly tables • sprinkle the /*+ result_cache */ hint on such queries • validate by comparing performance with/without caching • Monitor usage • client_result_cache_stats$ • Control the “lag” • client_result_cache_lag

42. Results CachesSummary • SQL Query Results Cache and PL/SQL Function Cache • Utilize server side memory for caching long running repetitive queries and functions • OCI Consistent Client Cache • Utilize cheap client memory, gravitate data towards application, eliminate server round trip • Client and Server Result Caches are autonomous, each be be enabled/disabled independently

43. Q A & <Insert Picture Here>

44. New Optimizer features in 11g

45. NOT YET Oracle Optimizer “The Optimizer works perfectly and is a black box to customers”

46. What are the problems • Plans change unexpectedly especially during upgrades • Cardinality estimate is wrong so plan goes wrong • Gathering Optimizer Statistics takes too long • Bind peeking doesn’t work when there is a data skew

47. What are the solutions in 11g • Plans change unexpectedly especially during upgrades • Guaranteed plan stability and controlled plan evolution • Controlled statistics publication • Cardinality estimate is wrong so plan goes wrong • Collect appropriate statistics • Eliminate wrong cardinality estimates • Gathering Optimizer Statistics takes too long • Faster statistics gathering • Improved statistics quality • Bind peeking doesn’t work when there is a data skew • Enhanced plan sharing with binds

48. <Insert Picture Here> SQL Plan Management Guaranteed plan stability and controlled plan evolution

49. Solution • Optimizer automatically manages ‘execution plans’ • Only known and verified (accepted) plans are used • Plan changes are automatically verified • Only comparable or better plans are used going forward SQL Plan Management is controlled plan evolution SQL Plan Management Business Problem • Unpredictable changes in execution plans can happen • new Statistics • changes in the Environment • software upgrades • Today you have to ‘freeze’ critical plans or statistics

50. GB Plan Acceptable Parse Execute HJ HJ • Something changes in the environment • Statistics are re-gathered, DB upgrade or parameter change • Changes result in new plan • New plan implemented regardless of resulting performance GB Parse Plan NOT Acceptable Execute NL NL Without SQL Plan Management • SQL statement is parsed for the first time and a plan is generated • Does plan gives good performance? Plan is “verified by execution”