oracle 事务槽等待处理-allocate ITL entry

原创四九年入国军 2024-08-08

select   sql_id,count(1) 
  from dba_hist_active_sess_history
 where sample_time >= to_date('20240807 10:00:00', 'yyyymmdd hh24:mi:ss')
   and sample_time <= to_date('20240807 10:02:00', 'yyyymmdd hh24:mi:ss')
  group by sql_id
having count(1)  >1
order by 2;
 复制



select   sql_id,event,count(1) 
  from dba_hist_active_sess_history
 where sample_time >= to_date('20240807 10:26:00', 'yyyymmdd hh24:mi:ss')
   and sample_time <= to_date('20240807 10:27:00', 'yyyymmdd hh24:mi:ss')
  group by sql_id,event
having count(1)  >0
order by count(1);

复制

select   sql_id, CURRENT_OBJ#, CURRENT_BLOCK# ,CURRENT_FILE#,count(1)
  from dba_hist_active_sess_history
 where sample_time >= to_date('20240807 10:20:00', 'yyyymmdd hh24:mi:ss')
   and sample_time <= to_date('20240807 10:27:00', 'yyyymmdd hh24:mi:ss')
   and event='enq: TX - allocate ITL entry'
group by sql_id, CURRENT_OBJ#, CURRENT_BLOCK# ,CURRENT_FILE#
order by count(1);复制

set line 200
col segment_name for a15
col owner for a20
select tablespace_name,segment_type,owner,segment_name from dba_extents
where file_id=75  and  1008391 between block_id and block_id+blocks -1;复制


alter  index   scott.IND_LM_GLNO_LOG  rebuild  online  parallel 20 pctfree 30  initrans  50;


--分区表
select  PARTITION_NAME,pct_free,INI_TRANS FROM  dba_tab_partitions where table_name='PDBA';
--更改所有分区是初始事务槽为50
ALTER  TABLE  PDBA   pctfree 30  initrans 50;


select  INI_TRANS,MAX_TRANS,pct_free  from dba_indexes where index_name='IND_LM_GLNO_LOG';

 INI_TRANS  MAX_TRANS   PCT_FREE
---------- ---------- ----------
         2        255         10复制