ITPub博客

背景：今天开发反馈到我这，业务日志打出，有条sql在早上7点的时候，有两次执行特别慢，希望我这协助排查下。查看awr报告等待事件为db file sequential readlog file syncread by other session 继续查看awr报告，发现热点都在索引上。查看下索引状态都正常select * from dba_indexes where table_name

1.创建回收站清理的存储过程create or replace procedure purge_recyclebin asbeginexecute immediate 'purge dba_recyclebin';end;/2.创建定时任务variable job number;begin  dbms_job.submit(:job,'purge_recycleb

1.创建分区表，使用用户默认表空间create table par_tab_test (id varchar2(20) not null, time date default sysdate)      partition by range(t

1.查询历史连接信息select *from dba_hist_active_sess_history awhere a.SAMPLE_TIME BETWEENTO_DATE('2020-10-04 12:00:00', 'YYYY-MM-DD HH24:MI:SS') ANDTO_DATE('2020-10-04 13:00:00', 'Y

1.查看分区表分区字段select * from dba_part_key_columns where name='LOAN_UN_APP_INFO_CLOB' and owner ='OLS';  OWNER             NAME      

=========================================1.查看表空间使用情况=========================================with ts as (SELECT d.tablespace_name "Name", d.status "Status",      &nb

oracle查看执行时间长sql1.查看发生时间的snap_idselect snap_id,dbid,instance_number,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot where begin_interval_time between to_date('20200525 08

查看表是否是分区表select t.owner,p.table_name,count(p.partition_name) from dba_tab_partitions p,dba_tables t where p.table_name = t.table_name and t.table_name like '%xx%'  group by t.owner,

1.1 记录存储的WWN号及磁盘信息1.1.1 记录已有磁盘信息root@pols1:/sys/class/scsi_host# fdisk -l|grep /dev/WARNING: fdisk GPT support is currently new, and therefore in an experimental phase.Disk /dev/sda: 1798.7 GB, 179865

vi /root/db_clearlog.shchmod 700 /root/db_clearlog.sh55 6 * * * /bin/sh db_clearlog.shcat db_clearlog.sh###############################################################################################!

问题：当前部分生产库查询表空间查询从10秒到1分钟，非常缓慢看了一下，这几个生产数据库数据量都比较大，会周期性进行drop表的操作，怀疑是回收站对象过大导致导致的。1.查看回收站select count(*) from dba_recyclebin;2.清空回收站对象purge dba_recyclebin;3.查询表空间使用情况，恢复正常如果还有问题，可以重新收集统计信息exec dbms_st

分区的异步全局索引维护时间修改