sys.dual any issues?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: sys.dual any issues?

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    sys.dual any issues?

    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.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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;

    Code:
    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
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    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

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    If you are convinced that
    Code:
    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  5. #5
    Join Date
    Apr 2003
    Posts
    353
    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.

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Yea, someone wasn't think'n when they put that sys. there. I'm betting that's one of your issues.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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!
    anandkl

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I dont think query the dual would hang the database. It would hang if you have smth like this

    Code:
    select 1 
      from dual
       for update
    I have seen this sort of technique in Peoplesoft (to serialize a process)

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    > 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.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I thought Connor McDonald used the xdual thingie before others?

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width