sys.dual any issues?

[engiri]

Is there any known issues with selecting as sys.dual.

Some of our application modules are coded with selections from above kind of query.

We are getting calls from users that their sessions are hung.
But when we see their sessions stats at database as inactive.

The last query executed by them mostly the query with selection as above. An sample query is given below.

Select userenv('sessionid') from sys.dual

Thanks in advance.

[OracleDoc]

Dual is owned by SYS but is assigned a public synonym so everyone can use it. Basically what is happening when your users are querying off of sys.dual are quering directly off the table rather than the synonym( not a good thing).
I highly doubt that the query you specified is hanging the users sessions. Take a look at the code below;

SQL> select owner,object_type from dba_objects
  2  where object_name='DUAL';

OWNER                          OBJECT_TYPE
------------------------------ ------------------
SYS                            TABLE
PUBLIC                         SYNONYM

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')
--------------------
            30632131

[engiri]

Hi

The user session at gv$session (Ours is RAC)
is INACTIVE, for the time since they reported the hang.
Example
If the user reported hang since 5 minutes,
the value of the last_call_et is 300 and status =INACTIVE
in gv$session for that user.

Thanks

[OracleDoc]

If you are convinced that

select userenv('sessionid') from dual;

is hanging your users session try running that same query from your desktop via a sqlplus session and see if it hangs your session. Either way you'll be able to tell two things.
1. If it doesn't hang your session its something else that's causing your havoc.
2. If it does hang your session then you may have some issues with querying off of dual.

[engiri]

Yes. I have done various checkings with the above command in parallel and found executing. We have requested for a change in that module to trim off that sys. and awiting for the testing.

[OracleDoc]

Yea, someone wasn't think'n when they put that sys. there. I'm betting that's one of your issues.

[anandkl]

An alternative DUAL
--------------------

There is a cost when selecting from DUAL, even if we’re only selecting a constant value. To demonstrate:
SQL> select 1 from dual;
Elapsed: 00:00:00.01
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
We see that each select from DUAL costs 3 consistent gets. Now, if you do a lot of selecting from DUAL your code might benefit from using an alternative DUAL, one that only requires 1 consistent get. Here’s how it’s done:
create table xdual (
dummy varchar2(1) primary key
)
organization index;
insert into xdual varchar2(9);
analyze table xdual compute statistics for table for all indexes for all
indexed columns;
The trick is to create the XDUAL as an index-organized table and analyze it properly. This allows the cost-based optimizer to generate the most efficient plan possible, which requires only 1 consistent get:
SQL> select 1 from xdual;

Elapsed: 00:00:00.01
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_33973' (UNIQUE) (Cost=1
Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
380 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
To see how these two compare, consider the following code example:
declare
x number;
begin
for i in 1 .. 10000 loop
select 1 into x from dual;
end loop;
end;
This required about 0.32 seconds on my machine using dual, whereas if I exchanged XDUAL with DUAL I had to wait only 0.25 seconds. Not a whole lot, but it’s still about 20% reduction. Also keep in mind that consistent gets = CPU resources so if you reduce the required number of consistent gets you reduce the need for CPU.
Note that in Oracle 10g this situation disappears since the DUAL table now has become a special internal table that requires no consistent gets!

[pando]

I dont think query the dual would hang the database. It would hang if you have smth like this

select 1 
  from dual
   for update

I have seen this sort of technique in Peoplesoft (to serialize a process)

[padders]

> insert into xdual varchar2(9);

LOL. Like that's gonna work.

Let's see now. Mike Ault posts an article on DBAZine without properly testing his code and you copy his article (including stupid mistake) here without any acknowledgment. I don't know which is worse.

[pando]

I thought Connor McDonald used the xdual thingie before others?

http://www.oracledba.co.uk/tips/dual_speed.htm