异道的专栏

rac 1、clusterware三大功能    节点成员身份管理    全局资源管理    高可用性保证2、clusterware三大服务    CSS 自行重起节点 ocssd oprocd oclsomon    CRS crsd    EVM evmd racgevt3、clusterware文件     OCR 配置文件 类似win

<br /><br />para:max_shared_servers circuits processes<br />v$dispatcher v$dispatcher_rate v$queue<br />v$shared_server v$circuit

<br /><br /><br />optimizer modes:rule-based,cost-based<br />optimizer :throughput,response time<br />optimizer_features_enable=8.1.5<br />package:dbms_spm(sql plan management)<br />v$sql_plan<br />plan_table :explain plan for select * from t;

<br /><br />log_buffer<br />v$session_wait(log buffer space)<br />v$sysstat(redo entries,redo buffer allocation retries <1%)<br />v$system_event(log file switch%)<br />redo log buffer,java pool

<br /><br />dba_tables: empty_blocks/(blocks+empty_blocks)<0.1%<br />analyze table t compute statistics  -> get empty_blocks<br />truncate init hwm<br />package:dbms_space<br />recover space:export and import,alter table move ,alter table t deall

<br /><br />package:dbms_resource_manage<br />unit: group<br />auto group switch<br />execution time limit<br />undo pool<br />idle time limit<br />dd: dba_rsrc_*<br />view:v$session v$rsrc_plan v$rsrc_consumer_group

<br /><br />optimal,one pass,multi pass<br />auto manage para:pga_aggregate_target<br />v$pgastat v$sql_workarea v$sql_workarea_histogram v$sql_workarea_active v$sql v$sql_plan v$tempseg_usage v$pga_target_advice v$sysstat v$pga_target_advice_histogr

<br />rman<br />restore & recovery<br />dest: local disk,tape ,target database<br />repository,control file of target db,catalog db<br />auxiliary db<br />batch mode,here document<br /> <br /> <br />show<br />list<br />r

<br /><br />type:backup set, image copy<br />backup set ->backup piece<br />backup control file 4 methods: <br /> auto<br /> backup current controlfile<br /> backup datafile include current controlfile<br /> sql "alter database ba

<br /><br />type:whole,partial,consistent,inconsistent<br />os command:cp tar gzip<br />Open db backup :must archivelog mode,tablespace or datafile level<br /> <br />dd if=example01.dbf ibs=8192 skip=1011 count=2|strings<br /> <br />v

<br /><br />os command restore files<br /><br />sqlplus restore spfile->controlfile->datafile,logfile,tempfile<br /><br />no backup redo log不完全恢复:mount ->recover database until cancel using backup controlfiles->alter database open resetlogs<br /><br

<br />archive mode: mount->alter database archivelog|noarchivelog->open<br />auto:alter system archive log start|stop {to 'path'}<br />init para: log_archive_start=false|true<br />manual: alter system archive log current<br />log_archive_dest_n:local disk,

<br />SGA limited by: sga_max_size<br />memory unit:granule (sga <128m 4m, sga>128m 16m)<br />v$buffer_pool

<br />LRU list, checkpoint queue<br />buffer states:pinned,clean,free/unused,dirty<br />block check para: db_block_checksum  db_block_checking<br />cache para:db_block_size,db_cache_size,db_keep_cache_size,db_recycle_cache_size,db_cache_advice<br />

<br />shared pool:library cache, data dictionary cache, (user global area for shared server)<br />para:shared_pool_size,shared_pool_resved_size,open_cursors,cursor_space_for_time,session_cached_cursors,cursor_sharing<br />lib cache: sql statements and

<br /><br /><br />statistics:sys,table,index,column<br />package:dbms_stats (exec dbms_stats.gather_table_stats('HR','T'))<br />dba_tables dba_indexes <br />v$user_tab_statistics v$user_tab_col_statistics<br />v$segstat_name v$segstat v$segment_s

<br />redefine a table online: dbms_redefinition<br />compressed index<br />reverse key index<br />index organized table:pctthreshold,including,overflow,mapping table

<br />modes of locking:exclusive,share<br />types of locks:dml,ddl,internal<br />v$lock type tm:id1 -> table and meterailize log<br />v$lock type tx:id1 -> high 16 bit =xidusn(v$transaction) low 16 bit = xidslot(v$transaction)<br />v$lock v$transaction v$s

1、子查询展开    SINGLE-ROW（=，,=,    不能做子查询展开的通常会在SQL执行计划最后一步才执行，一般是FILTER类型计划，效率很差    IN ，EXISTS， =ANY 可转换为半连接（SEMI JOIN）    NOT IN， NOT EXISTS，     展开2条件：展开后语义完全等价，内嵌视图的子查询必须COST低于原SQL才展开2

1、降低SQL资源消耗2、并行执行SQL3、平衡系统的资源消耗4、实例　　　符合索引避免ＮＵＬＬ无法用索引　　　合适的索引避免排序　　　LIKE EMP% 可用函数索引REVERSE      5、数据库优化步骤     找到时间最长，资源消耗最多的TOP SQL     查看SQL执行计划合理性     进行修正     

--------HINT---------------------1、给优化器多出一种选择 种类： 单表 多表 子查询 整个SQL语句2、HINT 指定表名不能带上所在SCHEMA名 如果有别名应该使用别名3、HINT生效的范围仅限于本身所在的查询块（QUERY BLOCK） 如在查询块外则失效 除非指定查询块名称 full(@sel$1 t1) 或 full(

1、收集 ANALYZE DBMS_STATS    ANALYZE 不能收集分区表信息 不能并行收集    DBMS_STATS 只能收集CBO相关统计信息 不能收集行迁移/行链接 不能校验表和索引的结构信息 2、历史统计信息 WRI$_OPTSTAT_TAB_HISTORY （TRUNCATE后会自动收集统计信息 生产环境上线后应及时收集统计信息避免CBO选择错误计划）

1、SHARED CURSOR （位于SGA）     PARENT CURSOR 只对应SQL文本（V$SQLAREA 不同SCHEMA下相同的SQL ，PARENT CURSOR也相同）     CHILD CURSOR 对应 VERSION COUNT (V$SQL)     硬解析：至少需要生成 CHILD CURSOR 占用SHARED POOL LATCH LIBRAR

1、优化器种类  rule choose first_rows_n first_rows all_rows(10g后默认)2、访问表的方法：全表扫描（TABLE ACCESS FULL） rowid扫描（TABLE ACCESS BY USER ROWID|TABLE ACCESS BY INDEX ROWID）3、访问B树索引的方法 索引唯一性扫描 IND

oracle net 单次获取结果集 默认10条 可修改array size，jdbc fetchsize 针对大统计 union all 等改为分析函数提升性能

±ðÔÚÖ÷»úÉÏÓÃrman ÖŽÐÐ £¬Íš¹ýcommvault»ÖžŽ žßŒ¶ ×Ô¶šÒåœÅ±ŸÖŽÐÐSET DBID 3958247272;run {allocate channel ch1 type 'sbt_tape'PARMS="SBT_LIBRARY=/opt/simpana/Base64/libobk.a(shr.o),BLKSIZE=262

1、easy connect naming : connect username/password@host:port/service_name/server/instance(windows下cmd直接sqlplus user/pass在任务管理器中容易泄漏信息，可以sql

<br />暂停数据库的几种模式：<br />alter system enable restricted session;alter system disable restricted session;<br />alter system quiesce restricted;alter system unquiesce;<br />alter system suspend;alter system resume;<br /><br />分区和并行执行<br />alter sessi

<br />闪回版本查询 select rate, versions_starttime, versions_endtime from rates versions between timestamp to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss') and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')<br /><br />回滚监视 v$session_longops(elapsed

<br />tuning:memory io cpu<br />vmstat iostat top sar<br />para:lock_sga<br />cpu busy rate:90% ,os/usr 40/60<br />monitor usage

<br />refresh types:complete(delete->insert rowid changed),fast(update,rowid not changed)<br />dbms_mview refresh<br />materialized view log: mlog$_xxx<br />query rewrites para:query_rewrite_enabled,query_rewrite_integrity, query rewrite privilige,hints

<br />types:heap table ,cluster,index organized table,partition table<br />cluster:index cluster(<> between and),hash cluster(=)<br />partition:range,hash,list,composite<br />partition pruning<br />partition-wise join<br />ps:range(maxvalue新纪录如何整理，add part

<br />storage:file system,raw partitions,automatic storage management<br />distributing files: separate datafiles and redo log files,stripe table data,reduce disk i/o unrelated to db<br />i/o statistics: v$filestat v$tempstat v$datafile v$tempfile<br /

<br />alert log file: show parameter dump<br />ORA-600 internal error,block corruption error<br />clear alterlog: cat /dev/null >alert*.log<br />log_checkpoints_to_alert set true<br />alter session set sql_trace =true;<br />execute dbms_system.se

<br />sla :service level agreements<br />problems:session,cursor,relational design<br />tuning steps in devlepment: tune design,application,memory,i/o,contention,os<br />baseline <br />steps for a production db: define the problem,examine os/oracle

<br />1、data integrity:app code ,db trigger, integrity constraint 优先级增高<br />2、constraints type: not null,unique,primary key,foreign key,check<br />3、unique: allow null value  foreign key:allow null value<br />4、primary key: alter table (enable|disable

<br />1、listener 传递连接 types: spawns and bequeaths(父子）,hands off(邻居),redirect(某些操作系统不能传递socket连接）<br />2、service config register: dynamic, static (使用OEM需要static)<br />3、listener 中host需要在 /etc/hosts配置<br />4、工具：netca netmgr

<br />1、jdbc drivers :oci,thin(性能高）<br />2、service name: show parameter service<br />3、pmon process provide information to listener(db name ,instance name,service handler )<br />4、pmon every 60s to register listener , during this can immediately :  alt

<br />1、direct load types:serial, parallel<br />2、串行：insert /*+ append */ into emp nologging select * from t_employees<br />          数据直接插入hwm后面，速度最快，块对块拷贝，commit后才可查询<br />3、并行：alter session enable parallel dml;<br />          insert /*+ parallel(hr.

<br />1、索引提高查询速度，降低增删改速度<br />2、分类，逻辑分类，物理分类<br />        logical:单列多列，唯一不唯一,基于函数的， 外部 <br />        physical: btree平衡树， bitmap 字典项索引（例如性别 where中有or时性能高）,partition<br />3、内部组成结构<br />        btree :index entry header(transaction slot) |{key column