Database Performance History

by Jul 3, 2018

Servers are moving from on-prem to the cloud.  Applications are upgraded, so are operating systems, databases, and hardware.  New users, new transactions, and new usage patterns change how & when an application is used.   A history keeps track of change.  It allows you to make sense of how things were and how they are now.  The only constant is change. 

Precise keeps a 3-year history of activity, objects, space consumption, SQL statements, execution plans, parameters, wait state metrics, and such.  Precise supports showing a history when it is moved across platforms:  before cloud / after cloud.  The GUI is designed to show cause & effect. 

Did the move to cloud live up to all of the hype or was there only a cost savings?  The history can help solve numerous problems and document that a problem was solved.   It can document success.  Here is a selection where history was a big help.

Same SQL statement, multiple execution plans:   

This screen shows a SQL statement where the optimizer chose different plans, resulting in different execution times.

Cause and effect

SQL statement growing slower day by day:

This SQL statement runs fast.  It would not make the top-ten list.  None-the-less, Precise detected that its behavior was changing.  Here is a ten month graph.

Ten month SQL Statement History

SELECT MODE_CD , UPDATE_USER_ID , ARCHIVE_DT , APP_NUM , UPDATE_DT , ACTION_TYPE , UNIQUE_TRANS_ID , rowid , DCN , CREATE_DT , CREATE_USER_ID , CASE_NUM

FROM AR_APPLICATION_CASE_REF

WHERE CASE_NUM = :1 AND MODE_CD = :"SYS_B_0" AND DCN in (select DCN from ss_application_request where app_status_cd not in (:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5"))

The SQL statement’s performance degraded in almost a straight line.  The DBA recognized AR_APPLICATION_CASE_REF .  It is a new application table.   Each day more rows were added to it.  The DBA was not surprised to find that the SQL statement was using a full table scan to access the data.  More data equals more blocks reads results in a longer execution time.

Precise captures the execution plan, keeps a history, and provides for step by step analysis.  Precise provides shows the cost percentage of each step.  For a full table scan, more data equates to more blocks read; thus more time to complete the scan.  A new index not only for this SQL statement, but for all SQL statements accessing the table was recommended by Precise.  Precise’s What-If allowed to see that an across the board improvement would result.  This modeling capability minimizes that a false move will be made in production.

Bind Variables (captured in Oracle):

The same SQL statement can take much longer to complete if it is processing more data.  One factor can be the bind variable value.  The distribution of values for a row can vary greatly.  If all are the same, a SQL  statement with syntax like WHERE company_number = :b will return every row in the table.  This takes more time and uses more resources than if one row is returned.

The database’s Optimizer can also pick a new execution plan when it makes sense to use or avoid an index.  Most Optimizers assume a unique distribution of rows unless histograms have been generated with Analyze on the objects.

There are two practical remedies to this situation.  Help end-users understand that filling in fields on a form as completely as possible helps the system respond quickly.  Even when the user leaves a field blank or provides a wildcard and the database responds with a full table scan, there is a best execution plan.

Verification:

Before and after measurement closes the feedback loop.  A detailed history is required to quantify that a problem was solved and to what degree.  Please note the substantial reduction in CPU (the dark portion of the stacked bar graph).  The ripple effect is that these CPU cycles are available for other processes, potentially across other VMs.

Verification completes the feedback loop

Summary :  A history showing events that impact behavior is helpful to understand cause & effect.  Precise keeps a three year history including an execution plan history.  Three years including three year-end and twelve quarter-end processing windows where top SQL consumers may be different than during regular daily processing.  See Precise