Oracle Statistics – with a little bit extra on top

1. Oracle Statistics – with a little bit extra on top Lise Parker Technical Designer Scottish Life – part of The Royal London Group

2. Agenda Introduction Some background about my company, me and how we use Oracle Database Statistics What are they and what can they tell you? Bridging The Gap What we’ve done to better understand individual code behaviour The End! Questions?

3. Introduction About Royal London and Scottish Life About Me About the Database and Application

4. Introduction About Royal London • UK’s largest mutual (customer-owned) Life and Pensions provider • Multi-brand business run as separate business units • 2,880* employees, primarily UK-based with main offices in Edinburgh, Wilmslow and London • £46.8 billion* of funds under management • Around 4 million customers* * As at 31st March 2012

5. Introduction About Scottish Life • Founded in 1881, acquired by Royal London in 2001 • Pension specialist • Internal business users on two UK sites • Core business applications primarily developed in-house including secure web application for external customers and business partners

6. Introduction About Me • Worked with Oracle databases and tools since 1996 • Work within the Technical Architecture & Design team • Provide specialised technical support for Scottish Life Oracle development teams covering both database and code

7. Introduction About the Database • Main policy administration application uses Oracle 11gR1 database and PL/SQL for business logic • Complex application landscape with multiple integrations • One production database supporting OLTP during day time and batch during night time • c. 1k concurrent users per hour • c. 1m online transactions per day • c. 350 batch reports per night • Multiple concurrent batch streams with complex dependencies Large database interaction variation between online and batch scenarios

8. Introduction Unix Database Servers Oracle External Business Partners Windows Database Servers MS SQL Internal Users Windows Web Servers External Users(Financial Advisers, Employers, Employees/Policyholders) Windows Application Servers About the Applications Internal Network DMZ Internet Firewall Firewall

9. Database Statistics Why do we need them? What do you get “out the box”? What can they tell us? What else is available?

10. Database Statistics Why do we need them? • Customers expectations of information accessibility have changed as a result of the internet • There’s an expectation to receive information: • via multiple channels • in real-time • quickly • The database can be a bottleneck if performance is not understood and managed effectively on an on-going basis • Key to ensure that database performance doesn’t constrain our business proposition designs and customer experiences

11. Database Statistics What’s available “out the box” • Cumulative values • Metrics • Sampled data (ASH) • Real time data to historical data • AWR Statistics are available as both real time and historical data

12. Database Statistics What can they tell us – real-time? -- Top 10 CPU consumers in the last 5 mins SELECT * FROM (SELECT session_id ,session_serial# ,COUNT(*) FROM v$active_session_history WHERE session_state = 'ON CPU' AND sample_time > SYSDATE - INTERVAL '5' minute GROUP BY session_id ,session_serial# ORDER BY COUNT(*) DESC) WHERE rownum <= 10; -- Top 10 waiting sessions in the last 5 mins SELECT * FROM (SELECT session_id ,session_serial# ,COUNT(*) FROM v$active_session_history WHERE session_state = 'WAITING' AND sample_time > SYSDATE - INTERVAL '5' minute GROUP BY session_id ,session_serial# ORDER BY COUNT(*) DESC) WHERE rownum <= 10; -- Who is the SID? SELECT serial# ,username ,osuser ,machine ,program ,resource_consumer_group ,client_info FROM v$session WHERE sid = &sid; -- What did the SID do? SELECT DISTINCT sql_id ,session_serial# FROM v$active_session_history WHERE sample_time > SYSDATE - INTERVAL '5' minute AND session_id = &sid; -- Retrieve the SQL from the Library Cache: SELECT sql_text FROM v$sql WHERE sql_id = '&sqlid'; Real time data continuously updated whilst database is open and in use (v$)

13. Database Statistics What can they tell us – historical? -- Top 10 waiting sessions for a specific time period SELECT * FROM (SELECT session_id ,session_serial# ,COUNT(*) FROM dba_hist_active_sess_history WHERE session_state = ‘WAITING' AND sample_time BETWEEN TO_DATE('01-june-2012 14','dd-mon-yyyy hh24') AND TO_DATE('01-june-2012 16','dd-mon-yyyy hh24') GROUP BY session_id ,session_serial# ORDER BY COUNT(*) DESC) WHERE rownum <= 10; -- Top 10 CPU consumers for a specific time period SELECT * FROM (SELECT session_id ,session_serial# ,COUNT(*) FROM dba_hist_active_sess_history WHERE session_state = 'ON CPU' AND sample_time BETWEEN TO_DATE('01-june-2012 14','dd-mon-yyyy hh24') AND TO_DATE('01-june-2012 16','dd-mon-yyyy hh24') GROUP BY session_id ,session_serial# ORDER BY COUNT(*) DESC) WHERE rownum <= 10; -- What did the SID do? SELECT DISTINCT sql_id ,session_serial# FROM dba_hist_active_sess_history WHERE sample_time BETWEEN TO_DATE('01-june-2012 14', 'dd-mon-yyyy hh24') AND TO_DATE('01-june-2012 16', 'dd-mon-yyyy hh24') AND session_id = &sid; -- Retrieve the SQL from the Library Cache SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = '&sqlid'; Real time data available for set amount of days (DBA_HIST)

14. Database Statistics What else is available? • DBMS_APPLICATION_INFO • Oracle package used to record the names of the executing modules or transactions in the database. • These settings are very useful when it comes to tracking performance. • Set MODULE and ACTION sensibly throughout your code structure. • Profiling at run time: • Wrap the DBMS_APPLICATION_INFO within a PL/SQL package. • If profiling then record every setting of the MODULE and ACTION in a table. Consider use of DBMS_APPLICATION_INFO in your application designs

15. Bridging The Gap Our problem What we had and what we wanted The additional framework The GUI we have built

16. Bridging The Gap Our problem • Some examples of questions that we needed to answer: • The marketing department wants to know how long it takes to produce a statement from the web at the busiest time. • How can I decide on what to tune that will reduce the overall batch window? • The batch is starting to take longer and longer to run. What is causing this? Very difficult to identify relevant data from global database statistics

17. Bridging The Gap What we had and what we wanted Process C Process C Process E Process E Process A Process A Process G Process G Process D Process D Process B Process B Process F Process F Segments SQL statements Transactions CPU I/O ASH AWR PL/SQL SQL PL/SQL SQL PL/SQL SQL Segments SQL statements Transactions CPU I/O ASH AWR Needed statistics relevant to specific business processes

18. Bridging The Gap The additional framework Application C Application A Application B Application D Process Process Process Process Process Process Process Process Process Process Process Process Process Statistics Framework DB Process Process Process Process Statistics Framework PROCESS_MASTER PROCESS_STATS PROCESS_CONTROL_PARAMETERS PROCESS_STATS_ ENTRIES

19. Bridging The Gap Process Data Analysis Tool • This slide had a movie showing you our Process Stats GUI tool. • Instead I have added a few slides showing you screen dumps of this GUI tool so you still have an idea of how we did it.

20. Summary • Things to consider: • Invest some time to better understand what Oracle statistics “out the box” can tell you about your database behaviour • Is there anything “extra on top” you could add that would provide additional benefit to your particular environment • How you could design your application code to integrate with the Oracle statistics framework • How you can make the statistics information available to developers to help them improve their query designs Be proactive, not reactive!

21. Any questions? Lise Parker lise.parker@royallondongroup.co.uk ?