NetComrade wrote:
> 1 select extents,sqladdr,sqlhash from v$sort_usage su, v$session s
> 2* where s.saddr=su.session_addr
> SQL> /
>
> EXTENTS SQLADDR SQLHASH
> ---------- -------- ----------
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> 1 9EC73B78 1388977235
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> 1 9FCC6090 1891762568
> select sql_text, parsing_user_id from v$sql where address='9FCC6090';
>
> SQL_TEXT PARSING_USER_ID
> ---------------------------------------- ---------------
> SELECT 1 FROM dual 23
> SELECT 1 FROM dual 22
> SELECT 1 FROM dual 20
> SELECT 1 FROM dual 22
>
> there are 4 users that execute this statement, but only one of them
> seems to sort to disk (22, I joined the first query with v$session).
>
> I can't reproduce this via sql*plus.. I tried setting sort_area_size
> higher to .5M (on session level of app server), but that doesn't help,
> plus selecting from dual shouldn't sort to disk!
>
> Am I doing something wront? All other statements that sort to disk,
> seem to be large enough to sort to disk (sort_area_size is set to
> 128K) This is a production db.
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email
Enable SQL TRACE & then use TKPROF to obtain the actual EXPLAIN PLAN.
Post the results here.
Received on Mon Apr 21 2003 - 19:28:08 CDT
