Home » RDBMS Server » Performance Tuning » SQL with more parse (11g)
SQL with more parse [message #524848] Tue, 27 September 2011 04:01 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA where rownum < 5 ORDER BY PARSE_CALLS desc;



select
   sql_text,
   parse_calls,
   executions,
   parse_calls/executions "Ratio",
   loaded_versions
from
   v$sql
where
   parsing_user_id !=0
and
   parse_calls > 500
and
   executions  > 500
and
   rownum < 5
order by 2 desc;



Can somebody tel me why two queries return different results/different queries .
Re: SQL with more parse [message #524851 is a reply to message #524848] Tue, 27 September 2011 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because they do not intend to return the same ones.

Regards
Michel
Re: SQL with more parse [message #524863 is a reply to message #524851] Tue, 27 September 2011 04:19 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Whts the difference ?
Re: SQL with more parse [message #524872 is a reply to message #524863] Tue, 27 September 2011 04:30 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
requested columns, different where clause, order by? It is rather obvious...
Re: SQL with more parse [message #524874 is a reply to message #524872] Tue, 27 September 2011 04:38 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
both are order by "parse_calls" . and top in the output are parse_calls of 2000+ . so i was expecting both to return same results.Can someone give some "details" on whats wrong here.
Re: SQL with more parse [message #524888 is a reply to message #524874] Tue, 27 September 2011 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Different WHERE clauses.
Don't you see it?

Regards
Michel
Re: SQL with more parse [message #524898 is a reply to message #524888] Tue, 27 September 2011 05:39 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
rownum filter will throw it too, it's in the wrong place for a 'top n' style result.
Previous Topic: MERGE Statment repetitive subquery refactoring (3 Merged)
Next Topic: Query tuning
Goto Forum:
  


Current Time: Fri Mar 29 07:13:24 CDT 2024