Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Is Cursor Plan an SQL Trace Execution Plan ? ........... Basic Qs

RE: Is Cursor Plan an SQL Trace Execution Plan ? ........... Basic Qs

From: Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com>
Date: Thu, 6 Sep 2007 23:55:02 +0200
Message-ID: <607D6181D9919041BE792D70EF2AEC48242B2D@LIMENS.sivsa.int>


Vivek,  

Qs 1 How is the Cursor Plan for the following SQL to be Generated?  

by querying v$sql_plan and v$sql_plan_statistics for this hash, you will obtain one or more plans depending the number of childs of the parent cursor  

Qs 2 Is Cursor Plan same as Execution Plan (obtained from SQL Trace)?

NOTE - The respective NON-RAC Database, (existing on a Solaris Unix Mounted Filesystem), has already been Bounced.  

the plan in the sql trace (not one obtained via running explain = param of tkprof) will correspond to one or more of the plans generated in v$sql_plan... if the plan don't have flushed out when you do the query  

Statspack excerpt taken during a Benchmark Run of Application Transactions on this NON-RAC Database:-  

    CPU                  CPU per             Elapsd
Old

  Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value

    364.58 9,170 0.04 15.3 382.19 3,063,141 3246265085

Module: lisrvr-fin-listval_at_speaixp5lp11 (TNS V1-V3)

 SELECT COUNT(*) FROM ICI WHERE ICI.BANK_ID = :1 AND SOL_ID

MM-YYYY HH24:MI:SS') AND INST_NUM = :5     CASE 2 -
 

A Cold Copy of the above NON-RAC Database was taken into another Mounted Filesystem partition and Converted using RMAN (Detailed Command given below) into a 2-Node RAC-ASM Database (existing on RAW Device).  

On Repeating the Benchmark Run of the SAME Application Transactions on this 2-Node RAC ASM Database, using the SAME Application INPUT DATA Values, the Statspack generated had the SAME "Old Hash Value" i.e. 3246265085 for the SAME SQL Statement (though with Different Comparative Values of CPU Time (s), Executions, CPU per Exec (s), %Total, Elapsed Time (s) , Buffer Gets).  

Qs 1 Why is the Old Hash Value i.e 3246265085 the SAME for the SAME SQL Script for BOTH the Databases i.e NON-RAC & 2-Node RAC-ASM?  

Nope on this  

regards,  

alvaro

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 06 2007 - 16:55:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US