| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> cache buffers chains.
Dear All,
I am puzzled with the following
Solaris 8 Oracle 8.1.7.4 64 bit, DW environment
The following sql statement runs every 10 mins to retrieve some tablespace information.
select 'TBSP', a.tablespace_name, a.total mb_total, nvl(b.free,0) mb_free,
round((a.total-nvl(b.free,0))*100/a.total) pct_used
from (select tablespace_name,round(sum(bytes)/1024/1024) free, max(bytes) maxfree
from dba_free_space group by tablespace_name) b,
(select tablespace_name,round(sum(bytes)/1024/1024) total
from dba_data_files group by tablespace_name) a
where a.tablespace_name = b.tablespace_name (+)
order by 5 ;
However when insert are running against a partitioned table I can see that this previous process is waiting on latch free event. This event is skewed over a number of cache buffers chains latches 99% pointing to different index blocks belonging to two indexes that the table accepting the inserts has.
Insert statement ( concurrently two insert are running)
INSERT INTO LIRS.lir_call_detail_record( FNAME,CALLING_NUMBER,CALLED_NUMBER,ORIGINALLY_CALLED_NUMBER,REDIRECTING_ NUMBER,CALL_DATE,CALL_DURATION,INCOMING_ROUTE, OUTGOING_ROUTE,EXCHANGE_IDENTITY,NETWORK_CALL_REFERENCE) VALUES( :1,:2,:3,:4,:5,TO_DATE(:6,'YYMMDDHH24MISS'),:7,:8,:9,:10,:11) Indexes
CDR_CALLED_NUM_DATE columns called_number, call_date partitioned by call_date
CDR_CALLING_NUM_DATE column calling_number,call_date partitioned also by call_date
May you please explain why I have the first process waiting on cache buffers chains that point to hot blocks belonging to the aforementioned indexes since it is only reporting free and used tablespace space?
Also it would be great if you could give me a hint on how to resolve the situation described above. Will direct load insert be helpful ?
Kind Regards,
Hatzistavrou Yannis
Oracle Database Administrator
Atos Origin
Neapoleos 1a
GREECE Tel.: +30 210-8113478
Mob.: +30 693-78118029
www.atosorigin.com
 |
 |