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

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Fri, 7 Sep 2007 02:39:00 +0530
Message-ID: <69E1360E54B50C4A828A136C158E474201DBC2AA7B@BLRKECMBX02.ad.infosys.com>

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_at_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- -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***

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 06 2007 - 16:09:00 CDT