Screenshots: query patterns

This page shows some examples of query charts, just to get an idea what you can get out of those Phat views. Most of the time it’s all about a few metrics: elapsed time, cpu time, rows processed, buffer gets, disk reads. Comparing these graphs of one query can tell you a lot. I will dig a little deeper into this kind of analysis in some seperate pages.

Query 1

Most charts of queries I will show you will be about full table scans. These operations seem to be the #1 cause of performance problems. Graph above shows the number of diskreads and buffer gets needed for each execution of one OLTP query. Analysis is done, index is suggested accompanied by change of query which used a
“function(column) = :bindvariable”. Changes are done, and bingo ! Performace of the system has improved.

Query 2

Graph shows reduction of number of executions of query. Sometimes the frequency of an interface can be reduced without any consequences, but improving performance.

Query 3

Graph might show trouble of one query, it might not. It depends, maybe the peaks were moments where the batch picked up lots of new orders to be processed. You can’t tell if you do not some investigations on a lot of other metrics.

Query4

If you know that this is a graph of on OLTP query always retrieving 5 rows: trouble ! All reads are physical, and it even gets worse at some moment. Change might be because of data growth lengthening the time of a full table scan, or maybe a change in execution plan.

Query5

This looks good, CPU time per execution. Improvements have been made.

Query 6

Chart shows the number of rows processed by one query. This one is part of an interface, so graph above is a nice picture of interface processing activity.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: