Bobby Durrett's DBA Blog

Well, it was a long day but it ended in a fun way.

Today I was back to the normal OpenWorld sessions starting with the general session. It was eye-opening because the speakers described a new CPU chip that they were using in their latest servers.  It had some custom elements to support database processing.  It was strange because I have recently been studying the latest Intel x86 documentation and it was interesting to compare Intel’s chips with the latest Sun/Oracle chip.  I had read about the specialized SIMD instructions in the x86 family that Intel uses to speed up graphics. So, I was not surprised that Oracle is including more complex additions to their new chip with specialized instructions.  Still, I question whether people are really going to buy anything but Intel x86 at this point due to the price/performance.

Next I went to a session describing the way a company used a tool called Chef to manage their Weblogic deployments.  The session topic interested me because we also use Weblogic at US Foods.  But it was a little hard to follow.  Maybe it would have helped if I had been exposed to Chef before hearing the talk. Still, it was good to know that there are tools out there to help automate deployment of new systems.

Next I caught a PeopleSoft in the Cloud talk. It seems that you will install the latest version of PeopleTools in a very different way than you did in the past.  I got the feeling that this was just a part way step toward fully setting up PeopleSoft to run in the cloud.

Then I went to a really cool talk about how Ticketmaster sells 20,000 tickets in one minute. It was about their MySQL architecture.  They have a large farm of MySQL servers supporting the queries behind their ticketing web site.  But, they use Oracle for the updates related to ticket purchases.

Then I went to a talk on Oracle ZFS.  I get the feeling that I need to learn more about ZFS. It seems that ZFS is an ancestor of Delphix and I know that there is a free OpenZFS that I might play with. I think that Tim Gorman, who works for Delphix, mentioned something about OpenZFS  at his Ted talk at Oak Table World Tuesday so there may be some relationship.

Lastly I went to a talk about how you can use Oracle’s Enterprise Manager to support both on site and cloud databases.  It sounds good but I think it still need to mature over time to support the cloud systems more fully.

Then at 5:30 pm I went to a fun bloggers party sponsored by Pythian and the Oracle Technology Network (OTN).  I’m not a big party person but I had a good time.  It was easy to strike up a conversation with people since we had a lot in common.

Anyway, enough for today.  One more day Thursday and then my brain will overflow. 🙂

Bobby

Instead of going to the normal OpenWorld events today I went to OakTable World.  Now my brain is fried from information overload. 🙂

It started at 8 am with a nice talk about hash joins and Bloom filters.  Toon Koppelaars had some nice moving graphics showing how bloom filters work.  I’ve studied Bloom filters before but I’m not sure I understood it with the clarity that I had after this talk.

Then I did my talk at 9 am.  The best part for me was that we had a number of questions.  I ended up skipping several slides because of time but I felt like we helped people get what they wanted out of it by having the questions and discussion.  In retrospect my talk could have used more of an introduction to Delphix itself for this audience but I think we covered the essentials in the end.

Next Kellyn Pot’Vin-Gorman did more of a community service type of talk which was a change of pace.  She had a Raspberry Pi project which was a stuffed bear that would take your picture and post it on Twitter.  It was an example of the type of project that kids could do to get them interested in computer technology.

My brain began to turn to mush with Marco Gralike’s talk on XML and 12c In-Memory Column store.  I’m sure I’m absorbed something but I’m not that familiar with Oracle’s XML features.  Still, at least I know that there are in memory features for XML which I can file away for the future.

Several amusing 10 minute Ted talks followed.  Most notable to me was Jonathan Lewis’ talk about how virtual columns and constraints on virtual columns could improve cardinality estimates and thus query performance.

Cary Millsap talked about a variety of things including things like what he covered in his book.  He shared how he and Jeff Holt were hacking into what I assume is the C standard library to diagnose database performance issues, which was pretty techy.

Gwen Shapira’s talk on Kafka was a departure from the Oracle database topics but it was interesting to hear about this sort of queuing or logging service.  Reminds me in some ways of GGS and Tibco that we use at work but I’m sure it has different features.

Alex Gorbachev gave a high level overview of Internet of Things architectures.  This boiled down to how to connect many possibly low power devices to something that can gather the information and use it in many ways.

Lastly, we went back to the Oracle database and my brain slowly ground to a halt listening to Chris Antognini’s talk on Adaptive Dynamic Sampling.  I had studied this for my OCP but it has slowly leaked out of my brain and by 4 pm I wasn’t 100% efficient.  But, I got a few ideas about things that I can adjust when tuning this feature.

Anyway, brief overview.  I’m back to normal OpenWorld tomorrow but it was all OakTable today.  It was a good experience and I appreciated the chance to speak as well as to listen.

Bobby

It was a good first full day at Oracle OpenWorld.  It started with the keynote led by Oracle’s CEO.  Of course he was very upbeat about Oracle’s products.  But, I found his comments about the economy and the way it affects IT spending more interesting than Oracle’s cloud products.  My translation is that companies have a lot of older systems that they can’t easily retire or upgrade but they want to quickly add all this new functionality.  I see that in my company so it rings true.  I don’t really believe that the cloud is the long-term answer but it makes me wonder what the real answer is.  I always come back to prioritization.  I think prioritizing spending is more important than moving things to the cloud.  You can’t afford to do everything so you have to make tough choices about what to spend your IT dollars on. That’s my opinion at least.

Next I went to a session on Coherence.  I kind of went out of a sense of obligation since our company owns the product.  But, it was a surprisingly good session.  It had a lot in it about Java 8 and the new features in it for parallel processing.  It made me want to dust off my Java skills and generally think about parallel processing in the Oracle database and how it relates to that in Hadoop, Scala, etc.

I went to two sessions on analytics, again out of a sense that I needed to learn about analytics and not due to any enthusiasm about it.  The first session was really interesting, but the 3:30 session almost literally put me to sleep.  The first session reminded me of some of the things in Craig Shallahamer’s forecasting book such as making a model of a system and doing validation of the model.  Analytics seems to follow a similar process. But, by the late afternoon a non-technical session on analytics in banking nearly knocked me out.

Wedged between my two analytics sessions I went to a very cool In-Memory Option boot camp.  I have not had the time or taken the time to look at the In-Memory Option and I got a nice fast hour-long exposure to it.  I don’t know if the other people in the class were lost because there were a lot of explain plans flying by but it is the type of stuff I’m interested in so it was nice that it was so technical.  The In-Memory Option reminded me a lot of Exadata smart scans and hybrid columnar compression.

Strangely enough multiple speakers pronounced columnar differently than I have done so I guess I will have to change.  They emphasize the second syllable but I usually emphasize the first.

I also snuck in to the OakTable World presentation by Tanel Poder.  It had to do with querying Hadoop clusters from Oracle databases using odbc/jdbc.  Pretty cool.  I also got to scope out the venue for my talk tomorrow in the process.

That’s enough for today.  I got a lot of good information.  I’ve slotted tomorrow for OakTable world so it will be interesting to see what all I can learn there.

Bobby

I just finished reviewing my Delphix blog posts in preparation for the talks that I will give during OpenWorld. I find myself referring back to my blog to help me remember what I have done in the past. I was thinking that I needed to jog my memory so that I could answer questions when I give my talks.  Some of the Delphix topics that I am speaking about occurred a year or two ago so my memories are fuzzy.  But, reading my own posts brought a lot of the details back.

I thought I would list the Delphix posts, even though people can find these just by searching my web site. If you are coming to my talk and want more details or if you just want all the Delphix related information on my site I have collected it here.

https://www.bobbydurrettdba.com/2013/02/26/delphix-first-month/

https://www.bobbydurrettdba.com/2013/09/17/delphix-direct-io-and-direct-path-reads/

https://www.bobbydurrettdba.com/2014/01/08/delphix-upgrade-in-twenty-minutes/

https://www.bobbydurrettdba.com/2014/07/08/used-delphix-to-quickly-recover-ten-production-tables/

https://www.bobbydurrettdba.com/2015/03/11/delphix-user-group-presentation/

https://www.bobbydurrettdba.com/2015/10/07/my-delphix-presentation-at-oaktable-world/

I have two talks scheduled during OpenWorld next week.

The first talk is at OakTable World at 9 am on Tuesday.

The second talk is at Delphix Sync between 3 and 4 pm Thursday.  The second talk is a ten minute “lightning talk” version of the first.

I hope to see you at one of the talks and if not I hope this post is valuable to you.

Bobby

Here is a YouTube video of a short Toastmasters talk of mine about a DBA topic.  It was probably too technical for the audience but may interest the readers of this blog.

Here was a post about a similar topic:

https://www.bobbydurrettdba.com/2012/06/22/working-on-performance-toolkit/

• Bobby

It is official.  I will be doing my Delphix presentation at OakTable World during the Oracle OpenWorld conference at the end of this month.  My talk is at 9 am on Tuesday, October 27.

I will describe our journey as a new Delphix customer with its ups and downs. I tried to have the spirit of a user group talk where you get a real person’s experience that you might not get from a more sales oriented vendor presentation.

Kyle Hailey, a OakTable member and Delphix employee, will host my talk.  I have been very impressed by Kyle’s technical knowledge and he will be with me to answer questions about Delphix that I could not answer.  I think it will be a good combination of my real world user experience and his depth of technical background in Delphix and Oracle performance tuning.

If you are going to OpenWorld and if you want to know more about Delphix come check it out.  Also, feel free to email me or post comments here if you have any questions about what the talk will cover.

Bobby

The Arizona Oracle User Group has moved tomorrow’s meeting to Oracle’s offices on Camelback road:

Meetup link with meeting details

Bobby

This morning a colleague of mine noticed an alert from our plan change monitor and prevented any impact on the users by forcing a SQL’s plan to its normal fast plan before the users could be affected.  I want to share the monitor script and describe how we use its output.

I know this is long but I want to paste the SQL text of the monitor script here.  Review my comments on each step.  I can not remember the details of the script myself even though I wrote it but the comments hopefully will help:

set linesize 160
set pagesize 1000
set echo on
set termout on
set trimspool on
set define off

-- status active
-- this plan isn't the most commonly
-- run plan.
-- average this plan in v$sql > 10 x average of
-- most common plan

-- status active sessions
-- sql_id and plan_hash_value
-- elapsed and executions
-- max elapsed for the sql
-- eliminate pl/sql which has
-- plan hash value of 0

drop table active_sql;

create table active_sql as
select
vs.sql_id,
sq.plan_hash_value,
max(sq.ELAPSED_TIME) elapsed,
max(sq.executions) executions
from
v$session vs,
v$sql sq
where
vs.sql_id=sq.sql_id and
vs.SQL_CHILD_NUMBER=sq.child_number and
vs.status='ACTIVE' and
sq.plan_hash_value <> 0
group by 
vs.sql_id,
sq.plan_hash_value;

-- to get the most frequently
-- used plan first get the number
-- of exections by plan
-- for each of the active sqls

drop table plan_executions;

create table plan_executions as
select
ss.sql_id,
ss.plan_hash_value,
sum(ss.executions_delta) total_executions
from
dba_hist_sqlstat ss,
active_sql a
where
a.sql_id=ss.sql_id
group by 
ss.sql_id,
ss.plan_hash_value;

-- use the previous table to get
-- the plans that are most frequently 
-- used. note that two plans could
-- have the same number of executions
-- but this is unlikely.

drop table most_frequent_executions;

create table most_frequent_executions as
select
pe1.sql_id,
pe1.plan_hash_value
from plan_executions pe1
where
pe1.total_executions =
(select max(pe2.total_executions)
from plan_executions pe2
where
pe1.sql_id=pe2.sql_id);

-- handle special case of two plans with
-- same number of executions.
-- pick one with highest plan value
-- just to eliminate dups.

drop table most_frequent_nodups;

create table most_frequent_nodups as
select
mfe1.sql_id,
mfe1.plan_hash_value
from most_frequent_executions mfe1
where
mfe1.plan_hash_value =
(select max(mfe2.plan_hash_value)
from most_frequent_executions mfe2
where
mfe1.sql_id=mfe2.sql_id);

-- get list of active sql that 
-- are not running the most
-- frequently executed plan

drop table not_most_freq;

create table not_most_freq as
select * from active_sql
where
(sql_id,plan_hash_value) not in
(select sql_id,plan_hash_value from most_frequent_nodups);

-- working on this part of the logic:
-- average this plan in v$sql > 10 x average of
-- most common plan

-- get average elapsed of most
-- frequently executed plans
-- add 1 to handle case of 0 executions

drop table avg_elapsed_most_f;

create table avg_elapsed_most_f as
select
ss.sql_id,
ss.plan_hash_value,
sum(ss.ELAPSED_TIME_DELTA)/(sum(ss.executions_delta)+1) 
avg_elapsed
from DBA_HIST_SQLSTAT ss,
most_frequent_nodups nd
where 
ss.sql_id = nd.sql_id and
ss.plan_hash_value = nd.plan_hash_value
group by
ss.sql_id,
ss.plan_hash_value;

-- get list of the sqls that are running
-- the plan that isn't most frequently 
-- executed and has an average elapsed 
-- more than 10 times the average of 
-- the most frequently executed plan
-- add 1 to executions to prevent 
-- divide by zero

drop table more_than_10x;

create table more_than_10x as
select 
n.sql_id,
n.plan_hash_value
from 
not_most_freq n,
avg_elapsed_most_f m
where
(n.elapsed/(n.executions+1)) > 10 * m.avg_elapsed and
n.sql_id=m.sql_id;

spool planchangemonitor.log

select name db_name from v$database;

-- The listed sql_id and plan_hash_value items correspond to 
-- sql statements that have plans that may be
-- inefficient and need to be investigated.
-- The session id and username are included if a
-- session is currently running the sql with the plan.

select
'CHANGED '||'PLAN' flag,
m.sql_id,
m.plan_hash_value,
s.sid,
s.username
from 
more_than_10x m,
v$session s,
v$sql q
where
m.sql_id=s.sql_id(+) and
m.plan_hash_value=q.plan_hash_value(+) and
s.sql_id=q.sql_id and
s.SQL_CHILD_NUMBER=q.child_number
order by
m.sql_id,
m.plan_hash_value,
s.sid,
s.username;

spool off

If I remember correctly I think the script looks for sessions running a plan whose current run time is 10 times that of the most frequently executed plan. This script is not perfect. The join to v$sql is not perfect and in some cases you can get duplicates.  People could quibble about the approach.  Why 10 times the previous run time?  I thought about more complex approaches but I just needed to get something in place.  But, on one database with a lot of small transactions we have made good use of this script, despite its flaws.

This morning my colleague noticed emails from the script that runs this SQL listing a query whose plan had changed.  The output looked like this:

FLAG         SQL_ID        PLAN_HASH_VALUE        SID USERNAME
------------ ------------- --------------- ---------- ---------
CHANGED PLAN 75ufmwrcmsuwz      2484041482         35 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        394 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        395 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        446 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        463 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        464 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        544 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        613 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        631 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        665 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        678 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        738 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        746 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        750 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482        752 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       1333 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       1416 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       1573 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       1943 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       1957 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       3038 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       3445 YOURUSER
CHANGED PLAN 75ufmwrcmsuwz      2484041482       3816 YOURUSER

I changed the real user to YOURUSER.  This output indicates that 23 sessions were all running the same SQL – sql_id=75ufmwrcmsuwz – and that this SQL was running on a new plan that was causing the SQL to run at least 10 times the normal run time.  In fact it was about 30 times as long.

To resolve the issue my colleague used our script to find the history of plans for 75ufmwrcmsuwz.

     select ss.sql_id,
  2  ss.plan_hash_value,
  3  sn.END_INTERVAL_TIME,
  4  ss.executions_delta,
  5  ELAPSED_TIME_DELTA/(executions_delta*1000) "Elapsed Average ms",
  6  CPU_TIME_DELTA/(executions_delta*1000) "CPU Average ms",
  7  IOWAIT_DELTA/(executions_delta*1000) "IO Average ms",
  8  CLWAIT_DELTA/(executions_delta*1000) "Cluster Average ms",
  9  APWAIT_DELTA/(executions_delta*1000) "Application Average ms",
 10  CCWAIT_DELTA/(executions_delta*1000) "Concurrency Average ms",
 11  BUFFER_GETS_DELTA/executions_delta "Average buffer gets",
 12  DISK_READS_DELTA/executions_delta "Average disk reads",
 13  ROWS_PROCESSED_DELTA/executions_delta "Average rows processed"
 14  from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
 15  where ss.sql_id = '75ufmwrcmsuwz'
 16  and ss.snap_id=sn.snap_id
 17  and executions_delta > 0
 18  and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER
 19  order by ss.snap_id,ss.sql_id;

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
75ufmwrcmsuwz      2297146707 30-AUG-15 09.00.54.472 AM              830         587.207673     49.9638554    541.617188                  0                      0                      0          5234.01928         125.083133              332.66747
75ufmwrcmsuwz      2297146707 30-AUG-15 10.00.46.872 AM             1178         578.205867     49.3972835    532.377174                  0                      0                      0          4870.22326         126.048387              324.50764
75ufmwrcmsuwz      2297146707 30-AUG-15 11.00.39.206 AM             1433         631.484713     49.1486392    585.826676                  0                      0                      0          4624.11305         125.446615              299.57083
75ufmwrcmsuwz      2297146707 30-AUG-15 12.00.32.247 PM             1620         592.593823     49.5987654     546.29731                  0                      0                      0          4744.17284         121.961728             312.735185
75ufmwrcmsuwz      2297146707 30-AUG-15 01.00.20.783 PM             1774         534.412339      51.059752     485.46836                  0                      0                      0          4983.44983         119.564825             326.067644
75ufmwrcmsuwz      2297146707 30-AUG-15 02.00.08.843 PM             1757         447.385105     44.9345475    404.415659                  0                      0                      0          4525.13147         107.277746             293.739328
75ufmwrcmsuwz      2297146707 30-AUG-15 03.00.57.308 PM             1626         431.718507      45.904059    388.200416                  0                      0                      0          4462.93296         118.027675             300.724477
75ufmwrcmsuwz      2297146707 30-AUG-15 04.00.44.209 PM             1080         375.905966      44.212963    334.434835                  0                      0                      0          4766.81574         109.157407             310.712037
75ufmwrcmsuwz      2297146707 30-AUG-15 05.00.36.753 PM              707         368.289475     44.3140028    327.166223                  0                      0                      0          4894.20509         108.050919             315.565771
75ufmwrcmsuwz      2297146707 30-AUG-15 06.00.26.449 PM              529         341.483588     39.6408318     305.47356                  0                      0                      0          4381.19849         96.2646503             288.030246
75ufmwrcmsuwz      2297146707 30-AUG-15 07.00.17.636 PM              356         380.733635     41.5168539    342.034876                  0                      0                      0           4553.4691         105.272472             292.283708
75ufmwrcmsuwz      2297146707 30-AUG-15 08.00.11.170 PM              313         435.449406     37.1565495    402.636489                  0                      0                      0          4144.30351         92.8690096             264.923323
75ufmwrcmsuwz      2297146707 30-AUG-15 09.00.07.894 PM              214         516.455509     44.5794393    477.020692                  0                      0                      0          4567.67757         114.415888             289.607477
75ufmwrcmsuwz      2297146707 30-AUG-15 10.00.59.991 PM              182         720.749681     44.3956044    684.439467                  0                      0                      0          3811.83516         95.2362637             239.027473
75ufmwrcmsuwz      2297146707 30-AUG-15 11.00.47.388 PM               83          1043.1503     43.7349398    1008.41358                  0                      0                      0          3575.96386         114.289157             250.120482
75ufmwrcmsuwz      2484041482 30-AUG-15 11.00.47.388 PM                6         25314.6558     4311.66667    22971.4913                  0                      0                      0          78533.8333         69813.3333             157.833333
75ufmwrcmsuwz      2484041482 31-AUG-15 12.00.36.033 AM               96         25173.7346     5105.20833    21475.9516                  0                      0                      0          135242.802         62433.3125             118.395833
75ufmwrcmsuwz      2484041482 31-AUG-15 01.00.29.070 AM               39         26877.0626     5540.51282    22977.6229                  0                      0                      0          139959.308         68478.1795             93.7179487
75ufmwrcmsuwz      2484041482 31-AUG-15 02.00.18.755 AM               38          26993.419     5998.15789    22768.4285                  0                      0                      0          153843.342              74492             149.342105
75ufmwrcmsuwz      2484041482 31-AUG-15 03.00.09.615 AM               29         25432.5074     4952.06897    22288.7966                  0                      0                      0          112813.552         69803.0345             187.689655
75ufmwrcmsuwz      2484041482 31-AUG-15 04.00.01.749 AM               34         27281.7339     4541.47059    24543.1609                  0                      0                      0             95144.5         69187.3824             135.676471
75ufmwrcmsuwz      2484041482 31-AUG-15 05.00.55.876 AM              146         30512.9976     5421.43836    26984.2559                  0                      0                      0          115531.801         71886.6644             136.321918
75ufmwrcmsuwz      2484041482 31-AUG-15 06.00.50.769 AM              405         24339.6641     4853.40741    20794.0472                  0                      0                      0           115490.01         62004.4642             229.106173
75ufmwrcmsuwz      2484041482 31-AUG-15 07.00.43.047 AM              838         27552.3731     4903.06683    23661.2101                  0                      0                      0          111654.558         58324.9511             176.058473
75ufmwrcmsuwz      2484041482 31-AUG-15 08.00.50.864 AM             1653         30522.8358     4550.56261    26526.2183                  0                      0                      0           93818.418         49865.4701             137.212341

Based on this output my colleague chose 2297146707 as the good plan.  She ran coe_xfr_sql_profile.sql which is found in SQLT’s utl directory like this:

coe_xfr_sql_profile.sql 75ufmwrcmsuwz 2297146707

Then she ran the generated script:

coe_xfr_sql_profile_75ufmwrcmsuwz_2297146707.sql

This forced the plan back to its earlier efficient plan and my coworker did all of this early this morning before we reached our peak time of usage and before the bad plan could affect our users.

Bobby

P.S. This example is from an 11.2.0.3 database running on HP-UX Itanium.

I mentioned this class earlier in a blog post but I wanted to remind people who read this blog that the class is starting again on Wednesday.  Here is the URL for the class: link

The class is completely free and taught at a very high level of quality.

It teaches computer science concepts that apply in any programming language but also teaches Python programming.

It is valuable information in the increasingly computer oriented world and economy and the class is free which is remarkable given its quality.

Here is the class name:

MITx: 6.00.1x Introduction to Computer Science and Programming Using Python

Bobby

Development asked me to look at ways to cut the run time of a series of PeopleSoft payroll batch jobs so I took an AWR report of the entire 4 hour period.  Based on the waits, the percentage of the elapsed time spent using I/O and the buffer pool advisory I chose to double the size of the buffer cache. But, this added memory did not improve the run time of the batch jobs. Maybe the affected blocks are only read into memory once so they would not get cached no matter how big the buffer pool was.

Here is the original run on June 22 with the original memory settings:

Cache Sizes

Top 5 Timed Foreground Events

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

In the SQL ordered by Elapsed Time report the top batch job SQL was 99.14% I/O

Based on this report it seems that the number of physical reads could be reduced to about 20% what they were on June 22 by doubling the size of the buffer cache. But, adding the memory did not cut the number of physical reads in any major way.

Here is yesterday’s run:

Cache Sizes

Top 5 Timed Foreground Events

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

After the memory add the same top batch job SQL was 98.80% I/O.  Some improvement but not nearly as much as I expected based on the buffer pool advisory.

I guess the moral of the story is that the buffer pool advisory does not apply to specific workloads and is only a general guideline.  Maybe this is the same kind of fallacy that you have with buffer cache hit ratios where certain workloads make the ratio irrelevant.  Here were the hit ratios:  Before 98.59% After 98.82%.  Basically these are the same.

I just thought I would share this to document a real case of using the buffer pool advisory and having it not produce the expected results.

Bobby