SPOILER ALERT: This particular blog post is a blatant self promotion for me and the various Oracle database services I am offering.
I
may not have mentioned it explicitly before but I have left the
corporate world of permanent employment and have gone independent to
offer my services direct to clients (at reasonable rates, of course).
I'm doing this through my newly created company Bottleneck Data Solutions, which has its own associated, obligatory web site.
Basically
I am offering my many years of knowledge, experience and expertise on
Oracle database design and performance tuning directly to clients for
short term consulting engagements or longer term contracts. I'm not big
on doing the self promotion thing unfortunately, but I won't succeed
unless I get the message out and publicise myself one way or another.
So this is a one-off post to raise awareness of what I'm offering
through Bottleneck Data Solutions and make you aware that I'm available for Oracle database performance tuning, database design and development engagements.
I have also uploaded some of the SQL Scripts
that I commonly use, to share with everyone. Nothing revolutionary I
am afraid, just the standard "free space" and "system activity summary"
type reports.
At the moment I am offering to do a free, initial Performance Review of an AWR Report. If I can spot anything obvious affecting performance in the AWR Report then I'll provide feedback to you.
Why "Bottleneck Data Solutions"?
Well data is everywhere, and is the lifeblood of the modern company.
But I have seen a significant number of cases where slow application
performance was caused by a badly designed and implemented database. In
other words, the way the data was organized and stored in the database
was becoming the bottleneck, limiting how quickly data could be supplied
to the application software. Solving this kind of bottleneck requires a
mix of short term performance tuning to deal with the most obvious hot
spots, and a longer term review of the database and redesigning the most
critical parts.
Thank you for your patience. The normal service of technical blog posts will be resumed again in the future.
Showing posts with label Performance Tuning. Show all posts
Showing posts with label Performance Tuning. Show all posts
Monday, 12 December 2016
Monday, 21 March 2016
Creeping Slow Performance
A recent OTN post
requesting help for a slow performing update where Oracle was doing a
full table scan, also revealed that the update was getting slower and
slower each month, and that new data was loaded into the table each
month to be "processed" i.e. updated. Unfortunately this kind of design
suffers from a natural creeping slowdown in the elapsed time of the
update process. Each month it will take longer and longer to complete
the processing of the newly loaded data, until any elapsed time targets
are exceeded and it continues to take longer and longer to complete.
There is a bad design pattern here, but obviously the original designers
and implementers did not spot it.
Consider the following - in the first month after go-live the table only contains newly loaded data, so a full table scan is used. Let's say it takes 2 minutes to complete, and our target is 30 minutes for the whole process to complete, which includes other steps as well. Everything looks good performance wise, so we leave everything as it is and carry on.
The next month - month 2 - the size of the table has doubled, and Oracle still uses a full table scan because you want to update half the data in the table. This now takes 4 minutes because the table has doubled in size. But the overall process still completes within 30 minutes, so everything still looks good.
Over the next few months the table grows each month, and the elapsed time of the update continues to increase by 2 minutes each month, assuming a similar volume of data is loaded in each month. After 5 months the update now takes 10 minutes, but this is still within our 30 minute target.
But after 10 months the elapsed time is now 20 minutes, which is a significant proportion of the 30 minutes, and people are starting to ask questions. And after 15 months, if not sooner, the 30 minute target has been missed and the overall data load and process is now taking too long. Not only that, it is continuing to take longer and longer to complete each month, and will only get worse.
What can be done? Could an index help? No, is the simple answer. As I recently blogged in Full Table Scan - Friend or Foe?, the cutover point for the Optimizer to choose to use an index is when you want to retrieve less than 0.5% or so of the data in a table. After 10 months you are still processing 10% of the data in the table each month. And worse, even after 100 months (8+ years) you are still processing 1% of the data in the table each month, and a full table scan is still a lower execution cost than using any index on the table.
What could you do about this? One obvious solution is to use a "staging table" of the same structure into which the data is initially loaded each month. Then do the update and other processing on this table, and finally insert all the data into the main table. This solution does not suffer from the constant degradation in performance that the current one does. Oracle will still use a full table scan, but it is only reading from disk the data for this month, and so should complete in a relatively constant time each month - assuming data volumes are similar each month. The insert to the main table could be done quite quickly using the "
Other possibilities include partitioning by a key unique to each month's data, which is also used in the update statement so that partition pruning occurs. This may be problematic if there is not a clean partition key to use. It should not be a "processed flag", as this would be updated by the processing itself which would result in row movement between partitions and extra work by Oracle.
Ultimately the issue is about spotting these kind of bad design patterns early on and avoiding these pending future performance problems. To get to one month's data being less than 0.5% of the total data volume in the table would take you over 200 months, or almost 17 years. And during those 17 years this monthly data load processing would always take longer and longer to complete. A different design can avoid this future performance problem completely.
Consider the following - in the first month after go-live the table only contains newly loaded data, so a full table scan is used. Let's say it takes 2 minutes to complete, and our target is 30 minutes for the whole process to complete, which includes other steps as well. Everything looks good performance wise, so we leave everything as it is and carry on.
The next month - month 2 - the size of the table has doubled, and Oracle still uses a full table scan because you want to update half the data in the table. This now takes 4 minutes because the table has doubled in size. But the overall process still completes within 30 minutes, so everything still looks good.
Over the next few months the table grows each month, and the elapsed time of the update continues to increase by 2 minutes each month, assuming a similar volume of data is loaded in each month. After 5 months the update now takes 10 minutes, but this is still within our 30 minute target.
But after 10 months the elapsed time is now 20 minutes, which is a significant proportion of the 30 minutes, and people are starting to ask questions. And after 15 months, if not sooner, the 30 minute target has been missed and the overall data load and process is now taking too long. Not only that, it is continuing to take longer and longer to complete each month, and will only get worse.
What can be done? Could an index help? No, is the simple answer. As I recently blogged in Full Table Scan - Friend or Foe?, the cutover point for the Optimizer to choose to use an index is when you want to retrieve less than 0.5% or so of the data in a table. After 10 months you are still processing 10% of the data in the table each month. And worse, even after 100 months (8+ years) you are still processing 1% of the data in the table each month, and a full table scan is still a lower execution cost than using any index on the table.
What could you do about this? One obvious solution is to use a "staging table" of the same structure into which the data is initially loaded each month. Then do the update and other processing on this table, and finally insert all the data into the main table. This solution does not suffer from the constant degradation in performance that the current one does. Oracle will still use a full table scan, but it is only reading from disk the data for this month, and so should complete in a relatively constant time each month - assuming data volumes are similar each month. The insert to the main table could be done quite quickly using the "
append" hint for a direct load, and then the
staging table could be truncated which should be very quick. Although
the data is copied around in Oracle twice, it should still be much
quicker than scanning a table with hundreds of months of data in it.Other possibilities include partitioning by a key unique to each month's data, which is also used in the update statement so that partition pruning occurs. This may be problematic if there is not a clean partition key to use. It should not be a "processed flag", as this would be updated by the processing itself which would result in row movement between partitions and extra work by Oracle.
Ultimately the issue is about spotting these kind of bad design patterns early on and avoiding these pending future performance problems. To get to one month's data being less than 0.5% of the total data volume in the table would take you over 200 months, or almost 17 years. And during those 17 years this monthly data load processing would always take longer and longer to complete. A different design can avoid this future performance problem completely.
Monday, 25 January 2016
What's Going On? Oracle Activity Monitoring Views
How do you find out what is happening now
inside an Oracle database instance when you only have SQL level access
to the database instance, and no nice GUI management tool? I'll cover
how to start doing this with some SQL query examples.
A user is complaining that the application is running slowly, or a report someone scheduled is taking too long to finish - where do you look to find out what is going on inside the Oracle database right now? Well if you have a nice GUI based tool such as Enterprise Manager then go there first, because that will have summary screens of what is going on, and also drill downs into the details behind what is going on. Such tools are easier and quicker to use, and mean you don't have to remember the names of internal Oracle dynamic performance views.
But what if you don't have a nice GUI based tool? Or you want to work out for yourself what is really going on in Oracle? Or something has happened to the nice GUI tool and you cannot use it for one reason or another? Then you need to know about Dynamic Performance Views in Oracle, also known as the
When investigating performance problems you should generally narrow it down to a specific problem and the users being affected by it. In Oracle each connected user has a corresponding "session" inside the Oracle database instance, and information on each session is made available in the
This is both an advantage and disadvantage of the Dynamic Performance Views - they are a snapshot of current values in the SGA, and these values can be changing all of the time. Look again and you will get different results. Some views show cumulative values rather than current values, but they are always increasing over time and also constantly changing.
But with that in mind, you can use the
This gives you a lot more information from
Snapper is written in PL/SQL and uses two in-memory PL/SQL tables for each snapshot, and then lists out anything that has changed between them. It does not write anything to the database, and only needs minimal permissions -
If you want even more information on what a single session is doing then you can SQL Trace it, which creates a trace file on the database server of all the SQL statements executed by that session and all the waits it experienced while tracing was enabled. Nothing is missed out because every executed SQL statement from that session is traced. You can then use utilities such as "
If the slow down is not currently happening but was very recent then you can look back at the recent past using ASH (Active Session History), but note that ASH is an extra cost option - you must have the Diagnostic Pack of the Enterprise Edition of Oracle. I'll write something about using ASH in a future post.
A user is complaining that the application is running slowly, or a report someone scheduled is taking too long to finish - where do you look to find out what is going on inside the Oracle database right now? Well if you have a nice GUI based tool such as Enterprise Manager then go there first, because that will have summary screens of what is going on, and also drill downs into the details behind what is going on. Such tools are easier and quicker to use, and mean you don't have to remember the names of internal Oracle dynamic performance views.
But what if you don't have a nice GUI based tool? Or you want to work out for yourself what is really going on in Oracle? Or something has happened to the nice GUI tool and you cannot use it for one reason or another? Then you need to know about Dynamic Performance Views in Oracle, also known as the
V$ tables, and which ones are the most relevant ones.
The Dynamic Performance Views are views defined internal to Oracle that
in fact map onto memory structures inside the SGA (System Global Area).
They enable you to see in real time various data within the SGA using
normal SQL queries.When investigating performance problems you should generally narrow it down to a specific problem and the users being affected by it. In Oracle each connected user has a corresponding "session" inside the Oracle database instance, and information on each session is made available in the
V$SESSION dynamic performance view. This is both an advantage and disadvantage of the Dynamic Performance Views - they are a snapshot of current values in the SGA, and these values can be changing all of the time. Look again and you will get different results. Some views show cumulative values rather than current values, but they are always increasing over time and also constantly changing.
But with that in mind, you can use the
V$SESSION
view to look at what specific sessions are doing at the moment, and
look for any slow, long running SQL statements. We can group the data
columns into the following related groups:- Session Connection Information - User name, Client Application software, Logon time
- Current Activity Information - Status, SQL statement executing
- Delays - both normal Waits and abnormal Blocks i.e. small delays or completely blocked
- Waits are delays that will definitely finish e.g. read a block from disk
- Blocks are delays that are outside the control of the session and can potentially go on forever
- e.g. blocked waiting for a row lock held by another session
V$SESSION
look in the "Oracle Database Reference" Manual under "Dynamic
Performance Views", where you will get a brief description of each
column.All Active Sessions
Want a list of all active sessions and whether they are waiting or not? Try this query from SQL*Plus:-- List the active sessions and break down key data
--
column sid format 999999
column db_user format a8 heading 'USER'
column db_login_time format a6 heading 'LOGON'
column program format a10 heading 'APP_N'
column active_for format 999 heading 'ACTV|FOR'
column state format a5 heading 'STATE'
column waiting_for format 999 heading 'WAIT|FOR'
column event format a12 heading 'WAIT ON'
column sql_id format a13 heading 'SQL ID'
--
select s.sid,
nvl (s.username, 'SYS') db_user,
to_char (s.logon_time, 'hh24:mi') db_login_time,
substr (s.program, 1, 10) program,
s.last_call_et active_for,
decode (s.state,
'WAITING', 'WAIT',
'WAITED KNOWN TIME', 'NW L',
'NW S') state,
decode (s.wait_time, 0, seconds_in_wait, 0) waiting_for,
substr (s.event, 1, 12) event,
nvl (s.sql_id, 'Not Executing') sql_id
FROM v$session s
WHERE s.type = 'USER'
AND s.status = 'ACTIVE'
AND s.wait_class != 'Idle'
/
--
- It restricts the
sessions to only those from real users (not internal background
sessions) that are active and experiencing non-idle waits.
- Idle waits are generally waits external to Oracle, such as waiting for the next SQL statement to execute
- The number of columns of data output is kept low to fit within an 80 column wide terminal screen
- If you have a wider output format then you could add extra data columns to the "select"
- The "
decode" of "state" is meant to encode the following combinations of possibilities:- WAIT = WAITING, NW = NOT WAITING; PREV WAIT:- S = SHORT, L = LONG
- i.e. it combines both whether the session is currently waiting or not, with whether the previous wait was a short or long wait
- It is assumed that users have all connected today, so only the time of connection is shown
- If connections are long lived, then change the date format used in the "
to_char" to include the day
- If connections are long lived, then change the date format used in the "
- How many sessions are active, and how many are currently waiting on something?
- Sessions that are active and not waiting are therefore running on a CPU
- How many sessions are running the same SQL statement? Look at the values of
SQL_ID - How many sessions are experiencing the same kind of wait event?
- Are sessions experiencing long or short waits most of the time?
One Session's Details
If you know theSID
(Session ID) of one session you are interested in and want to drill
down further, then you could run the following set of queries from
within SQL*Plus:--
set newpage none
--
set define on
undefine SESSION_ID
--
accept SESSION_ID prompt 'Enter Session ID > '
--
column sid FORMAT 99999
column username FORMAT a12
column logt FORMAT a12 HEADING 'Logon Time'
column sql_start format a9 heading 'SQL Start'
column last_call_et heading 'Time at Status'
column module FORMAT a16 heading 'Module'
column client_info format a16 heading 'Client Info'
column command format 999 heading 'cmd'
column taddr format a8 heading 'TX Addr'
column server heading 'Server|Type'
column schemaname format a12 heading 'Schema|Name'
column type heading 'Session|Type'
column sql_id heading 'Curr SQL ID'
column prev_sql_id heading 'Prev SQL ID'
column lockwait format a8 heading 'Wait Lock|Addr'
column event format a25 heading 'Waiting For'
column wait_class format a12 heading 'Wait Class'
column row_wait_obj# heading 'Object|Waiting On'
column wait_time heading 'Last Wait Time|(0=Waiting)'
column seconds_in_wait heading 'Elapsed From|Last Wait'
column blocking_session heading 'Blocking|Session ID'
column blocking_session_status heading 'Blocking|Sess Status'
--
select sid,
username,
to_char (logon_time, 'dd/mm hh24:mi') logt,
status,
last_call_et,
to_char (sql_exec_start, 'HH24:MI:SS') sql_start
from v$session
where sid = &&SESSION_ID
/
--
select sid,
substr (module, 1, 24) module,
substr (client_info, 1, 30) client_info,
server, schemaname, type
from v$session
where sid = &&SESSION_ID
/
--
select sid,
sql_id, prev_sql_id,
event,
substr (wait_class, 1, 20) wait_class
from v$session
where sid = &&SESSION_ID
/
--
select sid,
lockwait,
row_wait_obj#,
wait_time, seconds_in_wait,
blocking_session, blocking_session_status
from v$session
where sid = &&SESSION_ID
/
--
column sql_text format a62
--
select sql_id, sql_text from v$sql
where sql_id = (select sql_id from v$session where sid = &&SESSION_ID)
/
--
undefine SESSION_ID
--
set newpage 1
This gives you a lot more information from
V$SESSION for the one session, broken down into related sets of data, as described before. Note that the final query selects from V$SQL
which is probably the other main Dynamic Performance View, which has an
entry for each SQL statement currently in the library cache in the SGA.
Remember that a SQL statement in the library cache can be shared
between many sessions.One Session Detailed Drill Down
If you really want to drill down more into what a single session or a set of sessions is doing over a period of time then you could use Tanel Poder's snapper.sql. This snapshots the contents ofV$SESSION
and other Dynamic Performance views over a period of time and reports
on what it captured. Mainly this will be the wait events experienced by
the sessions, but a lot of other activity statistics are reported too.Snapper is written in PL/SQL and uses two in-memory PL/SQL tables for each snapshot, and then lists out anything that has changed between them. It does not write anything to the database, and only needs minimal permissions -
SELECT_CATALOG_ROLE and execute on DBMS_LOCK.SLEEP. Thus it is safe to run on any database.Conclusion
I've shown how to list all the currently active sessions on a database including the SQL they are executing and what they are waiting on, and how to get more details on what just one session is doing.If you want even more information on what a single session is doing then you can SQL Trace it, which creates a trace file on the database server of all the SQL statements executed by that session and all the waits it experienced while tracing was enabled. Nothing is missed out because every executed SQL statement from that session is traced. You can then use utilities such as "
tkprof" to post process the trace file and report on the SQL statements executed, their elapsed times, and the waits they experienced.If the slow down is not currently happening but was very recent then you can look back at the recent past using ASH (Active Session History), but note that ASH is an extra cost option - you must have the Diagnostic Pack of the Enterprise Edition of Oracle. I'll write something about using ASH in a future post.
Subscribe to:
Posts (Atom)
