-
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.
-
gpaste the execution plan =)
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|