oracle运行情况总结.doc

netstat -tln 命令是Linux查看端口使用情况netstat an命令是windows查看端口使用情况一、查看执行sql还剩多少时间:select b.sql_text, -SQL内容a.MACHINE, -哪台机器运行的SQLa.USERNAME,-哪个用户运行的SQLa.MODULE,-是哪运行方式c.sofar/totalwork*100,-工作进行了百分之多少 c.elapsed_seconds, -己经用了多少时间(秒)c.time_remaining -还剩多少时间(秒)from v$session a, v$sqlarea b,v$session_longops cwhere a.sql_hash_value=b.HASH_VALUEand 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.STATUSFROM DBA_TABLESPACES T,DBA_DATA_FILES DWHERE T.TABLESPACE_NAME =D.TABLESPACE_NAMEORDER BY TABLESPACE_NAME,FILE_NAME;修改数据库文件sql：alter database datafile D:ORACLEORA_DATADATACORE_0003.ORA autoextend on next 100M maxsize 6G;alter database datafile D:ORACLEORA_DATAAMLM_0002.ORA resize 2048M;增加数据库文件sqlLalter tablespace tablespace_name add datafile D:ORACLEORA_DATAAMLM_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_free_space group by tablespace_name) bwhere a.tablespace_name = b.tablespace_nameorder 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) bwhere a.tablespace_name=b.tablespace_name;4. 下面这条语句查看所有segment的大小（相当于查看表使用的大小）。Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name5. 还有在命令行情况下如何将结果放到一个文件里。SQL spool out.txtSQL select * from v$database;SQL spool off6、查看表空间中分布的用户信息selecttablespace_name,owner,sum(bytes)fromdba_segments groupbytablespace_name,owner四、查看redo log文件及切换情况，通常切换时间为1530分钟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:ORACLEPRODUCT10.2.0ORADATAUPRRREDO03.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/ perties默认地址：http:/$IP:1158/em/consoleLinux下启动、停止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 db_file_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+PGAshow parameter sga;SQLALTER system SET sga_max_size=1504m scope=spfile;SQLALTER system SET sga_target=1504m scope=spfile;SQLshutdown immediate;SQLstartup;SQLshow parameter pga;SQLALTER 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_cpusql语句平均解析等待时间=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调整SQLshow parameter spfile；-查看是否是spfile启动SQL create spfile from pfile；-如果不是创建并重启数据库；SQL alter system set processes=1000 scope = spfile;SQL show parameter processes;SQLshutdown immediate;SQL startupSQL show parameter sessions;【注:sessions是个派生值,由processes的值决定,公式sessions=1.1*process + 5】SQLalter system set log_buffer= 16M scope=spfile(默认4M)SQLshow 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 NUMBERNAME VARCHAR2(80)TYPE NUMBERVALUE 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 FALSEFALSE问题：ORA-12519: TNS:no appropriate service handler found 的解决有时候连得上数据库,有时候又连不上.可能是数据库上当前的连接数目已经超过了它能够处理的最大值.SQL select count(*) from v$process;-当前连接数 COUNT(*) 63SQL select value from v$parameter where name = processes -数据库允许的最大连接数VALUE500修改最大连接数: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 bwhere a.sql_address =b.address order by cpu_time/executions desc;-快速删除不活动进程set heading offspool onselect p.SPID from v$session s,v$process p where s.paddr= p.addr and s.machine=woogle;spool offset serveroutput on ;declarev_sid number;v_serial number;v_sql varchar2(200) ;CURSOR cur_session is select sid, serial# from v$session where machine=woogle;beginopen cur_session ;fetch cur_session into v_sid , v_serial ;while cur_session%foundloopdbms_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/bashtmpfile=/tmp/tmp.$sqlplus / as sysdba create undo tablespace UNDOTBS2 datafile /opt/oracle/db02/app/oracle/oradata/ORCL/undotbs02.dbf size 3000m;SQL show parameter undoundo_management string MANUALundo_retention integer 3600undo_suppress_errors boolean FALSEundo_tablespace string UNDOTBS1SQL alter system set undo_management=auto scope=spfile;SQL alter system set undo_tablespace=UNDOTBS2 scope=spfile;SQL show user -用dba以上用户SQL shutdown immediateSQL startupSQL drop tablespace undotbs1;5. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。SQL select s.username, from v$transaction t,v$rollstat r,ITPUB个人空间 OkL15rxl 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,shrinks1Co(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,shrinksG cCt44 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表空间可能由于没有需要用到而不释放空间。(2) 我们可以用这个语句查一下SELECT se.username,sid,serial#,sql_address,machine,program,tablespace,segtype,contentsFROM v$session se,v$sort_usage suWHERE se.saddr=su.session_addr;其实v$session是会话信息，v$sort_usage是正在使用排序段的会话。可以检查当前数据库有没有语句正在用排序段，如果有的话可以使用一下语句删除寻找出的相关语句：SQLalter system kill session sid,serial#;(3) 检查temp表空间是不是自动扩展的SQLselect file_name,bytes/1024/1024 MB,autoextensible,tablespace_name from dba_temp_files;(4) 查询目前数据库中默认的临时表空间SQLselect * from database_properties where property_name like DEFAULT%;或SQLselect username,temporary_tablespace from dba_users;3处理过程(1) 重起数据库，看看有没有释放临时表空间Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。(2) 如果检查出temp表空间是自动扩展的，建议先修改成非自动扩展由于数据库temp表空间是自动扩展，有可能它不断使用新的空间，建议先把它变成非自动扩展。例如：alter database tempfile E:ORACLEPRODUCT10.2.0ORADATAUPRRTEMP01.DBF autoextend off;(3) 如果temp表空间不是自动扩展，而且空间比较小例如：数据库的temp表空间只有1.6G，觉得可以扩大一点，扩大3G左右（上千万的数据可以扩大到810G）SQL alter system temp add tempfile /u0/oradata/sm/temp02.dbf size 3096M;(4) 如果temp表空间由于自动扩展变得太大，可以新建一个temp表空间把它取代详细可参看eygle大师的Oracle9i中如何重建与切换临时表空间/archives/2006/04/oracle9i_default_temporary_tablespace.html例如：用temp2表空间取代temp表空间 SQL create temporary tablespace temp2 tempfile E:ORACLEPRODUCT10.2.0ORADATAUPRRTEMP02.DBF size 200M autoextend off;SQL alter database default temporary tablespace temp2;SQL drop tablespace temp;最后在操作系统上把temp的文件删除，就可以释放空间。如果为了更美观可以重新建temp表空间，删除temp2：SQL create temporary tablespace temp tempfile E:ORACLEPRODUCT10.2.0ORADATAUPRRTEMP01.DBF size 10240M autoextend off;SQL alter database tempfile E:ORACLEPRODUCT10.2.0ORADATAUPRRTEMP01.DBF autoextend on;SQL alter database default temporary tablespace temp;SQL drop tablespace temp2 including contents and datafiles; (5) 如果使用的版本是oracle 10g以上，可以收缩表空间SELECT se.username,sid,serial#,sql_address,machine,program,tablespace,segtype,contentsFROM v$session se,v$sort_usage suWHERE se.saddr=su.session_addr;其实v$session是会话信息，v$sort_usage是正在使用排序段的会话。可以检查当前数据库有没有语句正在用排序段，如果有的话可以使用一下语句删除寻找出的相关语句：SQLalter system kill session sid,serial#;SQLAlter tablespace TEMP coalesce;(6) 网上Metalink给出的一个方法修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。SQLalter tablespace temp increase 1; SQLalter tablespace temp increase 0;(7) 网上给出诊断事件的一种方法确定TEMP表空间的ts#SQL select ts#, name from sys.ts$ ; TS# NAME- - 0 SYSTEM 1 UNDOTBS1 2 TEMP 3 INDX 4 TOOLS 5 USERS 6 DKH_DATA 7 DKH_INDX 8 PHS_DATA 9 PHS_INDX10 rows selected.执行清理操作SQLalter session set events immediate trace name DROP_SEGMENTS level 3 ;说明：temp表空间的TS# 为 2*, So TS#+ 1= 3PS注意几个问题：(1) 在oracle中，temp表空间是独立于其他的表空间的(回滚表空间UNDOTBS，数据表空间DATATBS等)，因为它的数据是临时的，不用保存的所以查数据表都不一样：temp: dba_temp_files v$tempfile其他: dba_data_files v$datafile完成的说，整个数据库的大小是由 temp + 其他表空间大小，一般我们就只算其他表空间大小，例如在月报中(2) 建表空间不一样temp表空间：SQLcreate temporary tablespace temp2 tempfile /opt/oracle/oradata/conner/temp1.dbf size 10M autoextend off