Home » RDBMS Server » Server Administration » DUAL, FAST DUAL et. al.
| DUAL, FAST DUAL et. al. [message #515910] |
Thu, 14 July 2011 03:07  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As suggested by John Watson, LF split this topic from Not able to gather Statistics of neither schema nor table
Michel Cadot wrote on Thu, 14 July 2011 09:51Littlefoot wrote on Thu, 14 July 2011 07:56So it seems that DUAL is OK (as it contains only 1 record).
No because Oracle now bypasses dual table and use "fast dual" access (x$dual).
This is the reason I posted a so complicated script to check the content of the real table.
True, that's beyond my knowledge, but ...- you suggested
select rowid from sys.dual;
- he ran
SQL> select rowid from sys.dual;
ROWID
------------------
AAAAECAABAAAAgiAAA
SQL>
What does your "No" mean? I expected 1 record, he got 1 record (being it "dual" or "fast dual").
I Googled a little bit for "fast dual" so I guess I understood the concept, saying something that if you SELECT 'X' FROM DUAL, "fast dual" is used. If you SELECT DUMMY FROM DUAL, "dual" is used. I suppose you used SELECT ROWID FROM DUAL for the same reason (to force it to use "dual")? If so, well, the SELECT returned a single record anyway. Doesn't that mean that DUAL is really OK?
[Updated on: Thu, 14 July 2011 03:43] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Not able to gather Statistics of neither schema nor table [message #515916 is a reply to message #515913] |
Thu, 14 July 2011 03:37  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Would it be possible for this topic to be split? Sriram has already given the answer to the original question (bug 3945156, fixed in 10.2.0.4). The subsequent discussion of DUAL has been very instructive, and I'ld like to be able to get to it through its own topic.
If it is a hassle to do that, forget it. I find this site very useful, and have no right to ask for more.
|
|
|
|
| Re: Not able to gather Statistics of neither schema nor table [message #515944 is a reply to message #515916] |
Thu, 14 July 2011 05:10 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:How come a "normal" user doesn't see newly added records? You did COMMIT those inserts, after all?
It sees them but Oracle bypasses the result and returns what it wants.
See the following test (10.2.0.4 but I bet the result is different across versions and even patchsets).
I screw up again my DUAL table:
SYS> insert into dual values ('X');
1 row created.
SYS> insert into dual values ('Y');
1 row created.
SYS> commit;
Commit complete.
Then I trace the session to query DUAL from my account (after flushing the sga):
MICHEL> alter system flush shared_pool;
System altered.
MICHEL> alter system flush buffer_cache;
System altered.
MICHEL> set serveroutput off
MICHEL> @traceon
Session altered.
MICHEL> select count(*) from sys.dual;
COUNT(*)
----------
1
1 row selected.
MICHEL> select dummy from sys.dual;
D
-
X
1 row selected.
MICHEL> select * from sys.dual;
D
-
X
1 row selected.
First the trace file and TKPROF show a bunch of recursive queries to get the information about sys.dual.
Then there is the part concerning the first query:
select count(*)
from
sys.dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.03 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=56 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=18 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 2.23 2.24
As you can see Oracle takes the FAST DUAL access path which is x$dual with no access to the actual table, so read, no io...
Then the second query, after one more recursive query to get if there is any histogram statistics on the table:
select dummy
from
sys.dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 2 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 2 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=1112 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 2 0.00 0.00
SQL*Net message from client 2 6.73 6.73
As you can see, Oracle actually read the real table (3 reads, 2 IO, 2 db sequential read waits) but returns ONE row. Why? because it knows there is (should be) only one row and then ignore the actual result to give us the first row.
Then the last one to confirm:
select *
from
sys.dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=108 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.95 3.96
Still the 3 reads but no more io (the table is in the cache).
Oracle knows there should be one row and so returns one row and does not care if there are more rows.
Regards
Michel
|
|
|
|
| Re: Not able to gather Statistics of neither schema nor table [message #515949 is a reply to message #515944] |
Thu, 14 July 2011 05:22 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Uh, oh ... I see. That's, though, somewhat "strange" because Oracle misinterprets reality. There actually ARE 3 records in the DUAL table, but it (Oracle) *knows* it should (actually: MUST) contain just a single record - so it returns that "single" record. I guess that my "misinterpreted reality" is Oracle internal stuff and suppose that normal user's tables don't behave like that.
As you, obviously, know enormously more about it than I do, would you be so kind and do another test (I'm afraid I'd screw my database and don't really feel like restoring it if I don't have to): what happens if you DELETE everything from dual, so that it is empty? What would Oracle return in that case? Would it "make up" one record? You know, some kind of analogy: "if there are too many records, return just one. If there are no records, also return (an imaginary) one".
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Not able to gather Statistics of neither schema nor table [message #516043 is a reply to message #516013] |
Thu, 14 July 2011 13:49 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I talked several times about x$dual.
What is it?
Before it is used for the FAST DUAL access, it was used to query DUAL when the database is not opened and so the instance can't access the table:
SYS> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup nomount
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1295832 bytes
Variable Size 130026024 bytes
Database Buffers 67108864 bytes
Redo Buffers 11284480 bytes
SYS> desc x$dual
Name Null? Type
-------------------------------- -------- ----------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(1)
SYS> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
038B05D4 0 1 X
1 row selected.
SYS> select dummy from dual;
D
-
X
1 row selected.
SYS> select dummy from sys.dual;
D
-
X
1 row selected.
And you can then do some "weird" thing like:
SYS> select inst_id from dual;
INST_ID
----------
1
to get the instance number, instead of:
SYS> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
1
It is long since I hacked some part of Oracle but I think I remember that SQL*Plus sends a bunch of queries on DUAL, even if the database is not opened, at least to get this (retrieved from another SQL*Plus session started after the above restart, so can't be gotten from the previous instance):
SYS> def
DEFINE _DATE = "14/07/2011 20:37:27" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "MIKA" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000400" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1002000400" (CHAR)
The last two ones can only be retrieved from the server part as it is the instance version.
If you put a trace admin level on SQL*Net, client side, you will see the statements SQL*Plus sends (I'm too lazy to do it now);
Regards
Michel
|
|
|
|
|
|
| How does it ruins Your database if you play with dual [message #516174 is a reply to message #516085] |
Fri, 15 July 2011 06:03 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Yes...This is Why TOM says DUAL is "magic".
Now you got the proof from Michel for DML what about DDL?
Does it(oracle) allow you to perform ?If you try to drop the table and select rows from it..then
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 15 14:40:38 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$database;
NAME
---------
ORAFAQ
SQL> drop table dual;
Table dropped.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
SQL> create public synonym dual for x$dual;
create public synonym dual for x$dual
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> desc dual;
SP2-0749: Cannot resolve circular path of synonym "dual"
SQL> create table dual as select * from x$dual;
Table created.
Quote:It seems that - if you know what you are doing - you can write code that'll work OK even though DUAL is screwed up.
similar you can truncate...create case sensitive synonyms,alter its structure....then you will not allowed to alter it again.
SQL> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
0366CD54 0 1 X
SQL> sho user
USER is "SYS"
SQL> truncate table dual;
Table truncated.
SQL> select * from dual;
no rows selected
SQL> insert into dual select * from x$dual;
1 row created.
SQL> select * from dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
0366CD54 0 1 X
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> select rowid from dual;
ROWID
------------------
AAAM1BAABAAAAgiAAA
SQL> select sysdate from dual;
SYSDATE
---------
15-JUL-11
SQL> rollback;
Rollback complete.
SQL> select count(*), INST_ID from dual
2 group by INST_ID ;
no rows selected
SQL> select * from dual;
no rows selected
SQL> insert into dual select * from x$dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table dual modify dummy varchar2(10);
Table altered.
SQL> select * from dual;
ADDR INDX INST_ID DUMMY
-------- ---------- ---------- ----------
0366CD54 0 1 X
SQL> select sysdate from dual;
SYSDATE
---------
15-JUL-11
SQL> update dual set dummy='SRIRAM' where dummy='X';
1 row updated.
SQL> select sysdate from dual;
SYSDATE
---------
15-JUL-11
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> select count(*) from sys.dual;
COUNT(*)
----------
1
SQL> create or replace synonym "x$dual" for sys.dual;
Synonym created.
SQL> select * from x$dual;
ADDR INDX INST_ID D
-------- ---------- ---------- -
0366CD54 0 1 X
SQL> select * from "x$dual" ;
ADDR INDX INST_ID DUMMY
-------- ---------- ---------- ----------
0366CD54 0 1 SRIRAM
SQL> drop table dual;
drop table dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
SQL> sho user
USER is "SYS"
SQL> truncate table dual;
Table truncated.
SQL> drop table dual;
drop table dual
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(10)
SQL> desc x$dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(1)
SQL> drop synonym "x$dual";
drop synonym "x$dual"
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
SQL> select * from dual;
no rows selected
SQL> desc dual;
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
DUMMY VARCHAR2(10)
SQL> alter table dual drop column ADDR;
alter table dual drop column ADDR
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS
[Updated on: Fri, 15 July 2011 06:06] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Nov 29 06:41:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
