RE: Using DUAL

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Thu, 30 May 2002 15:33:22 -0800
Message-ID: <F001.00470765.20020530153322@fatcity.com>

Thanks to all (especially Rachel) for the suggestions. I did a little testing and found:

# of IOs in 9i is 3
Creation of index (with analyze on table and index) made no change to IOs in 9i
Creating a view & synonym on sys.x$dual reduced the IOs to 0...

However, there is an inst_id column in sys.x$dual, which makes me wonder what would happen in an OPS/RAC environment. Unfortunately, I don't have access to that kind of system and can't test it out. Any brave (or foolish) souls out there who have (or are willing to) checked the contents of this object in the OPS/RAC environment?

Daniel W. Fink

-----Original Message-----
Sent: Thursday, May 30, 2002 12:39 PM
To: Multiple recipients of list ORACLE-L

I was not directly involved with that particular discussion. But one of the things that I remembered (and played with, not in production yet!) is to create 'dual' as a view using 'select dummy from x$dual;' This causes less number of Logical IOs. In addition, creating an index on dummy can also help. I have not tested that part yet. Cary Millsap mentioned it in one of his e-mails. From what I understand the nunber of LIOs is Oracle version dependent and 9i uses the least. Gaja had suggested to recreate 'dual' as an IOT. Anjo Kolk mentioned that Oracle has been using x$dual in RMAN code.

HTH,

• Kirti
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Fink, Dan INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 30 2002 - 18:33:22 CDT