木匠的微型博客 Charlie Twitter

    follow me on Twitter

    Tuesday, March 31, 2009

    另一种方法显示ROWID扩充信息-table function

    TrackBack: http://www.ningoo.net/html/2009/how_to_get_extent_id_from_rowid.html

    NinGoo的把常用工具打包进工具箱的方法很好.
    然而木匠天生缺乏分类和组织能力,一路坚持学习,持续遗忘,自身却没有留下啥积累;好比猴子搬苞谷,抱在怀里的玉米棒永远也不会增多,郁闷.
    所以鄙人决定依靠GTD方法,每周总结学到的新知识,然后放进自己的便携工具箱.

    好了,鉴于本人不喜欢dbms_output.put_line,索性把NinGoo的RowID工具改造成了Table Function.
    通过简单的查询,可以在任何地方使用.

    后面附的PL/SQL Package源代码,包含了许多PL/SQL编程优秀实践,将来找机会慢慢展开来讲.

    用法:

    SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));

    COLUMN_VALUE
    --------------------------------------------------------------------------------
    rowid: AAARUtAAEAAABm2AAA
    file_id: 4
    block_id: 6582
    data_object_id: 70957
    owner: INVDB
    object_name: BOOK_UPLOAD_LOG
    extent_id: 0

    7 rows selected.

    tb_ 是toolbox的缩写.



    工具用户需要的对象权限:

    grant select on dba_objects to utility;
    grant select on dba_extents to utility;

    源代码:

    注意: 如果你还没有安装捕获错误工具包u$err和(instrument)代码跟踪包debug, 可以先用备注屏蔽掉相关的代码.


    CREATE OR REPLACE PACKAGE UTILITY.tb_obj
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview:
    |
    | Author(s):
    |
    | Modification History:
    | Date Who What
    |
    */
    IS

    TYPE print_tab IS TABLE OF varchar2(200);

    FUNCTION rowid_info (p_rowid in varchar2) return print_tab PIPELINED DETERMINISTIC;
    PROCEDURE subprogram_name;

    END tb_obj;
    /

    CREATE OR REPLACE PACKAGE BODY UTILITY.tb_obj
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview:
    Tool box to get object information.

    | Author(s):
    |
    | Modification History:
    | Date Who What
    31-Mar-2009 Charlie (Yi): Create the package.

    It meets user requirements
    It is maintainable
    It runs fast enough to minimize user frustration

    **) Pl/SQL development workflow, Four steps of preparing an application

    - Validate program requirements
    # ask lots of questions
    # what users ask for is not always the easiest way to solve a problem
    # consider other approaches, include business processes and programming algorithms
    - Implement header of the program
    # good name for the program, accurately represent the purpose of the program
    # inputs and outputs
    # overload sub-procedure ?
    - Define the test cases
    # Verify it works
    # how will I know when I am done with this program
    - Build test code

    |
    |
    */
    IS
    PROCEDURE initialize_pkg
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE( 'tb_obj','init' );
    END initialize_pkg;

    FUNCTION rowid_info (p_rowid in varchar2)
    return print_tab PIPELINED DETERMINISTIC
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview: Display extended rowid information.
    |
    | Author(s): Charlie MuJiang
    |
    | Modification History:
    | Date Who What
    31-Mar-2009 Charlie (Yi): Create the function.

    Privilege:

    grant select on dba_objects to utility;
    grant select on dba_extents to utility;

    Call example:

    SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));

    COLUMN_VALUE
    --------------------------------------------------------------------------------
    rowid: AAARUtAAEAAABm2AAA
    file_id: 4
    block_id: 6582
    data_object_id: 70957
    owner: INVDB
    object_name: BOOK_UPLOAD_LOG
    extent_id: 0

    7 rows selected.

    |
    */
    IS

    ln_block_id number;
    ln_file_id number;
    ln_object_id number;
    ln_extent_id number;
    ls_object_name varchar2(30);
    ls_owner varchar2(30);

    PROCEDURE initialize
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION( 'rowid_info' );
    END initialize;

    PROCEDURE cleanup
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
    END cleanup;
    BEGIN
    initialize;
    /*
    Main body of program
    */
    debug.f('start by user %s', user);

    select dbms_rowid.ROWID_BLOCK_NUMBER(p_rowid),
    dbms_rowid.ROWID_RELATIVE_FNO(p_rowid),
    dbms_rowid.ROWID_OBJECT(p_rowid)
    into ln_block_id,ln_file_id,ln_object_id
    from dual;

    select owner,object_name
    into ls_owner,ls_object_name
    from dba_objects
    where data_object_id=ln_object_id;

    PIPE ROW(' rowid: '||p_rowid);
    PIPE ROW(' file_id: '||ln_file_id);
    PIPE ROW(' block_id: '||ln_block_id);
    PIPE ROW('data_object_id: '||ln_object_id);

    for c in (
    select extent_id,
    owner, segment_name,partition_name, segment_type,tablespace_name
    from dba_extents
    where
    owner=ls_owner and
    segment_name=ls_object_name and
    file_id=ln_file_id and
    ln_block_id between block_id and block_id + blocks - 1
    )
    loop
    PIPE ROW(' owner: '||c.owner);
    PIPE ROW(' object_name: '||c.segment_name);
    PIPE ROW('partition_name: '||c.partition_name);
    PIPE ROW(' segment_type: '||c.segment_type);
    PIPE ROW(' tbs_name: '||c.tablespace_name);
    PIPE ROW(' extent_id: '||c.extent_id);
    end loop;

    debug.f('process %s rows', SQL%RowCount);

    cleanup;
    EXCEPTION
    WHEN NO_DATA_NEEDED THEN
    cleanup;
    return;
    WHEN OTHERS
    THEN
    /* Don't forget to clean up here, too! */
    cleanup;

    /* Standard error logging mechanism */
    u$err.err;
    RollBack;
    raise;
    END rowid_info;

    PROCEDURE subprogram_name
    /*
    | Copyright Information Here
    |
    | File name:
    |
    | Overview:
    |
    | Author(s):
    |
    | Modification History:
    | Date Who What
    |
    */
    IS
    PROCEDURE initialize
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_ACTION( 'subprogram_name' );
    END initialize;

    PROCEDURE cleanup
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
    END cleanup;
    BEGIN
    initialize;
    /*
    Main body of program
    */
    debug.f('update %s rows', SQL%RowCount);

    cleanup;
    EXCEPTION
    WHEN OTHERS
    THEN
    /* Don't forget to clean up here, too! */
    cleanup;

    /* Standard error logging mechanism */
    u$err.err;
    --RollBack;
    raise;
    END subprogram_name;

    BEGIN
    initialize_pkg;
    END tb_obj;
    /


    deprecated ROWID hint 废弃的行标识提示

    在Oracle 10g以后,RowID 这个SQL提示就被废弃了.搜索Google也找不到替代方案.
    在一些应用场景里面利用RowID存取路径,还是很有帮助的. 比如回改, 就是在用户界面显示用户感兴趣的数据,数据被修改以后,再更新回数据库.

    那么怎样强制优化器使用存取路径TABLE ACCESS BY ROWID呢? 我暂时发现了两种方法:

    1) 使用No_Index SQL提示
    2) 使用表达式或者运算符禁止索引扫描.

    关于具体的搭建测试环境和演示, 参考文章NO_INDEX hint and deprecated ROWID hint.

    测试结果
    --
    当查询优化器采用TABLE ACCESS BY USER ROWID存取路径时,我们发现逻辑读(Buffer列)由2降到1.

    Saturday, March 28, 2009

    Earth Hour 关灯一小时

    在维多利亚,地球日被提前到了今天,星期六.

    各种人物提出了多种方式度过 "地球一小时".
    比如看电视,点蜡烛.这不还是继续浪费能源吗? 穿了个马甲而已.

    我有两个计划,8:30pm游泳完以后,直接上床睡觉. 或者在黑暗中静思冥想,想想家庭生活有哪些项目可以改进,修马桶,给外墙刷油漆,屋顶换瓦, 想想工作中的哪些数据处理逻辑可以改进,提高系统的扩展性,可靠性,应用各种11g的新特性.

    去野外看星星 也不错,可惜维多利亚阴天下雨; 维多利亚(BC省西海岸)有个体育活动,夜晚健行(night hiking),可以去中国海滩(China beach) 欢德福卡野径(trail),我的同事老奔驰等人去过好几次了,下次跟上他们;
    或者给多年没有联系的老朋友打电话.


    Enjoy sitting in the dark!

    Thank you for your support!

    参考:
    --
    <http://www.earthhour.org/home/> Saturday March 28th @ 8:30pm!

    What is it?
    Switch off your lights for one hour and send a message to the world.

    Reality check:
    Earth Hour is a symbolic event. Turning off our lights for an hour won’t stop climate change but it does demonstrate that our individual action is important and adds up to make a big difference.

    Results & photos from 2008
    What’s happening in Vancouver
    Here are some simple tips to make Earth Hour a success:
    1. Turn off all non-essential lights on Saturday, March 28 at 8:30 pm.
    2. Encourage family, friends and local businesses to participate.
    3. Sign-up (AbeBooks.com already has!)
    10% Off @ WWF Store
    As a thank you for signing up:
    Enjoy 10%* off at the WWF Store where you will find unique and sustainable products. Visit wwfstore.ca and enter promo code EH2009.
    So let’s get some more businesses to sign up & turn off those lights on Saturday night!


    维多利亚大学(UVic)的图书馆.

    Monday, March 09, 2009

    Troubleshooting Oracle Performance下载文件更新

    TrackBack: http://antognini.ch/2009/03/troubleshooting-oracle-performance-downloadable-files/

    像我一样的TOP书迷们注意了, TOP脚本文件更新了, 这里是下载地址链接: http://antognini.ch/top/downloadable-files/.

    供参考: 我的学习方法是这样的,先读完整本书(每周100页), 然后到附录A, 顺着每一章的脚本目录(page 552~562) ,做练习,依靠实践加深印象,巩固记忆.不然,过不了两天,学到的知识点就会忘掉大半.


    加拿大西海岸三月份又下雪了,维多利亚市也不例外,预计降雪量是5厘米.
    气温: -4°C~2°C. 注意保暖,行车注意安全.
    今天又开始夏时制了,少睡一个小时, 困哉.
    .

    Thursday, March 05, 2009

    Index space management索引空间管理

    这回咱先问个问题, 啥时候数据库自动回收索引的空闲空间?

    这里是我昨天学到的索引空间管理知识:

    一个UPDATE语句,对索引来说,就是一个DELETE和一个INSERT,删掉旧键值,插入新键值.
    如果是唯一索引(unique index),删掉旧键值的空槽位可以被同一个事务里面接下来的操作重用.
    如果不是唯一索引(unique index),删掉旧键值的空槽位,只有在事务提交以后,才可以被重用.
    索引段上,只有INSERT和DELETE操作.

    如果删除了大部分数据,再没有新的数据插入,用alter index coalesce;释放可重用的空间. coalesce应该不会锁住表,我需要进一步确认.

    参考
    ----
    Oracle® Database SQL Language Reference
    11g Release 1 (11.1)

    ALTER INDEX ... SHRINK SPACE COMPACT 等价于 ALTER INDEX ... COALESCE.

    How does insert and delete use space regarding to indexes?

    查看索引空间: SQL>exec segment_space_a(P_SEGNAME=>'INDEX_Name',p_type=>'INDEX');

    Skip unchanged columns when UPDATE时,如何跳过没有改动的列

    现在很难在 AskTom 提新的问题了.
    只好找个类似的问题,然后跟贴评论,问一个新问题. 但是一定要和原帖主要问题紧密相关,不然,Tom兄会毫不留情的删掉你的评论. 嘻嘻.

    好了,我的问题是:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1524420700346152252

    先简述一下业务逻辑, 我们的业务模式类似eBay, B2C, 我们的主要商品是图书, table book, 书商每天上传文件更新他们的库存.
    书商不管三七二十一,送上所有图书的信息,无论是要改动的属性,还是不需要改动的属性.
    大部分情况只是改动一两个短列,比如数量,单价,ISBN;少部分情况修改长列,比如书名,作者,出版社,图书简介...等等.
    这就产生了一个重复(冗余)修改的问题,会浪费PGA内存,产生十倍之多的Redo/Undo.

    如何跳过这些没有修改的列呢?

    今天耗费了一整天跟Tom兄切磋.

    我设计的解决方案都在上面链接里面了. 还没有最后成形,处在演变进化(evolution)当中.

    对了, 我的署名是Charlie MuJiang from Victoria, BC Canada, AskTom不能存中文,所以用拼音MuJiang.


    再来一个维多利亚的BC省议会大楼,2005年我刚到维多利亚时,住的公寓走到议会大楼不到十分钟,每周过来散步数次.

    Monday, March 02, 2009

    PATH in 10053 Trace 的存取路径代码和解释

    越来越多的DBA和Developer开始研究CBO相关的10053 Trace.
    可以参考老刘(Lewis)的Cost Based Oracle Fundamentals第14章 - The 10053 Trace File.

    Oracle 11g之前,PATH 用代码表示,阅读起来极不方便, 因此木匠干脆罗列出来有关表扫描的Access PATH代码含义:

    # PATH in 10053 trace
    -- table access path


    0 parallel hint
    1 no access path spec
    2 table scan
    3 index unique
    4 index range
    5 index and-equal
    6 order by using an index
    7 open cluster
    8 hash cluster
    9 rowid lookup
    10 range scan backwards
    11 rowid range scan
    12 driving_site hint
    14 cache hint
    15 nocache hint
    16 partitions hint
    17 nopartitions hint
    18 anti-join
    19 index rowid range scan
    20 bitmap index
    21 parallel_index hint
    22 noparallel_index hint
    23 index fast full scan
    24 swap inputs to join
    25 fact table
    26 not a fact table
    27 merge of this view
    28 do not push join predicate into this view
    29 push join predicate into this view
    30 no_merge of this view
    31 semi-join