Oracle Database Discussions

Hi Gurus,

Can you please kindly provide some points on my below questions. thanks


my env

select * from v$version;

BANNER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

OS: Linux 4 2.6.39-100.5.1.el5uek

session operation: update a partition which have 4 partitions and total 16G

session trace info:

the session keep at active status and waiting for below wait event for more than 70 hours, and os iostats and cpu are almost idle on most time.

WAIT #8: nam='direct path read temp' ela= 7615 file number=202 first dba=105072 block cnt=1 obj#=104719 tim=1344850223569499
WAIT #8: nam='direct path read temp' ela= 5989 file number=202 first dba=85264 block cnt=1 obj#=104719 tim=1344850392833257
WAIT #8: nam='direct path read temp' ela= 319 file number=202 first dba=85248 block cnt=1 obj#=104719 tim=1344850399563184
WAIT #8: nam='direct path read temp' ela= 358 file number=202 first dba=85232 block cnt=1 obj#=104719 tim=1344850406016899
WAIT #8: nam='direct path read temp' ela= 349 file number=202 first dba=85216 block cnt=1 obj#=104719 tim=1344850413023792
WAIT #8: nam='direct path read temp' ela= 7975 file number=202 first dba=85200 block cnt=1 obj#=104719 tim=1344850419495645
WAIT #8: nam='direct path read temp' ela= 331 file number=202 first dba=85184 block cnt=1 obj#=104719 tim=1344850426233450
WAIT #8: nam='direct path read temp' ela= 2641 file number=202 first dba=82880 block cnt=1 obj#=104719 tim=1344850432699800

pgastat:

NAME VALUE/1024/1024 UNIT
------------------------------------------------------------ --------------- ------------------------------------
aggregate PGA target parameter 18432 bytes
aggregate PGA auto target 16523.1475 bytes
global memory bound 1024 bytes
total PGA inuse 75.7246094 bytes
total PGA allocated 162.411133 bytes
maximum PGA allocated 514.130859 bytes
total freeable PGA memory 64.625 bytes
PGA memory freed back to OS 40425.1875 bytes
total PGA used for auto workareas 2.75195313 bytes
maximum PGA used for auto workareas 270.407227 bytes
total PGA used for manual workareas 0 bytes

NAME VALUE/1024/1024 UNIT
------------------------------------------------------------ --------------- ------------------------------------
maximum PGA used for manual workareas 24.5429688 bytes
bytes processed 110558.951 bytes
extra bytes read/written 15021.2559 bytes


Most operation in PGA via query on V$SQL_WORKAREA_ACTIVE

IDX maintainenance (sort)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
My questions:

1. why 'direct path read temp' just read one block every time, my understanding is this event can read one block and multiple blocks at one read call, why it keep read one block in my session?

2. my pga size is big enough, why this operation can not be treated with in PGA memory, instead of read block from disk into temp tablespace?


Thanks for you inputs.
Roy