Hello, all
Please, understand me, what is going on in my system (DB: Oracle database 11.2.0.3, OS: Windows 2008 R2).
In AWR report (1 hour) I see next:
Foreground Wait Events
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path write temp 132,627 0 1,056 8 0.8 21.7
direct path read temp 308,969 0 565 2 2.0 11.6
log file sync 19,228 0 241 13 0.1 5.0
direct path write 17,698 0 135 8 0.1 2.8
db file sequential read 21,149 0 94 4 0.1 1.9
SQL*Net message from dblin 59 0 5 86 0.0 .1
...
Segments by Direct Physical Reads DB/Inst: SGRE/sgre Snaps: 1039-1040
-> Total Direct Physical Reads: 392,273
-> Captured Segments account for 94.7% of Total
Tablespace Subobject Obj. Direct
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
** MISSING TEMP ** TRANSIENT: 437734 MISSING ** UNDEF 38,290 9.76
DBSNMP TEMP MGMT_TEMPT_SQL TABLE 38,242 9.75
** MISSING TEMP ** TRANSIENT: 438784 MISSING ** UNDEF 37,790 9.63
** MISSING TEMP ** TRANSIENT: 437312 MISSING ** UNDEF 37,661 9.60
** MISSING TEMP ** TRANSIENT: 439257 MISSING ** UNDEF 37,477 9.55
Some selects:
SELECT S.sid,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.SEGTYPE
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
AND S.sid = 732
ORDER BY S.username, S.sid;
SID MB_USED TABLESPACE SEGTYPE
732 2 TEMP LOB_DATA
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP INDEX
732 1 TEMP DATA
732 1 TEMP INDEX
732 1 TEMP LOB_INDEX
select st.sid, sn.name, st.VALUE
from V$statname sn, v$sesstat st
where st.STATISTIC# = sn.STATISTIC#
and (sn.name like 'sorts%')
and st.sid in (select sid from v$session_wait where event like '%direct path write temp%')
order by st.sid
SID NAME VALUE
732 sorts (memory) 591408
732 sorts (rows) 102126
732 sorts (disk) 0
Why I do not see any disk sorts? If TEMP is used for no sort operations, then for which operations?
How I can see that? How can I decrease direct path write temp without tuning SQL?
Additional information:
PGA_AGGREGATE_TARGET is set to big value - 6GB (3GB enough due to advisory recommendation)
Please, help.
Regards, user12103911.