一键获取数据库整体的运行情况--脚本
来源：互联网 发布：c语言基础的内容及要求 编辑：程序博客网 时间：2024/06/01 23:42
此脚本转自梁敬彬老师的《收获，不止SQL优化》，感谢老师的分享
构造环境脚本，模拟数据库的各种问题
注意：提前改脚本中表空间的路径
create user ljb identified by ljb;alter user ljb default tablespace users;grant dba to ljb;spool mkdb.log---表空间扩展过于频繁DROP tablespace tbs_a including contents AND datafiles;create tablespace TBS_A datafile '/u01/app/oracle/oradata/orcl/TBS_A.DBF' size 1M autoextend on uniform size 64k;CREATE TABLE ljb.t_a (id int,contents varchar2(1000)) tablespace TBS_A;insert into ljb.t_a select rownum,LPAD('1', 1000, '*') from dual connect by level<=20000;COMMIT;--全局临时表被收集统计信息drop table ljb.g_tab_del purge;drop table ljb.g_tab_pre purge;create global temporary table ljb.g_tab_del on commit DELETE rows as select * from dba_objects WHERE 1=2;create global temporary table ljb.g_tab_pre ON commit preserve rows as select * from dba_objects WHERE 1=2;COMMIT;exec dbms_stats.gather_table_stats(ownname => 'LJB', tabname => 'g_tab_del',cascade => true);exec dbms_stats.gather_table_stats(ownname => 'LJB', tabname => 'g_tab_pre',cascade => true);--表有过时字段DROP TABLE ljb.t_char_long purge;CREATE TABLE ljb.t_char_long (NAME CHAR(10), VALUE LONG);--表未建任何索引DROP TABLE ljb.t_noidx purge;CREATE TABLE ljb.t_noidx AS SELECT * FROM dba_objects;INSERT INTO ljb.t_noidx SELECT * FROM ljb.t_noidx;INSERT INTO ljb.t_noidx SELECT * FROM ljb.t_noidx;INSERT INTO ljb.t_noidx SELECT * FROM ljb.t_noidx;COMMIT;--表建在系统表空间DROP TABLE ljb.t_sys purge;CREATE TABLE ljb.t_sys tablespace system AS SELECT * FROM dba_objects WHERE rownum<=100;--表带并行属性DROP TABLE ljb.t_degree purge;CREATE TABLE ljb.t_degree parallel 8 AS SELECT * FROM dba_objects WHERE rownum<=100;--表被压缩DROP TABLE ljb.t_compress purge;CREATE TABLE ljb.t_compress compress AS SELECT * FROM dba_objects ;--表的日志被关闭DROP TABLE ljb.t_nolog purge;CREATE TABLE ljb.t_nolog nologging AS SELECT * FROM dba_objects WHERE rownum<=100;--表的列过多(比如超过100个列）DROP TABLE ljb.t_col_max purge;DECLARE l_sql VARCHAR2(32767);BEGIN l_sql := 'CREATE TABLE ljb.t_col_max ('; FOR i IN 1..100 LOOP l_sql := l_sql || 'n' || i || ' NUMBER,'; END LOOP; l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10'; EXECUTE IMMEDIATE l_sql;END;/--表的列过少(比如少于2个列）DROP TABLE ljb.t_col_min1 purge;DROP TABLE ljb.t_col_min2 purge;CREATE TABLE ljb.t_col_min1 (id INT);CREATE TABLE ljb.t_col_min2 (id INT,NAME varchar2(100));--表的高水平位没释放DROP table ljb.t_high purge;create table ljb.t_high as select * from dba_objects WHERE rownum<=10000;insert into ljb.t_high select * from ljb.t_high;insert into ljb.t_high select * from ljb.t_high;insert into ljb.t_high select * from ljb.t_high;insert into ljb.t_high select * from ljb.t_high;insert into ljb.t_high select * from ljb.t_high;commit;delete from ljb.t_high ;COMMIT;--表有触发器DROP table ljb.t1_tri purge;drop table ljb.t2_tri purge;CREATE table ljb.t1_tri AS SELECT object_name, object_id, object_type FROM dba_objects WHERE rownum<=30;CREATE table ljb.t2_triasSELECT object_type, count(*) as cntFROM ljb.t1_trigroup by object_type;CREATE or replace trigger ljb.tri_t2_triggerafter insert on ljb.t1_trifor each rowbegininsert into ljb.t2_tri(object_type,cnt) values (:new.object_type,1);end tri_t2_trigger;/--表分区数过多，比如超过100个DROP TABLE ljb.T_PART1 PURGE;CREATE TABLE ljb.t_part1 partition BY range(dates) (partition P_MAX VALUES less than(maxvalue))AS SELECT rownum id ,trunc(sysdate-rownum) AS DATES, ceil(dbms_random.value(0,100000)) nbr FROM dual CONNECT BY level<=10000;CREATE or replace procedure proc_1 ASv_next_day DATE;v_prev_day DATE;v_sql_p_split_part varchar2(4000);beginfor i in 1 .. 100 loop select add_months(trunc(sysdate), i) into v_next_day from dual; select add_months(trunc(sysdate), -i) into v_prev_day from dual; v_sql_p_split_part := 'alter table ljb.t_part1 split partition p_MAX at ' || '(to_date(''' || to_char(v_next_day, 'yyyymmdd') || ''',''yyyymmdd''))' || 'into (partition T_PART_' || to_char(v_prev_day, 'yyyymm') || ' tablespace users ,partition p_MAX)'; execute immediate v_sql_p_split_part;end loop;END;/exec proc_1;--分区不均匀drop table ljb.t_part2 purge;create table ljb.t_part2 (id INT,VALUE number) partition by range (id) ( partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (3000), partition p4 values less than (4000), partition p5 values less than (5000), partition p6 values less than (6000), partition p7 values less than (7000), partition p8 values less than (8000), partition p9 values less than (9000), partition p10 values less than (maxvalue) ) ;INSERT INTO ljb.t_part2 SELECT rownum ,ceil(dbms_random.value(0,10000)) FROM dual CONNECT BY rownum<=10000;INSERT INTO ljb.t_part2 SELECT rownum ,9999 FROM dual CONNECT BY rownum<=100000;COMMIT;exec dbms_stats.gather_table_stats('LJB', 't_part2');--分区索引失效---DROP INDEX ljb.idx_t_part1_id;---DROP INDEX ljb.idx_t_part1_nbr;CREATE INDEX ljb.idx_t_part1_id ON ljb.t_part1(id) ;CREATE INDEX ljb.idx_t_part1_nbr ON ljb.t_part1(nbr) local;--全局索引失效ALTER INDEX ljb.idx_t_part1_id unusable;--局部索引失效alter table ljb.t_part1 move partition T_PART_201410;--普通索引失效DROP TABLE ljb.t1 purge;CREATE TABLE ljb.t1 AS SELECT * FROM dba_objects WHERE rownum<=2000;CREATE INDEX ljb.idx_t1_obj_id ON ljb.t1(object_id);ALTER INDEX ljb.idx_t1_obj_id unusable;--单表索引过多(比如超过6个）DROP TABLE ljb.t2 purge;CREATE TABLE ljb.t2 AS SELECT * FROM dba_objects WHERE rownum<=2000;CREATE INDEX ljb.idx_t2_obj_id ON ljb.t2(object_id);CREATE INDEX ljb.idx_t2_obj_type ON ljb.t2(object_type);CREATE INDEX ljb.idx_t2_obj_name ON ljb.t2(object_name);CREATE INDEX ljb.idx_t2_data_obj_id ON ljb.t2(data_object_id);CREATE INDEX ljb.idx_t2_status ON ljb.t2(STATUS);CREATE INDEX ljb.idx_t2_created ON ljb.t2(created);CREATE INDEX ljb.idx_t2_last_ddl_time ON ljb.t2(last_ddl_time);--索引被压缩DROP TABLE ljb.t_idx_compress purge;CREATE TABLE ljb.t_idx_compress AS SELECT * FROM dba_objects ;CREATE INDEX ljb.idx_id_compress ON ljb.t_idx_compress(object_id) compress;--单表索引组合列过多DROP TABLE ljb.t3 purge;CREATE TABLE ljb.t3 AS SELECT * FROM dba_objects WHERE rownum<=2000;CREATE INDEX ljb.idx_t3_union ON ljb.t3(object_id,object_type,object_name,last_ddl_time);--聚合因子明显不好的drop table ljb.colocated purge;create table ljb.colocated ( x int, y varchar2(80) );begin for i in 1 .. 10000 loop insert into ljb.colocated(x,y) values (i, rpad(dbms_random.random,75,'*') ); end loop;end;/alter table ljb.colocatedadd constraint colocated_pkprimary key(x);begindbms_stats.gather_table_stats(ownname => 'LJB', tabname => 'COLOCATED',cascade => true);END;/drop table ljb.disorganized purge;create table ljb.disorganized AS select x,y from ljb.colocated order by y;alter table ljb.disorganized add constraint disorganized_pk primary key (x);begindbms_stats.gather_table_stats(ownname => 'LJB', tabname => 'DISORGANIZED',cascade => true);END;/--索引建在系统表空间DROP TABLE ljb.t_idx_sys purge;CREATE TABLE ljb.t_idx_sys tablespace system AS SELECT * FROM dba_objects WHERE rownum<=100;CREATE INDEX ljb.idx_t_sys ON ljb.t_idx_sys(object_id) tablespace system;--索引带并行属性DROP TABLE ljb.t_idx_degree purge;CREATE TABLE ljb.t_idx_degree AS SELECT * FROM dba_objects WHERE rownum<=100;CREATE index ljb.idx_t_degree ON ljb.t_idx_degree(object_id) parallel 8;--外键未建索引drop table ljb.t_p cascade constraints purge;drop table ljb.t_c cascade constraints purge;CREATE TABLE ljb.T_P (ID NUMBER, NAME VARCHAR2(30));ALTER TABLE ljb.T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);CREATE TABLE ljb.T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));ALTER TABLE ljb.T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES ljb.T_P (ID);INSERT INTO ljb.T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;INSERT INTO ljb.T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS WHERE rownum<=100;COMMIT;--create index ljb.idx_IND_T_C_FID on T_C(FID);--有位图索引DROP TABLE ljb.t_bit purge;CREATE TABLE ljb.t_bit AS SELECT * FROM dba_objects WHERE rownum<=100;CREATE INDEX ljb.idx_bit_status ON ljb.t_bit(STATUS);--有函数索引DROP TABLE ljb.t_fun purge;CREATE TABLE ljb.t_fun AS SELECT * FROM dba_objects WHERE rownum<=100;CREATE INDEX ljb.idx_func_objname ON ljb.t_fun(upper(object_name));--有反向键索引DROP TABLE ljb.t_rev purge;--DROP INDEX ljb.idx_rev_objid;CREATE TABLE ljb.t_rev AS SELECT * FROM dba_objects WHERE rownum<=100;CREATE INDEX ljb.idx_rev_objid ON ljb.t_fun(object_id) REVERSE;--组合和单列索引存在交叉DROP TABLE ljb.t_cross purge;CREATE TABLE ljb.t_cross AS SELECT * FROM dba_objects WHERE rownum<=100;CREATE INDEX ljb.idx_obj_id_name ON ljb.t_cross(object_id,object_name);CREATE INDEX ljb.idx_obj_id ON ljb.t_cross(object_id);--sql长度超过100行DROP TABLE ljb.t_sql purge;CREATE TABLE ljb.t_sql AS SELECT * FROM dba_objects;SELECT * FROM ljb.t_sql WHERE object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ANDobject_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ANDobject_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ANDobject_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ANDobject_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ANDobject_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ANDobject_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 AND object_id=1 ;--未使用绑定变量drop table ljb.t_unbind purge;create table ljb.t_unbind ( x int );create or replace procedure ljb.proc_unbindasbegin for i in 1 .. 20000 loop execute immediate 'insert into ljb.t_unbind values ( '||i||')'; end loop; COMMIT;end;/ exec ljb.proc_unbind;--未使用批量提交drop table ljb.t_unbatch purge;create table ljb.t_unbatch ( x int );create or replace procedure ljb.proc_unbatchasbegin for i in 1 .. 20000 loop insert into ljb.t_unbatch values (i); commit; end loop;end;/exec ljb.proc_unbatch;--失效的过程包等drop table ljb.t_proc purge;CREATE table ljb.t_proc (id number,col2 number);INSERT into ljb.t_proc select rownum, ceil(dbms_random.value(1,10)) from dual connect by level<=10;COMMIT;-----有效的Create or replace procedure ljb.p_insert1_t_proc (p in number ) isbegininsert into ljb.t_proc (id,col2) VALUES(rownum,p);commit;end ;/-----失效的Create or replace procedure ljb.p_insert2_t_proc (p in number ) isbegininsert into ljb.t_proc (id,col3) VALUES(rownum,p);commit;end ;/---构造CACHE小于20的序列DROP SEQUENCE ljb.seqtest1;CREATE SEQUENCE ljb.seqtest1INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE CACHE 20; spool offEXIT运行脚本构造环境
[oracle@orasql ~]$ sqlplus / as sysdbaSQL> set echo on SQL> @mkdb.sql
获取数据库整体运行情况的脚本
SET markup html ON spool ON pre off entmap offset term offset heading onset verify offset feedback offset linesize 2000set pagesize 30000set long 999999999set longchunksize 999999column index_name format a30column table_name format a30column num_rows format 999999999column index_type format a24column num_rows format 999999999column status format a8column clustering_factor format 999999999column degree format a10column blevel format 9column distinct_keys format 9999999999column leaf_blocks format 9999999column last_analyzed format a10column column_name format a25column column_position format 9column temporary format a2column partitioned format a5column partitioning_type format a7column partition_count format 999column program format a30column spid format a6column pid format 99999column sid format 99999column serial# format 99999column username format a12column osuser format a12column logon_time format datecolumn event format a32column JOB_NAME format a30column PROGRAM_NAME format a32column STATE format a10column window_name format a30column repeat_interval format a60column machine format a30column program format a30column osuser format a15column username format a15column event format a50column seconds format a10column sqltext format a100SET markup html offcolumn dbid new_value spool_dbidcolumn inst_num new_value spool_inst_numselect dbid from v$database where rownum = 1;select instance_number as inst_num from v$instance where rownum = 1;column spoolfile_name new_value spoolfileselect 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_number from v$instance where rownum=1)||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi') as spoolfile_name from dual;spool &&spoolfile..htmlSET markup html offset serveroutput on;exec dbms_output.enable(9999999999);exec dbms_output.put_line('<html>');exec dbms_output.put_line('<style>');exec dbms_output.put_line('th {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}');exec dbms_output.put_line('</style>');exec dbms_output.put_line('<head>');exec dbms_output.put_line('</head>');exec dbms_output.put_line('<body>');SET markup html onprompt <p>版本select * from v$version;select * from dba_registry_history;prompt <p>最近一次启动时间，版本，以及是否RACselect * from (select name db_name from v$database), (select instance_name from v$instance), (select archiver from v$instance), (select snap_interval awr_interval, retention awr_retention FROM DBA_HIST_WR_CONTROL), (select flashback_on from v$database), (select parallel from v$instance), (select startup_time from v$instance), (select decode(name,null,'NOT ASM','ASM') IS_ASM from (select null name from dual union all (select name IS_ASM from v$datafile where name like '+%' and rownum = 1))), (select max(end_time) rman_lastcompleted from v$rman_status where status = 'COMPLETED' and object_type like 'DB FULL');prompt <p>30分钟内CPU或等待最长的select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser from (select c.USERNAME, a.event, to_char(a.cnt) as seconds, a.sql_id --,dbms_lob.substr(b.sql_fulltext,50,1) sqltext from (select rownum rn, t.* from (select decode(s.session_state, 'WAITING', s.event, 'Cpu + Wait For Cpu') Event, s.sql_id, s.user_id, count(*) CNT from v$active_session_history s where sample_time > sysdate - 30 / 1440 group by s.user_id, decode(s.session_state, 'WAITING', s.event, 'Cpu + Wait For Cpu'), s.sql_id order by CNT desc) t where rownum < 20) a, v$sqlarea b, dba_users c where a.sql_id = b.sql_id and a.user_id = c.user_id order by CNT desc) t, v$session swhere t.sql_id = s.sql_id(+);prompt <p>近期负载情况(根据AWR快照)select s.snap_date, decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME", to_char(round(s.seconds/60,2)) "elapse(min)", round(t.db_time / 1000000 / 60, 2) "DB time(min)", s.redosize redo, round(s.redosize / s.seconds, 2) "redo/s", s.logicalreads logical, round(s.logicalreads / s.seconds, 2) "logical/s", physicalreads physical, round(s.physicalreads / s.seconds, 2) "phy/s", s.executes execs, round(s.executes / s.seconds, 2) "execs/s", s.parse, round(s.parse / s.seconds, 2) "parse/s", s.hardparse, round(s.hardparse / s.seconds, 2) "hardparse/s", s.transactions trans, round(s.transactions / s.seconds, 2) "trans/s" from (select curr_redo - last_redo redosize, curr_logicalreads - last_logicalreads logicalreads, curr_physicalreads - last_physicalreads physicalreads, curr_executes - last_executes executes, curr_parse - last_parse parse, curr_hardparse - last_hardparse hardparse, curr_transactions - last_transactions transactions, round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds, to_char(currtime, 'yy/mm/dd') snap_date, to_char(currtime, 'hh24:mi') currtime, currsnap_id endsnap_id, to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time from (select a.redo last_redo, a.logicalreads last_logicalreads, a.physicalreads last_physicalreads, a.executes last_executes, a.parse last_parse, a.hardparse last_hardparse, a.transactions last_transactions, lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo, lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads, lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads, lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes, lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse, lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse, lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions, b.end_interval_time lasttime, lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id, b.startup_time from (select snap_id, dbid, instance_number, sum(decode(stat_name, 'redo size', value, 0)) redo, sum(decode(stat_name, 'session logical reads', value, 0)) logicalreads, sum(decode(stat_name, 'physical reads', value, 0)) physicalreads, sum(decode(stat_name, 'execute count', value, 0)) executes, sum(decode(stat_name, 'parse count (total)', value, 0)) parse, sum(decode(stat_name, 'parse count (hard)', value, 0)) hardparse, sum(decode(stat_name, 'user rollbacks', value, 'user commits', value, 0)) transactions from dba_hist_sysstat where stat_name in ('redo size', 'session logical reads', 'physical reads', 'execute count', 'user rollbacks', 'user commits', 'parse count (hard)', 'parse count (total)') group by snap_id, dbid, instance_number) a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.dbid = &&spool_dbid and a.instance_number = &&spool_inst_num order by end_interval_time)) s, (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time' and a.dbid = &&spool_dbid and a.instance_number = &&spool_inst_num) t where s.endsnap_id = t.endsnap_idorder by s.snap_date desc ,time asc;prompt <p>逻辑读最多select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS from v$sql s where s.buffer_gets > 300 order by buffer_gets desc)where rownum <= 10;prompt <p>物理读最多select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS from v$sql swhere s.disk_reads > 300order by disk_reads desc)where rownum<=10;prompt <p>执行次数最多select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS from v$sql s order by s.EXECUTIONS desc)where rownum <= 10;prompt <p>解析次数最多select * from (select sql_id, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS from v$sql s order by s.PARSE_CALLS desc)where rownum <= 10;prompt <p>磁盘排序最多select sess.username, sql.address, sort1.blocks from v$session sess, v$sqlarea sql, v$sort_usage sort1where sess.serial# = sort1.session_num and sort1.sqladdr = sql.address and sort1.sqlhash = sql.hash_value and sort1.blocks > 200order by sort1.blocks desc;prompt <p>提交次数超过10000的sessionselect t1.sid, t1.value, t2.name from v$sesstat t1, v$statname t2 where t2.name like '%user commits%' and t1.STATISTIC# = t2.STATISTIC# and value >= 10000 order by value desc;prompt <p>长度超过100的SQLSELECT SQL_ID, COUNT(*) line_count FROM V$SQLTEXT GROUP BY SQL_IDHAVING COUNT(*) >= 100 ORDER BY COUNT(*) DESC;prompt <p>查询共享内存占有率select count(*),round(sum(sharable_mem)/1024/1024,2) from v$db_object_cache a;prompt <p>表有带并行度select t.owner, t.table_name, degree from dba_tables twhere trim(t.degree) <>'1' and trim(t.degree)<>'0' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%';prompt <p>索引有带并行度select t.owner, t.table_name, index_name, degree, status from dba_indexes twhere trim(t.degree) <>'1' and trim(t.degree)<>'0' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%';prompt <p>失效索引select t.index_name, t.table_name, blevel, t.num_rows, t.leaf_blocks, t.distinct_keys from dba_indexes t where status = 'UNUSABLE' and table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and table_owner not like 'FLOWS%';select t2.owner, t1.blevel, t1.leaf_blocks, t1.INDEX_NAME, t2.table_name, t1.PARTITION_NAME, t1.STATUS from dba_ind_partitions t1, dba_indexes t2where t1.index_name = t2.index_name and t1.STATUS = 'UNUSABLE';prompt <p>失效对象select t.owner, t.object_type, t.object_name from dba_objects t where STATUS='INVALID'order by 1, 2;prompt <p>位图索引和函数索引、反向键索引select t.owner, t.table_name, t.index_name, t.index_type, t.status, t.blevel, t.leaf_blocks from dba_indexes t where index_type in ('BITMAP', 'FUNCTION-BASED NORMAL', 'NORMAL/REV') and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%';prompt <p>组合索引组合列超过4个的select table_owner,table_name, index_name, count(*) from dba_ind_columnswhere table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and table_owner not like 'FLOWS%' and table_owner not like 'WK%' group by table_owner,table_name, index_namehaving count(*) >= 4 order by count(*) desc;prompt <p>索引个数字超过5个的select owner,table_name, count(*) cnt from dba_indexeswhere owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%' group by owner,table_namehaving count(*) >= 5order by cnt desc;prompt <p>哪些大表从未建过索引。select segment_name, bytes / 1024 / 1024 / 1024 "GB", blocks, tablespace_name from dba_segments where segment_type = 'TABLE' and segment_name not in (select table_name from dba_indexes) and bytes / 1024 / 1024 / 1024 >= 0.5 order by GB desc;select segment_name, sum(bytes) / 1024 / 1024 / 1024 "GB", sum(blocks) from dba_segments where segment_type = 'TABLE PARTITION' and segment_name not in (select table_name from dba_indexes) group by segment_namehaving sum(bytes) / 1024 / 1024 / 1024 >= 0.5 order by GB desc;prompt <p>哪些表的组合索引与单列索引存在交叉的情况。select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate from dba_ind_columns where table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and table_owner not like 'FLOWS%' and table_owner not like 'WK%' group by table_namehaving count(distinct(column_name)) / count(*) < 1 order by cross_idx_rate desc;prompt <p>哪些对象建在系统表空间上。select * from (select owner, segment_name, tablespace_name, count(*) num from dba_segments where tablespace_name in('SYSTEM','SYSAUX')group by owner, segment_name, tablespace_name)where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS') and owner not like 'FLOWS%' and owner not like 'WK%';prompt <p>检查统计信息是否被收集select t.job_name,t.program_name,t.state,t.enabled from dba_scheduler_jobs twhere job_name = 'GATHER_STATS_JOB';select client_name,status from dba_autotask_client;select window_next_time,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;prompt <p>检查哪些未被收集或者很久没收集select owner, count(*) from dba_tab_statistics twhere (t.last_analyzed is null or t.last_analyzed < sysdate - 100) and table_name not like 'BIN$%'group by ownerorder by owner;prompt <p>被收集统计信息的临时表select owner, table_name, t.last_analyzed, t.num_rows, t.blocks from dba_tables twhere t.temporary = 'Y' and last_analyzed is not null;prompt <p>日志切换频率分析select * from (select thread#, sequence#, to_char(first_time, 'MM/DD/RR HH24:MI:SS') from v$log_history order by first_time desc) where rownum <= 50;prompt <p>最近10天中每天日志切换的量SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, COUNT(*) TOTALFROM v$log_history a where first_time>=to_char(sysdate-11)GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;prompt <p>日志组大小select group#,bytes,status from v$log;prompt <p>查看recovery_file_dest使用率 select substr(name, 1, 30) name, space_limit as quota, space_used as used, space_reclaimable as reclaimable, number_of_files as files from v$recovery_file_dest;select * from V$FLASH_RECOVERY_AREA_USAGE;prompt <p>检查序列小于20的情况select sequence_owner, count(*) CNT, sum(case when t.cache_size <= 20 then 1 else 0 end ) CNT_LESS_20, sum(case when t.cache_size > 20 then 1 else 0 end ) CNT_MORE_20 from dba_sequences t group by sequence_owner;prompt <p>表空间使用情况set markup html offprompt <p>declare type NUMBER_ARRAY is table of number(15) index by varchar2(30); ts_free_mb NUMBER_ARRAY; cursor c1 is select tablespace_name, sum(free_mb) + sum(expired_mb) free_mb from (SELECT tablespace_name, round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) free_mb, 0 expired_mb FROM dba_free_space GROUP BY tablespace_name union all select tablespace_name, 0 free_mb, round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) expired_mb from dba_undo_extents d where tablespace_name = (select value from v$parameter where name = 'undo_tablespace') and status = 'EXPIRED' group by tablespace_name) group by tablespace_name; cursor c2 is SELECT /*+ rule */ tablespace_name, round(sum(bytes) / 1024 / 1024, 2) total_mb FROM dba_data_files where tablespace_name not in (select tablespace_name from dba_data_files where upper(AUTOEXTENSIBLE) = 'YES') GROUP BY tablespace_name; ts_name varchar2(30); ts_total number(15); ts_used number(15); ts_free number(15); ts_rate varchar2(5);begin for rec1 in c1 loop ts_free_mb(rec1.tablespace_name) := rec1.free_mb; end loop; dbms_output.put_line('<table border="1" width="90%" align="center" summary="Script output">'); dbms_output.put_line('<tr><th>ts_name</th><th>ts_total</th><th>ts_used</th><th>ts_free</th><th>ts_rate</th></tr>'); for rec2 in c2 loop ts_name := null; ts_total := null; ts_used := null; ts_free := null; ts_rate := null; ts_name := rec2.tablespace_name; ts_total := rec2.total_mb; ts_free := nvl(ts_free_mb(ts_name), 0); ts_used := nvl(ts_total - ts_free, 0); ts_rate := to_char(round((ts_total - ts_free) / ts_total * 100, 2), 'fm990.99'); dbms_output.put_line('<tr><td>' || ts_name || '</td><td>' || ts_total || '</td><td>' || ts_used || '</td><td>' || ts_free || '</td><td>' || ts_rate || '</td></tr>'); end loop; dbms_output.put_line('</table>');end;/prompt <p>set markup html onprompt <p>整个数据库有多大select owner, round(sum(bytes) / 1024 / 1024 / 1024, 2) "GB" from dba_segments group by owner order by 2 desc;prompt <p>对象大小TOP10select * from (select owner, segment_name, segment_type, round(sum(bytes) / 1024 / 1024) object_size from DBA_segments group by owner, segment_name, segment_type order by object_size desc)where rownum <= 10;prompt <p>回收站情况(大小及数量）select * from (select SUM(BYTES) / 1024 / 1024 / 1024 as recyb_size from DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'BIN$%') a, (select count(*) as recyb_cnt from dba_recyclebin);prompt <p>查谁占用了undo表空间SELECT r.name "roll_segment_name", rssize/1024/1024/1024 "RSSize(G)", s.sid, s.serial#, s.username, s.status, s.sql_hash_value, s.SQL_ADDRESS, s.MACHINE, s.MODULE, substr(s.program, 1, 78) program, r.usn, hwmsize/1024/1024/1024, shrinks ,xactsFROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r, v$rollstat rsWHERE t.addr = s.taddr and t.xidusn = r.usn and r.usn=rs.USNOrder by rssize desc;prompt <p>查谁占用了temp表空间select sql.sql_id, t.Blocks * 16 / 1024 / 1024, s.USERNAME, s.SCHEMANAME, t.tablespace, t.segtype, t.extents, s.PROGRAM, s.OSUSER, s.TERMINAL, s.sid, s.SERIAL# from v$sort_usage t, v$session s , v$sql sqlwhere t.SESSION_ADDR = s.SADDR and t.SQLADDR=sql.ADDRESS and t.SQLHASH=sql.HASH_VALUE;prompt <p>观察回滚段，临时段及普通段否是自动扩展select t2.contents, t1.* from (select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_temp_files union all select file_name, tablespace_name, bytes, maxbytes, autoextensible from dba_data_files) t1, dba_tablespaces t2 where t1.tablespace_name = t2.tablespace_name;prompt <p>表大小超过10GB未建分区的select owner, segment_name, segment_type, round(sum(bytes) / 1024 / 1024 / 1024,2) object_size from dba_segmentswhere segment_type = 'TABLE' and bytes > 10*1024*1024*1024group by owner, segment_name, segment_typeorder by object_size desc;prompt <p>分区最多的前10个对象select * from (select table_owner, table_name, count(*) cnt from dba_tab_partitions group by table_owner, table_name order by cnt desc)where rownum <= 10;prompt <p>分区不均匀的表select * from (select table_owner, table_name, max(num_rows) max_num_rows, trunc(avg(num_rows), 0) avg_num_rows, sum(num_rows) sum_num_rows, case when sum(num_rows) = 0 then 0 else trunc(max(num_rows) / trunc(avg(num_rows), 0), 2) end rate, count(*) part_count from dba_tab_partitions group by table_owner, table_name) where rate > 5;prompt <p>列数量超过100个或小于2的表select * from (select owner, table_name, count(*) col_count from dba_tab_cols group by owner, table_name) where col_count > 100 or col_count <= 2 and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%';prompt <p>表属性是nologging的select owner, table_name, tablespace_name, logging from dba_tables where logging = 'NO' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%' ;prompt <p>表属性含COMPRESSION的select owner, table_name, tablespace_name, COMPRESSION from dba_tables where COMPRESSION = 'ENABLED' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%';prompt <p>索引属性含COMPRESSION的select owner, index_name, table_name, COMPRESSION from dba_indexes where COMPRESSION = 'ENABLED' and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%';prompt <p>触发器select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS from dba_triggers where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB') and owner not like 'FLOWS%'; prompt <p>将外键未建索引的情况列出select * from (select pk.owner PK_OWNER, pk.constraint_name PK_NAME, pk.table_name PK_TABLE_NAME, fk.owner FK_OWNER, fk.constraint_name FK_NAME, fk.table_name FK_TABLE_NAME, fk.delete_rule FK_DELETE_RULE, ind_col.INDEX_NAME FK_INDEX_NAME, ind.index_type FK_INDEX_TYPE, con_col.COLUMN_NAME FK_INDEX_COLUMN_NAME, con_col.POSITION FK_INDEX_COLUMN_POSITION, decode(ind_col.INDEX_NAME, NULL, NULL, NVL(x_ind.status, 'VALID')) IS_IND_VALID, decode(ind_col.INDEX_NAME, NULL, NULL, NVL(x_ind_part.status, 'VALID')) IS_IND_PART_VALID from (select * from dba_constraints where constraint_type = 'R') fk, (select * from dba_constraints where constraint_type = 'P') pk, dba_cons_columns con_col, dba_ind_columns ind_col, dba_indexes ind, (select index_owner, index_name, status from dba_ind_partitions where status <> 'VALID') x_ind_part, (select owner as index_owner, index_name, status from dba_indexes where status <> 'VALID') x_ind where fk.r_constraint_name = pk.constraint_name and pk.owner = fk.owner and fk.owner = con_col.owner and fk.table_name = con_col.table_name and fk.constraint_name = con_col.CONSTRAINT_NAME and con_col.owner = ind_col.TABLE_OWNER(+) and con_col.TABLE_NAME = ind_col.TABLE_NAME(+) and con_col.COLUMN_NAME = ind_col.COLUMN_NAME(+) and ind_col.INDEX_OWNER = ind.owner(+) and ind_col.INDEX_NAME = ind.index_name(+) and ind_col.INDEX_OWNER = x_ind.index_owner(+) and ind_col.INDEX_NAME = x_ind.index_name(+) and ind_col.INDEX_OWNER = x_ind_part.index_owner(+) and ind_col.INDEX_NAME = x_ind_part.index_name(+)) where FK_INDEX_NAME is null order by FK_OWNER ASC;/* 性能不好prompt <p>热点块(汇总）SELECT *+ rule * e.owner, e.segment_name, e.segment_type, sum(b.tch) tch FROM dba_extents e, (SELECT * FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM <= 10) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk group by e.owner, e.segment_name, e.segment_typeorder by tch desc;prompt <p>热点块(展开，未汇总）SELECT *+ rule * distinct e.owner, e.segment_name, e.segment_type, dbablk,b.tch FROM dba_extents e, (SELECT * FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM <= 10) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablkorder by tch desc;*/prompt <p>附录：查看session_cached_cursors的参数设置情况，如果使用率为100%则增大这个参数值SELECT 'session_cached_cursors' PARAMETER, LPAD(VALUE, 5) VALUE, DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE FROM (SELECT MAX(S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME = 'session cursor cache count' AND S.STATISTIC# = N.STATISTIC#), (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors') UNION ALL SELECT 'open_cursors', LPAD(VALUE, 5), TO_CHAR(100 * USED / VALUE, '990') || '%' FROM (SELECT MAX(SUM(S.VALUE)) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN ('opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID), (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors'); prompt <p>附录：供参考的Oracle所有参数show parameterset markup html offexec dbms_output.put_line('</body>');exec dbms_output.put_line('</html>');spool off/* 获取awr、addm、ash */--以下不使用html标签SET markup html off spool ON pre off entmap offset trim onset trimspool onset heading off--查询dbid、instance_numbercolumn dbid new_value awr_dbidcolumn instance_number new_value awr_inst_numselect dbid from v$database;select instance_number from v$instance;--半小时内的ash报告column ashbegintime new_value ashbegin_strcolumn ashendtime new_value ashend_strselect to_char(sysdate-3/144,'yyyymmddhh24miss') as ashbegintime, to_char(sysdate,'yyyymmddhh24miss') as ashendtime from dual;column ashfile_name new_value ashfileselect 'ashrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&ashbegin_str) || '_' || to_char(&&ashend_str) ashfile_name from dual;spool &&ashfile..htmlselect * from table(dbms_workload_repository.ash_report_html(to_char(&&awr_dbid),to_char(&&awr_inst_num),to_date(to_char(&&ashbegin_str),'yyyymmddhh24miss'),to_date(to_char(&&ashend_str),'yyyymmddhh24miss')));spool off;--按需创建awr断点column begin_snap new_value awr_begin_snapcolumn end_snap new_value awr_end_snapselect max(snap_id) begin_snap from dba_hist_snapshot where snap_id < (select max(snap_id) from dba_hist_snapshot);select max(snap_id) end_snap from dba_hist_snapshot;declare snap_maxtime date; snap_mintime date;begin select max(end_interval_time) + 0 into snap_maxtime from dba_hist_snapshot where snap_id = to_number(&&awr_end_snap); select max(end_interval_time) + 0 into snap_mintime from dba_hist_snapshot where snap_id = to_number(&&awr_begin_snap); if sysdate - snap_maxtime > 10/1445 then dbms_workload_repository.create_snapshot(); end if;end;/--最新两次snap_id间的awr报告column begin_snap new_value awr_begin_snapcolumn end_snap new_value awr_end_snapselect max(snap_id) begin_snap from dba_hist_snapshot where snap_id < (select max(snap_id) from dba_hist_snapshot);select max(snap_id) end_snap from dba_hist_snapshot;column awrfile_name new_value awrfileselect 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) awrfile_name from dual;spool &&awrfile..htmlselect output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap));spool off;--可获取的最长awr报告(一周以来的所有分析)column begin_snap new_value awr_begin_snapcolumn end_snap new_value awr_end_snapselect a.begin_snap, a.end_snap from (select startup_time, min(snap_id) begin_snap, max(snap_id) end_snap from dba_hist_snapshot group by startup_time) a, (select max(startup_time) startup_time from dba_hist_snapshot) b where a.startup_time = b.startup_time and rownum = 1;column awrfile_name new_value awrfileselect 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) ||'_all' awrfile_name from dual;spool &&awrfile..htmlselect output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap));spool off;--最新addm报告column addmfile_name new_value addmfileselect 'addmrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) addmfile_name from dual;set serveroutput onspool &&addmfile..txtdeclare id number; name varchar2(200) := ''; descr varchar2(500) := ''; addmrpt clob; v_ErrorCode number;BEGIN name := '&&addmfile'; begin dbms_advisor.create_task('ADDM', id, name, descr, null); dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', &&awr_begin_snap); dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', &&awr_end_snap); dbms_advisor.set_task_parameter(name, 'INSTANCE', &&awr_inst_num); dbms_advisor.set_task_parameter(name, 'DB_ID', &&awr_dbid); dbms_advisor.execute_task(name); exception when others then null; end; select dbms_advisor.get_task_report(name, 'TEXT', 'TYPICAL') into addmrpt from sys.dual; dbms_output.enable(20000000000); for i in 1 .. (DBMS_LOB.GETLENGTH(addmrpt) / 2000 + 1) loop dbms_output.put_line(substr(addmrpt, 1900 * (i - 1) + 1, 1900)); end loop; dbms_output.put_line(''); begin dbms_advisor.delete_task(name); exception when others then null; end;end;/spool off;exit;运行脚本以及脚本输出的文件
[oracle@orasql ~]$ sqlplus / as sysdbaSQL> @spooldb.sql[oracle@orasql ~]$ lltotal 1844-rw-r--r-- 1 oracle oinstall 7881 Jul 16 12:09 addmrpt_1_18_177.txt-rw-r--r-- 1 oracle oinstall 52985 Jul 16 12:09 ashrpt_1_20170716113859_20170716120859.html-rw-r--r-- 1 oracle oinstall 558941 Jul 16 12:09 awrrpt_1_176_177.html-rw-r--r-- 1 oracle oinstall 681960 Jul 16 12:09 awrrpt_1_18_177_all.html-rw-r--r-- 1 oracle oinstall 33147 Jul 16 12:06 mkdb.log-rw-r--r-- 1 oracle oinstall 25679 Jul 16 12:02 mkdb.sql-rw-r--r-- 1 oracle oinstall 8869 May 20 16:53 mksql.sql-rw-r--r-- 1 oracle oinstall 37096 May 20 16:53 spooldb.sql-rw-r--r-- 1 oracle oinstall 420751 Jul 16 12:08 spool_ORCL_1_17-07-16_12.08.html-rw-r--r-- 1 oracle oinstall 39773 May 20 16:53 spoolsql.sql
阅读全文
0 0
- 一键获取数据库整体的运行情况--脚本
- 监控数据库运行情况（一）
- Shell脚本实例（一） 监控一个程序的运行情况
- 监控数据库运行情况的一种解决方案
- 一键运行R脚本
- 关于hive运行脚本CPU占用率100%的情况
- 查看指定spid的脚本当前运行情况和状态
- Android 获取手机整体流量使用情况以及某个应用的流量的统计
- python 获取运行脚本的绝对路径
- [AHK]获取当前运行脚本的pid
- 获取当前运行脚本的父路径
- 获取Android系统memory的使用情况的脚本
- 监控数据库运行的一些常用脚本
- 转：获取脚本运行路径的VBS脚本
- 数据库一键退出脚本
- 数据库一键退出脚本
- mysql 数据库整体运行状态确认思路
- 一键获取SQL的各种详细信息--脚本
- (char *)&p[x]=x
- ActiveMQ分布式网络(Forward Bridge)
- npm命令行查找全局安装过的包
- JVM 垃圾回收（GC）和HotSpot算法实现
- java接口小练习
- 一键获取数据库整体的运行情况--脚本
- POJ3469: Dual Core CPU 题解
- hdu 2512 第二类斯特林数
- PHP 回调函数理解
- 两个可以快速以当前路径为根目录建立服务的工具
- HDU
- Python爬虫从入门到懵逼-0
- docker使用
- Flask-Bootstrap安装失败问题解决
