`
zqding
  • 浏览: 95927 次
  • 性别: Icon_minigender_1
  • 来自: 苏州
社区版块
存档分类
最新评论

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.

分享到:
评论

相关推荐

    \Oracle 表空间与数据文件

    通过以上内容,我们详细了解了Oracle中的表空间与数据文件的概念、分类以及它们在Oracle数据库存储结构中的作用,并学习了如何通过SQL查询来查看和管理这些表空间和数据文件。这对于管理和优化Oracle数据库的性能至...

    Oracle 表空间 收缩

    在开始任何操作前,我们首先需要查询表空间的当前使用情况。可以使用以下SQL语句来获取相关信息: ```sql SELECT total.tablespace_name, Round(total.MB,2) AS Total_MB, Round(total.MB-free.MB,2) AS Used_MB,...

    ORACLE 表空间的部分操作

    本文将详细介绍Oracle中关于表空间的一些关键操作,包括创建、修改、管理和删除表空间的过程,以及如何处理表空间的在线与离线状态,数据文件的管理,以及表空间的扩展策略。 #### 一、建立表空间 创建表空间是...

    oracle表空间常用操作

    oracle表空间常用操作,包括表空间剩余使用大小、名称及大小、物理文件的名称及大小 、回滚段名称及大小 、控制文件 、日志文件 、数据库的创建日期和归档方式 、创建表空间、修改表空间等操作,供大家参考。

    oracle 表空间创建

    Oracle 表空间创建是 Oracle 数据库中的一种基本操作,它允许用户创建一个或多个表空间,以存储数据库中的数据。表空间是 Oracle 数据库的基本存储单元,每个表空间都可以包含多个数据文件和索引文件。 在创建表...

    oracle查询表碎片

    ### Oracle查询表碎片知识点详解 在Oracle数据库管理与优化的过程中,了解并处理表碎片是非常重要的一个环节。本文将详细介绍如何通过SQL查询来检测Oracle表的碎片情况,并对给出的SQL查询语句进行深入解析。 ####...

    学习oracle创建一个表空间创建临时表空间创建用户表空间资源的权限

    在Oracle数据库系统中,表空间(Tablespace)是存储数据对象(如表、索引、视图等)的逻辑单位,而临时表空间(Temporary Tablespace)则用于存储临时数据,比如排序或联接操作产生的中间结果。创建和管理表空间及...

    oracle表空间操作详解

    Oracle 表空间操作详解 Oracle 表空间操作详解是数据库管理员在设计和管理 Oracle 数据库时的一项重要任务。通过表空间,可以控制用户对磁盘空间的使用,限制用户可以使用的磁盘空间大小,避免硬盘空间耗竭。 一、...

    oracle表空间命令语句大全

    Oracle 表空间命令语句大全提供了 Oracle 数据库管理的重要操作,包括建立表空间、建立 UNDO 表空间、建立临时表空间、改变表空间状态、删除表空间、扩展表空间和查看表空间信息等。这些命令对于 Oracle 数据库管理...

    Oracle多个表空间合并成一个表空间,验证通过

    ### Oracle 多个表空间合并成一个表空间详解 #### 背景介绍 在进行数据库维护时,可能会遇到需要将多个表空间合并为一个的情况。这种情况通常发生在新项目的开发过程中,尤其是当新项目需要引用来自其他表空间的...

    Oracle表空间操作命令

    本文介绍了一些基本的表空间操作命令,包括查看表空间信息、创建表空间以及调整表空间大小的方法。这些知识对于Oracle数据库管理员来说是非常实用的。理解并熟练掌握这些命令将有助于更好地管理和优化Oracle数据库...

    oracle temp表空间不足解决方法

    Oracle数据库系统中,Temp表空间是用于存储临时段的地方,主要在执行排序、连接和创建索引等操作时使用。当遇到"Oracle temp表空间不足"的问题时,这通常意味着数据库运行过程中产生的临时数据超过了现有Temp表空间...

    如何正确的删除Oracle表空间数据文件

    在Oracle数据库管理中,删除表空间数据文件是一项关键操作,涉及到数据的安全性和系统的稳定性。本文将详细介绍如何正确地删除Oracle表空间数据文件,探讨OFFLINE和OFFLINE DROP的区别,以及在操作系统层面删除数据...

    oracle 创建表空间命令

    在Oracle数据库管理系统中,创建表空间是管理数据库存储空间的关键操作。表空间是数据库中用于存储数据对象(如表、索引、视图等)的逻辑结构。它将物理磁盘上的一个或多个数据文件组织成一个逻辑单元,使得数据库...

    oracle定时删除表空间的数据并释放表空间

    在Oracle数据库管理中,定期清理和优化表空间是至关重要的任务,这有助于保持数据库的高效运行和合理利用存储资源。本文将深入探讨如何在Oracle环境中实现定时删除表空间的数据并释放空间,主要涵盖以下几个关键知识...

    查询一个Oracle表所占的物理空间大小

    2. **使用DBMS_SPACE.TOTAL_SPACE**:这是一个Oracle提供的包,可以用来查询表空间中使用的空间大小。例如: ```sql SELECT DBMS_SPACE.TOTAL_SPACE('FILE_LOG') AS total_space; ``` 3. **使用DBMS_SPACE.COUNT...

    oracle表空间详解

    Oracle 表空间是指一个逻辑概念,由若干个操作系统文件组成的。这些文件可以不是很大,但是它们组成了一个统一的存储空间。一个数据文件只能属于一个表空间,而一个数据库空间由多个表空间组成。 Oracle 中所有的...

    oracle创建表空间用户等

    查询表空间空闲空间 查询每个表空间的空闲空间大小: ```sql SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_space_mb FROM dba_free_space GROUP BY tablespace_name; ``` ##### 3. 调整数据文件大小 ...

Global site tag (gtag.js) - Google Analytics