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

  • From: VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 7 Sep 2007 02:39:00 +0530

Folks

CASE 1 -
======

Qs 1 How is the Cursor Plan for the following SQL to be Generated?
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.

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@speaixp5lp11 (TNS V1-V3)
 SELECT  COUNT(*) FROM ICI WHERE ICI.BANK_ID =  :1  AND  SOL_ID
=  :2   AND ZONE_CODE =  :3   AND ZONE_DATE = TO_DATE( :4  ,'DD-
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?

RMAN COMMAND used to Copy from a Database existing on a Unix Mounted Filesystem 
TO an ASM Disk Group (existing on a RAW Device)
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 0 AS COPY TAG 
'ASMDBR_Migration' DATABASE FORMAT
'+DG_DATA';

Configuration :-
Solaris 10,
Oracle 10.2.0.3



**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are 
not to copy, disclose, or distribute this e-mail or its contents to any other 
person and any such actions are unlawful. This e-mail may contain viruses. 
Infosys has taken every reasonable precaution to minimize this risk, but is not 
liable for any damage you may sustain as a result of any virus in this e-mail. 
You should carry out your own virus checks before opening the e-mail or 
attachment. Infosys reserves the right to monitor and review the content of all 
messages sent to or from this e-mail address. Messages sent to or from this 
e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

Other related posts: