Problem solved but the mystery continue.
I tried the describe in TOAD and got 'object x$dual not found'.
The describe works in sqlplus.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, April 23, 2002 8:23 PM
Hi
Concurrency test from 1 = 10 on Solaris 8.1.7.3
revealed no crashes - using
#!/bin/ksh
for i in 1 2 3 4 ...
do
sqlplus u/p @the_test.sql
done
hth
connor
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
as simultaneously as I could make it on W2K, 9.0.1.2
(which means the
first one was
For this script I get following results on 8161 on DGUX
SQL 1
run time using view x_$dual in centiseconds=1400
run time using table dual in centiseconds=2600
-
On 9012 on AIX I get
SQL 1
run time using view x_$dual in centiseconds=600
run time using table dual in
All,
I think the issue of using SYS.DUAL vs. X$DUAL is much
beyond just response time. It is more related to
easing a potential bottleneck in your database, in a
production environment supporting multiple sessions.
Given that it takes 5 LIOs (upto Oracle8i) and 3 LIOs
(in Oracle9i for every
Title: RE: Currval and buffer gets
Waleed, Kevin, Jonathan, Alexander
8.1.7.0 on Windows 2000
run
time using view x$dual in centiseconds=494run time using table
dual in centiseconds=896run time using direct
:= in centiseconds=1007
Ramon
-Original Message-From: [EMAIL PROTECTED
Gaja is correct. I had big problems recently with a highly concurrent
application selecting from DUAL too many times, resulting in an extremely
hot DUAL table. I wish I had knew this trick a few weeks ago. As it was, an
bit of application tuning sorted it out.
Jim
-Original Message-
I think there is a completely different level at which
to view this issue.
If your application is using dual like there is no tomorrow
then there is almost certainly something wrong with
your application design or code which is a much
more significant threat to performance - both through
Hello Gaja
I checked the report from YAPP and library cache load lock accounts
only to 0.75% of the wait time. 28 seconds during 6100 seconds between
snaps.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, April 24,
Do you know of any reason that keeps Oracle implementing dual the way they
have now and its effect on all the other PL/SQL functions?
Also I saw something on the Metalink where some one inserted many records in
dual and when selecting count(*) from dual it returns the right count but
when
The acess path knows about DUAL and that it will return only 1 row. Create any
dual table under another user and you will see that it returns all rows.
Anjo.
Khedr, Waleed wrote:
Do you know of any reason that keeps Oracle implementing dual the way they
have now and its effect on all the
Hello Yechiel,
Not sure what you mean...I was referring to the cache
buffers chains latch in my note NOT the library cache
load lock.
Gaja
--- Yechiel Adar [EMAIL PROTECTED] wrote:
Hello Gaja
I checked the report from YAPP and library cache
load lock accounts
only to 0.75% of the wait
Title: RE: Currval and buffer gets
On
HP-UX:
model
= 9000/800/N4000-55OS release level= B.11.11processor count = 8clock
speed = 550
MHzmemory = 16384
Mb
SQL set serveroutput on size
1;SQL @test.sql27 /run time using view x_$dual
in centiseconds=600run time using table dual
Agreed! No arguments there. I am all for designing and
writing scalable code from the starting block and
avoiding wastage of resources. But sometimes the
problem is much more complex. I am sure you have
inherited bad application design or bad code
enough times, and may I add Not by choice. Add to
On ORACLE 8163/Win2K , I got some interesting results
though...
ONE SESSION ONLY
SYS@ZETA@RSAKTHI
run time using table dual in centiseconds=400
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.75
SYS@ZETA@RSAKTHI
SYS@ZETA@RSAKTHI
TWO CONCURRENT SESSIONS
SYS@ZETA@RSAKTHI/
run time
Hello Gaja
Thanks for the detailed info.
I created a view and did grant to public.
I did 10 selects and they did only 2 buffers get.
BTW - describe on x$dual does not work but select * works.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL
Hello Gaja,
Here is the line to talk about:
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=5656332
As we see, cu+cr=0 and returned raw is r=1. Normally,
it's not possible to return a raw without touching a block. But there are
some cases such as X$ tables that these statistics are zero.
what would you do if Oracle removed this x$dual or made it multiple row
table in future upgrades?
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 4/23/02 4:08 AM
Hello Gaja
Thanks for the detailed info.
I created a view and did grant to public.
I did 10 selects and
Redefine the view to work on dual and pay the price !!!
Checked it on oracle 8.1.6 and 9.0.1.
I will take the risk that maybe in some future date this will not work
if it can save a lot of resources now.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L
Speaking of Oracle 9i and dual...
But first, some background. In Oracle 8i and earlier, if you refrenced
SYSDATE, USER or some number of other built-in functions, Oracle
automatically converted that into a SELECT ... FROM dual call. Very
expensive (hence this talk about x$dual!)
Enter Oracle
Hi Dan,
I ran the query against x$dual multiple times and I am
seeing a pattern. There are 2 fetches for each access,
only the first one has a value for r. The second
fetch does not have any values for r. I even tried
with an arraysize to 5000, just to make sure.
Comments??
So even if access to
Hi Yechiel,
Below is a spool file from my 8.1.7 database. I am
logged in as SYS and I am able to do a DESC on
X$DUAL. Wonder why it did not work on yours.
SQL desc x$dual
Name Null?
Type
-
I do not think this is right!
Waleed
-Original Message-
Sent: Tuesday, April 23, 2002 2:00 PM
To: Multiple recipients of list ORACLE-L
Speaking of Oracle 9i and dual...
But first, some background. In Oracle 8i and earlier, if you refrenced
SYSDATE, USER or some number of other
SYSDATE for sure, but USER is still done the old fashioned way as far as I
can see.
Anjo.
Toepke, Kevin M wrote:
Speaking of Oracle 9i and dual...
But first, some background. In Oracle 8i and earlier, if you refrenced
SYSDATE, USER or some number of other built-in functions, Oracle
Just a clarification (thanks Gaja!)
With Oracle 9i, calls to SYSDATE in PL/SQL or in-line in your DML statements
do not get converted to SELECT SYSDATE FROM DUAL.
Oracle 9i does not speed up explicit selects from dual.
So, if you have an application where the developers coded SELECT ... FROM
Waleed:
I should have clarified...calls to SYSDATE from PL/SQL get converted.
Try executing the following PL/SQL code in Oracle 8i (or Oracle 8) and again
in Oracle 9i after turning tracing on. In Oracle 8i you will see SELECT
SYSDATE FROM DUAL in your tkprof output. In Oracle 9i you will not!
Well you may be right, but rman is using x$dual.
X$dual has been there since 8.1.5 (?).
Anjo.
Yechiel Adar wrote:
Redefine the view to work on dual and pay the price !!!
Checked it on oracle 8.1.6 and 9.0.1.
I will take the risk that maybe in some future date this will not work
if it can
I did in 8i (8.1.7.3) and did not see what you said:
alter session set sql_trace = true
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse0 0.00 0.00 0
Can you guys tell me about all the overhead to resolve the view definition
to reach the magical X$dual table?
Waleed
-Original Message-
Sent: Tuesday, April 23, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L
Redefine the view to work on dual and pay the price !!!
Checked it on
It's a change that also made it into 8.1.7.3
(or possibly 8.1.7.2) - check in
$ORACLE_HOME/rdbms/admin/standard.sql
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
Can somebody check how many rows we have in sys.x$dual in an Oracle database
running in OPS mode?
Thanks
Waleed
-Original Message-
Sent: Tuesday, April 23, 2002 2:56 PM
To: Multiple recipients of list ORACLE-L
Just a clarification (thanks Gaja!)
With Oracle 9i, calls to SYSDATE in
I am writing a paper on application efficiency and I have a number of simple
tests in that paper that illustrates that in a very efficient application the
switch to x$dual can make around a 70-80 percent improvent. But beaware: the
test suite only accesses x$dual and not other tables so the
Never mess up with Oracle's memory:
It seems that x$dual is not designed for concurrent access. When I try to
run the code below in two simultaneous windows the first one comes back and
the second crash (8173):
declare
nn number;
ss1 date;
ss2 date;
begin
---
ss1 := sysdate;
for i in 1..10
No, I couldn't duplicate this effect (8.1.7.0.0/Linux
and 9.0.1.1.1/Win2k) - 1) first test 2 simultaneous
sessions and then 2) second test 3 simultaneous
sessions running.
Regards
Lyubomir Petrov
--- Khedr, Waleed [EMAIL PROTECTED] wrote:
Never mess up with Oracle's memory:
It seems that
Thanks for the test.I ran this
test to compare the performance difference dealing with dual and
x$dual:
declarenn number;ss1 date;ss2
date;beginss1 := sysdate;for i in 1..10 loopselect 2 into nn
from sys.x_$dual;end loop;ss2 := sysdate;dbms_output.put_line('run
time using view x_$dual
Waleed,
I am using 81714 under NT4.
I can NOT duplicate the problem you're seeing (but I ran it logged in as sys and used
sys.x$dual).
ie - I can run your script from 2 simultaneous windows it works ok.
What platform patch are you on?
Timing wise - the version using sys.x$dual took 500 or
Kevin and Jonathan,
Thanks for the explanation. It's weird for me that Oracle is still
maintaining this kind of dependency between the SQL and PL/SQL engines for
minor sql functions. Also regarding the dual and x$dual, it does not sound
good to me that Oracle still is implementing dual as a
*** 2002-04-23 20:11:26.129
*** SESSION ID:(198.62474) 2002-04-23 20:11:26.125
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object),
addr: 0x1c, PC: opifch()+4668
*** 2002-04-23 20:11:26.766
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump
Title: RE: Currval and buffer gets
Waleed/Kevin/Jonathan ...,
8.1.7.2 on HP-UX 64bit.
Modified version:
declare
n1 number;
n2 number;
ss date;
begin
n1 := sys.dbms_utility.get_time;
for i in 1..10 loop
select sysdate into ss from sys.x_$dual;
end loop;
n2 := sys.dbms_utility.get_time
as simultaneously as I could make it on W2K, 9.0.1.2 (which means the
first one was running as I started the second one)
no problems. Now I ran as sys and changed it to x$dual because I got
errors (table or view not found) when I ran as system and used x_$dual
so I don't know if that changed
I found that the error happens when I run the test using some Oracle tool
but worked fine using sqlplus.
Thanks
-Original Message-
Sent: Tuesday, April 23, 2002 10:38 PM
To: Multiple recipients of list ORACLE-L
as simultaneously as I could make it on W2K, 9.0.1.2 (which means the
Title: RE: Currval and buffer gets
Thanks (this is
why I'm surprised that Oracle did not improve dual and the way they implemented
pl/sql functions. SYSDATE is done using a C interface now in
PL/SQL)
Just to confirm
your results I got these times:
run time using view x_$dual
If you are using PL/sql then try to reference the sequence next value in
the update/insert statement itself. Aso the update/insert can return the
value of the sequence to a PL/SQL memory variable.
regards,
Waleed
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent:
Thank all of you for the replies.
Unfortunately the program is in c++.
Gaja, I will forward your suggestion to the development team.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Sunday, April 21, 2002 8:28 PM
Hi Yechiel,
Hello Gaja
I could not find x$dual. Did select on all_objects got zip.
Oracle 8.1.6.3.4 on NT.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Sunday, April 21, 2002 8:28 PM
Hi Yechiel,
Any full-table-scan in Oracle 8i
returning_clause is supported in most languages.
Also you might need to encapsulate some of your logic using stored
procedures.
Good luck.
Waleed
-Original Message-
Sent: Monday, April 22, 2002 1:06 PM
To: Multiple recipients of list ORACLE-L
Thank all of you for the replies.
Hello Yechiel,
X$DUAL is an Oracle-internal table in the SGA and
will not be shown in an ALL_OBJECTS listing.
Obviously, you need to be SYS to see this. You can do
a describe as SYS and you will see it. Which is the
reason why I recommended creating a view and a public
synonym on the view, so
Hello Gaja,
am I missing something ?
is it 0 logical IO indeed ? or it's not accounted for X$ tables ?
regards...
Gaja Krishna Vaidyanatha wrote:
Hi Yechiel,
Any full-table-scan in Oracle 8i (or below) consumes 4
LIOs to the segment header. This number has reduced to
2 in 9i. Given
Hi Dan and list,
Here is the output from a test after setting 10046 at
level 12. Please tell me whether this is really 0 or
not accounted for. If it is not accounted for, then I
stand corrected.
Thanks,
Gaja
Trace file output starts here
*** 2002-04-22 16:04:14.090
*** SESSION
Hi Yechiel,
Any full-table-scan in Oracle 8i (or below) consumes 4
LIOs to the segment header. This number has reduced to
2 in 9i. Given that the 1 row that you are going after
is in 1 data block, there is 1 LIO for the data block
itself, given you a total of 5 LIOs. You can verify
this by
The 5 buffer gets are 4 CURRENT on the
segment header block and one consistent
on the data block. It's a (relatively minor)
bug in the internal code for scans.
This changes in 9.0.1 to 2 Consistent on
the segment header and one consistent
on the data block in version 9.
The large number of
50 matches
Mail list logo
