Oracle 表空间查询与操作

一。查询篇
[摘自]http://www.iteye.com/topic/82977

1.查询oracle表空间的使用情况

select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name

 

2.查询oracle系统用户的默认表空间和临时表空间

select default_tablespace,temporary_tablespace from dba_users

 

3.查询单张表的使用情况

select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and owner = USER

RE_STDEVT_FACT_DAY是您要查询的表名称

 

4.查询所有用户表使用大小的前三十名

select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30

 

5.查询当前用户默认表空间的使用情况

select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)
from
(
SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0))) as usecontent,sum(nvl(a.bytes,0)) as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent
FROM dba_free_space a,dba_data_files b
WHERE a.file_id=b.file_id and b.tablespace_name = (select default_tablespace from dba_users where username = user)
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
)
GROUP BY tablespacename

 

6.查询用户表空间的表

select * from user_tables

 

==================================================================================

一、建立表空间

CREATE TABLESPACE test
DATAFILE 'c:/oracle/oradata/db/test01.dbf' SIZE 50M
UNIFORM SIZE 1M; #指定区尺寸为128k,如不指定，区尺寸默认为64k

或

CREATE TABLESPACE test
DATAFILE 'c:/oracle/oradata/db/test01.dbf' SIZE 50M
MINIMUM EXTENT 50K EXTENT MANAGEMENT LOCAL
DEFAULT STORAGE (INITIAL 50K NEXT 50K MAXEXTENTS 100 PCTINCREASE 0);

可从dba_tablespaces中查看刚创建的表空间的信息

二、建立UNDO表空间

CREATE UNDO TABLESPACE test_undo
DATAFILE 'c:/oracle/oradata/db/test_undo.dbf' SIZE 50M

UNDO表空间的EXTENT是由本地管理的，而且在创建时的SQL语句中只能使用DATAFILE和EXTENT MANAGEMENT子句。

ORACLE规定在任何时刻只能将一个还原表空间赋予数据库，即在一个实例中可以有多个还原表空间存在，但只能有一个为活动的。可以使用ALTER SYSTEM命令进行还原表空间的切换。

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = test_undo;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE '/oracle/oradata/db/test_temp.dbf' SIZE 50M

查看系统当前默认的临时表空间
select * from dba_properties where property_name like 'DEFAULT%'

改变系统默认临时表空间
alter database default temporary tablespace test_temp;

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE test OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game test FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE test ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE test READ ONLY;

6.使表空间可读写

ALTER TABLESPACE test READ WRITE;

五、删除表空间

DROP TABLESPACE test INCL ING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

DROP TABLESPACE　表空间名 [INCL ING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]

1. INCL ING CONTENTS　子句用来删除段

2. AND DATAFILES　子句用来删除数据文件

3. CASCADE CONSTRAINTS　子句用来删除所有的引用完整性约束

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

1.增加数据文件
ALTER TABLESPACE test
ADD DATAFILE '/oracle/oradata/db/test02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE 'c:/oracle/oradata/db/test01.dbf'
RESIZE 100M;

3.设定数据文件自动扩展
ALTER DATABASE DATAFILE 'c:/oracle/oradata/db/test01.dbf'
AUTOEXTEND ON NEXT 100M
MAXSIZE 200M;

设定后可从dba_tablespace中查看表空间信息，从v$datafile中查看对应的数据文件信息

==================================================================================

 

create tablespace scgl
datafile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\scgl2.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;


create tablespace test_data
logging
datafile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\user_data.dbf'
size 50m

autoextend on

next 50m maxsize 20480m

extent management local;


create user scgl identified by qwer1234
default tablespace scgl
temporary tablespace scgl_temp;


tempfile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\user_temp.dbf'

create temporary tablespace scgl_temp
tempfile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\scgl_temp.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

grant connect,resource, dba to scgl;

oracle创建表空间 SYS用户在CMD下以DBA身份登陆：

在CMD中打sqlplus /nolog

然后再

conn / as sysdba

//创建临时表空间



create temporary tablespace user_temp

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;



//创建数据表空间

create tablespace test_data

logging

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;



//创建用户并指定表空间

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;
查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1

查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;

--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;

查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;


-----------------------------------------------------------------------------------------------------------------------------
1.建立表空间:create tablespace test datafile '/u01/test.dbf' size 10M uniform size 128k

#指定区尺寸为128k ,块大小为默认8K

#大文件表空间 create bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf ' size 100G

2.建非标准表show parameter db alter system set db_2k_cache_size=10M create tablespace test datafile '/u01/test.dbf' size 10M blocksize 2K uniform size 128k

#常见错误

SQL> alter system set db_2k_cache_size=2M; alter system set db_2k_cache_size=2M ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache

#解决

SQL> alter system set sga_max_size=400M scope=spfile; SQL> shutdown immediate; SQL> startup SQL> alter system set db_2k_cache_size=10M; System altered.

3.查看区大小与块大小#区大小 conn y / 123 create table t(i number) tablespace test; Insert into t values(10) select bytes/1024 from user_segments where segment_name=upper('t');

#块大小 Show parameter block(默认64K)

#非标准表空间的blocksize SQL> select * from v$dbfile; SQL> select name,block_size,status from v$datafile; SQL> select block_size from v$datafile where file#=14;

4.删除表空间drop tablespace test including contents and datafiles

5.查表空间:#查数据文件 select * from v$dbfile; #所有表空间 select * from v$tablespace;

#表空间的数据文件 select file_name,tablespace_name from dba_data_files;

6.建立undo表空间create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf' size 5M;

#切换到新建的undo表空间 alter system set undo_tablespace=undotbs01;

7.建立临时表空间create temporary tablespace temp_data tempfile '/u01/temp.db' size 5M; create bigfile temporary tablespace bigtem tempfile '/u01/bigtemp.db' size 5M;

8.改变表空间状态
(0.)查看状态

#表空间状态 select tablespace_name,block_size,status from dba_tablespaces;

#数据文件状态 select name,block_size,status from v$datafile;

(1.)表空间脱机alter tablespace test offline

#如果意外删除了数据文件 alter tablespace test offline for recover

(2.)表空间联机alter tablespace test online

(3.)数据文件脱机select * from v$dbfile; alter database datafile 3 offline

(4.)数据文件联机recover datafile 3; alter database datafile 3 online;

(5.)使表空间只读alter tablespace test read only

(6.)使表空间可读写alter tablespace test read write;

9.扩展表空间#首先查看表空间的名字和所属文件及空间 select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; #三种扩展方法

1.alter tablespace test add datafile '/u01/test02.dbf' size 10M(自动加一个datafile)

2.alter database datafile '/u01/test.dbf' resize 20M;

3.alter database datafile '/u01/test.dbf' autoextend on next 10M maxsize 1G;

#设定后查看表空间信息

select a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes*100)/a.bytes "% used",(c.bytes*100)/a.bytes "% free" from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;

10.移动表空间的数据文件

#先确定数据文件据在表空间

SQL>select tablespace_name,file_name from dba_data_files where file_name='/u01/test.dbf';

#open状态

SQL>alter tablespace test offline; SQL>host move /u01/test.dbf /u01/oracle/test.dbf; SQL>alter tablespace test rename datafile '/u01/test.dbf' to '/u01/oracle/test.dbf'; SQL>alter tablespace test offline;

#mount状态 SQL>shutdown immediate; SQL>startup mount SQL>host move /u01/test.dbf /u01/oracle/test.dbf; SQL>alter database rename file '/u01/test.dbf' to '/u01/oracle/test.dbf';

11.表空间和数据文件常用的数据字典与动态性能视图v$dbfile v$datafile dba_segments user_segments dba_data_files v$tablespace dba_tablespaces user_tablespaces

 

--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
--查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;


//给用户授予权限



grant connect,resource to username;



//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，

这就不用在每创建一个对象给其指定表空间了

撤权：

revoke 权限... from 用户名;

删除用户命令

drop user user_name cascade;

建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

删除表空间

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

一、建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

二、建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE temp_data

TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE game OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE game ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE game READ ONLY;

6.使表空间可读写

ALTER TABLESPACE game READ WRITE;

五、删除表空间

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

1.增加数据文件

ALTER TABLESPACE game

ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'

RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE

oracle数据库性能监控的SQL

　　转载:世纪易网 整理:playboy

　　1. 监控事例的等待

　　select event,sum(decode(wait_Time,0,0,1)) "Prev",

　　sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"

　　from v$session_Wait

　　group by event order by 4;

　　2. 回滚段的争用情况

　　select name, waits, gets, waits/gets "Ratio"

　　from v$rollstat a, v$rollname b

　　where a.usn = b.usn;

　　3. 监控表空间的 I/O 比例

　　select df.tablespace_name name,df.file_name "file",f.phyrds pyr,

　　f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw

　　from v$filestat f, dba_data_files df

　　where f.file# = df.file_id

　　order by df.tablespace_name;

　　4. 监控文件系统的 I/O 比例

　　select s str(a.file#,1,2) "#", s str(a.name,1,30) "Name",

　　a.status, a.bytes, b.phyrds, b.phywrts

　　from v$datafile a, v$filestat b

　　where a.file# = b.file#;

　　5.在某个用户下找所有的索引

　　select user_indexes.table_name, user_indexes.index_name,uniqness, column_name

　　from user_ind_columns, user_indexes

　　where user_ind_columns.index_name = user_indexes.index_name

　　and user_ind_columns.table_name = user_indexes.table_name

　　order by user_indexes.table_type, user_indexes.table_name,

　　user_indexes.index_name, column_position;

　　6. 监控 SGA 的命中率

　　select a.val + b.val "logical_reads", c.val "phys_reads",

　　round(100 * ((a.val+b.val)-c.val) / (a.val+b.val)) "B?R HIT RATIO"

　　from v$sysstat a, v$sysstat b, v$sysstat c

　　where a.statistic# = 38 and b.statistic# = 39

　　and c.statistic# = 40;

　　7. 监控 SGA 中字典缓冲区的命中率

　　select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",

　　(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"

　　from v$rowcache

　　where gets+getmisses <>0

　　group by parameter, gets, getmisses;

　　8. 监控 SGA 中共享缓存区的命中率，应该小于1%

　　select sum(pins) "Total Pins", sum(reloads) "Total Reloads",

　　sum(reloads)/sum(pins) *100 libcache

　　from v$librarycache;

　　select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"

　　from v$librarycache;

　　9. 显示所有数据库对象的类别和大??

　　select count(name) num_instances ,type ,sum(source_size) source_size ,

　　sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,

　　sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required

　　from dba_object_size

　　group by type order by 2;

　　10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%

　　SELECT name, gets, misses, immediate_gets, immediate_misses,

　　Decode(gets,0,0,misses/gets*100) ratio1,

　　Decode(immediate_gets+immediate_misses,0,0,

　　immediate_misses/(immediate_gets+immediate_misses)*100) ratio2

　　FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

　　11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size

　　SELECT name, val FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

　　12. 监控当前数据库谁在运行什么SQL语句

　　SELECT osuser, username, sql_text from v$session a, v$sqltext b

　　where a.sql_address =b.address order by address, piece;

　　13. 监控字典缓冲区

　　SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;

　　SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

　　SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;

　　后者除以前者,此比率小于1%,接近0%为好。

　　SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"

　　FROM V$ROWCACHE

　　14. 找ORACLE字符集

　　select * from sys.props$ where name='NLS_CHARACTERSET';

　　15. 监控 MTS

　　select busy/(busy+idle) "shared servers busy" from v$dispatcher;

　　此值大于0.5时，参数需加大

　　select sum(wait)/sum(totalq) "dispatcher waits" from v$q where type='dispatcher';

　　select count(*) from v$dispatcher;

　　select servers_highwater from v$mts;

　　servers_highwater接近mts_max_servers时，参数需加大

　　16. 碎片程度

　　select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name

　　having count(tablespace_name)>10;

　　alter tablespace name coalesce;

　　alter table name deallocate unused;

　　create or replace view ts_blocks_v as

　　select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space

　　union all

　　select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

　　select * from ts_blocks_v;

　　select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space

　　group by tablespace_name;

　　查看碎片程度高的表

　　SELECT segment_name table_name , COUNT(*) extents

　　FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name

　　HAVING COUNT(*) = (SELECT MAX( COUNT(*) FROM dba_segments GROUP BY segment_name);

　　17. 表、索引的存储情况检查

　　select segment_name,sum(bytes),count(*) ext_q n from dba_extents where

　　tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;

　　select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'

　　group by segment_name;

　　18、找使用CPU多的用户session

　　12是cpu used by this session

　　select a.sid,spid,status,s str(a.program,1,40) prog,a.terminal,osuser,val/60/100 val

　　from v$session a,v$process b,v$sesstat c

　　where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by val desc;


本文是关于Oracle数据库调试与优化方面的文章，主要介绍Oracle数据库中命中率相关的问题，包括不同的算法之间性能的比对。

　　关于Oracle中各个命中率的计算以及相关的调优

　　1)Library Cache的命中率:

　　.计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)

　　SQL>SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;

　　通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。

　　2)计算共享池内存使用率:

　　SQL>SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%' FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

　　其中: &TSP_IN_M是你的总的共享池的SIZE(M)

　　共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。

　　查询空闲的共享池内存:

　　SQL>SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

　　3)db b?r cache命中率:

　　计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]

　　SQL>SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$B?R_POOL_STATISTICS WHERE NAME='DEFAULT';

　　通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE

　　另外一种计算命中率的方法(摘自ORACLE官方文档<<数据库性能优化>>):

　　命中率的计算公式为:

　　Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets + consistent gets - physical reads direct - physical reads direct (lob))

　　分别代入上一查询中的结果值,就得出了B?r cache的命中率

　　SQL>SELECT NAME, VAL FROM V$SYSSTAT WHERE NAME IN('session logical reads', 'physical reads', 'physical reads direct', 'physical reads direct (lob)', 'db block gets', 'consistent gets');

　　4)数据缓冲区命中率：

　　SQL> select val from v$sysstat where name ='physical reads'; SQL> select val from v$sysstat where name ='physical reads direct'; SQL> select val from v$sysstat where name ='physical reads direct (lob)'; SQL> select val from v$sysstat where name ='consistent gets'; SQL> select val from v$sysstat where name = 'db block gets';

　　这里命中率的计算应该是

　　令 x = physical reads direct + physical reads direct (lob)

　　命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

　　通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

　　5)共享池的命中率：

　　SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;

　　假如共享池的命中率低于95%,就要考虑调整应用(通常是没使用bind var )或者增加内存

　　6)计算在内存中排序的比率:

　　SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';—查询内存排序数 SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';—查询磁盘排序数 --caculate sort in memory ratio SQL>SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM d l;

　　此比率越大越好,太小整要考虑调整,加大PGA

　　7)PGA的命中率:

　　计算公式:BP x 100 / (BP + EBP)

　　BP: bytes processed

　　EBP: extra bytes read/written

　　SQL>SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';

　　或者从OEM的图形界面中查看

　　我们可以查看一个视图以获取Oracle的建议值:

　　SQL>SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE; The output of this qry might look like the following: TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT ---------- -------------- -------------------- 63 23 367 125 24 30 250 30 3 375 39 0 500 58 0 600 59 0 700 59 0 800 60 0 900 60 0

　　在此例中:PGA至少要分配375M

　　我个人认为PGA命中率不应该低于50%

　　以下的SQL统计sql语句执行在三种模式的次数: optimal memory size, one-pass memory size, multi-pass memory size:

　　SQL>SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4)) percentage FROM (SELECT name, val cnt, (sum(val) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%');

　　8)共享区字典缓存区命中率

　　计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)

　　命中率应大于0.85

　　SQL>select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;

　　9)数据高速缓存区命中率

　　计算公式：1-(physical reads / (db block gets + consistent gets))

　　命中率应大于0.90最好

　　SQL>select name,val from v$sysstat where name in ('physical reads','db block gets','consistent gets');

　　10)共享区库缓存区命中率

　　计算公式：SUM(pins - reloads) / SUM(pins)

　　命中率应大于0.99

　　SQL>select sum(pins-reloads)/sum(pins) from v$librarycache;

　　11)检测回滚段的争用

　　SUM(waits)值应小于SUM(gets)值的1%

　　SQL>select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat;

　　12)检测回滚段收缩次数

　　SQL>select name,shrinks from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn; -----------------------------------------------------------------------------

　　几个常用的检查语句

　　1. 查找排序最多的SQL:

　　SQL>SELECT HASH_VAL, SQL_TEXT, SORTS, EXECUTIONS FROM V$SQLAREA ORDER BY SORTS DESC;

　　2.查找磁盘读写最多的SQL:

　　SQL>SELECT * FROM (SELECT sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" FROM v$sql WHERE executions>0 and is_obsolete='N' ORDER BY 4 desc) WHERE ROWNUM<11 ;

　　3.查找工作量最大的SQL(实际上也是按磁盘读写来排序的):

　　SQL>select s str(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes,p.sql_text from(select address,disk_reads,executions,pct,rank() over (order by disk_reads desc) ranking from (select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over () pct from sys.v_$sql where command_type != 47) where disk_reads > 50 * executions) s,sys.v_$sqltext p where s.ranking <= 5 and p.address = s.address order by 1, s.address, p.piece;

　　4. 用下列SQL工具找出低效SQL:

　　SQL>select executions,disk_reads,b?r_gets,round((b?r_gets-disk_reads)/b?r_gets,2) Hit_radio,round(disk_reads/executions,2) reads_per_run,sql_text From v$sqlarea Where executions>0 and b?r_gets >0 and (b?r_gets-disk_reads)/b?r_gets<0.8 Order by 4 desc;

　　5、根据sid查看对应连接正在运行的sql

　　SQL>select /*+ push_s q */command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,b?r_gets,rows_processed,sysdate start_time,sysdate finish_time,’>’||address sql_address,’N’status From v$sqlarea Where address=(select sql_address from v$session where sid=&sid); ***************Oracle 缓冲区命中率低的分析及解决办法******************

　　首先确定下面的查询结果:

　　1,缓冲区命中率的查询(是否低于90%):

　　select round((1 - sum(decode(name,'physical reads',val,0)) / (sum(decode(name,'db block gets',val,0)) + sum(decode(name,'consistent gets',val,0))) ),4) *100 || '%' chitrati from v$sysstat;

　　2,使用率的查询(有无free状态的数据快.):

　　select count(*), status from v$bh group by status ;

　　3,相关等待事件的查询(是否有相关等待事件)

　　select event,total_waits from v$system_event where event in ('free b?r waits');

　　4,当前大小(是否已经很大)

　　select val/1024/1024 cache_size from v$parameter where name='db_cache_size'

　　5,top等待事件分析(Db file scatered read的比率是否大)

　　select event ,total_waits,suml from (select event,total_waits,round(total_waits/sumt*100,2)||'%' suml from (select event,total_waits from v$system_event ), (select sum(total_waits) sumt from v$system_event) order by total_waits desc) where rownum<6 and event not like 'rdbms%' and event not like 'pmon%' and event not like 'SQL*Net%' and event not like 'smon%';

　　6,db_cache_advice建议值(9i后的新特性,可以根据他更好的调整cache_size)

　　select block_size,size_for_estimate,size_factor,estd_physical_reads from v$db_cache_advice;

　　说明分析:

　　缓冲区命中率(低于90的命中率就算比较低的).

　　没有free不一定说明需要增加,还要结合当前cache_size的大小(我们是否还可以再增大,是否有需要增加硬件,增加开销),

　　空闲缓冲区等待说明进程找不到空闲缓冲区，并通过写出灰缓冲区，来加速数据库写入器生成空闲缓冲区，当DBWn将块写入磁盘后，灰数据缓冲区将被释放，以便重新使用.产生这种原因主要是:

　　1，DBWn可能跟不上写入灰缓冲区：i/0系统较慢，尽量将文件均匀的分布于所有设备，

　　2，缓冲区过小或过大。

　　3，可以增加db_writer_processes数量。

　　4,可能有很大的一个事物，或者连续的大事物

　　我们需要长期观察这个事件是否长期存在并数值一直在增大,如果一直在增大,则说明需要增大db_cache大小.或优化sql.

　　数据分散读等待,通常表现存在着与全表扫描相关的等待，逻辑读时，在内存中进行的全表扫描一般是零散地，而并非连续的被分散到缓冲区的各个部分，可能有索引丢失，或被仰制索引的存在。该等待时间在数据库会话等待多块io读取结束的时候产生，并把指定的块数离散的分布在数据缓冲区。这意味这全表扫描过多，或者io不足或争用，

　　存在这个事件,多数都是问题的,这说明大量的全部扫描而未采用索引.

　　db_cache_advice对我们调整db_cache_size大小有一定的帮助,但这只是一个参考，不一定很精确。

　　通过上面6种情况的综合分析,判断是否需要增加大cache_size. 或者把常用的(小)表放到keep区。

　　但多数的时候做这些不会解决质的问题,

　　而真正的问题主要是对sql语句的优化(如:是否存在大量的全表扫描等)

　　索引是在不需要改变程序的情况下,对数据库性能，sql语句提高的最实用的方法.

　　我在生产中遇到过类似的问题,200M的cache_size,命中率很低21%,但通过对sql语句的优化(添加索引,避免全表扫描),命中率增加到96%,程序运行时间由原来的2小时减少到不到10分钟.

　　这就提到了怎么定位高消耗的sql问题.全表扫描的问题,在这里不做细致的解说,这里只说明方法,我会在相关的章节专门介绍怎么使用这些工具

　　1,sql_trace跟踪session.用tkprof 分别输出磁盘读,逻辑读,运行时间长的sql进行优化.这些高消耗的sql一般都伴随着全表扫描.

　　2,statspack分析.在系统繁忙时期进行时间点的统计分析,产看TOP事件是否有Db file scatered read.并查看TOP sql语句是否存在问题等.

　　注：电脑学习网首发。

　　还要说一句：当然在硬件允许的情况下，尽量增大db_cache_size 减少磁盘读，但并不是越大越好，一定要根据自己的库数据量的程度来调节，因为大的db_cache_size同样会增大数据库管理的开销，当然可能开销并不会明显的影响数据库的性能，硬件价格也越来越低，这就需要我们具体问题具体分析了，在我看来物尽其用就最好了，尽量不要浪费，找到问题的本质。调优是一件很艺术的事。

　　***********************Oracle数据库缓冲区命中率*****************

　　1、查看Oracle数据库缓冲区命中率

　　select a.val + b.val "logical_reads", c.val "phys_reads", round(100 * ((a.val+b.val)-c.val) / (a.val+b.val)) "B?R HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;

　　2、Tags: oracle

　　数据库缓冲区命中率：

　　sql>select val from v$sysstat where name ='physical reads'; val 3714179 sql>select val from v$sysstat where name ='physical reads direct'; val 0 sql>select val from v$sysstat where name ='physical reads direct(lob)'; val 0 sql>select val from v$sysstat where name ='consistent gets'; val 856309623 sql>select val from v$sysstat where name ='db block gets'; val 19847790

　　这里命中率的计算应该是

　　令x=physical reads direct + physical reads direct(lob)

　　命中率=100-(physical reads -x)/(consistent gets +db block gets -x)*100

　　通常如果发现命中率低于90%，则应该调整应用可以考虑是否增大数据加

　　共享池的命中率

　　sql> select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

　　如果共享池的命中率低于95%就要考虑调整应用(通常是没应用bind var)或者增加内存。

　　关于排序部分

　　sql> select name,val from v$sysstat where name like '%sort%';

　　如果我们发现sorts(disk)/(sorts(memory)+sorts(disk))的比例过高，则通常意味着sort_area_size部分内存教较小，可考虑调整相应的参数。

　　关于log_b?r

　　sql>select name,val from v$sysstat where name in ('redo entries','redo b?r allocation retries');

　　假如redo b?r allocation retries/redo entries的比例超过1%我们就可以考虑增加log_b?r.