ORACLE基础小结

1.ORACLE诊断错误的三种类型的文件 ，打开SQL_TRACE的功能
2.动态性能视图：数据来源 ， 可以从那张表中查询所有的动态性能试图
3.控制文件：a.每个数据库至少有两组控制文件，并且在不同的硬盘上，在mount时候只读列在参数文件
            最前面的一个， 无论什么时候，只要数据库发现任何一个不可用了，数据库立即shutdown
   b.修改参数文件，添加控制文件
         通过什么查看控制文件                                                                 （v$CONTROLFILE ,show parameter）
   
联机重做日志文件： 1.当发生log switch时候， 伴随着 checkpoint 也发生
                   2.手动log switch    alter system switch logfile
       3. checkpoint       alter system checkpoint
                   4.一个回复的时间    FAST_START_MTTR_TARGET
       5.添加log_file。 添加组和 成员
       6relocate, Rename的一般步骤 回想一下oracle的启动顺序
       7.清空redolog  和避免redo归档
                   8.相关的视图 v$log, v$logfile, v$log_history
             
表空间和数据文件 ：
1.数据字典管理表空间 变成 local方式 DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); 
2.Undo表空间：CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M  
3.临时表空间: CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradta/temp01.dbf' SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;   
4.当系统表空间是local方式的时候，必须指定一个独立的temporary 表空间
5.改变默认临时表空间: alter databse default temporary tablespace  default_tmp2;
6.临时表空间不能被删除，不能离线
7.只读模式： alter tablespace xxxx READ ONLY  (引起一个checkpoint, Objecs can be dropped)
8.离线表空间 ALTER TABLESPACE userdata OFFLINE  ALTER TABLESPACE userdata ONLINE 
9.改变尺寸 ALTER DATABASE DATAFILE '/u01/oradata/userdaa0.dbf' RESIZE 200M 
10.移动表空间： ALTER TABLESPACE userdate RENAME DATAFILE '/u01/oradata/allan/1.dbf' TO '/u01/radata/allan/2.dbf' 
                ALTER DATABASE RENAME FILE '/u01/oradata/system01.dbf' TO '/u01/oradata/system02.dbf' 
11.得到表空间相关的信息： DBA_TABLESPACE, v$TABLESPACE,DBA_DATA_FILES,v$DTATAFILE, DBA_TEMP_FILES, v$TEMPFILE

存储和关系：
1.DB_BLOCK_SIZE 和 DB_nK_CACHE_SIZE关系
2.ASSM      SEGMENT SPACE MANAGEMENT AUTO 
3. 手动管理:PCTFREE,PCTUSED,FREELIST
4.一些性能视图： DBA_EXTENTS, DBA_SEGMENTS,DBA_TABLESPACES,DBA_DATA_FILES,DBA_FREE_SPACE

UNDO:
1.空间管理：UNDO_MANAGEMENT  //推荐设置为auto  
2.增加，删除，修改 ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS 
3.一个数据库只能有一个活跃的undo表空间
4.v$transaction  其中　USED_UBLK 说明该事务使用的undo 数据块
5.show parameter undo_retention ;  带来表空间大小的计算问题   select sum(undoblks) /sum((end_time-begin_time)*24*3600) from v$undostat
6. 为了减少ORA-1555事件  ，引入undo retiontion参数，同时要考虑表空间问题
7.相关的动态性能视图及数据字典：DBA_ROLLBACK_SEGS　　//所有的segment
                                V$ROLLNAME //在线的segment信息
        V$ROLLSTAT //在线的segment统计信息
        V$UNDOSTAT V$SESSION  V$TRANSACTION

TABLE：
1.ROWID
2.row of structure:  存储结构是变长的 Row header+Column length+ Column value
3.以后我们都只用自动化管理，所以一般只用考虑pctfree, initrans这两个参数
4.临时表： 并不马上分配空间，直到有数据插入的时候才分配。
           空间从哪里来？ 从自己属于的临时表空间
     也产生微量 undo,redo 信息
     表结构定义了其他都能看见，数据生存期：在事务 或 session级别
5.得到表的定义语句： select dbms_metadata.get_ddl('TABLE','TableName') from dual; 
6.Truncate Table:
7.ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOING 1000 
   

索引：
1.考虑放到不同的表空间，INITRANS  应该比corresponding tables 多
2.rebuild index  off line  on line
3.分析索引：ANALYZE INDEX indexName VALIDATE STRUCTURE;
             SELECT height,name,lf_rows,lf_blks,del_lf_rows FROM index_stats;
4.找出没有使用中的索引
    a. ALTER INDEX ht.dept_id_idx MONITORING USAGE
 b.ALTER INDEX hr.dept_id_idx  NOMONITORING USAGE
 c.结果在  v$object_usage
5.索引相关的信息：
   1.DBA_INDEXES, DBA_IND_COLUMNS, V$OBJECT_USAGE
6.validate, enable 的关系
   select constraint_name, status ,validated from user_constraints;
7.Immediate Deferred关系,Deferred 并不是不检查，只是说在commit 的时候检查
8.FOREIGN　KEY:  DELETE ON ACTION ,  DELETE CASCADE , DELETE SET NULL
9.alter table t enable validate primary key exceptions into  exceptions;
  不符合约束规范的就进入到 exceptions中
 
 
USER:
1.$OPS意义，配额
２.相关信息：DBA_USERS，DBA_TS_QUOTAS

权限：
1.system 和 object权限
2.有赋权限和收回权限的权利： gran any privilege   or  ADMIN OPTION
3.PUBLIC　group
4.查看自己有哪些权限 select * from session_privs;
5.07_DICTINARY_ACCESSIBILITY
6.create session,Create any proceduere ,  execute any procedure 权限的危害
7.系统权限撤销没有传递性，　object权限　有传递性
8. Privileges Inform:
  DBA_SYS_PRIVS
  SESSIN_PRIVS
  DBA_TAB_PRIVS    Object privilege
  DBA_COL_PRIVS   Object privilege
  role_role_prive 查询角色中有哪些角色
  role_sys_privs 角色中有哪些系统权限
  role_tab_privs: 角色中有哪些object权限
9.角色
10.审计

插入数据：
1.　Parallel Direct-Path INSERT 　分区表和非分区表的方式
2.Direct-Path INSERT NOLOGGING 选项无效　,要用表　或表空间的log状态

全球化
1.是按字符长度 还是 字节长度
2.主字符集 AL32UTF8 ,  国际字符集 AL16UTF16 ，   主字符集一般 不用AL16UTF16
3.NLS_LANGUAGE
4.nlssort(name,'xxxx');

连接：
动态listener:  不需要 listener.ora
  需要配置两个参数： SERVICE_NAMES=DB_DOMAIN.DB_NAME
                     INSTANCE_NAME=SID
  其中 DB_NAME, DB_DOMAIN可以用show parameter 查看
静态listener:  需要listener.ora
共享服务器模式的两个参数： DISPATCHERS, SHARED_SERVERS
相关的信息：　v$CIRCUIT
v$SARED_SERVER
v$DISPATCHER
v$SHARED_SERVER_MONITOR,v$QUEUE,v$SESSION

恢复：
V$FAST_START_SERVERS: Provides information about all the recovery slaves performing parallel transaction recovery
V$FAST_START_TRANSACTIONS: Contains information about the progress of the transactions that Oracle is recovering

归档：
1.如何使数据库处在归档模式
2.LOG_ARCHIVE_START ：手动还是自动归档
3.归档进程最大数量: LOG_ARCHIVE_MAX_RPOCESSED，如果负荷重归档进程就会自动加大
4.ALTER SYSTEM ARCHIVE LOG CURRENT 
  ALTER SYSTEM ARCHIVE LOG START
  ALTER SYSTEM ARCHIVE LOG STOP (如果OG_ARCHIVE_START  =true,系统也会重启arcn )
5.相关参数： 
   LOG_ARCHIVE_DEST_n (MANDATORY OPTIONAL)
   LOG_ARCHIVE_FORMAT
   LOG_ARCHIVE_MIN_SUCCEED_DEST
   LOG_ARCHIVE_DEST_STATE_n (DEFER || ENABLE )
6.相关视图：
   V$ARCHIVD_LOG ，V$ARCHIVE_DEST，V$LOG_HISTORY，V$DATABASE，V$ARCHIVE_PROCESSES，

 RMAN  RECOVER
 1.参数：CONTROL_FILE_RECORD_KEEP_TIME
   相关命令：CONFIGUR DEFAULT DEVICE TYPE TO sbt;
             CONFIGURE DEVICE TYPE DISK PARALLELISM 3
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT='/BACKUP/RMAN/%U'
             CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
2.冷备：目标机器必须在 mounted
  热备：表空间不要在 backup模式
3.Backup Set  , Backpiece区别
4.CONFIGURE CONTROLFILE AUTBACKUP  自动备份控制文件
5.backup和copy的区别

User-Managed Backup

得到DB File 的相关信息：  V$DATAFILE , v$CONTROLFILE ,v$LOGFILE ,DBA_DATA_FILES
V$BACKUP得到备份的相关试图

ALTER DATABASE BACKUP CONTROLFILE TO 'control1.bkp'
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/con/cxxx.sql'

1.View V$RECOVER_FILE to datermine which datafiles ndd recovery. 如果控制文件是备份的控制文件，这个信息估计不准确
2.View V$ARCHIVED_LOG for a list of all archived redo logfiles for the database.
3.View V$RECOVERY_LOG for a list of all archived redo log files required for recovery

调优工具：
1.如果要使日志文件记录Checkpoint,则必须设置 LOG_CHECKPOINTS_TO_ALERT=true参数
2.Statspack 的使用  To collect timing information , set TIMED_STATISTICS=True;
3.相关视图： a.V$session_event: Waits for an event for each session that had to wait;
             b.V$session_wait: Waits for an event for current active sessions that are waiting
             c.V$system_event: Total waits for an event, all sessions together

4.检查物理读写文件：select f.phyrds, f.phywrts, d.name from v$datafile d, v$filestat f  where d.file#=f.file# order by d.name;   
5.DB_FILE_MULTIBLOCK_READ_COUNT:   进行全表扫描，一次读的数据库块数 ，受底层操作系统限制
6.全表扫描次数：select name , value from v$sysstat where name like '%table scan%';  
7.完成过程：select sid, serial#,opname, to_char(start_time,'hh24:MI:SS') as "START" , (sofar/totalwork)*100 AS PERCENT_COMPLETE from v$session_longops; 

Shared Pool
1.大小由 SHARED_POOL_SIZE 决定，有三部分组成:Library cache , Data dictionary cache,UGA(存放session,如果配置Large pool则放在 large pool)
2.SGA_TARGET 决定SGA 的大小
3.V$SGASTAT  cursor_sharing  
4.select * from v$sysstat where name like '%parse count (hard)%'; 
5.select namespace, gets, gethits,pins, pinhits,reloads, invalidations from v$librarycache;
  select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;
 
6.v$SQL_SHARED_CURSOR: 为什么木有发生SQL共享的语句
7.select sql_text,users_executing,executions,loads from v$sqlarea;  查询正在跑的SQL
8.select shared_pool_size_for_estimate as pool_size ,estd_lc_size,estd_lc_time_saved from v$shared_pool_advice; 
9. SHARED_POOL_RESERVED_SIZE   一般不用调 保存空间的大小  见识shard_pool_size的 10%
10. ALTER SYSTEM FLUSH SHARED_POOL; //清空SHARED_POOL ，已PIN的不影响
11.pin 常用的SQL  在shared_pool
12.Use v$rowcache to obtain information about the data dictionary cache

13.select namespace, gets, gethits,pins, pinhits,reloads, invalidations from v$librarycache;

LARGE POOL的作用：
1.DBWR_IO_SLAVES
2.Backup and restore opertions
3.Session memory  for  shared servers
4.Parallel query messaging

大小由 large_POOL_SIZE决定

select * from v$sgastat where POOL like 'large%'

Buffer Cache
1.数据在buffer Cache的四种状态: Pinned,Clean, Free/unused ,Dirty
2.db_cache_advice=on 时 v$db_cache_advice 这个动态试图 才有数据
3.三个指标：Free Buffer Inspected  Free Buffer Waits  Buffer Busy Waits
     select name,value from V$sysstat where name like '%free buffer in%';
     select event, total_waits FROM v$system_event where event in('free buffer waits','buffer busy waits'); 
     select name,parameter1,parameter2,parameter3 from v$event_name where name='buffer busy waits'; 
4.计算命中率： 11g
   select name,value from v$sysstat where name in ('db block gets from cache','consistent gets from cache','physical reads cache');
   1-(('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache'))
  
   也可以从这里算： select name , 1-(physical_reas/(db_block_gets+consistent_gets)) "HIT_RATIO" FROm V$buffeer_pool_statistics where  db_block_gets_consistent_gets>0
  
  
5.DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE , DB_RECYCLE_CACHE_SIZE（v$cache）
6.Cach Table:  select /*+CACHE*/ ， Create table .... cache,  alter table ... cache 三种方式， 一般放在keep区里
     原因：  进行全表扫描的时候 一般都会放在LRU list的末端  被淘汰出去，但是我们希望能在buffer里多留一段时间，因为这些表都是小表，而且常用，所以就有  Caching Talbes的由来  和 pin不一样， Cache table只是保证被淘汰出去的概率少点
7.sga_max_size   , DB_WRITER_PROCESSES
8.v$buffer_pool

Sizing Other SGA structures
1.Granule 是内存分配的最小单位，  SGA size <=128M ,  granule size=4MB 
                                  SGA size> 128M     granule size=16MB
2.LOG_BUFFER : 512K  or 128K * CPU_COUNT
  SQL> select * from v$sysstat where name like 'redo buffer%' or name like 'redo entries';                                    

     STATISTIC# NAME                 CLASS      VALUE    STAT_ID
      --------- --------------- ---------- ---------- ----------
         133 redo entries             2      43861 3488821837
         135 redo buffer all          2          0 1446958922
           ocation retries       

3.select sid,event,seconds_in_wait,state FROM v$session_wait where event='log buffer space%';

4. LOG Buffer:调优
    a.  redo log 文件太小， log switch 切换太多
  select event, total_waits, time_waited,average_wait  from v$system_event where event like 'log file switch completion%'

 b.redo 文件太小，log swaitch 切换太多  导致 DBWn checkpoint 跟不上 ,解决办法：提高DBWn写的力度或并行，数据文件换到数据块的盘符
         增大redo log 文件 ,检查 FAST_START_MTTR_TARGET
    select evnet, totaol_waits, time_waited,average_wait from v$system_event where event like 'log file switch (check%'
   
 c.归档进程太慢
      select event, totao_waits, time_waited,average_wait from v$system_event where event like 'log file switch(arch%' 

  
Dispatchers:
dispatchersmax_dispatchers  
select sum(owned) "clinets",sum(busy)*100/(sum(busy)+sum(idle)) "Busy rate" from v$dispatcher; 
select * from v$queue;      //第一个是 接受队列 后三个是响应队列
select name, bytes, idle,busy,requests from v$shared_server;     

PGA
1.PGA_AGGREGATE_TARGET 此参数用来指定所有session总计可以使用最大PGA内存
2.WORKAREA_SIZE_POLICY (AUTO|MANUAL)
3. Leave 20% of the available memory to other applications
   Leave 80% of memory to the Oracle instance
  
  For OLTP
     PGA_AGGREGATE_TARGET=(totao_mem*80%)*20%
  For OLAP:
     PGA_AGGREGATE_TARGET=(totao_mem*80%)*50%
4.
v$pgastat Statistics
over_allocation_count: PGA空间不够  突破PGA_AGGREGATE_TARGET限制的次数
total bytes processed: 一共处理了多少字节
extra bytes read/written: 如果工作区空间太小，就需要把数据而外的读写入到磁盘上的次数
cache hit percentage: 累计值，如果是100% 所有的读写操作都是在 内存完成的

5.select round(pga_target_for_estimate/1024/1024) as target_mb,estd_pga_cache_hit_percentage as hit_ratio,estd_overalloc_count from v$pga_target_advice order by target_mb; 
6.避免排序 排序很占性能 
 排序过程中数据内存不够 ，会利用 temporary tablespace来排序
 临时表空间管理 不会更新数据字典，只在一个segment上
 
7.select name,value from v$sysstat  where name like '%sort%';       
  磁盘排序 /内存排序<5% ，如果大于则应该增大PGA_AGGREGATE_TARGET  或 SORT_AREA_SIZE
8.v$sort_segment , v$sort_usage

SQL tuning
1.OPTIMIZER_MODE   ALL_ROWS | FIRST_ROWS
2.用存储在数据字典里的统计指标，经常要保持这些指标的更新。如果没有可用的指标可参考，优化器会根据OPTMIZER_DYNAMIC_SAMPLING参数来动态临时采样，这个时候执行时间就会过长
3.Hint FIRST_ROWS(n)|ALL_ROWS     SELECT /*+ Frist_rows */ from tab1; 
4.Stored outlines  -> SQL Plan Management  可以使用 DBMS_SPM包里的 LOAD_PLANS_FROM CURSOR_CACHE  或 LOAD_PLANS_FROM_SQLSET过程来执行，执行完毕之后 就应该 禁止或移除 stored outlines
5.Viewing Execution Plan
  EXPLAIN PLAN
  SQL Trace
  Automatic Workload Repository
  V$SQL_PLAN
  SQL*Plus AUTOTRACE

6.SQL_TRACE
SQL> alter session set sql_trace=true; 
SQL> select * from tt where rownum<30; 
[root@localhost udump]# pwd 
/u01/app/oracle/admin/king/udump 
[root@localhost udump]# /u01/app/oracle/product/10.2.0/db_1/bin/tkprof king_ora_5097.trc  myoutput.txt 

维护指标
gather_table_stats
gather_index_stats
gather_schema_stats
gather_database_stats
gather_stale_stats

1.table statistics:
  exec dbms_stats.gather_table_stats('sys','ta');                        
  select num_rows, blocks from dba_tables where owner='SYS' and table_name='TA'; 

2.Index Statistics  (dba_indexes)
3.Column Statistics ( user_tab_col_statistics)
4.Segment-Level Statistics :a.v$segstat_name  b.v$segstat c.v$segment_statistics
5.动态取样:OPTIMIZER_DYNAMIC_SAMPLING (0 不执行，值越大，取的数字越多)
   当查询的表多个一个;表没有被分析过， 没有索引; 由于没有统计指标全表扫描时候
6.创建直方图：  dbms_stats.gather_table_stats, dba_tab_histogram
7.直方图用于不均衡的数据：
  select endpoint_number,endpoint_value from dba_histograms where owner='USR1' and table_name='TA' and column_name='ID'; 
  exec dbms_stats.gather_table_stats('usr1','TA',method_opt=>'FOR COLUMNS SIZE 10 ID'); 
  select endpoint_number,endpoint_value from dba_histograms where owner='USR1' and table_name='TA' and column_name='ID'; 
8.自动收集：EXECUTE dbms_stats.gather_system_stats(interval=>120,stattab=>'mystats', statid=>'OLTP')
            EXECUTE dbms_stats.gather_system_stats(interval=>120,stattab=>'mystats', statid=>'OLAP')
9.手动收集： EXECUTE dbms_stats.gather_system_stats (gathering_maode=>'START')
             EXECUTE dbms_stats.gather_system_stats (gathering_mode=>'STOP')
10.导入system statistics
     EXECUTE dbms_stats.import_system_stats(stattab=>'mystats' statid=>'OLTP')
     EXECUTE dbms_stats.import_system_stats(stattab=>'mystats' statid=>'OLAP') 
 dbms_stats.create_stat_table
 dbms_stats.export_table_stats
 
Oracle Blocks Efficiently
大Extents的优缺点： 减少分配次数 ，性能稍微有点提高，一次I/O 就能读整个 extent map
                    小空间可能不被利用 

     
     
回收空间：
高水位线以下：  drop or truncate 原表
                Alter Table employees move
高水位线以上： alter table employees Deallocate Unused

Block大小： 小块： 优点：减少BLOCK 竞争 ，对小记录比较好 ，对 随机访问比较好
                   缺点：额外开销多(每个块都有控制信息) ，一个块容纳的记录条数少， 索引的块就多   
索引：rebuild
      找出没有被使用的索引
      1.Alter INDEX hr.emp_name_ix MONITORING USAGE
   2.SELECT index_name,used from v$object_usage;
   3.ALTER INDEX hr.emp_name_ix NOMONITORING USAGE;

  
存储机构：
1.基表  集群表  索引表
2.分区表：Range,Hash，List , Composite
3.分区索引:Local partitioned indexes , Global partitioned Indexed, global none partitioned indexes

应用调优：
 1.压缩索引：ALTER INDEX emp_last_name_idx REBUILD COMPRESS(2),对前两列压缩
 2.Reverse Key Index ：  Create UNIQUE INDEX i2_t1 ON t1(c2);
                         ALTER INDEX i2_t1 REBUILD REVERSE
 3.Bitmap Indexes: 数值情况不多的列， 用最少的存储空间， read-only系统比较实用 ， 对大表有好处
 4.索引驱动表：
 5.ANALYZE :收集指标信息（优化器不使用的） 例如：free list blocks
              ANALYZE TABLE hr.employees VALIDATE STRUCTURE
 
 
物化视图：
1.数据更新类型： complete, fast,never
               手动和自动
2.query rewriter
3.存的数据和基表是两份
4. 刷新数据的函数: EXEC dbms_mview.refresh('MVname','F','',ture,false,0,0,0,false)
   并行　dbms_mview.refresh('CUST_SALES',parallelism=>10)
   刷新基于多个基表的物化视图  dbms_mview.refresh_dependent('SALES');
   刷新所有物化视图: dbms_mview.refresh_all_mviews
5.用fast方式刷数据，必须有　 meterialized view log
   一个基表只能有一个 meterialized view log (mlog$_tablename)
6.嵌套物化视图
7.Query Rewrite Overview

  alter session set query_rewrite_enabled=true;
  alter session set query_rewrite_integrity=enforced;   
  create materialized  view my_all_objects_aggs  
   build immediate 
   refresh on commit 
   enable query rewrite 
   as  
  select owner,count(*) from my_all_objects group by owner; 
  analyze table my_all_objects_aggs compute statistics;  
  select owner, count(*) from my_all_objects group by owner;   //这里很快就出来了 
 

 查询SQL　指明不要SQL REWRITE ,用 /*+NOREWRITE*/ 
 
 
 
 LOCK
1.排他锁：在锁修改之前 不能被其他seesion锁
2.共享锁：在共享锁上 不能加排他锁
3.一个DML事物 得倒至少两把锁：  一个共享的表锁  一个排他的行锁
4.select sid, type, id1,id2,lmode,request from v$lock; 
5.对于排他性的tx锁， ID1分为 高 16，低16位，分别对应 v$transaction 的 xidusn, xidslot 的两列
  对于共享的tm锁， id1对应user_obects中的OBJECT_ID
6.手动操作锁：lock table t in row exclusive mode
7.ORA-00060 Deadlock detected while waiting for resource
8.相关的视图：v$lock , v$locked_object dba_waiters,dba_blockers