Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

For a query that returns if there are any active transactions within current session ,

  SELECT COUNT(*) 
  FROM 
  v$transaction t
  INNER JOIN v$session s ON (t.ses_addr = s.saddr )      
  INNER JOIN v$mystat m  ON  (s.sid = m.sid )    
  WHERE ROWNUM = 1;

EXPLAIN shows 0 cost. However, that doesn't seem to be true. Periodically running this query in high load environment causes server to waste almost all its resources on it.

What is the right way to estimate impact of such queries (I believe the same problem occurs not for just this particular query, but anything that involves system views)?

Thanks.

share|improve this question
You can autotrace the statement, but that gives information after the execution, not before. This particular statement will perform better by replacing INNER JOIN v$mystat m ON (s.sid = m.sid ) with AND s.sid = sys_context('USERENV','SID'), although I'm not sure what the this query could accomplish. Seems like it could just as well be SELECT 0 from dual;. – Leigh Riffel Jun 4 '12 at 18:17
@LeighRiffel this will tell if the current transaction has uncommited changes. There are more efficient ways to do that however. – Vincent Malgrat Jun 5 '12 at 12:09
@Vincent Malgrat Thank you for the explanation. – Leigh Riffel Jun 5 '12 at 12:15
I agree, it's not very efficient way, but how to measure efficiency in such cases except static code analysys ? – a1ex07 Jun 5 '12 at 16:25
@a1ex07: the explain plan cost is a rough approximation so it shouldn't be your main way to evaluate the efficiency of a query. The true cost of a query can be revealed with a trace. Trace your actual query and all alternative queries and the tkprof will reveal which one is the most efficient. – Vincent Malgrat Jun 7 '12 at 14:14
show 1 more comment

1 Answer

up vote 1 down vote accepted

Some (all?) dynamic V$ views are based not on dictionary tables but on memory structures, so the traditinoal stats are not collected and therefore the optimizer can't compute the cost of queries on those views.

However, the explain plan cost is only a rough approximation of the expected work so it shouldn't be your only way to evaluate the efficiency of a query. The true cost of a query can be revealed with a trace. Trace your actual query and all alternative queries and the tkprof will reveal which one is the most efficient.

For your query in particular, it seems you want to determine if your current session has uncommited work. Alternate queries are described in the following SO questions :

Can you try the queries in these questions and tell us which one is the most efficient (on your high load environment)?

share|improve this answer
Thanks. Also, after doing some research , I believe the cheapest way to check if there is a transaction within current session, is to start a new transaction (which throws an error if there is a pending transaction) and rollback it. It also doesn't require any extra privileges – a1ex07 Jun 7 '12 at 21:28
How do you start a new transaction? – Vincent Malgrat Jun 8 '12 at 7:45
ok, Oracle doesn't let start/begin transaction. However, I can do, for instance, SET TRANSACTION ISOLATION LEVEL READ COMMITTED that fails if I have any pending transactions. – a1ex07 Jun 8 '12 at 14:20
Thanks, it definitely looks like the most efficient way. – Vincent Malgrat Jun 8 '12 at 14:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.