Oracle学习笔记

/ 技术文章默认分类 / 0 条评论 / 583浏览

SQL

Oracle 学习笔记

登陆

sqlplus sys as sysdba
sqlplus / as sysdba      (只要用户在Oracle 的组中，作为超级用户，不用密码)
sqlplus SYS/Wincor2008@192.168.1.164:1521 as sysdba

（Oracle SID 和 DB name 没有任何关系）

数据库优化，关心哪些指标？ set autot trace exp stat;

  1. 逻辑读 consistent gets (CPU 消耗)

buffer cache命中率 SQL> col name for a30 SQL> select statistic#,name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');

STATISTIC# NAME VALUE

    63 db block gets                      167326
    67 consistent gets                   1192052
    72 physical reads                      15800          越低越好

desc v$sga_target_advice Name Null? Type

SGA_SIZE NUMBER SGA_SIZE_FACTOR NUMBER ESTD_DB_TIME NUMBER ESTD_DB_TIME_FACTOR NUMBER ESTD_PHYSICAL_READS NUMBER

sys@QJS>select * from v$sga_target_advice;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

  4832             .25      1479857              1.8835           323533740
  7248            .375       812644              1.0343           276464163   性能提升10%以上，才会考虑改变内存
  9664              .5       804159              1.0235           204300597
 12080            .625       797952              1.0156           153500079
 14496             .75       788131              1.0031            69799456
 16912            .875       786088              1.0005            52506524
 19328               1       785695                   1            50623336
 21744           1.125       785302               .9995            49990544
 24160            1.25       785145               .9993            48319974
 26576           1.375       784988               .9991            46907583

sho parameter memo 查看配置参数

NAME TYPE VALUE

hi_shared_memory_address integer 0 memory_max_target big integer 3264M memory_target big integer 3264M shared_memory_address integer 0

sys 超过5%， 应用程序有问题

无法创建会话，增大processes数目 SQL> sho parameter process

NAME TYPE VALUE

aq_tm_processes integer 0 cell_offload_processing boolean TRUE db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150

transactions=(1.1 * SESSIONS) or transactions=(1.1 * ((1.5 * PROCESSES) + 22)) sessions=(1.5 * PROCESSES) + 22 ！隐含参数 千万不要修改！

闪回 select * from tablename as of timestamp systimestamp -5/(60*24); flashback table tablename to before drop;

sql loader 可以把一些以文本格式存放的数据顺利的导入到oracle数据库中， 是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。

08/16 表压缩： 适合查询频繁的表，不适合写入频繁的表 创建表时可以设置pctfree， 默认值为10%， 对于热点表，可以设置为20% 左右 模糊查询尽量带前缀，'A%' 在实际应用中，如果某个字段的值需要频繁更新，那么就不适合在它上面创建位图索引。 删除索引之前先置invisible，优化程序会忽略不可见的索引

set autot trace exp;

分区： 不提高性能，方便管理，一般10G一分区 分区可以跨存储

将一个表或索引物理地分解为多个更小、更可管理的部分。 1）区间分区：指定数据区间来决定数据存储在哪个分区。 2）散列分区：在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。（分区数应该是2的幂，这样才能让数据在各个分区均匀分布。） 3）列表分区：指定一个离散值集，来数据存储在哪个分区。 4）组合分区：区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。 5）间隔分区：和区间分区类似，但是可以在新数据到来时，如果不能放入已有分区，根据规则创建新的分区。

每个分区要有单独的索引，分区索引要建在分区页 否则删除分区后需要重建索引

索引可以单独建分区

Oracle表分区进行(DML)维护后对索引的影响的分析 对索引的几种情况分别进行总结： 1、如果删除分区，则会导致该表下所有的索引状态处于不可用状态（除本地前缀索引外）； 2、如果重命名表，同上 3、如果truncate分区数据，同上； 4、重命名分区，不会对索引产生任何影响。 解释： 对于分区表，索引有以下几种方式： 1、普通索引，与分区无关，就是平常的索引； 2、全局分区索引，独立于分区的索引，按照独立的分区方式分区。按照网上介绍的和个人的实践，这个方式查询效率不高； 3、本地前缀分区索引，即基于分区表的字段进行的分区，创建方式很简单，在普通索引建立的语句后面加个local即可，效率相对于普通索引高一点； 4、本地非前缀分区索引，即非基于分区表的分区字段，除非是特殊情况，这种方式效率较低，不推荐使用。 当索引处于不可用状态时，数据将不能插入，会提示：ora-01502:state unusuable。 且这种情下，只要表中有一个索引处于不可用状态，都会使表处于不可插入状态。 可以修改参数来实现这种情况下的数据插入问题：alter system set skip_unusable_indexes=true;

锁：

使用下面的sql语句区查看锁的信息 col kaddr heading 'lock|address' col sid heading 'session id' format 9999999 col sp_id for a10 col type heading 'lock|type' format a6 col id1 heading 'id1' format a36 col id2 heading 'id2' format 99999999 col lmode heading 'lock mode' format 99999999 col request heading 'req mode' format 99999999 col blocking_sid format 999999 heading 'blocked | sessid' set verify off select /*+ rule */a.kaddr,a.sid as sid,to_char(d.spid) as sp_id,a.type,decode(a.type,'TM',E.OBJECT_NAME,A.ID1) as id1 ,a.id2, a.lmode,a.request,a.block,b.sid blocking_sid from v$lock a,( select * from v$lock where request > 0 and type not in ('MR','AE','TO') ) b,v$session c,v$process d,dba_objects e where a.id1 = b.id1(+) and a.id2 = b.id2(+) and a.id1 = e.object_id(+) and a.lmode > 0 and a.type not in ('MR','AE','TO') and a.sid = c.sid and c.paddr = d.addr /

使用下面的sql语句可以杀掉所有持有锁的进程。 select 'kill -9 '||spid from v$process where addr in (select paddr from v$session where sid in ( select session_id from v$locked_object));

执行计划:

看懂执行计划，优化关注指标 顺序： 从上往下第一个没有子节点的action (并非最末叶子) 先执行兄弟项

set autot trace exp stat;

  1. 逻辑读 consistent gets (CPU 消耗)

交易时执行赋权或回收，可能失效

union all：不去重复,不排序 union: 去重复，排序 （消耗高） （Blue means has index） 原始： SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10; 优化： SELECT * FROM emp WHERE job = 'CLERK' UNION ALL SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';

条件传递 原始： SELECT * FROM emp, dept WHERE emp.deptno = 20 AND emp.deptno = dept.deptno; 优化： SELECT * FROM emp, dept WHERE emp.deptno = 20 AND emp.deptno = dept.deptno AND dept.deptno = 20;

08/23

Rowid scan: 访问Oracle 使用Rowid最快 Index scan: Oracle 自动为主键和唯一键创建一个索引 order by 成本高， 设计索引时，应去除索引中的order by，用复合索引代替 函数会使索引失效，可创建一个基于函数的复合索引

索引失效的情况： 函数； 参数隐性转换； 拼接表达式； 索引字段值为空

查询表中重复率>3%的field, 不会走索引

Guidelines for Managing Indexes • Create indexes after inserting table data. • Index the correct tables and columns. • Order index columns for performance. • Limit the number of indexes for each table. • Drop indexes that are no longer required. • Specify the tablespace for each index. • Consider parallelizing index creation. • Consider creating indexes with NOLOGGING. • Consider costs and benefits of coalescing or rebuilding indexes. • Consider cost before disabling or dropping constraints.

select * from emp where deptno in (1,2); select * from emp where deptno = 1 or deptno =2 ; 成本基本相同

Joins： alter session set optimizer_mode=first_rows_10; 优先返回前十行， oracle特有 alter session set optimizer_mode=first_allrows; 优先返回所有

统计信息： SELECT column_name AS "NAME", num_distinct AS "#DST", low_value, high_value, density AS "DENS", num_nulls AS "#NULL", avg_col_len AS "AVGLEN", histogram, NUM_BUCKETS AS "#BKT" FROM USER_TAB_COL_STATISTICS WHERE table_name = 'EVENTBASE';

运行以下语句，可收集直方图 exec dbms_stats.gather_table_stats('PV42','EVENTBASE',cascade=>true);

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2018/04/08 21:46