oracle运行情况总结.docx

netstat -tln 命令是Linux查看端口使用情况
netstat –an命令是windows查看端口使用情况

一、查看执行sql还剩多少时间:
select
b.sql_text, --SQL内容
a.MACHINE, --哪台机器运行的SQL
a.USERNAME,--哪个用户运行的SQL
a.MODULE,--是哪运行方式
c.sofar/totalwork*100,--工作进行了百分之多少
c.elapsed_seconds, --己经用了多少时间(秒)
c.time_remaining --还剩多少时间(秒)
from v$session a, v$sqlarea b,v$session_longops c
where a.sql_hash_value=b.HASH_VALUE
and a.sid=c.sid and a.SERIAL#=c.SERIAL#

监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by address, piece;

二、查看数据库文件情况（大小、是否扩展等）
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

修改数据库文件sql：
alter database datafile ' D:\ORACLE\ORA_DATA\DATACORE_0003.ORA' autoextend on next 100M maxsize 6G;

alter database datafile 'D:\ORACLE\ORA_DATA\AMLM_0002.ORA' resize 2048M;

增加数据库文件sqlL
alter tablespace tablespace_name add datafile 'D:\ORACLE\ORA_DATA\AMLM_0002.ORA' size 1024M;

三、查看表空间使用情况及版本
查看oracle版本：
SELECT * FROM PRODUCT_COMPONENT_VERSION;

查看Oracle数据库表空间的占有率
select a.tablespace_name,
       round((a.maxbytes / 1024 / 1024), 2) "sum MB",
       round((a.bytes / 1024 / 1024), 2) "datafile MB",
       round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB",
       round(( (a.maxbytes-a.bytes+b.bytes) / 1024 / 1024), 2) "free MB",
       round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes,sum(maxbytes) maxbytes
          from dba_data_files where maxbytes!=0
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_freespace
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.maxbytes) desc;

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
这个命令可以查看当前用户下所有表占用的空间大小。
还有在命令行情况下如何将结果放到一个文件里。

1. 查看所有表空间大小

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

2. 已经使用的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
2 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;

4. 下面这条语句查看所有segment的大小（相当于查看表使用的大小）。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

5. 还有在命令行情况下如何将结果放到一个文件里。
SQL> spool out.txt
SQL> select * from v$database;
SQL> spool off

6、查看表空间中分布的用户信息
select tablespace_name, owner,sum(bytes) from dba_segments   
group by tablespace_name, owner   

四查看redo log文件及切换情况，通常切换时间为15~30分钟
select * from v$logfile;
select * from v$log;

修改redo log文件大小：
alter system checkpoint;
alter system switch logfile;
alter database drop logfile group 3;
alter database add logfile group 3 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\UPRR\REDO03.LOG') size 200M reuse;

五、碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)> 10;

六、em的地址查看及em和isqlplus启动及关闭：
查看地址：/oracle/oracle10g/sysman/config/ emd.properties
默认地址：http://$IP:1158/em/console
Linux下启动、停止EM和isqlplus：
su - oracle
【oracle# 】emctl stop dbconsole
【oracle# 】emctl start dbconsole
【oracle# 】isqlplusctl sotp;
【oracle# 】isqlplusctl start；
附注:
Q: 在命令行方式下，如何查看CPU、内存的使用情况，网络流量和磁盘I/O？
A: 在命令行方式下，
1. 查看CPU使用情况的命令
""每5秒刷新一次，最右侧有CPU的占用率的数据
$ vmstat 5
""top 然后按Shift P，按照进程处理器占用率排序
$ top

2. 查看内存使用情况的命令
""用free命令查看内存占用情况
$ free
""top 然后按Shift M, 按照进程内存占用率排序
$ top

3. 查看网络流量
""能够用工具iptraf工具
$ iptraf -g
""针对某个Interface的网络流量能够通过比较两个时间网络接口的RX和TX数据来获得
$ date; ifconfig eth1
$ date; ifconfig eth1

4. 查看磁盘i/o
""用iostat查看磁盘/dev/sdc3的磁盘i/o情况，每两秒刷新一次
$ iostat -d x /dev/sdc3 2
""用vmstat查看io部分的信息
$ vmstat 2
七、内存参数调整
64位linux，64位oracle10g，64位websphere6.1,；服务器内存8G的情况下配置：
ALTER SYSTEM SET open_cursors = 3000 SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 256M SCOPE=SPFILE;

ALTER SYSTEM SET db_cache_size = 50M SCOPE=SPFILE;
ALTER SYSTEM SET dbfile_multiblock_read_count = 256 SCOPE=SPFILE;
ALTER SYSTEM SET java_pool_size = 1024M SCOPE=SPFILE;
ALTER SYSTEM SET large_pool_size = 768M SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size = 3G COMMENT='internally adjusted' SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size = 1024M SCOPE=SPFILE;
--ALTER SYSTEM SET sort_area_size = 1000M SCOPE=SPFILE;
--ALTER SYSTEM SET undo_retention = 10800 SCOPE=SPFILE;

------------------------------------------------------------------------------------------------------------------------------
在32位的操作系统中，Oracle最大可用内存为1.75g，也就是说SGA+PGA<=1.75g，超过这一限制的内存将不会被Oracle用到(测试及分配1400M、300M)
SQL>show parameter sga;
SQL>ALTER system SET sga_max_size=1504m scope=spfile;
SQL>ALTER system SET sga_target=1504m scope=spfile;
SQL>shutdown immediate;
SQL>startup;

SQL>show parameter pga;
SQL>ALTER system SET pga_aggregate_target=256m;

内存参数的调整主要是oracle的sga的调整，sea主要由共享池、数据缓冲区、日志缓冲区三部分组成。

1、共享池有两部分组成：共享sql区和数据字典缓冲区。
查看共享sql区得使用率,应该在90%以上，否则需增加共享池的大小。
select (sum(pins-reloads))/sum(pins) "lib cache" from v$librarycache;

select sum(pins) "请求存取数",sum(reloads) "不命中数",sum(reloads)/sum(pins) from v$librarycache;
其中，pins,显示在库高速缓存中执行的次数;reload,显示在执行阶段库高速缓存不命中的数目，一般sum(reloads)/sum(pins)的值应接近于零.如果大于1%就应该增加shared_pool_size的值, 来提高数据字典高速缓存可用的内存数量,减少不命中数.

查看数据字典缓冲区的使用率，应该在90%以上，否则需要增加共享池的大小。
select (sum(gets-getmisses-usage-fixed))/sum(gets) "row cache" from v$rowcache;

2、数据缓冲区的使用命中率=1-(physical reads/(db block gets+consistent gets)),应该在90%以上，否则需增加数据缓冲区大小。
select name, value from v$sysstat where name in('db block gets','consisten gets','physical reads');

3、日志缓冲区失败率=requests/entries,应该接近0，否则说明日志缓冲区太小，需增加。
select name,value from v$sysstat where name in ('redo entries','redo log space requests')

4、刷共享池

alter system flush shared_pool;---是小块的内存合并成大块的内存，暂时恢复性能

----------
5、临时表空间的调整技巧如下:
如果即使在稳定的状态下也存在很多的排序扩展锁（sort extent pool latch）的竞争，应该通过修改临时表空间的default storage 字句的next值来增大扩展块的大小。如果存在很多的排序扩展锁（sort extent pool latch）的竞争并且这种等待是由于过多的并发的排序造成的，应该增大 sort_area_size参数的大小，以便更多的派讯能保存在内存中，建议让扩展块的大小和sort_area_size参数相同

----
6、查看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_hard

---
7、查看低效率的sql语句 优化有助于提高cpu的利用率
select buffer_gets,executions,sql_text from v$sqlarea;

-------------
8、通过v$system_event的latch free查看数据库冲突情况，如果没有冲突则latch free无结果值。如果冲突太大则可降低spin_count参数值来消除高的cpu使用率。

八、索引、磁盘及表空间调整
将物理磁盘分多个盘，不同的数据文件放不同的磁盘上课提高速度。
将不同的表放不同的表空间里（多建几个表空间）可提高速度。
将索引和表分别放到不同的表空间里可提高速度。
（其实就是I/O调优的一种方式。）

九、log_buffer 、processes及session调整
SQL>show parameter spfile；--查看是否是spfile启动
SQL> create spfile from pfile；--如果不是创建并重启数据库；
SQL> alter system set processes=1000 scope = spfile;
SQL> show parameter processes;
SQL>shutdown immediate;
SQL> startup

SQL> show parameter sessions;
【注:sessions是个派生值,由processes的值决定,公式sessions=1.1*process + 5】

SQL>alter system set log_buffer= 16M scope=spfile(默认4M)
SQL>show parameter log_buffer;

问题：ORA-02095: 无法修改指定的初始化参数
 alter system set log_buffer= 16777216 scope=spfile;（不能用M直接用字节ok）
环境 ：11gR2
 alter system set sessions=250 scope=both。
提示无法修改制定的初始化参数。
alter system set sessions=250 scope=spfile；
提示系统参数已修改。
这种情况下，重新启动数据库才会生效。这涉及到oracle中有些参数可以动态修改，有些参数不能动态修改，如何具体哪些参数可动态修改，哪些参数不可以动态修改，通过v$parameter 视图可以清晰查看。
SQL> desc v$parameter;
 名称                                      是否为空? 类型
 -------------- -------- --------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 ISBASIC                                            VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER
其中name 标示参数名字，isses_modifiable标示是否可以会话级别修改，如何是false，则不可以动态修改，true则可以动态修改。issys_modifiable标示是否系统级别修改，false则不可修改true则可动态修改。
SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='
sessions';
NAME                 ISSES      ISSYS_MOD
--------------------  -----           ---------
sessions             FALSE      FALSE
问题：ORA-12519: TNS:no appropriate service handler found 的解决

有时候连得上数据库,有时候又连不上.
可能是数据库上当前的连接数目已经超过了它能够处理的最大值.
SQL> select count(*) from v$process;--当前连接数
     COUNT(*)
     63
SQL> select value from v$parameter where name = 'processes' --数据库允许的最大连接数
VALUE
500

修改最大连接数:
SQL> alter system set processes = 1000 scope = spfile;

重启数据库:
SQL> shutdown immediate;
SQL> startup;

--查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
--快速删除不活动进程
set heading off
spool on
select p.SPID from v$session s,v$process p where s.paddr= p.addr and s.machine='woogle';
spool off
set serveroutput on ;
declare
v_sid number;
v_serial number;
v_sql varchar2(200) ;
CURSOR cur_session is
   select sid, serial# from v$session where machine='woogle';
begin
open cur_session ;
fetch cur_session into v_sid v_serial ;
while cur_session%found
loop
dbms_output.put_line(v_sid||' killed!') ;
v_sql:= 'alter system kill session '||''''||v_sid||','||v_serial||'''';
execute immediate v_sql ;
fetch cur_session into v_sid , v_serial ;
end loop ;
close cur_session ;
end ;
/

Linux 下快速删除不活动进程
#!/bin/bash

tmpfile=/tmp/tmp.$$

sqlplus ' / as sysdba' << EOF

set heading off
spool on
spool $tmpfile
select p.SPID from v$session s,v$process p where s.paddr= p.addr and s.STATUS='SNIPED';
spool off
set serveroutput on ;
declare
v_sid number;
v_serial number;
v_sql varchar2(200) ;
CURSOR cur_session is
   select sid, serial# from v$session where STATUS='SNIPED';
begin
open cur_session ;
fetch cur_session into v_sid , v_serial ;
while cur_session%found
loop
dbms_output.put_line(v_sid||' killed!') ;
v_sql:= 'alter system kill session '||''''||v_sid||','||v_serial||'''';
execute immediate v_sql ;
fetch cur_session into v_sid , v_serial ;
end loop ;
close cur_session ;
end ;
/
问题：ORA-01552: cannot use system rollback segment for non-system tablespace 'AMLM’的解决

那就新建一个undo表空间，并弃用原先的：

SQL> create undo tablespace UNDOTBS2 datafile '/opt/oracle/db02/app/oracle/oradata/ORCL/undotbs02.dbf' size 3000m;

SQL> show parameter undo
undo_management                      string      MANUAL
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_management=auto scope=spfile;
SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;
SQL> show user --用dba以上用户
SQL> shutdown immediate
SQL> startup
SQL> drop tablespace undotbs1;

5. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。
SQL> select s.username, u.name from v$transaction t,v$rollstat r,ITPUB个人空间 Ok"L1[5rx@[l
    v$rollname u,v$session s where s.taddr=t.addr and
)x&^8H+Gw_9i0    t.xidusn=r.usn and r.usn=u.usn order by s.username;
6. 检查UNDO Segment状态；
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
1C@o(V#tP ea0    from v$rollstat order by rssize;

7. SQL> create undo tablespace undotbs1 datafile '/opt/oracle/oradata/uprr/undotbs01.dbf' size 3072M reuse autoextend on next 800m maxsize 16G;
8. 动态更改spfile配置文件；
SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.
9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE；
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinksITPUB个人空间bXG?y'[
  from v$rollstat order by rssize;
10. 再执行看UNDO表空间所有UNDO SEGMENT ONLINE；
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
G cCt~"\4[4` hr0ez0  from v$rollstat order by rssize;
11. 删除原有的UNDO表空间；
SQL> drop tablespace undotbs2 including contents;
Tablespace dropped.
12. 确认删除是否成功；
SQL> select name from v$tablespace;

十、调整参数sort_area_size
常常遇到这样的问题：某某参数应改怎么设，设置多大合适？其实很多情况下只能给出个大概的估计值。例如参数sort_area_size，太小，排序时是会访问磁盘，影响性能，太大，浪费内存空间。那么设多大合适呢？
使用如下脚本：
SQL> select A.Value Disk_Sorts,
          B.Value Memory_Sorts,
           ROUND(100*A.Value/
           DECODE((A.Value+B.Value),0,1,(A.Value+B.Value)),2)
               Pct_Disk_Sorts
      from V$SYSSTAT A, V$SYSSTAT B
     where A.Name = 'sorts (disk)'
       and B.Name = 'sorts (memory)';
DISK_SORTS MEMORY_SORTS PCT_DISK_SORTS
---------- ------------ --------------
         1         1200            .08
当然我的只是一个例子，你应改从你自己的在用数据库查询，例如设置一个限值，例如10％,如果磁盘排序占总排序的10％，则应改增大sort_area_size， 如果1占的比例太小，是否应改考虑减小sort_area_size。
ps: 一直以为sort_area_size的空间在会话连接时就会分配，但看一些资料各不相同。还得细心琢磨。
我觉得对于这个建议，在Oracle9i可以借鉴为调整pga大小。9i以上用pga_aggregate_target参数控制，sort_area_size参数无效。

十一、TEMP表空间调整
在优化SQL语句的基础上，加大内存，适当调整Sort_area_size的大小，因为Oracle优先使用内存排序，内存不够才使用磁盘中的临时表空间，对于超大临时表空间可以删除这个超大临时表空间，重新创建一个合适大小的，不可自动扩展。
一般初始的临时表空间都很小，当系统运行的时候，临时表空间会自动扩展，但是使用率一直都会非常高，所以，主要看一下 dba_temp_files 里面 autoextensible（是否字段扩展）和 maxbytes以及磁盘空间是否足够，只要临时表空间支持扩展，且磁盘空间足够，一般都不会出问题的。如果你希望使用率降低，那就 resize 临时表空间的文件，将文件修改大一点，比如 20G 以上。
1、 查看临时表空间信息
select * from dba_temp_files;

--查看临时表空间使用率
select tablespace_name,user_bytes,bytes,round(user_bytes/bytes,6) as pct from dba_temp_files;

--查看临时表空间数据文件的大小
select file_name,bytes/1024/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;
或者select tablespace_name, bytes/power(1024,3) as pct from dba_temp_files;（单位G）
2、检查过程
(1) 我们可以查查数据库alert日志
例如：/u0/admin/sm/bdump/alert_sm.log
数据库中并没有报temp表空间的错，一般来说不报错，可以先不管，因为没有temp表空间可能由于没有需要用