- 博客(178)
- 收藏
- 关注
RSS订阅原创 ORACLE GRID
rac 1、clusterware三大功能 节点成员身份管理 全局资源管理 高可用性保证2、clusterware三大服务 CSS 自行重起节点 ocssd oprocd oclsomon CRS crsd EVM evmd racgevt3、clusterware文件 OCR 配置文件 类似win
2016-08-24 15:34:36
1067
原创 oracle sql优化方法论
1、降低SQL资源消耗2、并行执行SQL3、平衡系统的资源消耗4、实例 符合索引避免ＮＵＬＬ无法用索引 合适的索引避免排序 LIKE EMP% 可用函数索引REVERSE 5、数据库优化步骤 找到时间最长，资源消耗最多的TOP SQL 查看SQL执行计划合理性 进行修正
2016-08-18 16:55:56
531
原创 oracle hint 和 并行
--------HINT---------------------1、给优化器多出一种选择 种类： 单表 多表 子查询 整个SQL语句2、HINT 指定表名不能带上所在SCHEMA名 如果有别名应该使用别名3、HINT生效的范围仅限于本身所在的查询块（QUERY BLOCK） 如在查询块外则失效 除非指定查询块名称 full(@sel$1 t1) 或 full(
2016-08-18 10:49:14
4158
原创 oracle 统计信息
1、收集 ANALYZE DBMS_STATS ANALYZE 不能收集分区表信息 不能并行收集 DBMS_STATS 只能收集CBO相关统计信息 不能收集行迁移/行链接 不能校验表和索引的结构信息 2、历史统计信息 WRI$_OPTSTAT_TAB_HISTORY （TRUNCATE后会自动收集统计信息 生产环境上线后应及时收集统计信息避免CBO选择错误计划）
2016-08-16 09:16:28
613
原创 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
1060
原创 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
981
原创 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
1018
原创 oracle sql 优化相关
oracle net 单次获取结果集 默认10条 可修改array size，jdbc fetchsize 针对大统计 union all 等改为分析函数提升性能
2016-07-13 09:54:03
286
原创 hadoop 测试环境搭建
ssh 无密码验证登陆配置比较麻烦主要是 .ssh文件夹的 700权限，centos7 里默认建立的文件夹权限不行。sshd_config 里面要改几个地方，要不然securecrt登录不上。RSAAuthentication yesPubkeyAuthentication yesAuthorizedKeysFile .ssh/authorized
2015-01-15 12:54:42
499
原创 使用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
3905
原创 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
488
原创 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
678
原创 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
781
1原创 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
471
原创 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
525
原创 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
547
原创 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
450
原创 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
444
原创 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
580
原创 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
448
原创 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
480
原创 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
457
原创 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
472
原创 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
381
原创 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
423
原创 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
539
原创 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
493
原创 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
435
原创 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
422
原创 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
707
原创 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
440
原创 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
540
原创 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
433
原创 Oracle rman maintenance
<br />retention plicy:recovery window,redundancy,none<br />crosscheck<br />backup archivelog delete input
2010-12-07 17:04:00
487
原创 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
420
原创 Oracle recovery catalog creation and maintenance
<br /><br />grant recovery_catalog_owner to ..
2010-12-07 17:03:00
413
原创 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
389
原创 Oracle rman complete recovery
<br />database tablespace datafile<br />backup-> restore -> recover->open<br />
2010-12-07 17:01:00
435
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人