oracle运行情况总结.docx

《oracle运行情况总结.docx》由会员分享，可在线阅读，更多相关《oracle运行情况总结.docx（41页珍藏版）》请在第一文库网上搜索。

1、oracle运行情况总结netstat -an命令是windows查看端口使用情况一、查看执行Sql还剩多少时间：selectb.sql-tt, -SQL 内容a.MACHINE,哪台机器运行的SQLa.USERNAME,-哪个用户运行的SQLa.MODULE,一是哪运行方式c.sofartotalwork*100,-工作进行了百分之多少c.elapsecLSeCOnds,-己经用了多少时间(秒)c.time_remaining -还剩多少时间(秒)from v$session a, v$sqlarea b,v$SeSSior1OngoPS cwhere a.sql_hash_value=b.

2、HASH_VALUEand a.sid=c.sid and a.SERIAL#=c.SERIAL#监控当前数据库谁在运行什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqItext b where a.sql_address = b.address order by address, piece;二、查看数据库文件情况(大小、是否扩展等)SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.ATOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATSFROM DBAJABLE

3、SPACES TDBA_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 IOOM maxsize 6G;alter database datafile D:ORACLEORA_DATAAMLM_0002.ORA resize 2048M;增加数据库文件sqlLalter tablespac

4、e tablespace_name add datafile D:ORACLEORA_DATAAMLM_0002.ORA size 1024M;三、查看表空间使用情况及版本查看oracle版本：SELECT * FROM PRODUCT_COMPONENT_VERSION;查看Oracle数据库表空间的占有率select a.tablespac-name,round(a.maxbytes / 1024 / 1024), 2) sum MB,round(a.bytes /1024/ 1024), 2) datafile MB,round(a.bytes - b.bytes)/ 1024/1024

5、), 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_usedfrom (select tablespace_name, sum(byts) bytes,sum(maxbytes) maxbytesfrom dba_data_files where maxbytes!=0 group by tablespace_name) a,(select tablespac-name3 sum(by

6、tes) bytes, max(bytes) largestfrom dba_free_spacegroup 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 .查看所有

7、表空间大小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_space2 group by tablespace_name;3 .因此使用空间能够这样计算select a.tablespace_name,total5free,total-free used from(select tablespace_name,

8、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 .下面这条语句查看所有SegITlent的大小(相当于查看表使用的大小)。Select Segment_Name,Sum(bytes)/1024/1024 From U

9、ser_Extents Group By Segment_Name5 .还有在命令行情况下如何将结果放到一个文件里。SQL spool out.txtSQL select * from v$database;SQL spool off6、查看表空间中分布的用户信息select tablespace_name, owner,sum(bytes) from dba_segmentsgroup by tablespace_name, owner四、查看redo log文件及切换情况，通常切换时间为1530分钟select * from v$IogfiIe;select * from v$log;修改

10、redo log文件大小：alter system checkpoint;alter system switch logfile;alter database drop logfile group 3;alter database add logfile group 3 (D:ORACLEPRODUCT1020ORADATAUPRRREDO03.LOG) size 200M reuse;五、碎片程度select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_namehaving co

11、unt(tablespace_name) 10;六、em的地址查看及em与isqlplus启动及关闭：查看地址：/oracIe/oracIe 10gsysman/config/ emd.propertiesLinUX下启动、停止EM与isqlplus： su - oracle oracle# emctl stop dbconsole oracle# emctl start dbconsole oracle# isqlplusctl sotp; oracle# isqlplusctl start；附注：Q:在命令行方式下，如何查看CPU、内存的使用情况，网络流量与磁盘I/O?A:在命令行方式下，

12、1 .查看CPU使用情况的命令“每5秒刷新一次，最右侧有CPU的占用率的数据$ vmstat 5top然后按Shift P,按照进程处理器占用率排序$ top2 .查看内存使用情况的命令.用free命令查看内存占用情况$ freetop然后按Shift M,按照进程内存占用率排序$ top3 .查看网络流量“能够用工具iptraf工具$ iptraf -g“针对某个Interface的网络流量能够通过比较两个时间网络接口的RX与TX数据来获得$ date; ifconfig ethl$ date; ifconfig ethl4 .查看磁盘i/o“用iostat查看磁盘devsdc3的磁盘i/o

13、情况，每两秒刷新一次$ iostat -d -X devsdc3 2“用vmstat查看io部分的信息$ vmstat 2七、内存参数调整64位Iinux, 64位oraclel0g, 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 d

14、b_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

15、SET sort_area_size = IOOOM SCOPE=SPFILE;-ALTER SYSTEM SET undo_retention = 10800 SCOPE=SPFILE;在32位的操作系统中，OraCIe最大可用内存为L75g,也就是说SGA+PGA 300M)SQLshow 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;内存参数的调整要紧是OraCIe的Sga的调整，Sea要紧由共享池、数据缓冲区、日志缓冲区 三部分构成