High Parse Count but Zero Executions?

From: Leyi Zhang (Kamus) <"Leyi>
Date: Thu, 15 Apr 2010 13:52:36 +0800
Message-ID: <x2v94b9f9d01004142252id77ae0e8t1874318d31ae6526_at_mail.gmail.com>



Hi, all

I found something in Ct. AWR report.

AWR ReportSQL ordered by Parse Calls

  • Total Parse Calls: 258,803
  • Captured SQL account for 17.3% of Total

  Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text 38,170 0 14.75 7rm49gh33ar9y <#7rm49gh33ar9y> w3wp.exe SELECT * FROM areacode 38,170 0 14.75 cn2t3r906n9my <#cn2t3r906n9my> w3wp.exe SELECT * FROM mobilecode 16,404 16,404 6.34 9ryrk2j31926w <#9ryrk2j31926w> w3wp.exe select prodname , scode from... 9,007 9,007 3.48 47r1y8yn34jmj <#47r1y8yn34jmj> select default$ from col$ wher... 3,995 3,995 1.54 0267xn40a4y9z <#0267xn40a4y9z> w3wp.exe
insert into callhist (callid, ... 3,995 3,995 1.54 cxqrvjucw6xj0<#cxqrvjucw6xj0> w3wp.exe
SELECT SEQCALLHIST.NEXTVAL FR... 2,514 0 0.97 59r52k9z6k955 <#59r52k9z6k955> w3wp.exe
SELECT * FROM ems 2,514 0 0.97 77rh9419xawcr <#77rh9419xawcr> w3wp.exe SELECT * FROM contact 2,514 0 0.97 cxwh71mxyq587 <#cxwh71mxyq587> w3wp.exe SELECT * FROM topic 2,514 0 0.97 dhmx88cb52sb9 <#dhmx88cb52sb9> w3wp.exe SELECT * FROM address
Please notice "Parse Calls" and "Executions", this report was generated in 20 mins time period, so 38170 parse calls for SQL cn2t3r906n9my<?ui=2&view=btop&ver=810sazokfgk1#cn2t3r906n9my>is very high.

And the funny things is: we *CANNOT* find all "Executions=0" SQL from the application source code, it looks like oracle database generate them itself!

Ct. using .NET to develop. The only statement about "mobilecode" table (SQL cn2t3r906n9my <?ui=2&view=btop&ver=810sazokfgk1#cn2t3r906n9my>) we can find shows as below, I knew Ct. not using binding var, but this should not be the cause:

if( m2=="13" || m2=="15" || m2=="18" || m3=="013" || m3=="015" || m3=="018" ) {
//mobile
sw = new StringWriter();
//2009-11-02 seagull
if (mobile.Length >= 7)
{
mobile = mobile.Substring( 0, 7 );
sw.Write( "select * from mobilecode,areacode " + "where mobilecode.areacode=areacode.areacode " + "and mobilecode.mobilecode='{0}'",
mobile);
Agentwork.db.FillTable( dt, sw.ToString() ); }
}

public int FillTable(DataTable dt,string SelectCmd) {
int ret = -1;
LilacTrace.Write("FillTable sql:{0}\n", SelectCmd);
_conn = new System.Data.OleDb.OleDbConnection();
_conn.ConnectionString = this.myConn;
_adapter = new System.Data.OleDb.OleDbDataAdapter();
_command = new System.Data.OleDb.OleDbCommand();
_command.Connection = _conn;

try
{
_conn.Open ();

}
catch(Exception ee)
{
//Write Error
LilacTrace.Write("Error FillTable Open Connection Select sql=[{0}], Error={1}, conn={2}{4}, state={3}\n", SelectCmd, ee.Message,
_conn.GetHashCode(),

Enum.GetName(typeof(ConnectionState), _conn.State), _conn.ConnectionString);
_command.Dispose();
_adapter.Dispose();
_conn.Dispose();

return ret;
}
_command.CommandText = SelectCmd;
_adapter.SelectCommand =_command;

try
{
_adapter.Fill(dt);

ret = dt.Rows.Count;
}
catch(Exception ee)
{
//Write Error
LilacTrace.Write("Error FillTable adapter fill Select sql=[{0}], Error={1}, conn={2}{4}, state= {3}\n", Select命令, ee.Message, _conn.GetHashCode(), Enum.GetName(typeof(ConnectionState), _conn.State), _conn.ConnectionString); }
_conn.Close();
_command.Dispose();
_adapter.Dispose();
_conn.Dispose();

return ret;
}

Any hint will be very appreciated.

--
Kamus <kamusis_at_gmail.com>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 15 2010 - 00:52:36 CDT

Original text of this message