Complied Version of Statement (Prepared=True)

  • Thread starter Thread starter Sameer
  • Start date Start date
S

Sameer

Hello,

I have a small .NET application. I use bind variables all
overe my application. But my parse:execute ratio is 1 for
all of my SELECT statements. There are many softparses in
my application. I have also set
session_cached_cursors.

DB Version 9.2.0
OS NT
Provider: OraOLEDB 9.2.0.1.0

While using ADODB command object I set the "Prepared"
parameter to true. But
even though
there are many parses.

Heres is the simple block of code ..

strCmd = "SELECT DISTINCT TO_CHAR(exp_date,'Month') Months, " _
TO_CHAR(exp_date,'MM') MM FROM expenses ORDER BY MM"

cmd1 = New ADODB.Command()
cmd1.ActiveConnection = cConn
cmd1.CommandText = strCmd

cmd2 = New ADODB.Command()
cmd2.ActiveConnection = cConn
cmd2.CommandText = strCmd
cmd2.Prepared = True

For intLoop = 1 To 4
cmd1.Execute()
Next intLoop

For intLoop = 1 To 4
cmd2.Execute()
Next intLoop


Heres the tkprof trace output

################################################################################
SELECT DISTINCT TO_CHAR(exp_date,'Month') Months,
TO_CHAR(exp_date,'MM') MM
FROM
expenses ORDER BY MM

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ----------
----------
----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.04 0.06 0 0 0 0
Fetch 8 0.01 0.01 0 56 0 226
------- ------ -------- ---------- ---------- ----------
----------
----------
total 24 0.06 0.07 0 56 0 226

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
23 SORT ORDER BY
23 TABLE ACCESS FULL EXPENSES

################################################################################

Logically it should be one parse and 8 execution. Will
anybody please
suggest me, how do I minimise the number of parses?

Thanks in advance

Sameer
 
Hi Sameer,

Try using managed Oracle provider (either MS' one or Oracle's) or even OleDb
one.
It is not a good practise to use (old) ADO for .net data access.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Hello,

I have a small .NET application. I use bind variables all
overe my application. But my parse:execute ratio is 1 for
all of my SELECT statements. There are many softparses in
my application. I have also set
session_cached_cursors.

DB Version 9.2.0
OS NT
Provider: OraOLEDB 9.2.0.1.0

While using ADODB command object I set the "Prepared"
parameter to true. But
even though
there are many parses.

Heres is the simple block of code ..

strCmd = "SELECT DISTINCT TO_CHAR(exp_date,'Month') Months, " _
TO_CHAR(exp_date,'MM') MM FROM expenses ORDER BY MM"

cmd1 = New ADODB.Command()
cmd1.ActiveConnection = cConn
cmd1.CommandText = strCmd

cmd2 = New ADODB.Command()
cmd2.ActiveConnection = cConn
cmd2.CommandText = strCmd
cmd2.Prepared = True

For intLoop = 1 To 4
cmd1.Execute()
Next intLoop

For intLoop = 1 To 4
cmd2.Execute()
Next intLoop


Heres the tkprof trace output

############################################################################
####
SELECT DISTINCT TO_CHAR(exp_date,'Month') Months,
TO_CHAR(exp_date,'MM') MM
FROM
expenses ORDER BY MM

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ----------
----------
----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.04 0.06 0 0 0 0
Fetch 8 0.01 0.01 0 56 0 226
------- ------ -------- ---------- ---------- ----------
----------
----------
total 24 0.06 0.07 0 56 0 226

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
23 SORT ORDER BY
23 TABLE ACCESS FULL EXPENSES

############################################################################
####

Logically it should be one parse and 8 execution. Will
anybody please
suggest me, how do I minimise the number of parses?

Thanks in advance

Sameer
 
Hi Miha,

I have tried that.. Heres again. My code and tkprof output
files.. Funniest part is when I set Prepare and execute
twice, there is one more PARSE call to the statement
without execute.

And when I remove Prepare, and execute it twice, there are
2 PARSE and 2 EXECUTE calls.

All I want to stop or eliminate this second parse call with
the same session.


Sub foo()
Dim sStmt As String
Dim sStmt2 As String
Dim strConn As String =
"Provider=OraOLEDB.Oracle;Data Source=orcl;User
ID=scott;Password=tiger;"
Dim cConn As New OleDb.OleDbConnection(strConn)

cConn.Open()

Dim sSQLQuery As New OleDb.OleDbCommand(sStmt, cConn)
Dim oReader As OleDb.OleDbDataReader
Dim sMonth As String

sStmt2 = "ALTER SESSION SET SQL_TRACE=TRUE"
sSQLQuery.CommandText = sStmt2
sSQLQuery.ExecuteNonQuery()

sStmt = "SELECT Id, to_char(Exp_date,'DD.MM.YYYY')
Expdate " _
& " FROM expenses WHERE to_char(exp_date,'MM') =
:expMonth "

sSQLQuery.CommandText = sStmt

sMonth = "10"
sSQLQuery.Parameters.Add("expMonth",
Data.OleDb.OleDbType.VarChar, 10).Value = sMonth
sSQLQuery.Prepare()
oReader = sSQLQuery.ExecuteReader()

While oReader.Read()
Response.Write(oReader.GetString(1).ToString +
"<br>")
End While
oReader.Close()

sMonth = "12"
sSQLQuery.Parameters(0).Value = sMonth
oReader = sSQLQuery.ExecuteReader()

While oReader.Read()
Response.Write(oReader.GetString(1).ToString +
"<br>")
End While

oReader.Close()
cConn.Close()
End Sub


****************************************************
With Prepare TKPROF output
****************************************************


ALTER SESSION SET SQL_TRACE=TRUE


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 1 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62
********************************************************************************

SELECT Id, to_char(Exp_date,'DD.MM.YYYY') Expdate
FROM
expenses WHERE to_char(exp_date,'MM') = :expMonth


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0
0 0
Execute 0 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 1 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62
********************************************************************************

SELECT Id , to_char(Exp_date,'DD.MM.YYYY') Expdate
FROM
expenses WHERE to_char(exp_date,'MM') = :expMonth


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 2 0.00 0.00 0 0
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 61
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 6 0.00 0.00 0 14
0 61

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
34 TABLE ACCESS FULL EXPENSES




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 3 0.00 0.00 0 0
0 0
Execute 3 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 61
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 8 0.00 0.00 0 14
0 61

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 0 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 0 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0

4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.


****************************************************
Without Prepare TKPROF output
****************************************************
ALTER SESSION SET SQL_TRACE=TRUE


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 1 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62
********************************************************************************

SELECT Id , to_char(Exp_date,'DD.MM.YYYY') Expdate
FROM
expenses WHERE to_char(exp_date,'MM') = :expMonth


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 2 0.00 0.00 0 0
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 41
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 6 0.00 0.00 0 14
0 41

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
34 TABLE ACCESS FULL EXPENSES




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 2 0.00 0.00 0 0
0 0
Execute 3 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 41
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 7 0.00 0.00 0 14
0 41

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 0 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 0 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
 
Hi Sameer,

Are you sure it is actualy parsing the statament again?
It seems odd to me.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Hi Miha,

I have tried that.. Heres again. My code and tkprof output
files.. Funniest part is when I set Prepare and execute
twice, there is one more PARSE call to the statement
without execute.

And when I remove Prepare, and execute it twice, there are
2 PARSE and 2 EXECUTE calls.

All I want to stop or eliminate this second parse call with
the same session.


Sub foo()
Dim sStmt As String
Dim sStmt2 As String
Dim strConn As String =
"Provider=OraOLEDB.Oracle;Data Source=orcl;User
ID=scott;Password=tiger;"
Dim cConn As New OleDb.OleDbConnection(strConn)

cConn.Open()

Dim sSQLQuery As New OleDb.OleDbCommand(sStmt, cConn)
Dim oReader As OleDb.OleDbDataReader
Dim sMonth As String

sStmt2 = "ALTER SESSION SET SQL_TRACE=TRUE"
sSQLQuery.CommandText = sStmt2
sSQLQuery.ExecuteNonQuery()

sStmt = "SELECT Id, to_char(Exp_date,'DD.MM.YYYY')
Expdate " _
& " FROM expenses WHERE to_char(exp_date,'MM') =
:expMonth "

sSQLQuery.CommandText = sStmt

sMonth = "10"
sSQLQuery.Parameters.Add("expMonth",
Data.OleDb.OleDbType.VarChar, 10).Value = sMonth
sSQLQuery.Prepare()
oReader = sSQLQuery.ExecuteReader()

While oReader.Read()
Response.Write(oReader.GetString(1).ToString +
"<br>")
End While
oReader.Close()

sMonth = "12"
sSQLQuery.Parameters(0).Value = sMonth
oReader = sSQLQuery.ExecuteReader()

While oReader.Read()
Response.Write(oReader.GetString(1).ToString +
"<br>")
End While

oReader.Close()
cConn.Close()
End Sub


****************************************************
With Prepare TKPROF output
****************************************************


ALTER SESSION SET SQL_TRACE=TRUE


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 1 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62
****************************************************************************
****

SELECT Id, to_char(Exp_date,'DD.MM.YYYY') Expdate
FROM
expenses WHERE to_char(exp_date,'MM') = :expMonth


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 1 0.00 0.00 0 0
0 0
Execute 0 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 1 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62
****************************************************************************
****

SELECT Id , to_char(Exp_date,'DD.MM.YYYY') Expdate
FROM
expenses WHERE to_char(exp_date,'MM') = :expMonth


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 2 0.00 0.00 0 0
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 61
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 6 0.00 0.00 0 14
0 61

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
34 TABLE ACCESS FULL EXPENSES




****************************************************************************
****

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 3 0.00 0.00 0 0
0 0
Execute 3 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 61
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 8 0.00 0.00 0 14
0 61

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 0 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 0 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0

4 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.


****************************************************
Without Prepare TKPROF output
****************************************************
ALTER SESSION SET SQL_TRACE=TRUE


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 1 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62
****************************************************************************
****

SELECT Id , to_char(Exp_date,'DD.MM.YYYY') Expdate
FROM
expenses WHERE to_char(exp_date,'MM') = :expMonth


call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 2 0.00 0.00 0 0
0 0
Execute 2 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 41
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 6 0.00 0.00 0 14
0 41

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
34 TABLE ACCESS FULL EXPENSES




****************************************************************************
****

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 2 0.00 0.00 0 0
0 0
Execute 3 0.00 0.00 0 0
0 0
Fetch 2 0.00 0.00 0 14
0 41
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 7 0.00 0.00 0 14
0 41

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse 0 0.00 0.00 0 0
0 0
Execute 0 0.00 0.00 0 0
0 0
Fetch 0 0.00 0.00 0 0
0 0
------- ------ -------- ---------- ---------- ----------
---------- ----------
total 0 0.00 0.00 0 0
0 0

Misses in library cache during parse: 0
 
Hi Miha,

yes, it parses the statement again. And that is why the
parse count is equal to execute count in tkprof report.
Otherwise it should be one parse call and 2 execute calls.

Is it possible for you to make same excerise?
 
Back
Top