TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
-- 数据库总空间
- set linesize 160 pagesize 999;
- select sum(bytes)/(1024*1024*1024) from dba_data_files;
- select sum(bytes)/(1024*1024*1024) from dba_segments;
- select sum(bytes)/(1024*1024*1024) from dba_free_space;
复制代码
-- FSFI值
- select a.tablespace_name,
- sqrt(max(a.blocks) / sum(a.blocks)) * (100 / sqrt(sqrt(count(a.blocks)))) FSFI
- from dba_free_space a, dba_tablespaces b
- where a.tablespace_name = b.tablespace_name
- and b.contents not in ('TEMPORARY','UNDO')
- group by a.tablespace_name
- order by FSFI;
复制代码
-- 按表空间显示连续的空闲空间
- ========
- Script : tfstsfgm
- ========
- SET ECHO off
- REM NAME:TFSTSFRM.SQL
- REM USAGE:"@path/tfstsfgm"
- REM ------------------------------------------------------------------------
- REM REQUIREMENTS:
- REM SELECT ON DBA_FREE_SPACE
- REM ------------------------------------------------------------------------
- REM PURPOSE:
- REM The following is a script that will determine how many extents
- REM of contiguous free space you have in Oracle as well as the
- REM total amount of free space you have in each tablespace. From
- REM these results you can detect how fragmented your tablespace is.
- REM
- REM The ideal situation is to have one large free extent in your
- REM tablespace. The more extents of free space there are in the
- REM tablespace, the more likely you will run into fragmentation
- REM problems. The size of the free extents is also very important.
- REM If you have a lot of small extents (too small for any next
- REM extent size) but the total bytes of free space is large, then
- REM you may want to consider defragmentation options.
- REM ------------------------------------------------------------------------
- REM DISCLAIMER:
- REM This script is provided for educational purposes only. It is NOT
- REM supported by Oracle World Wide Technical Support.
- REM The script has been tested and appears to work as intended.
- REM You should always run new scripts on a test instance initially.
- REM ------------------------------------------------------------------------
- REM Main text of script follows:
-
- create table SPACE_TEMP (
- TABLESPACE_NAME CHAR(30),
- CONTIGUOUS_BYTES NUMBER)
- /
-
- declare
- cursor query is select *
- from dba_free_space
- order by tablespace_name, block_id;
- this_row query%rowtype;
- previous_row query%rowtype;
- total number;
-
- begin
- open query;
- fetch query into this_row;
- previous_row := this_row;
- total := previous_row.bytes;
- loop
- fetch query into this_row;
- exit when query%notfound;
- if this_row.block_id = previous_row.block_id + previous_row.blocks then
- total := total + this_row.bytes;
- insert into SPACE_TEMP (tablespace_name)
- values (previous_row.tablespace_name);
- else
- insert into SPACE_TEMP values (previous_row.tablespace_name,
- total);
- total := this_row.bytes;
- end if;
- previous_row := this_row;
- end loop;
- insert into SPACE_TEMP values (previous_row.tablespace_name,
- total);
- end;
- /
-
- set pagesize 60
- set newpage 0
- set echo off
- ttitle center 'Contiguous Extents Report' skip 3
- break on "TABLESPACE NAME" skip page duplicate
- spool contig_free_space.lis
- rem
- column "CONTIGUOUS BYTES" format 999,999,999,999
- column "COUNT" format 999999
- column "TOTAL BYTES" format 999,999,999,999
- column "TODAY" noprint new_value new_today format a1
- rem
- select TABLESPACE_NAME "TABLESPACE NAME",
- CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
- from SPACE_TEMP
- where CONTIGUOUS_BYTES is not null
- order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;
-
- select tablespace_name, count(*) "# OF EXTENTS",
- sum(contiguous_bytes) "TOTAL BYTES"
- from space_temp
- group by tablespace_name;
-
- spool off
-
- drop table SPACE_TEMP
- /
复制代码
-- 普通表碎片评估方法
- col frag format 99.99
- col owner format a20;
- col table_name format a30;
- select * from (
- select a.owner,
- a.table_name,
- a.num_rows,
- a.avg_row_len * a.num_rows space,
- b.bytes,
- (a.avg_row_len * a.num_rows / b.bytes) frag,
- to_char(a.last_analyzed,'YYYY-MM-DD')
- from dba_tables a, dba_segments b
- where a.table_name = b.segment_name
- and a.owner= b.owner
- and a.owner not in
- ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
- 'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN')
- and (a.avg_row_len * a.num_rows / b.bytes) < 0.7
- -- and PARTITIONED='NO'
- order by b.bytes desc)
- where rownum <= 50;
复制代码
-- 单表碎片情况
- select a.owner,
- a.table_name,
- a.num_rows,
- a.avg_row_len * a.num_rows space,
- b.bytes,
- (a.avg_row_len * a.num_rows / b.bytes) frag,
- to_char(a.last_analyzed,'YYYY-MM-DD')
- from dba_tables a, dba_segments b
- where a.table_name = b.segment_name
- and a.owner= b.owner
- and a.owner not in
- ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
- 'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN')
- and PARTITIONED='NO'
- and a.table_name='TF_B_TRADE_BOOKING'
- and a.owner='UCR_CRM1'
- order by b.bytes desc
- /
复制代码
-- 分区表碎片评估方法
- col frag format 99.99
- col table_owner format a20;
- col table_name format a30;
- col partition_name format a15;
- select * from (
- select a.table_owner,
- a.table_name,
- a.partition_name,
- a.num_rows,
- a.avg_row_len * a.num_rows space,
- b.bytes,
- (a.avg_row_len * a.num_rows / b.bytes) frag,
- to_char(a.last_analyzed,'YYYY-MM-DD')
- from dba_tab_partitions a, dba_segments b
- where a.table_name = b.segment_name
- and a.table_owner= b.owner
- and a.partition_name = b.PARTITION_NAME
- and a.table_owner not in
- ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
- 'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN','SH')
- and (a.avg_row_len * a.num_rows / b.bytes) < 0.7
- and b.segment_type='TABLE PARTITION'
- order by b.bytes desc)
- where rownum <= 50;
复制代码
-- 单个分区表碎片情况
- select a.table_owner,
- a.table_name,
- a.partition_name,
- a.num_rows,
- a.avg_row_len * a.num_rows space,
- b.bytes,
- (a.avg_row_len * a.num_rows / b.bytes) frag,
- to_char(a.last_analyzed,'YYYY-MM-DD')
- from dba_tab_partitions a, dba_segments b
- where a.table_name = b.segment_name
- and a.table_owner= b.owner
- and a.partition_name = b.PARTITION_NAME
- and b.segment_type='TABLE PARTITION'
- and a.table_name='SERV'
- and a.table_owner='USER_PRODUCT'
- order by 1,2,3;
复制代码
-- 索引碎片评估方法
- col tablespace_name format a20;
- col owner format a10;
- col index_name format a30;
- select *
- from (select id.tablespace_name,
- id.owner,
- id.index_name,
- id.blevel,
- sum(sg.bytes) / 1024 / 1024 "size(M)",
- sg.blocks,
- sg.extents
- from dba_indexes id, dba_segments sg
- where id.owner = sg.owner
- and id.index_name = sg.segment_name
- and id.tablespace_name = sg.tablespace_name
- and id.owner not in
- ('SYS', 'SYSTEM', 'USER', 'DBSNMP', 'ORDSYS', 'OUTLN')
- and sg.extents > 100
- and id.blevel >= 3
- group by id.tablespace_name,
- id.owner,
- id.index_name,
- id.blevel,
- sg.blocks,
- sg.extents
- having sum(sg.bytes) / 1024 / 1024 > 100
- order by sum(sg.bytes) / 1024 / 1024 desc )
- where rownum <= 50;
复制代码
-- 单索引碎片情况
- select id.tablespace_name,
- id.owner,
- id.index_name,
- id.blevel,
- sum(sg.bytes) / 1024 / 1024,
- sg.blocks,
- sg.extents
- from dba_indexes id, dba_segments sg
- where id.owner = sg.owner
- and id.index_name = sg.segment_name
- and id.tablespace_name = sg.tablespace_name
- and id.owner ='UCR_CRM1'
- and id.index_name = 'PK_TF_B_TRADE_4G_VAC_PRODUCT'
- group by id.tablespace_name,
- id.owner,
- id.index_name,
- id.blevel,
- sg.blocks,
- sg.extents;
复制代码
-- 索引大小>80%表大小的索引列表
- select * from (
- WITH Q AS (
- SELECT
- S.OWNER A_OWNER,
- TABLE_NAME A_TABLE_NAME,
- INDEX_NAME A_INDEX_NAME,
- INDEX_TYPE A_INDEX_TYPE,
- I.PARTITIONED A_PART,
- SUM(S.bytes) / 1048576 A_MB
- FROM DBA_SEGMENTS S,
- DBA_INDEXES I
- WHERE S.OWNER = I.owner
- AND INDEX_NAME = SEGMENT_NAME
- GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE,PARTITIONED
- HAVING SUM(S.BYTES) > 1048576 * 100
- )
- SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
- A_OWNER OWNER,
- A_TABLE_NAME TABLE_NAME,
- A_INDEX_NAME INDEX_NAME,
- A_INDEX_TYPE INDEX_TYPE,
- A_PART PARTITIONEND,
- A_MB IND_MB,
- sum(bytes)/1024/1024 tab_MB
- FROM Q,
- dba_segments d
- WHERE
- D.owner(+)= q.A_OWNER AND
- D.segment_NAME = q.A_TABLE_NAME
- GROUP BY
- A_OWNER,
- A_TABLE_NAME,
- A_INDEX_NAME,
- A_INDEX_TYPE,
- A_PART,
- A_MB HAVING A_MB>0.8*(SUM(bytes) / 1024/1024 )
- ORDER BY
- A_OWNER,
- A_TABLE_NAME,
- A_INDEX_NAME,
- A_INDEX_TYPE,
- A_MB DESC
- ) where rownum <=50;
复制代码
|
|