oracle 常用查询语句
一、一般日常用的脚本
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 * from dba_scheduler_running_jobs a ;
BEGIN
DBMS_SCHEDULER.STOP_JOB(job_name => 'P_USR_BET_STAT_AG_REBA__DD',force => TRUE);
END;
/
--查看表的信息
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 like '%TABLE%') table_G,
(select sum(bytes)/1024/1024/1024 from dba_segments where SEGMENT_TYPE like '%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 upgradeORACLE instance started. Total System Global Area 1241513984 bytesFixed Size 8792248 bytesVariable Size 788531016 bytesDatabase Buffers 436207616 bytesRedo Buffers 7983104 bytesDatabase mounted.Database opened.SQL>SQL> alter database local undo off; Database altered.SELECT name FROM v$system_parameter
WHERE ispdb_modifiable = 'TRUE'ORDER BY name;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;
查看表空间数据文件是否自动扩展情况：
WITH D1 AS
(SELECT B.TABLESPACE_NAME,
ROUND(SUM(B.BYTES / 1024 / 1024 / 1024), 2) USERD_G
FROM DBA_FREE_SPACE B
GROUP BY B.TABLESPACE_NAME),
D2 AS
(SELECT TABLESPACE_NAME,
SUM(MAX_EXTENDED_G) AS MAX_EXTENDED_G,
SUM(TOTAL_G) AS TOTAL_G
FROM (SELECT TABLESPACE_NAME,
AUTOEXTENSIBLE AS "EXTEND",
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) "FREE_EXTEND_G",
ROUND(SUM(BYTES) / SUM(MAXBYTES), 4) * 100 "USERD_RATE%"
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'YES'
GROUP BY TABLESPACE_NAME, AUTOEXTENSIBLE
UNION
SELECT TABLESPACE_NAME,
AUTOEXTENSIBLE AS "EXTEND",
ROUND(SUM(USER_BYTES / 1024 / 1024 / 1024), 2) "TOTAL_G",
ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) "MAX_EXTENDED_G",
ROUND(SUM((BYTES - USER_BYTES) / 1024 / 1024 / 1024), 2) "FREE_EXTEND_G",
ROUND(SUM(USER_BYTES) / SUM(BYTES), 4) * 100 "USERD_RATE%"
FROM DBA_DATA_FILES
WHERE AUTOEXTENSIBLE = 'NO'
GROUP BY TABLESPACE_NAME, AUTOEXTENSIBLE)
GROUP BY TABLESPACE_NAME )
SELECT D2.TABLESPACE_NAME,
D2.MAX_EXTENDED_G,
D2.TOTAL_G,
D1.USERD_G,
round((D1.USERD_G/D2.MAX_EXTENDED_G*100),2) AS "RATE%"
FROM D2,D1
WHERE D2.TABLESPACE_NAME(+) = D1.TABLESPACE_NAME
ORDER BY 5 DESC;
--查看表空间剩余
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
/
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列得到不能归档的原因
8.查看DG详细信息和状态
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
9.查看备库已经应用的历史归档日志
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY where rownum=1 order by 2 desc;
10.查看备库应用过的归档日志和正在应用的日志
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
六、修改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;
