| How to find throughput by query, session and database wise? [message #609101] |
Sun, 02 March 2014 01:29  |
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
|
Member |
|
|
Hi,
I could not find answer for the below doubt anywhere. Could you please help me?
I don't have OEM, so need to depend on data dictionary views.
Questions -
How to find throughput by query, session and database wise?
How to find bytes processes per second (or) rows processes per second by a query?
Thanks in advance for your help.
Suresh
|
|
|
|
|
|
|
|
| Re: How to find throughput by query, session and database wise? [message #609249 is a reply to message #609105] |
Tue, 04 March 2014 12:06  |
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
|
Member |
|
|
Hi,
Thanks for the reply.
To be specific, one of the query is taking long time in giving output. From v$session, I can see the session is inactive and the user is still receiving records (a large number of records). so, I want to check at what rate the rows are being transferred / processed for that particular query.
Hope this will give you some idea about my doubt.
Suresh
|
|
|
|
|
|
|
|
|
|
| Re: How to find throughput by query, session and database wise? [message #609253 is a reply to message #609249] |
Tue, 04 March 2014 13:33 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
suresh.wst wrote on Tue, 04 March 2014 18:06Hi,
I can see the session is inactive and the user is still receiving records (a large number of records)
This sounds as though Oracle has finished everything, and that either your network is still transmitting data or your client process is busy displaying it. If so, you need to tune your network and your client software. Adjusting the client array fetch size to the maximum it can handle might help.
|
|
|
|
| Re: How to find throughput by query, session and database wise? [message #609259 is a reply to message #609101] |
Tue, 04 March 2014 22:01 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
This kind of information has always been notoriously difficult to come by and anyone who could eek some of it out of the database had a significant advantage over those who could not, at least from a performance optimization angle. Here are some places to look. Most of these are raw but still useful:
1. V$SESSION_LONGOPS. This dynamic performance object shows the duration of "long operations". Last I knew, a long operation was anything Oracle expected to take longer than 3 seconds. Oracle decides what a long operation is and then puts a row into this view so that it can be monitored. This row will show among other things, expected workload and workload completed. When the workload is measured in blocks, you can compute an expected completion time and Megabytes Per Second throughput. However, there are many limitations. Not all operations are monitored. Things you will see monitored are sorts and joins and table scans and index scans. So for example this can be handy to see how fast a tables scan is progressing in MB/sec and thus help you gauge performance of disk systems with some planning. Second, Oracle does not always decide to put a long op into this view even though it clearly is a long operation. Oracle seems to ignore v$session_longops when a query is running in parallel.
2. V$SESS_IO. This will show you total IO metrics for a session. If you are willing and able to capture two moments in time from this dynamic performance object, you can get a feel for logical and physical IO measured again in blocks. Unfortunately this does not lend itself well to any understanding of how fast a query is going or not, assuming you can equate a query to a session. But it does tell you that a session is doing work.
3. V$TRANSACTION. This dynamic performance object shows used_UREC. Here is more from Jonathan Lewis. Again there is no correlation between number of URECs and number of rows processed but this is how most people tell if a session is doing a rollback. When a transaction is changing data the urec goes up. When a transaction is doing rollback the urec goes down eventually to zero.
4. V$SQL_WORKAREA_ACTIVE. This shows workareas used by a sql statement. Workareas are created for sorts and hash joins. The neat thing about this dynamic performance object is that it has a mapping back to the OPERATION_ID column of a query plan. Thus if your query is using workareas, you can guess somewhat as to where your query is in its execution plan by noting which hash areas a filled, which are currently filling, and which are empty. It is not a precise measure but can be useful at times.
5. V$SQL_PLAN_MONITOR (et.al.). This is I believe new to 11g. It also may be the closest thing to what you are asking for since it opens the door to real time monitoring of executing SQL. Here is more from the Toad people. The basic idea is you can monitor SQL as it executes to see CPU/IO/OUTPUT ROWS. I personally am only getting into this now myself. Give it a go and let me know what you think.
Good luck. Kevin
[Updated on: Tue, 04 March 2014 22:05] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
