碎片检查
select tablespace_namesqrt(max(blocks)/sum(blocks))*
(/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by ;
fsfi值越小碎片越大 自由空间碎片索引
检查reverse_key index
select oobject_name
from dba_objects o
where wner=DB_ACCT
AND OOBJECT_ID IN
(SELECT IOBJ# FROM SYSIND$ I
WHERE BITAND(IPROPERTY)=)
查具体后台进程号
select spid from v$session a v$process b where aPADDR=bADDR and sid=
查看死锁表
SELECT SID DECODE(BLOCK NO YES ) BLOCKER
DECODE(REQUEST NOYES ) WAITER
FROM V$LOCK
WHERE REQUEST > OR BLOCK >
ORDER BY block DESC;
查看剩余表空间
select atablespace_namefree/total* pct_freefree// free(M) from
(select sum(bytes) free tablespace_name from dba_free_space group by tablespace_name) a
(select sum(bytes) total tablespace_name from dba_data_files group by tablespace_name) b
where atablespace_name=btablespace_name
order by pct_free;
查看创建索引的进度
select sidmessage from v$session_longops where sid = order by start_time
查看缴费到帐
SELECT AREA_IDto_char(sysdateyyyymmdd hh:mi:ss)to_char(count()) as cnt FROM ACCT_PAY_INTERFACE
WHERE PAY_DATE>=sysdate and FLAG= group by AREA_Id
查看最消耗资源的sql
SELECT * FROM (SELECT PARSING_USER_IDEXECUTIONSSORTSCOMMAND_TYPE
DISK_READSsql_text FROM v$sqlareaORDER BY disk_reads DESC ) WHERE ROWNUM<
查看占用系统资源的进程号spid
SELECT ausernameamachineaprogramasidaserial#astatuscpiececsql_text
FROM v$session av$process bv$sqltext cWHERE bspid=AND baddr=apaddrAND asql_address=caddress(+)
ORDER BY cpiece
查看占用系统io较大的session
SELECT sesidseserial#prSPIDseusernamesestatusseterminalseprogram
seMODULEsesql_addresssteventstptextsiphysical_readssiblock_changes
FROM v$session sev$session_wait stv$sess_io siv$process pr WHERE stsid=sesid AND stsid=sisid
AND sePADDR=prADDR AND sesid>AND stwait_time= AND stevent NOT LIKE %SQL% ORDER BY physical_reads DESC
对检索出的结果的几点说明
我是按每个正在等待的session已经发生的物理读排的序因为它与实际的IO相关
你可以看一下这些等待的进程都在忙什么语句是否合理？
Select sql_address from v$session where sid=;
Select * from v$sqltext where address=;
执行以上两个语句便可以得到这个session的语句
你也以用alter system kill session sidserial#;把这个session杀掉
应观注一下event这列这是我们调优的关键一列下面对常出现的event做以简要的说明
abuffer busy waitsfree buffer waits这两个参数所标识是dbwr是否够用的问题与IO很大相关的当v$session_wait中的free buffer wait的条目很小或没有的时侯说明你的系统的dbwr进程决对够用不用调整free buffer wait的条目很多你的系统感觉起来一定很慢这时说明你的dbwr已经不够用了它产生的wio已经成为你的数据库性能的瓶颈这时的解决办法如下
a增加写进程同时要调整db_block_lru_latches参数
示例修改或添加如下两个参数
db_writer_processes=
db_block_lru_latches=
a开异步IOIBM这方面简单得多hp则麻烦一些可以与Hp工程师联系
bdb file sequential read指的是顺序读即全表扫描这也是我们应该尽量减少的部分解决方法就是使用索引sql调优同时可以增大db_file_multiblock_read_count这个参数
cdb file scattered read这个参数指的是通过索引来读取同样可以通过增加db_file_multiblock_read_count这个参数来提高性能
dlatch free与栓相关的了需要专门调节
e其他参数可以不特别观注
外部联接+的用法
外部联接+按其在=的左边或右边分左联接和右联接
若不带+运算符的表中的一个行不直接匹配于带+预算符的表中的任何行
则前者的行与后者中的一个空行相匹配并被返回若二者均不带+
则二者中无法匹配的均被返回利用外部联接+
可以替代效率十分低下的 not in 运算大大提高运行速度例如下面这条命令执行起来很慢
select aempno from emp a where aempno not in
(select empno from emp where job=SALE);
倘若利用外部联接改写命令如下:
select aempno from emp a emp b
where aempno=bempno(+)
and bempno is null
and bjob=SALE;
可以发现运行速度明显提高
如何更改UNDO tablespace
create undo tablespace undotbs datafile D:\oracle\product\\oradata\qa\undotbsdbf size M;
alter system set undo_tablespace=undotbs scope=both;
create pfile from spfile;
alter tablespace undotbs offline;
drop tablespace undotbs including contents;
将表改成
ALTER TABLE t_monitor_real_minute NOLOGGING;
Oracle RAC的参数文件和单实例参数文件不同所以修改参数文件时需要注意
首先设置归档路径
SQL> alter system set log_archive_dest=/opt/oracle/archive scope=spfile sid=*;
System altered
SQL> select sidnamevalue from v$spparameter where name=log_archive_dest;
SID NAME VALUE
* log_archive_dest /opt/oracle/archive
然后关闭两个实例启动实例更改数据库为归档模式
SQL> shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
SQL> startup mount;
ORACLE instance started
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
SQL> alter database archivelog;
Database altered
SQL> alter database open;
Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archive
Oldest online log sequence
Next log sequence to archive
Current log sequence
接下来启动另外一个节点完成归档模式的变更过程
