High Parse Count but Zero Executions?
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-lReceived on Thu Apr 15 2010 - 00:52:36 CDT