关于oracle优化的策略等SQL

首先是日常的table的check是必然的--->>>>>

--查询表空间使用情况
　　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;

数据库里各个表空间的利用率

select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc

Oracle数据库表空间使用量查询：

 

select b.file_name 物理文件名,
 b.tablespace_name 表空间,
 b.bytes/1024/1024 大小M,
 (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
 substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
 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.bytes
 order by b.tablespace_name;

 

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

 

SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;

Oracle表空间利用率及对象大小查询

1. 查看所有表空间大小
 
SQL> select tablespace_name,sum(bytes)/1024/1024 M from dba_data_files group by tablespace_name;
 

2. 已经使用的表空间大小
 
SQL> select tablespace_name,sum(bytes)/1024/1024 M from dba_free_space group by tablespace_name;
 

3. 所以使用空间可以这样计算
 
select a.tablespace_name,total,free,total-free used from
 
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
 
   group by tablespace_name) a,
 
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
 
   group by tablespace_name) b  
 
where a.tablespace_name=b.tablespace_name;

 

 

这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。
select * from (select sql_text,sql_id,cpu_time from v$sql order by cpu_time desc) where rownum<=10 order by      rownum asc ;

select * from (select sql_text,sql_id,cpu_time from v$sqlarea order by cpu_time desc) where rownum<=10 order by rownum asc ;

select sid,event,p1,p1text from v$session_wait;等待事件

然后查看这些占用CPU资源很高的Oracle进程究竟是在做什么操作，使用如下SQL语句：

select sql_text,spid,v$session.program,process  from

v$sqlarea,v$session,v$process

where v$sqlarea.address=v$session.sql_address

and v$sqlarea.hash_value=v$session.sql_hash_value

and v$session.paddr=v$process.addr

and v$process.spid in (PID);

用top中占用CPU很高的进程的PID替换脚本中的PID,得到相应的Oracle进程所执行的SQL语句，发现占用CPU资源很高的进程都是执行同一个SQL语句：

SELECT d.domainname,d.mswitchdomainid, a.SERVICEID,a.SERVICECODE,a.USERTYPE,a.STATUS,a.NOTIFYSTATUS,to_char(a.DATECREATED,'yyyy-mm-dd hh24:mi:ss') DATECREATED,VIPFLAG,STATUS2,CUSTOMERTYPE,CUSTOMERID  FROM service a, gatewayloc b, subbureaunumber c, mswitchdomain d   WHERE b.mswitchdomainid = d.mswitchdomainid and b.gatewaysn = c.gatewaysn  AND a.ServiceCode like c.code||'%' and a.serviceSpecID=1 and a.status!='4' and a.status!='10'  and a.servicecode like '010987654321%' and SubsidiaryID=999999999

基本上可以肯定是这个SQL引起了系统CPU资源大量被占用，那究竟是什么原因造成这个SQL这么大量占用CPU资源呢，我们先来看看数据库的进程等待事件都有些什么：

SQL> select sid,event,p1,p1text from v$session_wait;

从上面的查询我们可以看出，大都是latch free的等待事件，然后接着查一下这些latch的等待都是什么进程产生的：

SQL> select spid from v$process where addr in

(select paddr from v$session where sid in(84,102,101,106,155,151));

由此看出latch free这个等待事件导致了上面的那个SQL语句都在等待，占用了大量的CPU资源。我们来看看究竟主要是那种类型的latch的等待，根据下面的SQL语句:

SQL> SELECT latch#, name, gets, misses, sleeps

     FROM v$latch

     WHERE sleeps>0

     ORDER BY sleeps;

由上面的查询可以看出最主要的latch等待是cache buffers chains，这个latch的等待表明数据库存在单独的BLOCK的竞争这些latch，我们来看这个latch存在的子latch及其对应的类型：

SQL> SELECT addr, latch#, gets, misses, sleeps

     FROM v$latch_children 

     WHERE sleeps>0         

     and latch# = 98  

     ORDER BY sleeps desc;

接着我们来查看sleep较多的子latch对应都有哪些对象：

SQL> select distinct a.owner,a.segment_name,a.segment_type from

     dba_extents a,

(select dbarfil,dbablk

from x$bh

where hladdr in

     (select addr

     from (select addr

     from v$latch_children

     order by sleeps desc)

     where rownum < 5)) b

where a.RELATIVE_FNO = b.dbarfil

and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;

我们看到在开始的那个SQL语句中的几个对象都有包括在内，于是来看看开始的那个SQL的执行计划：

SQL> set autotrace trace explain

SQL>SELECT d.domainname,d.mswitchdomainid, a.SERVICEID,a.SERVICECODE,a.USERTYPE,a.STATUS,a.NOTIFYSTATUS,to_char(a.DATECREATED,'yyyy-mm-dd hh24:mi:ss') DATECREATED,VIPFLAG,STATUS2,CUSTOMERTYPE,CUSTOMERID  FROM service a, gatewayloc b, subbureaunumber c, mswitchdomain d   WHERE b.mswitchdomainid = d.mswitchdomainid and b.gatewaysn = c.gatewaysn  AND a.ServiceCode like c.code||'%' and a.serviceSpecID=1 and a.status!='4' and a.status!='10'  and a.servicecode like '010987654321%' and SubsidiaryID=999999999;

 在AIX 5.3平台下oracle 10.2.0.3测试环境中，经常发现某些oracle进程占用很大的CPU资源，但这些进程并不存在v$session中，具体发现脚本如下：

      SQL>select spid, pid, program from v$process
                 where addr not in (select paddr from v$session)

 查看系统中发现该进程占用CPU比例很高，每个进程消耗CPU大约20%资源。

       以下是该问题的具体分析：

      SQL> alter session set events ' immediate trace name systemstate level 266'
  或者
      SQL>oradebug setospid 1962208
      SQL>oradebug short_stack    

 

 

 

ORACLE CPU利用率高的解决方案

1、      找到利用率高的进程号PID

2、      查看利用率高的进程在干什么

SQL>select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value and v$session.paddr=v$process.addr and v$process.spid in(PID);

3、      看看数据库的等待事件都有些什么

SQL>select sid,event,p1,p1text from v$session_wait;

看看等待事件由什么进程造成的

SQL>select spid from v$process where addr in(select paddr from v$session where sid in(84,102,101));

利用一下脚本可以由已知session的SID来获得SQL语句

SQL>select sql_text from v$sql_text a where a.hash_value=(select sql_hash_value from v$session b where b.SID=’&sid’) order by piect ASC;

4、如果是latch原因的话

I查看是哪种类型的latch

SQL>select latch#,name,gets,misses,sleeps from v$latch where sleeps>0 order by sleeps;

利用最主要的latch等待来查看它的子latch

SQL>select addr,latch#,gets,misses,sleeps from v$latch_children where sleeps>0 and latch#=98 order by sleeps desc;

接着查看sleep较多的子latch对应的对象

接下来执行刚开始查询出来的SQL语句

SQL>set autotrace trace explain

此时可以把这条语句在其他的数据库上也执行一下，对比两次不同的结果，找出问题的所在。

 

 

关于oracle的CPU的优化以及check也是必然要考虑的------->>>>>>>>>>>>>>>>>>>

本人很喜欢Oracle，在工作中也很喜欢总结关于Oracle优化CPU使用率的经验教训，下面就这个问题来详细说说吧。CPU是服务器的一项重要资源，服务器良好的工作状态是在工作高峰时CPU的使用率在90%以上。如果空闲时间Oracle优化CPU使用率就在90%以上，说明服务器缺乏CPU资源；如果工作高峰时Oracle优化CPU使用率仍然很低，说明服务器CPU资源还比较富余。

查看Oracle优化CPU使用率
使用操作相同命令可以看到CPU的使用情况，一般Unix操作系统的服务器，可以使用“sar u”命令查看Oracle优化CPU使用率，Windows NT操作系统的服务器，可以使用性能管理器来查看CPU的使用率。

查看数据库使用CPU的时间
可以通过查看V$SYSSTAT数据字典中“CPU used by this session”统计项得知Oracle数据库使用的CPU时间，查看“OS User level CPU time”统计项得知操作系统的用户态下CPU时间，查看“OS System call CPU time”统计项得知操作系统的系统态下CPU时间，操作系统总的CPU时间就是用户态和系统态时间之和。如果Oracle数据库使用的CPU时间占操作系统总的CPU时间90%以上，说明服务器CPU基本上被Oracle数据库使用着。反之，则说明服务器CPU被其它程序占用过多，Oracle数据库无法得到更多的CPU时间。
数据库管理员还可以通过查看V$SYSSTAT数据字典来获得当前连接Oracle数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多。出现CPU资源不足的情况是很多的，SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。

查看SQL语句的解析情况
数据库管理员可以执行下述语句来查看SQL语句的解析情况：
SELECT * FROM V$SYSSTAT 
WHERE NAME IN 
('parse time cpu', 'parse time elapsed', 'parse count (hard)');
这里“parse time cpu”是系统服务时间，“parse time elapsed”是响应时间，用户等待时间waite time = parse time elapsed - parse time cpu。
由此可以得到用户SQL语句平均解析等待时间＝waite time / parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句来发现是什么SQL语句解析效率比较低。
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA 
ORDER BY PARSE_CALLS;
程序员可以优化这些语句，或者增加Oracle参数SESSION_CACHED_CURSORS的值。以上介绍Oracle优化CPU使用率。

 

查看Oracle优化CPU使用率

使用操作相同命令可以看到CPU的使用情况，一般Unix操作系统的服务器，可以使用“sar u”命令查看Oracle优化CPU使用率，Windows NT操作系统的服务器，可以使用性能管理器来查看CPU的使用率。

查看数据库使用CPU的时间

可以通过查看V$SYSSTAT数据字典中b“CPU used by this session”统计项得知Oracle数据库使用的CPU时间，查看“OS User level CPU time”统计项得知操作系统的用户态下CPU时间，查看“OS System call CPU time”统计项得知操作系统的系统态下CPU时间，操作系统总的CPU时间就是用户态和系统态时间之和。

如果Oracle数据库使用的CPU时间占操作系统总的CPU时间90%以上，说明服务器CPU基本上被Oracle数据库使用着。反之，则说明服务器CPU被其它程序占用过多，Oracle数据库无法得到更多的CPU时间。

数据库管理员还可以通过查看V$SYSSTAT数据字典来获得当前连接Oracle数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多。出现CPU资源不足的情况是很多的，SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。

查看SQL语句的解析情况

数据库管理员可以执行下述语句来查看SQL语句的解析情况：

SELECT * FROM V$SYSSTAT  WHERE NAME IN  ('parse time cpu', 'parse time elapsed', 'parse count (hard)'); 
这里“parse time cpu”是系统服务时间，“parse time elapsed”是响应时间，用户等待时间waite time = parse time elapsed - parse time cpu。

由此可以得到用户SQL语句平均解析等待时间＝waite time / parse count。这个平均等待时间应该接近于0，如果平均解析等待时间过长，数据库管理员可以通过下述语句来发现是什么SQL语句解析效率比较低。

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA  ORDER BY PARSE_CALLS; 
程序员可以优化这些语句，或者增加Oracle参数SESSION_CACHED_CURSORS的值。以上介绍Oracle优化CPU使用率。

 

熟悉一下我的getsql.sql脚本

 

  SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value ), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')) ORDER BY piece ASC /
 

注意这里我们涉及了3个视图,并应用其关联进行数据获取.
首先需要输入一个pid,这个pid即process id,也就是在Top或ps中我们看到的PID.
通过pid和v$process.spid相关联我们可以获得Process的相关信息
进而通过v$process.addr和v$session.paddr相关联,我们就可以获得和session相关的所有信息.
再结合v$sqltext,我们即可获得当前session正在执行的SQL语句.

通过v$process视图，我们得以把操作系统和数据库关联了起来

可以用下面的命令将 cpu 占用率高的线程找出来:
ps H -eo user,pid,ppid,tid,time,%cpu,cmd --sort=%cpu

精简版：
ps -eL -o pid,%cpu,lwp|grep 8424 | sort