未知都是已知的: Buffer gets increase on the same SQL

Buffer gets increase on the same SQL

作者:Fenng
出处:http://www.dbanotes.net
日期:August 10, 2005

« Alibaba acquire Yahoo! China ? | Blog首页 | 胡乱说说 »

同样一条SQL ,有的时候 buffer get 会暴增?! Oracle_L 中有人提了一个这样的问题：

I have a batch process that executes individual transactions, normally a transaccion e.g. a simple select would take 8-10 buffer gets but in the batch processing it takes 45 buffer gets.

Zhu Chao (Chao_ping,这家伙现在一篇文章都不写，只能从邮件列表里看到他的踪迹) 给了一个解释：

the job is processing some very hot blocks. So it always need to reverse back and find the CR block from buffer, so it will generate some more buffer gets for that execution.

如果是因为Hot Block 的原因,那么主要的症状应该是 Wait. 如果这个 SQL 在运行的时候数据已经发生了变化,那么为了维持一致性不可避免的会生成回滚,所以这个解释更为准确一些:

If a query does a consistent get on a block that has been changed since that query began or that had uncommitted changes at the time that that query began, then it is necessary to rollback those changes for read consistency. The consistent changes statistics counts the number changes rolled back. However, most consistent gets do not require any such rollback, and so it is normal for the number of consistent gets to be much greater than the number of consistent changes. This is reflected in the no work - consistent read gets statistic

我们不妨来做个例子.假定我们现在有两个Session,首先在第一个窗口做如下操作


SQL> create table t as select * from all_objects where rownum<1001;

Table created.

SQL> select owner,object_name from t where owner='SYS' for update;

在第二个Session,进行一个简单的统计:

SQL> SELECT COUNT(*) FROM T;

......

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1055  consistent gets
          0  physical reads
       1352  redo size
        379  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

注意到产生了 1055 个一致读.接着我们rollback 第一个Session ,然后在第二个Session中继续这个统计:

SQL> SELECT COUNT(*) FROM T;

.....

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

只有 29 个一致读了.验证了前面的解释.

又: xzh 说"这样肯定不行的,再说生产库上不可能锁表这么久的". 其实我这个例子不过是模拟了一个比较特殊的情况而已.邮件列表中提问者的场景是在一个批处理中的,这样的可能性是有的.

Posted by Fenng at August 10, 2005 03:37 PM
Last updated at 06:20 PM on August 20, 2005

相关文章：

对 Tom 的 RunStats 包的两点说明 - Sep 21, 2005
Undocumented secrets-- 一场新的争论 - Aug 21, 2005
AIX 上影响 Oracle 的参数 - Aug 05, 2005

Trackback Pings

TrackBack URL for this entry:
http://www.dbanotes.net/mt/mt-tb.cgi/250

Listed below are links to weblogs that reference Buffer gets increase on the same SQL:

» De La Croix from De La Croix
Juvanile Diabetes [Read More]

Tracked on May 17, 2007 03:53 AM

» Bears Best Golf Las Vegas from Bears Best Golf Las Vegas
Perr To Peer [Read More]

Tracked on May 17, 2007 03:57 AM

» Dock Ipod Onkyo from Dock Ipod Onkyo
Landscape Front House Curb Appeal Ideas [Read More]

Tracked on May 17, 2007 04:01 AM

» Lyrics To Jane Olivor's You from Lyrics To Jane Olivor's You
Eo1004b Compaq Kvm [Read More]

Tracked on May 18, 2007 10:54 PM

» Fiend Coal Chamber Lyrics from Fiend Coal Chamber Lyrics
Az Dui Laws [Read More]

Tracked on May 18, 2007 10:58 PM

» Springfield College Graduate from Springfield College Graduate
Croupy Cough [Read More]

Tracked on May 19, 2007 05:34 PM

» Ipod Mini Release Date from Ipod Mini Release Date
Con Pro Star Plasma Welder Cutter [Read More]

Tracked on May 19, 2007 11:22 PM

» Carrying Case For Jbl Speakers from Carrying Case For Jbl Speakers
Javier Gonzalez [Read More]

Tracked on May 19, 2007 11:25 PM

» Javanese Furniture from Javanese Furniture
Lawyer In Atlanta [Read More]

Tracked on May 19, 2007 11:26 PM

» Sbil from Sbil
Thermonuclear Devices [Read More]

Tracked on May 20, 2007 01:06 PM

» Swr Big Ben from Swr Big Ben
Cd Jewel Case Artwork [Read More]

Tracked on May 20, 2007 01:08 PM

» Rid Computer Of Viruses from Rid Computer Of Viruses
Mental Foramen [Read More]

Tracked on May 20, 2007 03:51 PM

» Pet Animal from Pet Animal
Kelp And Lecithin [Read More]

Tracked on May 20, 2007 03:54 PM

» Aarp Medicare Supplement Agent from Aarp Medicare Supplement Agent
Alphabet Letter In Center Of Crocheted Afgan [Read More]

Tracked on May 20, 2007 03:55 PM

» Pettifogging from Pettifogging
Coconut Cay Resort [Read More]

Tracked on May 21, 2007 05:26 AM

» Best Foods To Eat To Loose Weight from Best Foods To Eat To Loose Weight
Download Free Morpheus Music [Read More]

Tracked on May 21, 2007 05:29 AM

» Zonealarm Free Hacker Warnings from Zonealarm Free Hacker Warnings
Ugky [Read More]

Tracked on May 21, 2007 05:30 AM

» Troy Garrity from Troy Garrity
Low Cost Uk Website Hosting [Read More]

Tracked on May 21, 2007 10:38 AM

» Unlimited Local And Long Distance Plans from Unlimited Local And Long Distance Plans
Town Of Sand Lake [Read More]

Tracked on May 21, 2007 06:25 PM

» How To Consolidate A Citiassist Loan from How To Consolidate A Citiassist Loan
Catering Linen Sales [Read More]

Tracked on May 21, 2007 06:28 PM

» Npr Philadelphia from Npr Philadelphia
Jobs Search Australia [Read More]

Tracked on May 22, 2007 06:52 PM

» In Louis Mcdonalds Osborn Search Strip Vid from In Louis Mcdonalds Osborn Search Strip Vid
Iko Iko Zap Mama [Read More]

Tracked on May 23, 2007 01:09 PM

» Free First Grade Lesson Plans For Synonyms And Antonyms from Free First Grade Lesson Plans For Synonyms And Antonyms
Car Road Tests And Performance [Read More]

Tracked on May 24, 2007 07:17 AM

» Reviews Uk Most Attractive Conservatories from Reviews Uk Most Attractive Conservatories
Oktava Mk 319 [Read More]

Tracked on May 24, 2007 12:59 PM

» Hourly Weather Report from Hourly Weather Report
Wholesale Japanese Import Video Games [Read More]

Tracked on May 24, 2007 03:22 PM

» Lori Musil Architectural Drawings from Lori Musil Architectural Drawings
Web Cam Traverse City [Read More]

Tracked on May 25, 2007 09:41 AM

» Frame Animation from Frame Animation
Unique Baby Names For Boys [Read More]

Tracked on May 25, 2007 12:02 PM

» Altered Fates from Altered Fates
Murphy Bed Patterns [Read More]

Tracked on May 25, 2007 12:07 PM

» Paperspacedesignseasy Way To Get Large Poster Size Prints from Paperspacedesignseasy Way To Get Large Poster Size Prints
London Heathrow British Tickets Banjul [Read More]

Tracked on May 25, 2007 12:07 PM

» Wrinkle Resistant Shirt from Wrinkle Resistant Shirt
Sarcodiosis Lung Xray [Read More]

Tracked on May 25, 2007 09:34 PM

» Roast Crock Pot from Roast Crock Pot
Points And Origination Fees [Read More]

Tracked on May 26, 2007 07:11 AM

» Lag Screws Loads from Lag Screws Loads
Bmw Z4 Owners [Read More]

Tracked on May 28, 2007 09:58 PM

» Auckland Park from Auckland Park
Children Music Theory [Read More]

Tracked on May 29, 2007 01:08 AM

» Lighthouse Church Furnishings from Lighthouse Church Furnishings
College Level Theater History Courses On Line [Read More]

Tracked on May 29, 2007 01:13 AM

» Epson Printer Heads from Epson Printer Heads
Kentucky Cabins For Rent [Read More]

Tracked on June 1, 2007 08:39 PM

» Century Plant Lyrics from Century Plant Lyrics
Contemporary Furniture Staffordshire [Read More]

Tracked on June 1, 2007 08:41 PM

» Woman Is Walking The World For Breast Cancer from Woman Is Walking The World For Breast Cancer
Loyola University Stritch School Trauma Illinois Association [Read More]

Tracked on June 2, 2007 12:32 AM

» Scripps Inn Lajolla from Scripps Inn Lajolla
Custom Tradeshow Poster Printing [Read More]

Tracked on June 2, 2007 03:01 AM

» Johnny Bench And World Series And Statistics from Johnny Bench And World Series And Statistics
Tips For Business Ameriquest Mortgage Howtomakewea [Read More]

Tracked on June 2, 2007 03:04 AM

» Microsoft Frontpage Crackz from Microsoft Frontpage Crackz
Lacuna Coil Comalies Lyrics [Read More]

Tracked on June 2, 2007 08:00 AM

» Compact Flash Network Adapter from Compact Flash Network Adapter
Microsoft Office 97 Professional Edition [Read More]

Tracked on June 2, 2007 03:19 PM

» T3 T4 Tsh from T3 T4 Tsh
Thelightsaber.com [Read More]

Tracked on June 3, 2007 06:30 AM

» Mini Plug To Rca Cable from Mini Plug To Rca Cable
Savage Mill Md [Read More]

Tracked on June 3, 2007 06:33 AM

» Chico State Coeds Naked Labor Day from Chico State Coeds Naked Labor Day
Cd Download Free Music [Read More]

Tracked on June 3, 2007 08:59 AM

» Sell Northwest Territory Land Grant Document from Sell Northwest Territory Land Grant Document
Laughlinnv [Read More]

Tracked on June 3, 2007 01:52 PM

» Lowepro Orion Aw Belt Pack from Lowepro Orion Aw Belt Pack
Astrolabe History [Read More]

Tracked on June 3, 2007 09:16 PM

» Auto X Change from Auto X Change
Stm Laptop Bag [Read More]

Tracked on June 4, 2007 07:21 AM

» Texas Land Designation Indigenous Blackland Grants from Texas Land Designation Indigenous Blackland Grants
Bike And Helmet Coloring Books [Read More]

Tracked on June 4, 2007 02:51 PM

» Vxfsd from Vxfsd
International Flight Discount Airline Ticket Khartoum [Read More]

Tracked on June 4, 2007 02:54 PM

» Uu Letter Of Agreement from Uu Letter Of Agreement
Patent Number And Current Search [Read More]

Tracked on June 4, 2007 05:21 PM

» City Of Kingston Ontario from City Of Kingston Ontario
Reiki For Dogs [Read More]

Tracked on June 4, 2007 10:29 PM

» Kaioj Osdod from Kaioj Osdod
Hi,excellent site! Thank you! [Read More]

Tracked on June 13, 2007 04:02 AM

» Hasedg Fagsa from Hasedg Fagsa
Hi all! I really like this great site! [Read More]

Tracked on June 19, 2007 08:07 PM

» Hasedg Fagsa from Hasedg Fagsa
Hi all! I really like this great site! [Read More]

Tracked on June 19, 2007 08:08 PM

» Barz Ceweds from Barz Ceweds
Very nice work,You are doing great job! [Read More]

Tracked on June 23, 2007 06:05 AM

» Barz Ceweds from Barz Ceweds
Very nice work,You are doing great job! [Read More]

Tracked on June 23, 2007 06:06 AM

» Barz Ceweds from Barz Ceweds
Very nice work,You are doing great job! [Read More]

Tracked on June 23, 2007 06:08 AM

Buffer gets increase on the same SQL

Trackback Pings

Comments

Post a comment