17、oracle 性能管理

jingjihui

　　17、oracle 性能管理
　　1、使用EM监控性能（内存指导）
　　emctl  start  dbconsole
　　性能监控：内存分配情况、应用程序代码问题、资源竞争(lock)、网络瓶颈
　　2、使用自动管理功能
　　自动内存管理（AMM） 包括SGA+PGA
　　自动共享内存管理ASMM
　　SQL> show parameter memory_target
　　NAME     TYPE VALUE
　　------------------------------------ ----------- ------------------------------
　　memory_target     big integer 6848M
　　表示使用自动内存管理。
　　oracle性能建议： v$fixed_table
　　SQL> select name from v$fixed_table where name like 'V$%ADVICE%';
　　NAME
　　------------------------------
　　V$SHARED_POOL_ADVICE
　　V$JAVA_POOL_ADVICE
　　V$STREAMS_POOL_ADVICE
　　V$PX_BUFFER_ADVICE
　　V$MEMORY_TARGET_ADVICE
　　V$SGA_TARGET_ADVICE
　　V$DB_CACHE_ADVICE
　　V$MTTR_TARGET_ADVICE
　　V$PGA_TARGET_ADVICE_HISTOGRAM
　　V$PGA_TARGET_ADVICE
　　10 rows selected.
　　例子1、查看对SGA_target有什么好的建议呢？
　　SQL> select SGA_SIZE,ESTD_DB_TIME,ESTD_PHYSICAL_READS from V$SGA_TARGET_ADVICE order by 1;
　　SGA_SIZE ESTD_DB_TIME ESTD_PHYSICAL_READS
　　---------- ------------ -------------------
　　1024    765       39129
　　1536    765       39129
　　2048    765       39129
　　2560    765       39129
　　3072    765       39129
　　3584    765       39129
　　4096    765       39129
　　4608    765       39129
　　5120    765       39129
　　5632    765       39129
　　6144    765       39129
　　6656    765       39129
　　7168    765       39129
　　7680    765       39129
　　8192    765       39129
　　因为测试数据库没有什么压力，也就是说随着SGA_SIZE值的增大，ESTD_PHYSICAL_READS物理读并没有减少！！！
　　3、查看性能相关的动态性能视图
　　累计统计信息：
　　(1)系统范围 v$sysstat
　　(2)会话：v$session
　　(3)特定服务：v$service_stats
　　等待事件：
　　(1)系统范围 v$system_event
　　(2)会话：v$session_event
　　(3)特定服务：v$service_event
　　排查故障及优化视图：
　　实例及数据库：
　　v$database
　　v$instance
　　v$parameter
　　v$spparameter
　　v$system_parameter
　　v$process
　　v$bgprocess
　　v$px_process_sysstat
　　v$system_event
　　磁盘：
　　v$datafile
　　v$filestat
　　v$log
　　v$log_history
　　v$dbfile
　　v$tempfile
　　v$tempseg_usage
　　v$segment_statistics
　　内存：
　　v$buffer_pool_statistics
　　v$libararycache
　　v$sgainfo
　　v$pgastat
　　争用：
　　v$lock
　　v$undostat
　　v$waitstat
　　v$latch
　　下面举例看看动态性能视图都是怎么用的呢？
　　假如有一个用户scott执行了一个死循环，消耗大量的资源，dba该怎么发现问题及解决问题呢？
　　scott用户死循环
　　SQL> declare
　　2  i number;
　　3  j number;
　　4  begin
　　5  i :=0;
　　6  loop
　　7  j := sqrt(i);
　　8  i :=i+1;
　　9  end loop;
　　10  end;
　　11  /
　　具体处理步骤：
　　(1)通过top命令查看进程号
　　PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
　　9025 oracle    25   0 7125m  34m  30m R 98.5  0.2   3:26.48 oracle
　　(2)通过 v$process 查看地址
　　SQL> select ADDR from v$process where spid=9025;
　　ADDR
　　----------------
　　000000020874B060
　　(3)通过地址查看那个session 用到v$session
　　SQL> select sid,serial#,sql_id from v$session where PADDR='000000020874B060';
　　SID    SERIAL# SQL_ID
　　---------- ---------- -------------
　　14  269 97h1c7dzrx9as

　　(4)通过sql>　　SQL> select SQL_TEXT from v$sqlarea where SQL_ID='97h1c7dzrx9as';
　　SQL_TEXT
　　--------------------------------------------------------------------------------
　　declare i number; j number; begin i :=0; loop j := sqrt(i); i :=i+1; end loop; e
　　nd;
　　终于找到sql内容了。
　　(5)kill掉
　　alter system kill session '14,269';

　　SQL>>
　　System>　　ERROR at line 1:
　　ORA-00028: your session has been killed
　　ORA-00028: your session has been killed
　　4、排除无效和不可用对象
　　重新编译pl/sql代码对象
　　重建索引
　　例子1、查看无效对象。
　　(1)
　　conn scott/tiger;
　　desc dba_dependencies;
　　create table e as select * from  emp;
　　create view v_e as select * from e;
　　create index i_e on e(empno);
　　(2)查看依赖关系：
　　SQL> select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependenci
　　es where owner='SCOTT';
　　OWNER   NAME       TYPE REFERENCED REFERENCED REFERENCED
　　---------- ---------- ---------- ---------- ---------- ----------
　　SCOTT   V_E      VIEW SCOTT    E       TABLE
　　现在drop table e purge;
　　(3)desc user_objects;
　　查看用户及对象及其状态信息
　　select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects order by 1,2;
　　OBJECT_NAM OBJECT_TYP STATUS
　　---------- ---------- ----------
　　A2   TABLE          VALID
　　A3   TABLE          VALID
　　BONUS   TABLE      VALID
　　DEPT   TABLE      VALID
　　EMP       TABLE      VALID
　　IDX$$_023A INDEX      VALID
　　0001
　　IDX_TEST_A INDEX      VALID
　　DVISOR_ID
　　PK_DEPT    INDEX      VALID
　　PK_EMP   INDEX      VALID
　　SALGRADE   TABLE      VALID
　　TEST_ADVIS TABLE      VALID
　　OR
　　V_E   VIEW       INVALID
　　12 rows selected.
　　因为table E被drop掉 因此其关联的视图V_E就不可以用了，即状态为INVALID。
　　例子2、表的默认空间被移动，表的索引失效，对oracle性能的影响