Transcript Document

Oracle Statistics – with a little bit extra on top
Lise Parker
Technical Designer
Scottish Life – part of The Royal London Group
Royal London Group
A group of specialist businesses where the bottom line is always financial sense
1
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?
2
Introduction
About Royal London and Scottish Life
About Me
About the Database and Application
Royal London Group
A group of specialist businesses where the bottom line is always financial sense
3
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
4
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
5
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
6
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
7
Introduction
About the Applications
Internal Network
DMZ
Internet
Oracle
Unix Database Servers
External Business
Partners
Windows
Application Servers
Internal
Users
Windows
Web Servers
External
Users
(Financial Advisers,
Employers,
Employees/
Policyholders)
MS SQL
Windows Database Servers
Firewall
Firewall
8
Database Statistics
Why do we need them?
What do you get “out the box”?
What can they tell us?
What else is available?
Royal London Group
A group of specialist businesses where the bottom line is always financial sense
9
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
10
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
11
Database Statistics
What can they tell us – real-time?
-- Retrieve
Top is
Who
What
10
did
CPU
waiting
the
the
consumers
SID?
SID
SQL
sessions
do? in
from
the
the
in
Library
the
last
last
5Cache:
mins
5 mins
SELECT DISTINCT
*
serial#
sql_text sql_id
FROM ,username
(SELECT
v$sql
session_id
,session_serial#
FROM ,osuser
WHERE
v$active_session_history
sql_id
,session_serial#
=
'&sqlid';
WHERE ,machine
sample_time
,COUNT(*)
> SYSDATE - INTERVAL '5' minute
AND
,program
session_id
FROM
v$active_session_history
= &sid;
,resource_consumer_group
WHERE session_state = 'WAITING'
'ON CPU'
,client_info
AND
sample_time > SYSDATE - INTERVAL '5' minute
FROM
v$session
GROUP BY session_id
WHERE sid = &sid;
,session_serial#
ORDER BY COUNT(*) DESC)
WHERE rownum <= 10;
SERIAL# USERNAME
SQL_ID
SESSION_ID
SQL_TEXT
OSUSER
SESSION_SERIAL#
SESSION_SERIAL#
MACHINE
PROGRAM
COUNT(*)
RESOURCE_CONSUMER_
CLIENT_INFO
GROUP
6w4rhtrt95r7q
1468
552
select
/*+ first_rows index_desc(PA020
1 55055
55055
PA020_AM) */ AUDAPLCDE,
74
298
AUDSTF_NO, AUDUPD_ID, AUDUPDDTE,
55055
LLL01BCH
lll01bch
mercury
UT9051@mercu
OTHER_GROUPS
AUDUPDTME,
CLICAT, CLIREF,
CRGAMT,
CRGIND, EFVDTE,
ENDDTE, FNDTRNTYP,
FRQ, INRREF, INRTYP,
ry
(TNS
V1-V3)
2ap8g8jyak8wd
552
2197
55055
43291
55055
55
298
MAINT, ORIDTE, PCSSTG, PNTTRN_NO, POLREF, RE_APLIND, RHTTYP, ROLREF, SPSDTE, STA_PA,
STGSWHDTE, STMLOC, STMRSN, STRDTE, TRNCTL_NO, TRNSUBTYP, UNTSTM_NO, WDRAMT, WDRPCT,
2c32bv0km2q8k
572
1432
8117
16820
55055
11
210
UNIQUE_ID from PA020 where POLREF = :k_POLREF and ( ( TRNCTL_NO <= :k_TRNCTL_NO ) ) order by
POLREF ,TRNCTL_NO desc
Real time data continuously updated whilst database is open and in use (v$)
12
Database Statistics
What can they tell us – historical?
-- What
Top 10
did
waiting
CPU
the
consumers
SID
sessions
do? forfor
a specific
a specific
time
time
period
period
SELECT DISTINCT
*
sql_id
FROM
(SELECT ,session_serial#
session_id
FROM
dba_hist_active_sess_history
,session_serial#
WHERE sample_time
,COUNT(*)
BETWEEN TO_DATE('01-june-2012 14', 'dd-mon-yyyy hh24')
AND
FROM
dba_hist_active_sess_history
TO_DATE('01-june-2012 16', 'dd-mon-yyyy hh24')
AND
session_id
WHERE session_state
= &sid;
= 'ON
‘WAITING'
CPU'
AND
sample_time BETWEEN TO_DATE('01-june-2012 14','dd-mon-yyyy hh24')
-- Retrieve
AND the SQL from the Library
TO_DATE('01-june-2012
Cache
16','dd-mon-yyyy hh24')
SELECT sql_text
GROUP BY session_id
FROM
dba_hist_sqltext
,session_serial#
WHERE sql_id
ORDER =BY
'&sqlid';
COUNT(*) DESC)
WHERE rownum <= 10;
SESSION_ID
SQL_TEXT
SESSION_SERIAL#
COUNT(*)
Begin EN500_P.EN500_STARTUP(:v0,
1898
1469
35788
1
:v1, :v2, :v3, :v4, :v5,96
42
:v6, :v7, :v8, :v9, :v10, :v11, :v12, :v13, :v14, :v15,
:v16, :v17, :v18, :v19, :v20, :v21, :v22, :v23, :v24, :v25, :v26, :v27, :v28); End;
624
2176
29854
21427
16
19
1178
2197
47367
18141
12
16
Real time data available for set amount of days (DBA_HIST)
13
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
14
Bridging The Gap
Our problem
What we had and what we wanted
The additional framework
The GUI we have built
Royal London Group
A group of specialist businesses where the bottom line is always financial sense
15
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
16
Bridging The Gap
What we had and what we wanted
Process A
Process C
Process E
Process G
Process D
Process B
Process C
Process A
Process B
PL/SQL
PL/SQL
SQL
SQL
AWR
Process G
Process D
Process F
Segments
SQL statements
Transactions
CPU
I/O
ASH
Process E
Process F
PL/SQL
SQL
Process Name
Process A
Process B
...
Segments
SQL
Oracle Statsstatements
User Stats
Transactions
v$mystat
user-defined
CPU
I/O
v$mystat
user-defined
ASH
AWR
Needed statistics relevant to specific business processes
17
Bridging The Gap
The additional framework
Application A
Application B
Process
Process
Process
Process
Process
Process
Process Statistics Framework
PROCESS_MASTER
PROCESS_STATS
Process
Process
Process
PROCESS_CONTROL_
PARAMETERS
DB
PROCESS_STATS_
ENTRIES
Application C
Application D
Process
Process
Process
Process
Process
Process
18
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.
19
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!
25
Any questions?
Lise Parker
[email protected]
Royal London Group
A group of specialist businesses where the bottom line is always financial sense