oracle基础

一. 参数、视图等

1. 警报日志位置： show parameters background_dump_dest alert.log 日志

 

2. 跟踪日志文件： user_dump_dest

 

3. SGA 大小： show parameters sga_max_size; 或 sga_target

sga 各部分： show sga

PGA（程序合局区，服务器进程使用的包含数据和信息的内存，非共享）: 由 *_area_size 参数控制。

pga_aggregate_target：控制总共可用的pga

纯数据库服务器，oracle占用内存的大小大概为80%，pga设置为oracle占用内存的20%，也就是：总内存*80%*20%

 

4.各种视图 v$process: 进程视图

 

select * from v$session;  会话

select * from v$sgastat;  sga视图，还有 v$pgastat

v$sysstat

 

5.查找sql语句引起cpu高的问题的过程：

top查找哪个进程引起的

ps -ef|grep pid 查到是否本地还是远程用户  （local=no表示远程用户）

根据pid捕获有问题的语句：

 

select /*+ ORDERED */ sql_text

from v$sqltext a

where (a.hash_value,a.ADDRESS)in 

         (select decode(sql_hash_value,0, prev_hash_value,sql_hash_value),

                 decode(sql_hash_value,0,prev_sql_addr,sql_address)

          from v$session b

          where b.PADDR = (select addr from v$process c where c.spid='&pid'))

order by piece ASC;

（不太好用，要找更好的）

 

6.连接

左外连接：包括左边所有  A left join B on A.id=B.id

右外连接，类似

内连接即自然连接，inner join

一般不用加号，有较多限制

 

7. redo, undo

redo log记录执行的操作，使用改变向量（change vector）来记录，只记录操作代码、数据块地址等，记录量比实际执行语句少很多，能连续、顺序、快速地写出。

用户在 commit事务时，表示此时间点之前的 redo 写入了重做日志文件中而已，足以保证 提交成功的数据不会丢失。这

是 no-force-at-commit策略，也就是提交时不强制写。

由于ORACLE认为你一旦做数据更新，那么就意味着你要COMMIT（其他数据库不全是这种设计理念，比如DB2），所以在你更新数据的时候就做了大量的工作，在commit时所做工作并不多：产生SCN，写redo到磁盘，释放lock等。因此大事务和小事务commit时所花的时间差不多。

 

undo

读不阻塞写，写不阻塞读。

undo是为了回滚事务（rollback），也就是需要保存修改前数据的值，以便能够恢复到之前的状态。回滚的过程也会产生redo。

对于插入操作，回滚段记录的是 rowid。 update,记录的是被更新字段的旧值。delete记录的是整行的数据，因此delete的回滚是需要很长时间。

查看undo表空间大小：

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

 

查看回滚段的大小和状态

select usn,xacts,status,rssize/1024/1024 as "size(MB)",hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

 

 

归档日志：

1. 开启： alter database archivelog;

关闭： alter database noarchivelog;

必须在mount 模式下执行，startup mount

需重启数据库

2.查看： archive log list;

3. 删除：

rman target/

delete archivelog all completed before 'sysdate-2';  -- 删除2天之前的归档日志

delete archivelog from time 'sysdate-2';  -- 删除从两天前到现在的归档日志

 

 

等待事件、性能

所有等待事件：v$event_name

1.  v$session,  v$session_wait  v$sqltext

可以通过v$session_wait获取等待事件，得到等待时间最长的事件的 sid，在 v$session 根据 sid 查到 sql_hash_value, 根据 hash_value 在 v$sqltext 中查看相关语句。

2.  v$session_wait_history 可以查看之前活动session 的最近十个等待事件。

3. ASH : ash 是每一秒钟对 v$session 表进行采样得到的信息，但只记录活动的会话。使用的内存大小可以通过 v$sgastat 查询出来。内存写满了会覆盖前面的。

4. AWR ：采样工作由MMON后台进程进行，默认每60分钟把所有重要的统计信息和负载信息执行一次快照。ASH 信息是AWR信息的一部分。把ASH 10%左右的信息写到AWR负载库中。相关表以 WRH$ 开头，存储在 SYSAUX表空间。

5. v$system_event

可以查看各类事件的自数据库运行以来的总的等待时间。

关注 event, time_waited, wait_class 等字段。如果 wait_class是idle，是空闲等待，不需要看。

如 db file scattered read 事件一般表示全表扫描，等待时间很长的话，可能就是没有建索引了。

db file sequential read 事件： 一般表示读取索引或通过索引读取数据块，等待时间很长的话，表示索引有问题，或者多表链接顺序没有正确的使用驱动表，

direct path read/write: 直接路径读、写。是指绕过SGA，直接读写PGA。一般指磁盘排序IO操作、直接路径加载。要确定是否有过度排序的语句，或者增大临时文件使用的空间，如增大 pga_aggregate_target

 

 

6. v$session_longops: 查看花费时间长的事件

v$sql_plan:查看执行计费，可以通过 operation 、options字段查询匹配哪些sql语句（需关联到sql_text表）进行了全表、全索引的扫描。

 

 

性能诊断

1. 执行计划

set autotrace off | on | on explain | on statistics | traceonly

或者

explain plan for select * from dual;

@?/rdbms/admin/utlxplp;

有效地降低sql的逻辑读（consistent gets）是sql优化的基本 原则之一。

 

2. sql_trace

对sql进行跟踪，设置参数 timed_statistics 为 true； max_dump_file_size要足够大。

alter session set sql_trace=true;  (不能sysdba登录，会提示权限不够)

将会生成跟踪文件，用 tkprof 格式化跟踪文件，进行查看。

 

对特定的 session 进行 sql_trace:

获取session 的　sid　和　serial ,  select sid,serial#,username from $session;

exec dbms_system.set_sql_trace_in_session(sid, serial, true);

运行一段时间后关闭： exec dbms_system.set_sql_trace_in_session(sid, serial, false);

 

3. 10046事件 

有4个级别：level 1 相当于sql_trace,  level 4 相当于level 1 + 绑定值

level 8 相当于level 1 + 等待事件跟踪，  level 12 相当于 level 4 + level 8

全局设置：在参数文件中增加： event="10046 trace name context forever,level 12"

session: alter session set events '10046 trace name context forever";

或：alter session set events '10046 trace name context forever,level 8';

关闭：alter session set events '10046 trace name context off';

设定其它会话： 需要用到 sid, serial#, username（同2）

exec dbms_system.set_ev(sid, serial, 10046, level, username);

关闭： dbms_system.set_ev(sid, serial, 10046, 0, username);

经测试：10046事件实际打印的执行计划的信息没有sql_trace 详细

 

 

物化视图

视图的查询结果是执行视图定义的查询语句动态得到的。

而物化视图则使用中间表存储了视图定义时查询语句的结果，查询物化视图时就不需要再动态关联各个表执行查询了。

物化视图是典型的空间换时间。

什么时候使用物化视图：定义视图的查询语句较为复杂；where语句较为严格，查询结果的数据量比原来少很多；原表数据更新频率不高或物化视图不需与原表同步实时更新数据。这时使用物化视图可大大提高查询性能。