劳动节，劳动最光荣！
早起干活，还比较顺利，按时结束，统计的接力棒交给下一位同志。

突然想起了blog存在的2年多了，于是翻了一下Archives，2006年和2007年的这时候我在干啥呢？

2006年的这时候刚开了blog，还属于三分钟热度，五一七天乐之一 乒乓夺冠，“七天乐”无聊文章从此开始，现在没拉，黄金周变成小长假，其实对我没影响，4号铁定加班，不如不放假 :)
2007年三四月开始忙房子装修以及结婚诸事，累并快乐着。

PR仍然保持2，trackback一下dimlau，看到的话加个我的链接吧^_^

If you’ve missed our presentations on MySQL Users Conference you can catch up now by taking a look at the slides, which are now published in presentations section of our company web site. You can also find a lot of old presentations in the same location.

Enjoy !

---

Entry posted by peter | No comment

Add to: | | | |

Author:丹臣 posted on Taobao.com

今天给大家介绍一个SQL优化案例，这是statpack中逻辑读排名第一的SQL.当前创建的索引建在(username,ends,approve_status,promoted_status)上。
[coolcode]
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
————- ———— ————– —— ——– ——— ———-
116,608,373 164,083 710.7 40.6 7027.07 11922.30 3701069644
Module: java@test.cm2 (TNS V1-V3)
select count(*) from test
where username = :1 –这是一个高势列，
and ends>sysdate
and approve_status in (0,1,-9)
and id <> :2 –这是主键
and promoted_status = 1
[/coolcode]

如果大家见到这样的SQL语句会怎么样优化？通常的做法，是在当前索引中冗余id字段，以避免回表。但这样要去调整这张大表的索引.

在看到上面的SQL后，询问开发能否明确的知道id=:2并且满足其它条件的这样的记录是否一定存在。开发经过查证后，最后的答复是无法肯定.既然在应用层无法确定，那也要想个办法来解决大量回表的问题。在经过仔细观察后，我将上面这条SQL语句转换成下面两条SQL以及最后一步应用逻辑来实现：

第一条SQL：
[coolcode]
Select/*+ index(a, PK_test_ID) */ count(*) from test a
where id=:1 and ends>sysdate and approve_status in (0,1,-9) and promoted_status = 1 and username=:2
[/coolcode]
第二条SQL：
[coolcode]
select count(*) from test
where username = :1 and ends>sysdate and approve_status in (0,1,-9) and and promoted_status = 1
[/coolcode]
第三步，将两个结果相减即可实现业务

我们在做SQL优化时，如何把一条SQL根据需要等价转化成多条，需要考虑当前的应用逻辑，以及当前数据库中索引的情况，优化便会事半功倍。如何跳出ORACLE去思考问题，希望这个优化案例能对大家有所启示。

Add Comments(0)

今天协助其他部门解决了这个错误。由于是在其他人的机器上解决的问题，且由于时间比较紧迫，问题需要马上解决，因此没有留下相应的解决步骤，只好根据回忆来描述一下问题，以及解决思路和步骤。首先看到的现象是通过DBLINK向远端数据库的插入记录时出现了上面的错误，除了这个错误信息外，后面跟着ORA-2063错误。ORA-02049: timeout: distributed transaction waiting for lockCause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.Action: treat as a deadlock看到这个错误，第一个反应是碰到了分布式事务的两阶...

天天都在电脑前工作，写方案、写报告、写总结、写博客，通过MSN和QQ与朋友聊天，写作质量本应越来越高。可刚才看了这几天发布的日志，发现了太多的错别字，还出现了以前较少出现的标点符号错误和语法错误。一想到这样的东西被大家看到了，禁不住羞愧难当。

以前用五笔输入法，虽然速度慢，但错别字比较少。自从换成搜狗拼音输入法之后，出现错别字的几率就成倍地增加了。特别是用MSN和QQ聊天时，经常是通篇有大量的错别字。总感觉是聊天，所以有点错别字无所谓。没想到时间长了就慢慢习惯了错别字的存在，习惯了写完东西后不检查就发送。

更严重的是，由于目前公司业务量大，很多时候工作都是在短时间内赶出来的，写完了基本上没有多少时间去检查，养成了写完文档就实在不愿意再看一眼的坏毛病。所以每当回头再看时，就会发现文档中的大量的错别字，感觉真的很丢人。

很多朋友曾提过我博客有错别字，错别字不仅让人觉得作者不严谨，更多的是对读者的不尊重。一直在下决心改正这个毛病，但进步并不大，反而还有愈演愈烈的迹象。

刚才经常写博客的朋友，你的博客中是否也存在大量的错别字，是否也经常会因大量的错别字而羞愧？

• 主机换到bluehost了 (1)
• 为什么留言这么少？ (23)
• 新博客终于开张了 (4)
• 搞存储的不会建个人网站？ (7)
• 换了博客主题 (3)
• 把个人Blog作成一个成功品牌的10个步骤 (0)
• 我在写blog中所犯的错误 (0)
• 存储部落三天无法访问 (2)
• 存储部落已升级到wordpress2.5 (0)
• 存储部落连续两天出现中断 (3)

This article provides a primer for using Oracle 11g Database Replay to effectively predict the performance of rapidly changing applications within increasingly fluid database environments.

Some days... Some days the questions just make me scratch my head....

ROW TO COLUMN CONVERSION   April 30, 2008 - 5am US/Eastern 

Reviewer: ROOPA from india 

HOW TO CONVERT YEARLY DATA INTO MONTHLY DATA? 

Followup   April 30, 2008 - 10am US/Eastern: 

BY MAKING IT UP I GUESS? 

Could it be more ambiguous?  I have yearly data (one presumes that is data aggregated to the level of a year).  How do I convert that into monthly data.  Short of "making it up", I have no idea... do you?

Now, they did followup later with

 

table1 format
MONTH                  AMOUNT_PAID
01.12.2006 00:00:00        5395
01.11.2006 00:00:00         567
01.11.2006 00:00:00        1974
01.04.2007 00:00:00        2462
01.04.2007 00:00:00        1974
01.11.2006 00:00:00        5395
01.02.2008 00:00:00        5395   

table2 format
MONTH         JAN  FEB MAR APR MAY JUN JUL AUG SEP OCT  NOV  DEC
01-DEC-2006   0    0   0   0   0   0   0   0   0   0    0   5395
01-FEB-2007   0   5395 0   0   0   0   0   0   0   0    0    0
01-NOV-2006   0    0   0   0   0   0   0   0   0   0   5395  0

how to convert table1 format into table2 format i.e yearly data to monthly data.

 

Now, I don't know about you - but table1 looks suspiciously like "discrete observations with an associated date - the date consisting of year, month and day".  I certainly do not see "yearly data".

I also like how they used 5,395 three times, just to make it as ambiguous as possible (wonder what happened to 567, 1,974 and so on?)  They skipped what are likely the interesting output examples - their "yearly data that is not yearly" that has more than one observation in a month.

I guess, I GUESS, their date format is DAY-MONTH-YEAR now, that changes table1 to look suspiciously like "discrete observations with an associate date - the date consisting solely of year and month".  But, we'd be GUESSSING.

And I see a 01-FEB-2007 in table2, but I see 01.02.2008 in table1.  I have to presume that is a "typo"

sigh, and there wasn't even a create table, insert into table supplied - they want me to do that.

And the output looks utterly useless.  If column 1 is "01-dec-2006", why bother having a DEC column in the output?  We already KNOW what month this is for - every row will have 11 zeros, every single one.  Seems a bit "silly".

Asking good, well formed questions is not an art, not magic.  It is however a skill.  And I find many times that when I frame my question for someone else - I find my answer.

Goes back to yesterdays post.  Writing software requires some things - a plan being one of them.  Until you can phrase your requirements in a detailed fashion - I'm not sure you know what they are or why you are doing something.....

 

We have a runner up for second place..

entire question is:

Record level Audit Old\New value same Error  April 30, 2008 - 9am US/Eastern

Reviewer:  sasirekha  from India

I have some problem using Audit Record.

Generally if we map a record to the audit Record, it will track the details of

the table insert, update, delete.

While I update the record, it will insert two different row in audit record

like  Audit Action K and N.

But both are contain the same values..

I need the old and new value.

Can any one please give me the solution with this !

questions from me:

• what is an "audit record", must be well defined - they are using it



• "if we map a record to the audit record" - not really sure what that means



• "like audit action K and N" - K and N?? huh?



• "but both are contain the same values - I need the old and new value" - well, why didn't they access the old and new values?



• where is the sample, the example, the thing that shows us what you are really doing....

And in a close 3rd place...

How to speed up the insert and update in a partion table of more than 60 millions Rows ?

Best regards,

Sam.

I don't get it some days, just do not get it.

作者：Fenng 发布在 dbanotes.net.

下班后去南瓜同学的创业办公室拿票。这个我五一不出去了，准备二号三号两天去参加这个首届杭州·西湖现代音乐节。说是"现代音乐节"，其实几乎清一色的民谣歌手。

"叶蓓的深呼吸将在午后的阳光中浸透西湖天地的大草坪，小娟米汤般醇厚的声音将在杭州的暮色中响起。还有久违的老狼..."，狗屁，狗屁宣传文案! 去你的叶蓓、小娟、没听说过的朱七甚至老狼，老子只听周云蓬，万晓利就好了！ 如果说更多希望听的，将会是王娟+虎子、姜昕、苏阳。

南瓜同学手里还剩下少数几张 2 号下午的票，还是 8 折的，如果有感兴趣的朋友可以在豆瓣上联系一下他或者是在我这里留言。我个人认为，2 号下午这几个歌手是最值得一听的。尤其是周云蓬，一个人就值回票价的。

另外，南瓜同学最近在打造一个很前卫的音乐网站。预计不久后即可新鲜出炉，现在他也在继续找 UI 主管和 C++ 程序员。我在他那里还顺手牵羊拿了一张与人乐队的首张专辑《遇事拘谨》(淘宝有售)，收获颇丰。

生活在杭州，生活在现场。西湖天地地图。

--EOF--

相关文章|Related Articles

评论数量(1)|Add Comments

本文网址：http://www.dbanotes.net/mylife/hangzhou_music_festival.html
最近作者还说了什么? Follow Twitter / Fenng