- 博客(178)
- 收藏
- 关注
RSS订阅原创 ORACLE GRID
rac 1、clusterware三大功能 节点成员身份管理 全局资源管理 高可用性保证 2、clusterware三大服务 CSS 自行重起节点 ocssd oprocd oclsomon CRS crsd EVM evmd racgevt 3、clusterware文件 OCR 配置文件 类似win
2016-08-24 15:34:36
1301
原创 oracle sql优化方法论
1、降低SQL资源消耗 2、并行执行SQL 3、平衡系统的资源消耗 4、实例 符合索引避免ＮＵＬＬ无法用索引 合适的索引避免排序 LIKE EMP% 可用函数索引REVERSE 5、数据库优化步骤 找到时间最长，资源消耗最多的TOP SQL 查看SQL执行计划合理性 进行修正
2016-08-18 16:55:56
708
原创 oracle hint 和 并行
--------HINT--------------------- 1、给优化器多出一种选择 种类： 单表 多表 子查询 整个SQL语句 2、HINT 指定表名不能带上所在SCHEMA名 如果有别名应该使用别名 3、HINT生效的范围仅限于本身所在的查询块（QUERY BLOCK） 如在查询块外则失效 除非指定查询块名称 full(@sel$1 t1) 或 full(
2016-08-18 10:49:14
4382
原创 oracle 统计信息
1、收集 ANALYZE DBMS_STATS ANALYZE 不能收集分区表信息 不能并行收集 DBMS_STATS 只能收集CBO相关统计信息 不能收集行迁移/行链接 不能校验表和索引的结构信息 2、历史统计信息 WRI$_OPTSTAT_TAB_HISTORY （TRUNCATE后会自动收集统计信息 生产环境上线后应及时收集统计信息避免CBO选择错误计划）
2016-08-16 09:16:28
824
原创 oracle 查询转换
1、子查询展开 SINGLE-ROW（=，,=, 不能做子查询展开的通常会在SQL执行计划最后一步才执行，一般是FILTER类型计划，效率很差 IN ，EXISTS， =ANY 可转换为半连接（SEMI JOIN） NOT IN， NOT EXISTS， 展开2条件：展开后语义完全等价，内嵌视图的子查询必须COST低于原SQL才展开 2
2016-08-09 16:32:36
1223
原创 oracle cursor和绑定变量
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
2016-07-19 16:28:31
1137
原创 oracle 优化器 执行计划
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
2016-07-14 16:39:03
1200
原创 oracle sql 优化相关
oracle net 单次获取结果集 默认10条 可修改array size，jdbc fetchsize 针对大统计 union all 等改为分析函数提升性能
2016-07-13 09:54:03
399
原创 hadoop 测试环境搭建
ssh 无密码验证登陆配置比较麻烦 主要是 .ssh文件夹的 700权限，centos7 里默认建立的文件夹权限不行。 sshd_config 里面要改几个地方，要不然securecrt登录不上。 RSAAuthentication yes PubkeyAuthentication yes AuthorizedKeysFile .ssh/authorized
2015-01-15 12:54:42
606
原创 使用rman恢复部分表空间
±ðÔÚÖ÷»úÉÏÓÃrman ÖŽÐÐ £¬Íš¹ýcommvault»ÖžŽ žßŒ¶ ×Ô¶šÒåœÅ±ŸÖŽÐÐ SET DBID 3958247272; run { allocate channel ch1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base64/libobk.a(shr.o),BLKSIZE=262
2014-08-11 10:14:40
4100
原创 rman 特性
<br />rman 保留窗 自动删除obsolute<br />rman 增量备份 trace文件<br />log_archive_local_first 加快本地归档 <br />db_block_checksum db_block_checking<br />rman 检查数据损坏
2011-04-05 19:55:00
573
原创 Oracle 9i 数据库管理员指南
<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
2011-03-17 16:55:00
806
原创 Oracle 10g 新特性
<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
2011-03-16 17:50:00
932
原创 Oracle tuning the operating system
<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
2011-02-20 10:47:00
578
原创 Oracle monitoring and detecting lock contention
<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
2011-02-20 10:46:00
629
原创 oracle use materialized views
<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
2011-02-20 10:45:00
687
原创 Oracle using oracle data storage structures efficiently
<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
2011-02-20 10:44:00
537
原创 Oracle application tuning
<br />redefine a table online: dbms_redefinition<br />compressed index<br />reverse key index<br />index organized table:pctthreshold,including,overflow,mapping table
2011-02-20 10:44:00
542
原创 Oracle sql statement tuning
<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;
2011-02-09 14:25:00
679
原创 Oracle managing statistics
<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
2011-02-09 14:25:00
552
原创 Oracle using blocks efficiently
<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
2011-02-09 14:25:00
556
原创 Oracle optimizing sort operations
<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
2011-02-09 14:24:00
585
原创 Oracle using resource manager
<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
2011-02-09 14:24:00
582
原创 Oracle sizing other sga structures
<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
2011-02-09 14:23:00
468
原创 Oracle tuning the oracld shared server
<br /><br /> para:max_shared_servers circuits processes<br /> v$dispatcher v$dispatcher_rate v$queue<br /> v$shared_server v$circuit
2011-02-09 14:23:00
491
原创 Oracle dynamic instance resizing
<br />SGA limited by: sga_max_size<br /> memory unit:granule (sga <128m 4m, sga>128m 16m)<br /> v$buffer_pool
2011-01-04 16:45:00
633
原创 Oracle tuning the buffer cache
<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 />
2010-12-31 09:46:00
603
原创 Oracle tuning the shared pool
<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
2010-12-28 21:04:00
568
原创 Oracle database configuration and i/o issues
<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 /
2010-12-27 11:23:00
505
原创 Oracle diagnostic and tuning tools
<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
2010-12-21 11:11:00
816
原创 Oracle overview of oracle database performance tuning
<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
2010-12-20 17:22:00
556
原创 Oracle sql
<br />写程序时 select * 速度比直接写列名要慢，需要转换 <br /> null算数运算后还是null ||操作还是原来的值<br /> distinct a,b,c 选择的所有列都不同时才去除<br /><br /> where 子句不能用别名<br /> between and 相当于大于等于>= 小于等于<=<br /> in 相当于 or<br /><br /> like % _ <br /> where job_id like '%sa/_%' escape '
2010-12-14 15:41:00
614
原创 Oracle export and import utilities
<br /><br /> tables % owner <br /><br /> globalization support: source database<target database<br /><br /> imp / show=y file=1.dmp display nls info<br /><br /> direct-path<br />
2010-12-07 17:05:00
553
原创 Oracle rman maintenance
<br />retention plicy:recovery window,redundancy,none<br /> crosscheck<br /> backup archivelog delete input
2010-12-07 17:04:00
552
原创 Oracle rman incomplete recovery
<br /><br /> RMAN> run{<br /> allocate channel c1 type disk;<br /> allocate channel c2 type disk;<br /> allocate channel c3 type disk;<br /> set until time "to_date('2010-12-02:02:48:49','yyyy-mm-dd:hh24:mi:ss')" ;(否则需要设置env )<br /> restore data
2010-12-07 17:03:00
511
原创 Oracle recovery catalog creation and maintenance
<br /><br /> grant recovery_catalog_owner to ..
2010-12-07 17:03:00
499
原创 Oracle user -Managed incomplete recovery
<br /><br /> types:time-based,Cancel-based,Change-based<br /><br /> recover database until time '2010-11-30:02:52:16';<br /> (until not include-> 02:52:15)<br /><br /> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS
2010-12-07 17:02:00
521
原创 Oracle rman complete recovery
<br />database tablespace datafile<br /> backup-> restore -> recover->open<br />
2010-12-07 17:01:00
541
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人