4

I'm trying to understand what is causing an open query on an Oracle (10) database.

On AWR it shows a very high number of parse calls (e.g. 15,000+ in a 1 hour period), but 0 executions.

How it can the query not be executed, but then parsed 15000 times?

Parse Calls : 15,000+

Executions : 0

SQL Text : select * from AVIEW

Baron Ludwig
  • 41
  • 1
  • 2
  • 1
    have you tried to run the query? maybe trows an exception, let's say a `not a group by expresion`, so it doesn't count as execution... – Florin Ghita May 22 '15 at 10:40

4 Answers4

1

The * in the SQL would explain the repeated parsing. You should replace it with a list of field names.

Philip Sheard
  • 5,789
  • 5
  • 27
  • 42
  • Currently, I can't find / trace where this query is coming from - so unable to amend! How does this relate to the 0 executions? Is there one call, producing many parses, or many calls each causing a parse? – Baron Ludwig May 22 '15 at 10:33
  • You will probably find that the Oracle engine is constantly parsing SQL statements in the background. Yours is never fully explained, so it gets parsed over and over again, even though no one uses it. It is probable that someone just whacked in the query dynamically, to get a quick view of the table contents. This sort of thing is bound to happen. I would just ignore it. – Philip Sheard May 22 '15 at 14:27
  • Noticed the same parsing in our prod env as well for a couple of tables. Trying to gather more details and will update here if i get some clue... – pahariayogi Jun 17 '16 at 14:05
0

Oracle 11, java, jdbc 11.2.0.3

Problem occurs when you getting sequence from insert like this

PreparedStatement ps = connection.prepareStatement(QUERY, new String[] { "student_id" });

We found that jdbc driver prepares "SELECT * FROM " statement before every insert. There is only parse operation without execution.

T4CConnection.doDescribeTable

T4CStatement localT4CStatement = new T4CStatement(this, -1, -1);
localT4CStatement.open();
String str1 = paramAutoKeyInfo.getTableName();
String str2 = new StringBuilder().append("SELECT * FROM ").append(str1).toString();
localT4CStatement.sqlObject.initialize(str2);

Oracle parser doesn't cache parsed queries with "*" so there is additional parse operation per every insert.

Community
  • 1
  • 1
Atmega
  • 131
  • 4
0

Zero executions indicates that the query did not complete within the AWR snapshot

BobC
  • 4,208
  • 1
  • 12
  • 15
0

We have a similar issue, but the query was slightly different:

select col1, col2, col3 from table

Result was the same. A high parse rate but zero executions.

The reason was StatementCreatorUtils#setNull from spring-jdbc. ver 4.2.7 When executing:

insert into table (col1, col2, col3) values (val1, null, null)

there was a call to the database for a parameter type.

Dominik Wasniowski
  • 1