- 博客(40)
- 收藏
- 关注
原创 索引 split2
当往一个已经满了的索引块中插入新的索引条目时,将发生索引块的split,在9i下,分两种不同的情况进行split:(1)如果插入的索引键值不是最大的,将发生50-50的split,也就是说有block中一半的索引键值将被移到一个新的block中。就是说热点增长也始终在热点的右边,导致类似右手增长状况。但是由于不在最有边,将按照50:50 Split,导致接近50%的空间浪费。(2)如
2014-05-29 10:05:55 756
转载 iotop使用
介绍Linux下的IO统计工具如iostat, nmon等大多数是只能统计到per设备的读写情况, 如果你想知道每个进程是如何使用IO的就比较麻烦.iotop 是一个用来监视磁盘 I/O 使用状况的 top 类工具。iotop 具有与 top 相似的 UI,其中包括 PID、用户、I/O、进程等相关信息。 实例直接运行就好iotop输出如下Total DISK READ
2014-05-29 08:33:07 991 1
原创 单块读和多块读操作汇总
多块读的操作:全表扫描,index fast full scan ,merge,rowid range scan单块读:index unique scan ,index full scan ,index range scan,逻辑读
2014-05-27 10:51:59 1385
原创 FILTER 哪里没展开改哪里
SELECT * FROM F_PTY_INDIV_TMP O WHERE EXISTS (SELECT 1 FROM F_PTY_INDIV F WHERE O.PTY_ID = F.PTY_ID AND O.CORP_ORG = F.CORP_ORG
2014-05-26 16:25:36 568
原创 10g中注意子查询里主表谓词过滤的位置
在10g中当主查询的谓词信息,被错误的放入子查询中,会导致子查询无法展开explain plan for UPDATE DWF.F_PTY_INDIV O SET END_DT = TO_DATE('2012-12-31', 'YYYY-MM-DD') WHERE EXISTS (SELECT 1 FROM DWF.F_PTY_INDIV F WH
2014-05-26 08:53:11 623
原创 奇葩的SQL*Net more data from client等待,导致批处理巨慢
UPDATE Business_Duebill SET RelativeSerialNo1 = :1 , RelativeSerialNo2 = :2 , CustomerID = :3 , BusinessType = :4 , MFCustomerID = :5 , CustomerName = :6 , SubjectNo = :7 , BusinessSum = :8 , B
2014-05-24 09:27:56 3900
原创 B树叶子节点split
一、B-Tree索引的分裂1. 创建测试表SQL> create table split_tab (id number, name varchar2(100));表已创建。SQL> alter table split_tab add constraint pk_split_tab primary key (id) using index;表已更改。SQL> create sequenc
2014-05-23 14:49:33 1556
原创 彻底搞懂反转索引
Reverse Key IndexesCreating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can
2014-05-22 21:14:29 2787
原创 反转索引
Reverse Key IndexesCreating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can
2014-05-22 15:49:23 682
原创 auto space advisor
首先:oracle有自动Job,进行shrink spaceSQL> select client_name,status from dba_autotask_client;CLIENT_NAME STATUS---------------------------------------------------------------- --------auto opti
2014-05-22 12:38:50 819
原创 半链接和关联转换
select distinct dept.department_name,emp.employee_idfrom dept_test dept, emp_test emp where dept.department_id = emp.department_id;drop table emp_test;create table emp_test as select * from emp
2014-05-22 10:27:28 582
原创 OR扩展
SQL> select substr(xx.acct_no,1,5) agent_org, xx.vou_kind,sum( xx.trans_amt) trans_amt from ( select * from dwf.F_EVT_SAVD_LIST
2014-05-21 17:50:04 709
原创 linux vmstat使用说明
FIELD DESCRIPTION FOR VM MODE Procs r: The number of processes waiting for run time.表示运行队列,就是说多少个进程真的分配到CPU b: The number of processes in uninterruptible sleep.阻塞的进程数 Memory
2014-05-21 15:47:39 602
原创 linux sar查看网络流量
sar -n DEV 1 10 -n { keyword [,...] | ALL } Report network statistics. IFACE Name of the network interface for which statistics are reported. rxpck
2014-05-21 15:17:01 4882
原创 library cache: mutex X
我们先来看看 library cache: mutex X 。 是个什么东西 The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle. In 10g, mutexes were in
2014-05-21 10:57:08 1903
原创 to_char函数引发的不走索引
SQL> conn cowork_czsh/cowork_czshConnected.SQL> set linesize 200SQL> set pagesize 200SQL> set autot traceSQL> select workitemhi0_.PERFORMER as col_0_0_ from WORKITEM_HISTORY workitemhi0_ where
2014-05-20 10:36:46 4880 2
原创 Row Cache Objects
This latch comes into play when user processes are attempting to access or update the cached data dictionary values.SolutionsProblem:To determine if the row cache is being used efficiently, exec
2014-05-19 09:19:21 659
原创 translate函数说明
TRANSLATE(expr, from_string, to_string)from_string 与 to_string 以字符为单位,对应字符一一替换。SQL> SELECT TRANSLATE('ab你好abcdefg', 'abcdefg', '1234567') AS NEW_STR FROM DUAL;NEW_STR-------------12你好1234567
2014-05-18 20:59:02 4542
原创 模糊查询
转义字符:要求一:查出vname中包含字符串“CED”的行SQL> select * from v where vname like '%CED%';VNAME-------ABCEDF_BCEDF_\BCEDF要求二:查出vname中包含字符串“_BCE”的行SQL> SELECT * FROM v WHERE vname LIKE '_BCE%';VNAME--
2014-05-18 19:58:01 628
原创 SQL使用单引号
SQL> select 'xxxx'oooo' from dual;ERROR:ORA-01756: quoted string not properly terminatedSQL> select "xxxx'oooo" from dual;select "xxxx'oooo" from dual *ERROR at line 1:ORA-00904: "xxxx'
2014-05-18 16:12:37 2313
原创 PL/SQL执行动态SQL
create or replace procedure xx IS msql varchar2(200);begin--loopmsql := q'[SELECT ename, sal, CASE WHEN sal <= 2000 THEN '过低' WHEN sal >= 4000 THEN
2014-05-18 16:05:45 558
原创 coalesce和nvl函数
coalesce 函数 :Oracle COALESCE函数语法为COALESCE(表达式1,表达式2,...,表达式n),n>=2,此表达式的功能为返回第一个不为空的表达式,如果都为空则返回空值。注意:所有表达式必须为同一类型或者能转换成同一类型。返回第一个不为空的CREATE OR REPLACE VIEW v ASSELECT NULL AS C1, NULL AS
2014-05-18 14:45:26 3040 1
原创 创建索引锁的表
SQL> desc TEST_IDX Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJEC
2014-05-17 08:56:29 582
原创 Linux Top使用说明
运行top后,按P键就按CPU排序,按M键就按内存排序P – 以 CPU 占用率大小的顺序排列进程列表M – 以内存占用率大小的顺序排列进程列表在系统维护的过程中,随时可能有需要查看 CPU 使用率,并根据相应信息分析系统状况的需要。在 CentOS 中,可以通过 top 命令来查看 CPU 使用状况。运行 top 命令后,CPU 使用状态会以全屏的方式显示,并且会处在对话的模式 —
2014-05-16 09:48:01 608
原创 11G在线重建索引
SQL> select count(*) from test_idx; COUNT(*)---------- 19087751SQL> select segment_name,segment_type,bytes/1024/1024 as MB from user_segments where segment_name='TEST_IDX';SEGMENT_NAME
2014-05-14 14:51:11 593
原创 expect 批量监控主机
[oracle@OAPRIMARY shell]$ cat expect.sh while read linedouser=`echo $line | awk '{print $1}'`ip=`echo $line |awk '{print $2}'`passwd=`echo $line | awk '{print $3}'`#把shell中的$user $ip $passwd参数传递
2014-05-13 16:28:18 584
原创 expect 传参
set timeout -1puts "Start"set i 1spawn reloadtuxconfigwhile {"$i" < 13} {expect { ":" {send "czcb7753\r";}}incr i}XXXXXXX(A)/app/cbsrun/sbin> expect 2.shStartspawn reloadtuxconfigP
2014-05-13 13:21:38 714
原创 Shell continue循环
[oracle@june ~]$ cat continue.sh for i in a b c d e f gdoif [ "$i" = "c" ]thenecho xxxxxxxxxxxxxxecho "跳过的字符为"$icontinuefidone[oracle@june ~]$ sh -x ./continue.sh + for i in a b c d e f g
2014-05-13 10:12:50 555
原创 Shell循环处理
date=`echo $1 | tr -d '-'`date1=`echo $1`date_end=`get_date $2 +1 | sed 's/-//g'`while [ 1 ]dodateecho $date1 if [ ! -d "/home/dataun/ETL_init/DATA/$date1/BICA/" ] thenecho "开始创建"
2014-05-13 09:37:38 601
原创 分页技术总结
create table page as select * from dba_objects; create index idx_page on page(object_id);create index idx_page_1 on page(owner,object_id);create index idx_page_2 on page(owner);create index idx_p
2014-05-11 13:35:00 599
原创 当分页语句遇到union all
SELECT * FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_,
2014-05-09 17:05:57 1693
原创 两表关联更新,用于update 回滚
create table test1 as select * from dba_objects;create table test2 as select * from dba_objects;create unique index test1_idx1 on test1(object_id);select * from test1;create table test3 as s
2014-05-09 12:12:26 719
原创 Invalid file system control data detected
今天在做mkdir操作时报错:Invalid file system control data detected。检查用户和权限没问题,再检查磁盘空间也没问题。最后在网上找到如下信息:【problem】# mkdir aaamkdir: 0653-358 Cannot create aaa.aaa: Invalid file system control data detect
2014-05-09 10:11:28 2353
原创 expect: spawn id exp4 not open
spawn rsync -avH --delete /home/dwetl/bin dwetl@10.128.8.151:/home/dwetl/binsending incremental file listbin/bin/ftpgj.logbin/ftpjx.logbin/perl_add_gms.logbin/perl_add_ib.logbin/perl_add_uprr.l
2014-05-09 09:39:44 18136 2
原创 FILTER的执行次数和驱动表问题
drop table test1;create table test1 as select * from dba_objects where rownum<1000;drop table test2;create table test2 as select * from dba_objects where rownum<1000;insert into test1 select *
2014-05-08 13:35:12 688
原创 什么时候使用NO_UNNEST
select * from test a where object_id in (select department_id from hr.dept_1 dept where department_id IN (select department_id from hr.employees_1 emp)); SQL> select count(*) from test; C
2014-05-06 12:10:03 975
原创 走FILTER效率高的2种情况
FILTER的适用范围:1. 主表返回的记录数较少2.子查询返回记录数较小下面做实验证明:select department_name from hr.dept_1 dept where department_id IN (select department_id from hr.employees_1 emp);SQL> select count(*) from dept_
2014-05-06 10:15:24 991
原创 PL/SQL 包头和包体
包用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成,包规范用于定义公用的常量变量,过程和函数,在SQL*PLUS中建立包规范可以使用CREATE PACKAGE命令。实例如下:CREATE OR REPLACE PACKAGE emp_pkg IS PROCEDURE update_sal(name VARCHAR2,newsal NUMBER); FUNCTION an
2014-05-04 09:34:57 2723
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人