AnySQL.net

    DBA的一块主要的工作是后台系统维护, 当你在一家高速增长的网络公司做这个职位时, 已经不能光做些事后故障处理的事了, 需要对数据容容量(Capacity)作了一些有效的预测, 否则当业务高峰来临时, 就会手忙脚乱. 要将高可用性当成第一目标, 也不能光看目前的系统可用性, 要结合业务增长速度来看系统可用性, 这样容量预测的工作就势在必行.

    从数据库的角度来的看, 可以先去分析下面几个因素和系统负荷的关系.

1, Execute Count
2, Logical Read
3, Physical Read
4, Physical Write
5, Transactions

    当了解了上面的因素和系统负荷之间的变化关系后, 需要再分析资源和上面这些因素的关系, 以及资源的限制. 有那些方面资源呢?

1, CPU
2, IOPS & IO Time & Storage Space
3, Transaction Process

    再从业务上来分析, 分析一下完成你的一笔交易需要产生多少次数据库的活动, 再根据业务发展的速度就可以初步评价出, 目前的系统可以支持多少的事务量了, 从而知道现有的系统可以再支持多少时间, 有了这些预测, 就可以提前作出一些行动, 进行相关的升级或改造了.

    很想做一个比较好的预测模型, 由于不能用真实的数据去检验一下模型的有效性, 因此永远局限于理论模型, 目前还没有很好的很准的模型, 正在思考中......

    用全文索引做了一个根据表名查找SQL语句的功能. 在Statspack中有一个表存放了数据库中执行过的SQL, 虽然不是全部, 但也有差不多99.9%了. 只是由于它是分行存贮的, 不能直接用普通的SQL语句(like)来查找, 表名有可能被折分存放在两行中. 解决的方法有两种, 一是写过程, 将多行的串在一起. 二是用全文索引中的主从存贮方式, 建立全文索引来查询. 我选择的是全文索引的方式.

    先将这里面的数据拷出来, 分成两个表, 主表和从表.

CREATE TABLE SQLS AS
  SELECT DISTINCT HASH_VALUE,'X' BODY FROM STATS$SQLTEXT;
ALTER TABLE SQLS ADD PRIMARY KEY (HASH_VALUE);
CREATE TABLE SQL_DETAILS AS
  SELECT HASH_VALUE,PIECE,SQL_TEXT FROM STATS$SQLTEXT;
ALTER TABLE SQL_DETAILS ADD PRIMARY KEY (HASH_VALUE, PIECE);

    创建全文索引的存贮方式及Lexer属性.

begin
ctx_ddl.create_preference('sqltext_pref', 'DETAIL_DATASTORE');
ctx_ddl.set_attribute('sqltext_pref', 'binary', 'true');
ctx_ddl.set_attribute('sqltext_pref', 'detail_table', 'sql_details');
ctx_ddl.set_attribute('sqltext_pref', 'detail_key', 'hash_value');
ctx_ddl.set_attribute('sqltext_pref', 'detail_lineno', 'piece');
ctx_ddl.set_attribute('sqltext_pref', 'detail_text', 'sql_text');
end;
/
begin
ctx_ddl.create_preference('sqltext_lex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('sqltext_lex', 'continuation', '_');
ctx_ddl.set_attribute ('sqltext_lex', 'printjoins', '_');
ctx_ddl.set_attribute ('sqltext_lex', 'index_themes', 'NO');
ctx_ddl.set_attribute ('sqltext_lex', 'index_text', 'YES');
end;
/

    接下来创建全文索引.

CREATE INDEX SQL_CTX on SQLS(body) indextype is ctxsys.context
parameters('datastore sqltext_pref LEXER sqltext_lex');

    接下来就可以查询了.

SELECT HASH_VALUE FROM SQLS WHERE CONTAINS(BODY,'tablename and ...') > 0

    接下来要好好用用这个功能了.

    最近见到一条开发人员写的UPDATE语句, 觉得没什么不对, 可又觉得有地方不对, 因为性能低下.

UPDATE A SET
  (COL2, COL3) = (SELECT COL1,'T'
      FROM B WHERE B.COL1=A.COL1)
WHERE EXISTS
  (SELECT B.COL1 FROM B WHERE B.COL1=A.COL1)

    也就更新几十万条记录, 没这么慢的啊. 最后发现这个SQL语句可以简化为如下.

UPDATE A SET COL2=COL1, COL3='T'
WHERE EXISTS
  (SELECT B.COL1 FROM B WHERE B.COL1=A.COL1)

    说明还有很多可以提高的地方.

    最近在调数据迁移的性能, 其中有下面这样的一句SQL语句, 表的大小为25GB, 记录数为1亿.

INSERT /*+ APPEND */ INTO B
  SELECT /*+ PARALLEL(A,4) FULL(A) */
        SEQ_B.NEXTVAL, A.*
FROM A;

    这一步在很好的存贮及主机上测试时, 居然用了26分钟, 忍无可忍, 一定要调整一下. 仔细考虑了一下, 因应是序列的取下个值太慢的问题, 序列原来的CACHE值为20, 于是将脚本改成如下:

ALTER SEQUENCE SEQ_B CACHE 2000;
INSERT /*+ APPEND */ INTO B
  SELECT /*+ PARALLEL(A,4) FULL(A) */
        SEQ_B.NEXTVAL, A.*
FROM A;
ALTER SEQUENCE SEQ_B CACHE 20;

    这样改了后, 10分钟就完成了, 有效地节约了15分钟.

    在优化数据迁移的过程中, 发现有这样一段脚本, 要将一个表的数据分到很多的小表中. 如下所示(A表比较大, 其他的表相对较小):

CREATE TABLE A_1
  SELECT A.* FROM A
  WHERE A.ID IN (SELECT ID FROM B)
  WHERE A.ID IN (SELECT ID FROM C)
  WHERE A.ID IN (SELECT ID FROM D)
  WHERE A.ID IN (SELECT ID FROM E)

CREATE TABLE A_2
  SELECT A.* FROM A
  WHERE A.ID NOT IN (SELECT ID FROM B)
  WHERE A.ID IN (SELECT ID FROM C)
  WHERE A.ID NOT IN (SELECT ID FROM D)
  WHERE A.ID IN (SELECT ID FROM E)

CREATE TABLE A_3
  SELECT A.* FROM A
  WHERE A.ID NOT IN (SELECT ID FROM B)
  WHERE A.ID IN (SELECT ID FROM C)
  WHERE A.ID IN (SELECT ID FROM D)
  WHERE A.ID NOT IN (SELECT ID FROM E)

......

    每一步的执行步骤都在8-10分钟左右, 总共有类似的步骤7步, 全部耗时约60分钟. 于是将过程改写了一下: