Home » RDBMS Server » Performance Tuning » How to find throughput by query, session and database wise? (Oracle 11.2.0.3, Redhat Linux 5.)
How to find throughput by query, session and database wise? [message #609101] Sun, 02 March 2014 01:29 Go to next message
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 #609105 is a reply to message #609101] Sun, 02 March 2014 02:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Suresh, there probably is an answer to your questions, if you can define them better. What do you mean by "How to find throughput by query, session and database wise"? If you give an example of what the answer might look like, then perhaps someone can help you to find it.
Re: How to find throughput by query, session and database wise? [message #609107 is a reply to message #609105] Sun, 02 March 2014 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
query V$SQL

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
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 Go to previous messageGo to next message
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 #609250 is a reply to message #609249] Tue, 04 March 2014 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>I want to check at what rate the rows are being transferred
Transferred from where to where?

SQL_TRACE=TRUE can reveal what is actually happening inside the DB by dissecting the resultant trace file.
Re: How to find throughput by query, session and database wise? [message #609251 is a reply to message #609250] Tue, 04 March 2014 12:14 Go to previous messageGo to next message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
Transfer from database to client machine.

Is there anyway to check the transfer rate from database to client machine while the query is running?

Suresh
Re: How to find throughput by query, session and database wise? [message #609252 is a reply to message #609251] Tue, 04 March 2014 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
1) watch client machine
2) packet sniff
3) SQL_TRACE=TRUE
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 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
suresh.wst wrote on Tue, 04 March 2014 18:06
Hi,
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 Go to previous messageGo to next message
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

Re: How to find throughput by query, session and database wise? [message #609458 is a reply to message #609259] Fri, 07 March 2014 01:41 Go to previous message
suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad
Member
Hi Kevin,

Thanks a lot for the information.
I will dig more into these views and see if I can make use of them in my daily dba activities.

Suresh
Previous Topic: Difference in elapsed time of the query where everything in the trace seems to be identical
Next Topic: Query is taking long time
Goto Forum:
  


Current Time: Thu Mar 28 08:50:45 CDT 2024