DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Question about Sorts ( Need help ! )

  1. #1
    Join Date
    Jun 2002
    Posts
    11

    Unhappy

    Hi!,we are confuse about the results of a query that show us,the actives sorts in disk. The situation is: the query show us,that the following statement, "WITHOUT Order By":
    " SELECT NOMBRE,DIA_PROCESO FROM ARINCD WHERE NO_CIA = :b1 AND CENTRO = :b2 "
    (in this moment) has SORTS in our Temporary TS:
    111 extents and 2775 blocks for User#1, and
    112 extents and 2800 blocks for User#2.
    Some Facts:
    - The statement,is a "CURSOR" inside an application's form, that fetch both fields in 2 variables, and then is closed immediately it's done.
    - The table ARINCD,has only " 7 records " and about 30 fields Varchar2 of 3 or 2 positions, and the biggest field is a varchar2 (30).
    " Has a PK as follow: NO_CIA,CENTRO (used in the Where clause )" .
    - Our SORT_AREA_SIZE= 192K and the TS TEMPORARY= 240 M, with initial=next = 192K .

    QUESTIONS:
    - Why if we aren't using ORDER BY and the Where is based in the PK,it is doing SORTS ?
    - Is it possible, that the query used to obtain info about sorts, has wrong joins with the V$SQLAREA; or there is no way to know the text of the statements sorting in disk ?

    This is the Query we are using:
    SELECT s.username, u."USER", u.tablespace, u.contents,
    u.extents, u.blocks, a.sql_text, u.sqladdr, u.sqlhash
    FROM v$session s, v$sort_usage u, v$sqlarea a
    WHERE s.SADDR = u.SESSION_ADDR
    and a.address = u.sqladdr and a.hash_value = u.sqlhash

    What do you think about this ? Regards, Mauro.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    gpaste the execution plan =)

  3. #3
    Join Date
    Jun 2002
    Posts
    11
    This is the execution plan:
    SELECT STATEMENT Cost = 1
    21 TABLE ACCESS BY INDEX ROWID ARINCD
    31 INDEX RANGE SCAN UNIQUE PK_ARINCD

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