一般日常用的脚本

一般日常用的脚本

1、检查源库每个节点至少3组redo

select group#,thread#,bytes/1024/1024,members,status from v$log;

select group#,thread#,sequence#,BYTES/1024/1024,archived,used,status from v$standby_log;

--查看回收站

SELECT * FROM DBA_RECYCLEBIN;

 

--查看表的信息

select table_name,num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len，last_analyzed from user_tables; --普通表

select * from user_tab_partitions;--分区表

select table_name,partition_name,subpartition_name,num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len,last_analyzed from user_tab_subpartitions; --子分区

select table_name,

partitioning_type,

subpartitioning_type,

partition_count

from user_part_tables

where subpartitioning_type <> 'NONE'; --查看分区表中带子分区的个数

 

--当前用户下，某个分区的记录数是平均记录数的2倍以上

set linesize 266

col table_name format a20

select table_name,

max(num_rows),

trunc(avg(num_rows),0),

sum(num_rows),

trunc(max(num_rows) / sum(num_rows),2),

count(*)

from user_tab_partitions

group by table_name

having max(num_rows) / sum(num_rows) > 2 / count(*);

--查看有子分区的数据情况：

select table_name,partition_name,subpartition_name,

num_rows

 

 

--索引列的统计信息

BLEVEL, --索引的层数

LEAF_BLOCKS, --叶子结点的个数

DISTINCT_KEYS, --唯一值的个数

AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数

AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数

CLUSTERING_FACTOR --群集因子

select index_name,table_name,blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,avg_data_blocks_per_key, clustering_factor from user_indexes; --普通表

select index_name,"COMPOSITE",SUBPARTITION_COUNT,PARTITION_NAME,blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,avg_data_blocks_per_key, clustering_factor from user_ind_partitions --分区表

--查看普通索引失效：

select t.index_name,

t.table_name,

blevel,

t.num_rows,

t.leaf_blocks,

t.distinct_keys

from user_indexes t

where status = 'INVALID';

--查看分区索引失效：

select t.index_name,

t.table_name,

blevel,

t.num_rows,

t.leaf_blocks,

t.distinct_keys

from user_ind_subpartitions t

where index_name in (select index_name from user_indexes)

and status = 'INVALID'

--查看子分区索引

select t.index_name,

t.partition_name,

blevel,

t.num_rows,

t.leaf_blocks,

t.distinct_keys

from user_ind_subpartitions t

where index_name in (select index_name from user_indexes)

and status = 'INVALID'

 

分区表和子分区表

select index_name,PARTITION_NAME,SUBPARTITION_NAME,blevel,leaf_blocks,distinct_keys, avg_leaf_blocks_per_key,avg_data_blocks_per_key, clustering_factor from user_ind_subpartitions; --子分区表

查看直方图：

SELECT table_name,column_name, num_distinct,low_value, high_value, density, num_nulls, num_buckets, histogram from user_tab_columns;

select * from user_tab_histograms;

select * from user_part_histograms;

select * from user_subpart_histograms;

查看列的信息：

NUM_DISTINCT, --唯一值的个数

LOW_VALUE, --列上的最小值

HIGH_VALUE, --列上的最大值

DENSITY, --选择率因子（密度）

NUM_NULLS, --空值的个数

NUM_BUCKETS, --直方图的BUCKET个数

HISTOGRAM --直方图的类型

直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况，

SELECT table_name,column_name, num_distinct,low_value, high_value, density, num_nulls, num_buckets, histogram from user_tab_columns ;

直方图：直方图意义：在oracle数据库中，CBO会默认认为目标列的数据量在其最小值和最大值之间是均匀分布的（最小值最大值不准确会导致谓词越界），

并且会按照这个均匀分布原则来计算对目标列事假的where查询条件后的可选这率及结果集的cardinality,进而据此来计算成本值并选择执行计划。但是，目标列的数据是均匀分布的按照这个原则选择执行计划是正确的；

如果目标数据列分布不均匀，甚至是严重倾斜，分布极度不均匀，那么这个按照这个原则选择执行计划就不合适，甚至是错误的，为此我们需要对那些数据分布不均匀的列进行直方图收集。

直方图实际存储在数据字典sys.histgrm$中，可以通过数据字典dba_tab_historgrams,dba_part_histograms和dba_subpart_histograms来分别查看表，分区表的分区和分区表的子分区的直方图信息。

 

收集统计信息的语句：

analyze 命令的语法如下：

analyze table tablename compute statistics;

analyze table tablename compute statistics for all indexes;

analyze table tablename delete statistics;

dbms_stats.gather_table_stats 收集表、列和索引的统计信息；

dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；

dbms_stats.gather_index_stats 收集索引的统计信息；

dbms_stats.gather_system_stats 收集系统统计信息

表统计信息的查看：

包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小：

NUM_ROWS, --表中的记录数

BLOCKS, --表中数据所占的数据块数

EMPTY_BLOCKS, --表中的空块数

AVG_SPACE, --数据块中平均的使用空间

CHAIN_CNT, --表中行连接和行迁移的数量

AVG_ROW_LEN --每条记录的平均长度

统计信息的收集：

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'hr',

tabname => 'employees',

estimate_percent => 100,

method_opt => 'for all columns size',

no_invalidate => FALSE,

degree => 1,

cascade => TRUE);

END;

/

删除直方图的影响：

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'hr',

tabname => 'employees',

estimate_percent => 100,

method_opt => 'for all columns size 1',

no_invalidate => FALSE,

degree => 1,

cascade => TRUE);

END;

/

for all columns size 1 为所有size 放在一个桶里面（即为删除）

select a.table_name,a.column_name,

b.num_rows,

a.num_distinct Cardinality,

round(a.num_distinct / b.num_rows * 100, 2) selectivity,

a.histogram,

a.num_buckets

from user_tab_col_statistics a, user_tables b

where

a.table_name = b.table_name

oracle 一般查询数据行数在5%及以下,用到索引过略字段，CBO会走索引

生成了直方图之后 执行以下两个句子查看一下分别的执行计划对比看看

NUM_ROWS 表示总行数

CARDINALITY 表示基数

SELECTIVITY表示选择性 选择性在10%以上都比较高了

HISTOGRAM表示直方图的类型：

FREQUECNCY频率直方图、 当列中Distinct_keys 较少(小于254)，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于Distinct_Keys。

HEIGHT BALANCED 高度平衡直方图 当列中Distinct_keys大于254，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建高度平衡直方图。

NONE表示未收集直方图

NUM_BUCKETS 表示桶数

3、疑问使用直方图的场合

1、直方图到底应该什么时候收集直方图？

就查一下执行计划和实际查询行数进行比较 估算的基数ROWS是不是算错了。

构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划

注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。

2、只对有索引的列收集直方图也是错的！

3、直方图究竟是干嘛的？

告诉CBO 有没有收集直方图 这个列是不是均衡的

1. 没收集直方图 ---CBO认为这个列是分布均匀的

2. 收集过了 ---告诉CBO这个列数据有问题 分布不均衡,特别是频率直方图算的会很准

最终就是影响rows

 

#重启完后，可通过下面语句查看修改地方：

set lines 500 pages 0

col value for a90

col name for a50

select name,value

from v$parameter

where name in('db_name','db_unique_name',

'log_archive_config',

'log_archive_dest_1','log_archive_dest_2',

'log_archive_dest_1',

'log_archive_dest_2',

'remote_login_passwordfile',

'log_archive_format',

'log_archive_max_processes',

'fal_server',

'db_file_name_convert',

'log_file_name_convert',

'standby_file_management')

/

表空间总容量和使用的表空间：

select

(select sum(bytes)/1024/1024/1024/1024 bytes from dba_data_files) total_TB,

(select sum(bytes)/1024/1024/1024/1024 from dba_segments ) use_TB,

(select sum(bytes)/1024/1024/1024 from dba_segments where SEGMENT_TYPE='TABLE') table_G,

(select sum(bytes)/1024/1024/1024 from dba_segments where SEGMENT_TYPE='INDEX') index_G

from dual;

所有的hint，都可以从如下11g的视图中获取到。select * from

v$sql_hint;其中该视图的name和inverse是相反的hint，比较有意义。

其中有些变化，比如parallel的hint变成shared，在实际使用中，是一样的。

 11g默认启动了统计信息收集的任务，默认运行时间是周一到周五晚上10点和周6，周天的早上6点

select window_name,

window_next_time,

autotask_status,

optimizer_stats

from DBA_AUTOTASK_WINDOW_CLIENTS;

SMART_DT2

收缩表空间：

alter tablespace name shrink space 200; --收缩到200M。

alter tablespace name shrink space; 不加keep 收缩到最小。

alter tablespace name shrink datafile '路径';

由于以上shrink不能shrink 本地管理的表空间，只能shrink临时表空间，所以用resize;

 

SELECT 'alter database datafile ' || D.FILE_ID || ' resize 1G;'

FROM DBA_DATA_FILES D

WHERE D.TABLESPACE_NAME = 'SMART_NSNRP_2017';

查看执行计划真实hash值：

select distinct a.sql_text,a.sql_id,b.plan_hash_value

from v$sql a inner join v$sql_plan b on a.hash_value=b.hash_value

 PDB,CDB查看：

show pdbs；

 show con_id;

alter session set container=cdb$root;

 grant select_catalog_role to c##test container=all;

 

CONN / AS SYSDBA -- Create a pluggable database CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1 DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M; ALTER PLUGGABLE DATABASE pdb1 OPEN; ALTER SESSION SET CONTAINER = pdb1; -- Create a local user. CREATE USER local_user IDENTIFIED BY Local1 QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE TO local_user; CREATE TABLE local_user.local_user_tab AS SELECT level AS ID FROM dual CONNECT BY level <= 2; CONN / AS SYSDBA CREATE PLUGGABLE DATABASE pdb2 FROM pdb1; ALTER PLUGGABLE DATABASE pdb2 OPEN; CREATE PLUGGABLE DATABASE pdb3 FROM pdb1; ALTER PLUGGABLE DATABASE pdb3 OPEN;

 

grant create session to C##test;  

grant create table to   C##test;  

grant create tablespace to   C##test;  

grant create view to   C##test;

alter user C##test quota unlimited on CDBTEST; 

alter user C##test quota unlimited on USERS;

CDB 环境中的用户

CDB 环境中包含两类用户，公用用户和本地用户。

公用用户

创建公用用户

公用用户是在 root 容器数据库中和所有的 PDB 数据库中都存在的用户， 公用用户必须在根容器中创建 ，然后此用户会在所有的现存的 PDB 中自动创建， 公用用户标识必须以 c## 或者 C## 开头 ， sys 和 system 用户是 Oracle 在 CDB 环境中自动创建的公用用户。

创建语句为：

create user c##test identified by test;

 

(1)      公用用户在 root 容器中创建小结：

(2)      公用用户名称比较特殊，要以 c## 或者 C## 开头

公用用户赋权

创建完成公用用户，需要为公用用户赋予所有可插拔数据库的权限，公用用户才可以访问其他 PDB ，如果在连接根容器时仅仅为公用用户赋予了相关权限，那么该权限不会被传递到所有的可插拔数据库中，必须为公用用户赋予能够传递到 PDB 中的权限，可以创建公用角色然后赋予公用用户，或者在为公共用户付权时指定子句 container=ALL;

小结：

（1）        公用用户要连接 pdb 需要赋予对应权限

（2）        赋予的权限方式可以是加 container=all( 或 container=pdb) ；也可以通过赋予 role 角色权限。

赋予 c##mytest1 用户基本的连接权限。

grant resource,connect to c##test container=all;

 

alter pluggable database orclpdb open; -->指定某个pdb启动，比如orclpdb

alter pluggable database all open; -->所有的pdb数据库都启动

ALTER PLUGGABLE DATABASE pdb4 OPEN READ ONLY;

 

ALTER PLUGGABLE DATABASE pdb4 CLOSE;

ALTER PLUGGABLE DATABASE pdb4 OPEN READ ONLY RESTRICTED;

归档日志是公用的

在 CDB 环境中所有的 PDB 共用 CDB 的归档模式，以及归档文件，不可以单独为 PDB 设置自己的归档模式，只有特权用户连接根容器之后才可以启动归档模式。

UNDO MODE- -UNDO 表空间

（1）        在 12.2 之前的版本中，所有的 PDB 共用 CDB$ROOT 中的 UNDO 文件

（2）        在 12.2 之后的版本中 UNDO 的使用模式有两种： SHARED UNDO MODE 和 LOCAL UNDO MODE ，顾名思义， LOCAL UNDO MODE 就是每个 PDB 使用自己的 UNDO 表空间，但当 PDB 中没有自己的 UNDO 表空间时，会使用 CDB$ROOT 中的公共 UNDO 表空间。

SELECT property_name, property_value

FROM database_properties

WHERE property_name='LOCAL_UNDO_ENABLED';

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup upgrade

ORACLE instance started.

  

Total System Global Area 1241513984 bytes

Fixed Size                  8792248 bytes

Variable Size             788531016 bytes

Database Buffers          436207616 bytes

Redo Buffers                7983104 bytes

Database mounted.

Database opened.

SQL>

SQL> alter database local undo off;

  

Database altered.

 

可以通过 v$system_parameter 视图查看 PDB 中可修改的参数：

SELECT name FROM v$system_parameter

WHERE ispdb_modifiable = 'TRUE'

ORDER BY name;

 

查看pdb时区

select dbtimezone from dual;

 

(10046TRACE）

/*

步骤1：alter session set events '10046 trace name context forever,level 12'; （开启跟踪）

步骤2：执行你的语句

步骤3：alter session set events '10046 trace name context off'; （关闭跟踪）

步骤4：找到跟踪后产生的文件

步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela （格式化命令）

*/

set autotrace off

alter session set statistics_level=typical;

alter session set events '10046 trace name context forever,level 12';

SELECT *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19);

 

alter session set events '10046 trace name context off';

12c之前查看trace文件：

select d.value

|| '/'

|| LOWER (RTRIM(i.INSTANCE, CHR(0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

from (select p.spid

from v$mystat m,v$session s, v$process p

where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

(select t.INSTANCE

FROM v$thread t,v$parameter v

WHERE v.name='thread'

AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

(select value

from v$parameter

where name='user_dump_dest') d;

 

12c之后 查看trace文件：

select d.value

|| '/'

|| LOWER (RTRIM(i.INSTANCE, CHR(0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

from (select p.spid

from v$mystat m,v$session s, v$process p

where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

(select t.INSTANCE

FROM v$thread t,v$parameter v

WHERE v.name='thread'

AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

(select value from v$diag_info where name='Diag Trace') d;

 

临时表空间：

set lin 140

SELECT d.status "Status", d.tablespace_name "Name", d.CONTENTS "Type",

d.extent_management "Extent Management",

TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') "Size (M)",

TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')

|| '/'

|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') "Used (M)",

TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') "Used %"

FROM SYS.dba_tablespaces d,

(SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_temp_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (bytes_cached) BYTES

FROM v$temp_extent_pool

GROUP BY tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+)

AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management LIKE 'LOCAL'

AND d.CONTENTS LIKE 'TEMPORARY';

查看表使用的表空间：

select distinct tablespace_name from user_tab_partitions where table_name='S_T_RTNRP_STATUS';

resize 重置数据文件大小：

set lin 200 pages 2000

select ' alter database datafile '''||file_name ||''' resize 200m;' from dba_data_files where tablespace_name

in (

'SMART_NRRPSTA08',

'SMART_NRRPSTA09',

'SMART_NRRPSTA10',

'SMART_NRRPSTA05',

'SMART_NRRPSTA11',

'SMART_NRRPSTA12',

'SMART_NRRPSTA07',

'SMART_NRRPSTA06',

'SMART_NRRPSTA04',

'SMART_NRRPSTA02',

'SMART_NRRPSTA03',

'SMART_NRRPSTA01'

);

 

查看分区的个数以及每个分区记录数：

select t.table_name, t.partition_name, t.num_rows, t.last_analyzed

from user_tab_partitions t

where t.table_name = 'S_T_REPORT_PUSH';

如果统计信息不准确：

--做一次表分析

exec dbms_stats.gather_table_stats(ownname=>'PINTEST',tabname=>'BILLLOG_T',granularity=>'ALL');

 

create table S_OPERATELOG_TONGJI_62TEST tablespace SMART_DT2 as select * from S_OPERATELOG_TONGJI where 1=0;

set lin 200 pages 1000(这个有问题)

select a.tablespace_name,round(sum(a.bytes/1024/1024/1024),2) total_G,

round(sum(a.maxbytes/1024/1024/1024),2) max_extended_G,

round(sum(b.bytes/1024/1024/1024)) free_G,

round((sum(a.bytes/1024/1024/1024)-sum(b.bytes/1024/1024/1024)),2) used_G,

round(sum((a.maxbytes-a.bytes)/1024/1024/1024),2) need_extend_G

from dba_data_files a,dba_free_space b

where a.TABLESPACE_NAME=b.TABLESPACE_NAME

group by a.tablespace_name order by 5 desc ;

 

查看表空间最大扩展情况：

set lin 200 pages 1000

select tablespace_name,round(sum(bytes/1024/1024/1024),2) total_G,

round(sum(maxbytes/1024/1024/1024),2) max_extended_G,

round(sum((maxbytes-bytes)/1024/1024/1024),2) need_extend_G,

round(sum(bytes)/sum(maxbytes),4)*100 "MAXRate"

from dba_data_files group by tablespace_name order by 5 desc ;

62,61热盘的排序：

select tablespace_name,

ROUND(sum(bytes / 1024 / 1024 / 1024),2) G

from dba_data_files

where file_name like '+FDATADG%'

GROUP BY TABLESPACE_NAME ORDER BY 2 DESC;

查看表空间所包含的对象：

select owner,TABLE_NAME from dba_tables name where tablespace_name='&name'

union

select owner,index_name from dba_indexes where tablespace_name='&name'

union

select table_owner,table_name from dba_tab_subpartitions where tablespace_name='&name'

union

select table_owner,table_name from dba_tab_partitions where tablespace_name='&name'

分区大小：

select round(sum(bytes / 1024 / 1024 / 1024), 2) G

from dba_segments

where segment_name = 'S_OPERATELOG_2018' and partition_name like 'OPLOG_1811%'

 

查看ASM磁盘使用情况

select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024 TOTAL_G,FREE_MB/1024 FREE_G,

ROUND((TOTAL_MB-FREE_MB)/TOTAL_MB,4)*100 "RATE%" from v$asm_diskgroup;

查看数据文件：

select file_name,bytes/1024/1024/1024 total_G,tablespace_name,maxbytes/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='';

 

col tablespace_name for a30

col file_name for a60

set lin 140

set pagesize 50

with

D as (SELECT TABLESPACE_NAME ,

ROUND((SUM(BYTES)/(1024*1024)),2) SPACE,

SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME),

F as (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)

FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME),

T as (select distinct TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files)

SELECT D.TABLESPACE_NAME,

SPACE "SUM_SPACE(M)" ,

SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",

FREE_SPACE "FREE_SPACE(M)" ,

ROUND((1-NVL(FREE_SPACE,0)/SPACE) *100,2) "USED_RATE(%)",t.AUTOEXTENSIBLE

from D,F,T

WHERE T.TABLESPACE_NAME(+)=D.TABLESPACE_NAME AND D.TABLESPACE_NAME=F.TABLESPACE_NAME(+)

ORDER BY "USED_RATE(%)";

查看会话：

set lin 200 pages 100

col s.machine for a20

col substr(p.program, 1, 40) for a30

select s.machine,

substr(s.program, 1, 40) prog,

COUNT(*)

from v$session s, v$process p where s.username is not null

AND s.paddr = p.addr

GROUP BY s.machine,substr(s.program, 1, 40) ORDER BY 3 DESC;

查看SQL:

netstat -nuptl |grep 32756

 

查看sql语句：

select * from v$sqlarea c

where c.HASH_VALUE =(

select d.PREV_HASH_VALUE from v$session d

where d.sid='&sid'

);

查看等待事件（enquence）：

col event for a30

col username for a15

set lin 140

select sid,serial#,username, event, sql_id, last_call_et as "times(s)",p1, p2, p3,program

from v$session

where status ='ACTIVE' and username is not null

order by event,last_call_et desc;

ps -ef|grep pmon|grep -v grep|awk '{print "kill -9 "$1}'

杀进程

查看进程：

ps -ef|grep pmon|grep -v grep|awk '{print "kill -9 "$1,$2}'

查看生成的trac 文件；

SQL> ORADEBUG setmypid

分开窗口执行

SQL> ORADEBUG hanganalyze

利用trace文件，查看sql_id;

select sid,serial#,paddr,username,status,sql_id from v$session where sid=数字;

查看sql语句：

select sql_text from v$sqltext where sql_id = '&sql_id' order by address,piece；

方法一：杀进程源头：ps -ef |grep 655902（ospid）

查看spid(ospid)是否为local=no

kill -9 spid(ospid)

方法2：一个个杀：

select s.sid,p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid in

(select sid from v$session where event='latch: cache buffers chains' );

确认所有spid 为local=no;

kill -9 spid 一个个杀；

检查是否存在坏块：

desc v$database_block_corruption

select * from v$database_block_corruption;

 

select event,count(1) from gv$session group by event order by 2;

select * from v$lock_type where type='TT';

select sid,last_call_et,blocking_sid from v$session where xxxx

 

set line 999 pages 1000

set long 200

col machine for a20

col prog for a30

col OSUSER for a20

select to_char(s.LOGON_TIME,'yyyy-MM-dd HH24:mi:ss') LOGON_TIME,

s.sid,

s.serial#,

s.machine,

p.spid,

s.status,

substr(s.program, 1, 40) prog,

s.osuser,

s.USERNAME

from v$session s, v$process p where s.username is not null

AND s.paddr = p.addr and

--substr(s.program, 1, 40)

s.machine='WORKGROUP\MD-100-81';

 

输入：S_T_RTNRP_STATUS_2017

重置表空间的数据文件：

 

select ' alter ' || ' database datafile ''' || file_name ||

''' resize 2g ' || ';'

from dba_data_files

where tablespace_name = 'SMART_NRRPSTA_2017';

 

(10046TRACE）

/*

 

  步骤1：alter session set events '10046 trace name context  forever,level 12'; （开启跟踪）

  步骤2：执行你的语句

  步骤3：alter session set events '10046 trace name context off';   （关闭跟踪）

  步骤4：找到跟踪后产生的文件

  步骤5：tkprof  trc文件  目标文件  sys=no sort=prsela,exeela,fchela  （格式化命令）    

 

*/

set autotrace off

alter session set statistics_level=typical;    

alter session set events '10046 trace name context  forever,level 12';

 

SELECT  *

FROM t1, t2

WHERE t1.id = t2.t1_id

AND t1.n in(18,19);  

  

alter session set events '10046 trace name context off';  

 

12c之前查看trace文件：

 

select d.value

|| '/'

|| LOWER (RTRIM(i.INSTANCE, CHR(0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

from (select p.spid

      from v$mystat m,v$session s, v$process p

      where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

      (select t.INSTANCE

       FROM v$thread t,v$parameter v

       WHERE v.name='thread'

       AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

       (select value

       from v$parameter

       where name='user_dump_dest') d;

      

12c之后 查看trace文件：

 

select d.value

|| '/'

|| LOWER (RTRIM(i.INSTANCE, CHR(0)))

|| '_ora_'

|| p.spid

|| '.trc' trace_file_name

from (select p.spid

      from v$mystat m,v$session s, v$process p

      where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,

      (select t.INSTANCE

       FROM v$thread t,v$parameter v

       WHERE v.name='thread'

       AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,

       (select value from v$diag_info where  name='Diag Trace') d;

 exit

tkprof  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17496.trc /home/oracle/10046.txt

 

五、DG传输过程监控

1、查看备库工作模式及状态

set lin 140 pages 100

select inst_id,db_unique_name,database_role,open_mode,protection_mode,protection_level,switchover_status,force_logging from gv$database;

select db_unique_name,protection_mode,synchronization_status,SYNCHRONIZED from v$archive_dest_status;

 

2、日志恢复进程

archive log list;

select thread#,max(sequence#) from v$archived_log group by thread#;

select pid,process,client_process,client_pid,thread#,sequence#,status,DELAY_MINS from v$managed_standby;

 

--RFS进程从主数据库接收重做数据，并将其写入备用重做日志。

3、查看standbylog状态，如果是RAC两节点，那么每个节点至少有一个是ACTIVE的状态，否则不对

set lines 200

select group#,thread#,sequence#,bytes/1024/1024,archived,used,status,first_change#,last_change# from v$standby_log;

检查备库已恢复的最大归档日志序号

select thread#,max(sequence#),registrar,applied,status from v$archived_log where applied='YES' and registrar='RFS' and name is not null

group by thread#,registrar,applied,status;

 

4、检查应用率和活动率（PS）

--Redo Applied 值以MB衡量。剩余两个以KB/s计算。

select to_char(start_time,'DD-MON-RR HH24:MI:SS') start_time,ITEM,sofar

from v$recovery_progress

where item in ('Active Apply Rate','Average Apply Rate','Redo Applied');

5、审阅传输和应用滞后（PS+LS）

--transport lag 表明从主数据库到物理备用数据库的重做数据传输时间。

--apply lag 表明应用滞后时间，它反映了archive_log_dest_n参数中 DELAY 特性。

COL NAME FOR A13

COL VALUE FOR A20

COL UNIT FOR A30

SET LINES 200

select name,value,unit,time_computed

from v$dataguard_stats

where name like '%lag%';

 

6、查看Data Guard状态视图中的错误

set lines 132

col message for a80

col timestamp for a20

select error_code,severity,message,

to_char(timestamp,'DD-MON-RR HH24:MI:SS') timestamp

from v$dataguard_status

where callout='YES'

and timestamp > sysdate -1;

7、检查日志文件是否传输到备用数据库

select dest_name,status,error from v$archive_dest where dest_id=2;

DEST_NAME STATUS ERROR

-------------------- ---------- --------------------

LOG_ARCHIVE_DEST_2 VALID

status列如果为valid，说明归档成功，可以查看error列得到不能归档的原因

 

六、修改DG保护模式

1、查看当前保护模式 主库查询

select inst_id,db_unique_name,database_role,open_mode,protection_mode,protection_level,switchover_status,force_logging from gv$database;

2、将备库转换为最大可用模式

--主库数据库操作 --将最大性能转为最大可用

alter database set standby database to maximize availability;

 

 

二、较全一点的脚本

------------------------------SGA---------------------------------------------

--SGA 各部分大小

show sga

select * from v$sga;

SELECT * FROM V$SGAINFO;

--SGA设置大小

show parameter sga_target

--SGA各个池大小

COL name FORMAT a32;

SELECT pool, name, bytes/1024/1024 M

FROM v$sgastat

WHERE pool IS NULL

OR pool != 'shared pool'

OR (pool = 'shared pool' AND

(name IN

('dictionary cache', 'enqueue', 'library

cache', 'parameters', 'processes', 'sessions', 'free memory')))

ORDER BY pool DESC NULLS FIRST, name;

 

 

------------------------BUFFER CACHE-----------------------------------------------

--查看buffer cache 命中率

select 1 - (sum(decode(name, 'physical reads', value, 0)) /

(sum(decode(name, 'db block gets', value, 0)) +

(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"

from v$sysstat;

select name,

physical_reads,

(consistent_gets + db_block_gets) logic_reads,

1 - (physical_reads) / (consistent_gets + db_block_gets) hit_radio

from v$buffer_pool_statistics;

--查看buffer cache建议

select size_for_estimate,

estd_physical_read_factor,

to_char(estd_physical_reads, 99999999999999999999999) as"estd_physical_reads"

from v$db_cache_advice

where name = 'DEFAULT';

 

--查看buffer cache建议 --适用于指定SGA的目的

COL pool FORMAT a10;

SELECT (SELECT ROUND(value / 1024 / 1024, 0)

FROM v$parameter

WHERE name = 'db_cache_size') "Current Cache(Mb)",

name "Pool",

size_for_estimate "Projected Cache(Mb)",

ROUND(100 - estd_physical_read_factor, 0) "Cache Hit Ratio%"

FROM v$db_cache_advice

WHERE block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')

ORDER BY 3;

--查看cache

show parameter cache

--各种读取的统计

---Database read buffer cache hit ratio =

---1 – (physical reads / (db block gets + consistent gets))

SELECT to_char(value,'9999999999999'), name FROM V$SYSSTAT WHERE name IN

('physical reads', 'db block gets', 'consistent gets');

SELECT 'Database Buffer Cache Hit Ratio ' "Ratio"

, ROUND((1-

((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'physical reads')

/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')

+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets')

))) * 100)||'%' "Percentage"

FROM DUAL;

------------------------SHARED_POOL-----------------------------------------------

show parameter shared

---检查整体命中率(library cache)

select sum(pinhits) get ,

sum(pins)-sum(pinhits) miss,

sum(pinhits) / sum(pins)

from v$librarycache;

-- 查看library cache 命中率（分类）

select t.NAMESPACE,t.GETHITRATIO*100

from v$librarycache t;

 

select sum(pins) "hits",

sum(reloads) "misses",

sum(pins) / (sum(pins) + sum(reloads)) "Hits Ratio"

from v$librarycache;e

 

---检查shered pool free space

SELECT *

FROM V$SGASTAT

WHERE NAME = 'free memory'

AND POOL = 'shared pool';

---检查row cache(数据字典缓冲区)命中率

---当执行一个dml或ddl都会造成对数据字典的递归修改

column updates format 999,999,999

SELECT parameter

, sum(gets)

, sum(getmisses)

, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets

, sum(modifications) updates

FROM V$ROWCACHE

WHERE gets > 0

GROUP BY parameter;

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE"

FROM V$ROWCACHE;

---查看Shared pool latch(多池技术)

 

/*col parameter for a20

col session for a20*/

select a.ksppinm "Parameter",

b.ksppstvl "Session Value",

c.ksppstvl "Instance Value"

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

where a.indx = b.indx and a.indx = c.indx

and a.ksppinm = '_kghdsidx_count';

 

---查看shared pool建议

column c1 heading 'Pool |Size(M)'

column c2 heading 'Size|Factor'

column c3 heading 'Est|LC(M) '

column c4 heading 'Est LC|Mem. Obj.'

column c5 heading 'Est|Time|Saved|(sec)'

column c6 heading 'Est|Parse|Saved|Factor'

column c7 heading 'Est|Object Hits' format 999,999,999

SELECT shared_pool_size_for_estimate c1,

shared_pool_size_factor c2,

estd_lc_size c3,

estd_lc_memory_objects c4,

estd_lc_time_saved c5,

estd_lc_time_saved_factor c6,

to_char(estd_lc_memory_object_hits, 99999999999) c7

FROM V$SHARED_POOL_ADVICE;

--查看shared pool中 各种类型的chunk的大小数量

SELECT KSMCHCLS CLASS,

COUNT(KSMCHCLS) NUM,

SUM(KSMCHSIZ) SIZ,

To_char(((SUM(KSMCHSIZ) / COUNT(KSMCHCLS) / 1024)), '999,999.00') || 'k' "AVG SIzE"

FROM X$KSMSP

GROUP BY KSMCHCLS;

--查看是否有库缓冲有关的等待事件

select sid, seq#, event, p1, p1raw, p2, p2raw, p3, p3raw, state

from v$session_wait

where event like 'library%';

 

--row cache命中率

SELECT 'Dictionary Cache Hit Ratio ' "Ratio",

ROUND((1 - (SUM(GETMISSES) / SUM(GETS))) * 100, 2) || '%' "Percentage"

FROM V$ROWCACHE;

---library cache中详细比率信息

SELECT 'Library Lock Requests' "Ratio",

ROUND(AVG(gethitratio) * 100, 2) || '%' "Percentage"

FROM V$LIBRARYCACHE

UNION all

SELECT 'Library Pin Requests' "Ratio",

ROUND(AVG(pinhitratio) * 100, 2) || '%' "Percentage"

FROM V$LIBRARYCACHE

UNION all

SELECT 'Library I/O Reloads' "Ratio",

ROUND((SUM(reloads) / SUM(pins)) * 100, 2) || '%' "Percentage"

FROM V$LIBRARYCACHE ;

 

 

--查看library cache 内存分配情况(对哪类对象)

SELECT lc_namespace "Library",

LC_INUSE_MEMORY_OBJECTS "Objects",

LC_INUSE_MEMORY_SIZE "Objects Mb",

LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects",

LC_FREEABLE_MEMORY_SIZE "Freeable Mb"

FROM v$library_cache_memory;

---查看使用shard_pool保留池情况

SELECT request_misses, request_failures, free_space

FROM v$shared_pool_reserved;

 

 

 

---查看cache中所有pool,命中情况

COL pool FORMAT a10;

SELECT a.name "Pool", a.physical_reads, a.db_block_gets

, a.consistent_gets

,(SELECT ROUND((1-(physical_reads / (db_block_gets + consistent_gets)))*100)

FROM v$buffer_pool_statistics

WHERE db_block_gets+consistent_gets != 0

AND name = a.name) "Ratio"

FROM v$buffer_pool_statistics a;

 

 

相关命令

--- alter table xx cache

---ALTER TABLE(INDEX) xx STORAGE(BUFFER_POOL KEEP);

---取消cache或keep(keep pool)

---ALTER TABLE XX NOCACHE;

---SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'

---FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';

 

 

------------------------PGA-----------------------------------------------

 

---查看pga

show parameters area_size

--- 查看pga

SELECT * FROM v$pgastat;

 

--查看pga建议

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter

WHERE name = 'pga_aggregate_target') "Current Mb"

, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"

, ROUND(estd_pga_cache_hit_percentage) "%"

FROM v$pga_target_advice

ORDER BY 2;

 

------------------------其他指标类---------------------------------------

---查看数据库中行chain

SELECT 'Chained Rows ' "Ratio",

ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch continued row')/

(SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch byrowid')

)* 100, 3)||'%' "Percentage"

FROM DUAL;

 

---在内存中排序比率(最优排序)

SELECT 'Sorts in Memory ' "Ratio",

ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)') /

(SELECT SUM(value)

FROM V$SYSSTAT

WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100,

5) || '%' "Percentage"

FROM DUAL;

 

 

---查询解析比率

SELECT 'Soft Parses ' "Ratio",

ROUND(((SELECT SUM(value)

FROM V$SYSSTAT

WHERE name = 'parse count (total)') -

(SELECT SUM(value)

FROM V$SYSSTAT

WHERE name = 'parse count (hard)')) /

(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100,

2) || '%' "Percentage"

FROM DUAL

UNION

SELECT 'Hard Parses ' "Ratio",

ROUND((SELECT SUM(value)

FROM V$SYSSTAT

WHERE name = 'parse count (hard)') /

(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100,

2) || '%' "Percentage"

FROM DUAL

UNION

SELECT 'Parse Failures ' "Ratio",

ROUND((SELECT SUM(value)

FROM V$SYSSTAT

WHERE name = 'parse count (failures)') /

(SELECT SUM(value)

FROM V$SYSSTAT

WHERE name = 'parse count (total)') * 100,

5) || '%' "Percentage"

FROM DUAL;

 

 

--查询latch free 等待事件相关信息

COL event FORMAT a20;

COL waits FORMAT 9999990;

COL timeouts FORMAT 99999990;

COL average FORMAT 99999990;

SELECT event "Event",

time_waited "Total Time",

total_waits "Waits",

average_wait "Average",

total_timeouts "Timeouts"

FROM V$SYSTEM_EVENT

WHERE event = 'latch free'

ORDER BY EVENT;

---查看数据库中查询时主要访问方式,获取大表小表访问比率(2个表的访问算法不同)

---table scans (long tables)过多的话,一般db file scattered read比较显著

---_small_table_threshold来定义大表和小表的界限。缺省为2%的Buffer数量 ,>这个参数为大表

---default 大表的全表扫描会被置于LRU的末端(最近最少使用,冷端)，以期尽快老化(让其尽快换出buffer cache)，减少Buffer的占用

--表访问统计

SELECT value, name

FROM V$SYSSTAT

WHERE name IN ('table fetch by rowid',

'table scans (short tables)',

'table scans (long tables)');

-----查看大表小表扫描对应的值

SELECT value, name FROM V$SYSSTAT WHERE name IN

('table fetch by rowid', 'table scans (short tables)'

, 'table scans (long tables)');

 

 

SELECT 'Short to Long Full Table Scans' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table scans (short tables)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN

('table scans (short tables)', 'table scans (long tables)'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Short Table Scans ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table scans (short tables)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN

('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Long Table Scans ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name = 'table scans (long tables)')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Table by Index ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)', 'table scans (long tables)'

, 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL

UNION

SELECT 'Efficient Table Access ' "Ratio"

, ROUND(

(SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)','table fetch by rowid'))

/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name

IN ('table scans (short tables)', 'table scans (long tables)'

, 'table fetch by rowid'))

* 100, 2)||'%' "Percentage"

FROM DUAL;

------------------------------------------------------------------------------------------------------------------------------------

redo 相关

--查询归档模式

select name ,open_mode,log_mode from v$database;

archive log list

---检查日志切换频率

select sequence#,

to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,

round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,2) minutes

from v$log_history

where 1=1

-- and first_time > sysdate - 1

order by first_time, minutes;

 

---检查lgwr i/o性能 (time_waited/total_waits：表示平均lgwr写入完成时间 若>1(百分之一秒)表示写入过慢)

select total_waits,

time_waited,

average_wait,

time_waited / total_waits as avg_time

from v$system_event

where event = 'log file parallel write';

---检查与redo相关性能指标

select name,value from v$sysstat where name like '%redo%';

 

---查询redo block size

select max(lebsz) from x$kccle;

 

---查看redo allocation latch

col name for a30

select name, gets, misses, misses / gets

from v$latch

where name = 'redo allocation';

col name for a30

select name, gets, misses, misses / gets

from v$latch_children

where name = 'redo allocation';

 

---查看与redo相关等待事件

col event format a40

select event,total_waits,time_waited ,total_timeouts,average_wait

from v$system_event

where upper(event) like'%REDO%';

 

---查看user commit次数

select to_number(value,99999999999) from v$sysstat where name='user commits';

 

---查看系统运行时间

select (sysdate - startup_time)*24*60*60 as seconds from v$instance

 

---计算出每秒用户提交次数

select user_commit次数/系统运行时间 from dual;

 

---计算出每个事务平均处理多少个redo block

select a.redoblocks / b.trancount

from (select value redoblocks

from v$sysstat

where name = 'redo blocks written') a,

(select value trancount from v$sysstat where name = 'user commits') b

--------------------------------------------------------------------------------------------------------------------------------------

总体配置

---检查database基本信息

select * from v$version;

select name ,open_mode,log_mode from v$database;

--检查是否为rac 实例数量

select instance_number,instance_name ,status from gv$instance;

show parameter cpu_count

--默认数据块大小

show parameter block_size

--各种文件数量

select count(*) from v$controlfile

select count(*) from v$tempfile;

select count(*) from v$datafile;

--资源限制

SELECT * FROM V$RESOURCE_LIMIt ;

 

--数据库安装信息

SELECT *

FROM V$OPTION

--数据库参数

show parameter

 

-----------------------------------------------------------------------------------------------------------------------------------------

数据文件与空间类

 

--各种文件数量

select count(*) from v$tempfile;

select count(*) from v$datafile;

--表空间大小

select tablespace_name , sum(bytes)/1024/1024 M from dba_temp_files group by tablespace_name

union all

select tablespace_name , sum(bytes)/1024/1024 M from dba_data_files group by tablespace_name;

--数据文件状态

select t.online_status,count(*)

from dba_data_files t

group by t.online_status ;

--表空间基本信息

SELECT t.status,

t.tablespace_name,

t.extent_management,

t.segment_space_management,

t.contents

FROM DBA_TABLESPACES t

order by t.status

 

--临时段使用情况

COL username FORMAT a10;

COL segtype FORMAT a10;

SELECT username,

segtype,

extents "Extents Allocated",

blocks "Blocks Allocated"

FROM v$tempseg_usage;

 

--查看临时表空间总体使用情况

SELECT TMP_TBS.TABLESPACE_NAME,

SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,

SUM(USED_TOT.USED_MB) USED_MB,

SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT

FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) TMP_TBS,

(SELECT TMP_USED.TABLESPACE,

SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB

FROM V$SORT_USAGE TMP_USED,

(SELECT VALUE DB_BLOCK_SIZE

FROM V$PARAMETER

WHERE NAME = 'db_block_size') PARA

GROUP BY TMP_USED.TABLESPACE) USED_TOT

where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)

GROUP BY TMP_TBS.TABLESPACE_NAME;

--查看临时表空间中排序段和数据段的使用情况

SELECT TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE TEMP_SEG_TYPE,

SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,

SUM(USED_TOT.USED_MB) USED_MB,

SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT

FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) TMP_TBS,

(SELECT TMP_USED.TABLESPACE, TMP_USED.SEGTYPE,

SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB

FROM V$SORT_USAGE TMP_USED,

(SELECT VALUE DB_BLOCK_SIZE

FROM V$PARAMETER

WHERE NAME = 'db_block_size') PARA

GROUP BY TMP_USED.TABLESPACE, TMP_USED.SEGTYPE) USED_TOT

where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)

GROUP BY TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE;

 

--表空间

set linesize 200;

col TABLESPACE_NAME for a30;

select a.TABLESPACE_NAME tbs_name,

round(a.BYTES/1024/1024) Total_MB,

round((a.BYTES-nvl(b.BYTES, 0)) /1024/1024) Used_MB,

round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) Pct_Free,

nvl(round(b.BYTES/1024/1024), 0) Free_MB ,

auto

from (select TABLESPACE_NAME,

sum(BYTES) BYTES,

max(AUTOEXTENSIBLE) AUTO

from sys.dba_data_files

group by TABLESPACE_NAME) a,

(select TABLESPACE_NAME,

sum(BYTES) BYTES

from sys.dba_free_space

group by TABLESPACE_NAME) b

where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)

order by ((a.BYTES-b.BYTES)/a.BYTES) desc

/

 

---查看数据文件物理IO信息

SELECT fs.phyrds "Reads",

fs.phywrts "Writes",

fs.avgiotim "Average I/O Time",

df.name "Datafile"

FROM v$datafile df, v$filestat fs

WHERE df.file# = fs.file#

/

 

--查看所有数据文件i/o情况

/*COL ts FORMAT a10 HEADING "Tablespace";

COL reads FORMAT 999990999;

COL writes FORMAT 999999990;

COL br FORMAT 999999990 HEADING "BlksRead";

COL bw FORMAT 9999999990 HEADING "BlksWrite";

COL rtime FORMAT 9999999990;

COL wtime FORMAT 9999999990;

set linesize 3000;

set pagesize 9999;*/

SELECT ts.name AS ts,

fs.phyrds "Reads",

fs.phywrts "Writes",

fs.phyblkrd AS br,

fs.phyblkwrt AS bw,

fs.readtim/100 "RTime*s",

fs.writetim/100 "WTime*s"

FROM v$tablespace ts, v$datafile df, v$filestat fs

WHERE ts.ts# = df.ts#

AND df.file# = fs.file#

UNION

SELECT ts.name AS ts,

ts.phyrds "Reads",

ts.phywrts "Writes",

ts.phyblkrd AS br,

ts.phyblkwrt AS bw,

ts.readtim /100 "RTime*s",

ts.writetim/100 "WTime*s"

FROM v$tablespace ts, v$tempfile tf, v$tempstat ts

WHERE ts.ts# = tf.ts#

AND tf.file# = ts.file#

ORDER BY 1;

--定位哪些object在buffer cache中存在，占用的buffer cache的量是多少以及占用的是什么类型的buffer cache。

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,

bh.object_name,bh.blocks

from x$kcbwds ds,x$kcbwbpd pd,(select set_ds,

o.name object_name,count(*) BLOCKS

from obj$ o, x$bh x where o.dataobj# = x.obj

and x.state !=0 and o.owner# !=0

group by set_ds,o.name) bh

where ds.set_id >= pd.bp_lo_sid

and ds.set_id <= pd.bp_hi_sid

and pd.bp_size != 0

and ds.addr=bh.set_ds

order by decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),bh.blocks;

 

--针对不同用户的占用buffer的合计

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,

own,sum(bh.blocks)*8192/1024/1024 used_M

from x$kcbwds ds,x$kcbwbpd pd,(select set_ds,

o.name object_name,count(*) BLOCKS,u.name own

from obj$ o, x$bh x,user$ u where o.dataobj# = x.obj

and x.state !=0 and o.owner# !=0 and o.owner#=u.user#

group by set_ds,o.name,u.name) bh

where ds.set_id >= pd.bp_lo_sid

and ds.set_id <= pd.bp_hi_sid

And pd.bp_size != 0

and ds.addr=bh.set_ds

group by decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',

4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',

7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN'),

own

order by own;

 

--buffer cache 对象所有者，名称， 类型，总大小，cache大小

column c1 heading "Object|Name" format a30 truncate

column c2 heading "Object|Type" format a12 truncate

column c3 heading "Number of|Blocks" format 999,999,999,999

column c4 heading "Percentage|of object|data blocks|in Buffer" format 999

break on report

compute sum of c3 on report

select owner,

object_name,

object_type,

num_blocks,

sum(blocks),

trunc((num_blocks / decode(sum(blocks), 0, .001, sum(blocks))), 4) * 100 || '%' -- buffer中的数据块比例

from (select o.owner owner_name,

o.object_name object_name,

o.object_type object_type,

count(1) num_blocks

from dba_objects o, v$bh bh

where o.object_id = bh.objd

and o.owner not in ('SYS', 'SYSTEM')

group by o.object_name, o.object_type, o.owner

order by count(1) desc) t1,

dba_segments s

where s.segment_name = t1.object_name

and s.owner = t1.owner_name

and num_blocks > 10

group by object_name, object_type, num_blocks, owner

order by num_blocks desc;

--10个热点对象

col objct_name for a30

select * from

(select

ob.owner, ob.object_name, sum(b.tch) Touchs

from x$bh b , dba_objects ob

where b.obj = ob.data_object_id

and b.ts# > 0

group by ob.owner, ob.object_name

order by sum(tch) desc)

where rownum <=10

/

--逻辑读

col objct_name for a30

select *

from (select owner, object_name, value

from v$segment_statistics

where statistic_name = 'logical reads'

order by value desc)

where rownum <= 10

/

--物理读最多十个对象

col objct_name for a30

select *

from (select owner, object_name, value

from v$segment_statistics

where statistic_name = 'physical reads'

order by value desc)

where rownum <= 10

/

 

---查看热点数据文件(从单块读取时间判断)

COL FILE_NAME FOR A30

COL TABLESPACE_NAME FOR A20

SELECT T.FILE_NAME,

T.TABLESPACE_NAME,

ROUND(S.SINGLEBLKRDTIM / S.SINGLEBLKRDS, 2) AS CS,

S.READTIM/100 READTIME_S,

S.WRITETIM/100 WIRTETIME_S

FROM V$FILESTAT S, DBA_DATA_FILES T

WHERE S.FILE# = T.FILE_ID

AND ROWNUM <= 10

ORDER BY CS DESC

/

----------------------------------------------------------------------------------------------------------------------------------

undo 与回滚段

---检查undo

show parameter undo_

 

---检查undo rollback segment 使用情况

select name, rssize, extents, latch, xacts, writes, gets, waits

from v$rollstat a, v$rollname b

where a.usn = b.usn

order by waits desc;

 

---每个事务产生的redo 块大小

select a.redoblocks / b.trancount

from (select value redoblocks

from v$sysstat

where name = 'redo blocks written') a,

(select value trancount from v$sysstat where name = 'user commits') b;

 

---计算每秒钟产生的undoblk数量

select sum(undoblks) / sum((end_time - begin_time) * 24 * 60 * 60)

from v$undostat;

 

---查询undo具体信息

COL undob FORMAT 99990;

COL trans FORMAT 99990;

COL snapshot2old FORMAT 9999999990;

SELECT t.BEGIN_TIME BEGIN_TIME,

t.END_TIME END_TIME,

undoblks "UndoB",

txncount "Trans",

maxquerylen "LongestQuery",

maxconcurrency "MaxConcurrency",

ssolderrcnt "Snapshot2Old",

nospaceerrcnt "FreeSpaceWait"

FROM v$undostat t;

 

--查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)

--COL RBS FORMAT a4;

SELECT n.name "RBS",

s.extends "Extends",

s.shrinks "Shrinks",

s.wraps "Wraps",

s.aveshrink "AveShrink",

s.aveactive "AveActive"

FROM v$rollname n

JOIN v$rollstat s

USING (usn)

WHERE n.name != 'SYSTEM';

 

---查询当前rollback segment使用情况

COL RBS FORMAT a4;

SELECT n.name "RBS",

s.status,

s.waits,

s.gets,

s.writes,

s.xacts "Active Trans"

FROM v$rollname n

JOIN v$rollstat s

USING (usn)

WHERE n.name != 'SYSTEM';

 

---查询使用rollback segment时等待比率

SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM

v$rollstat;