orawin.info

I’d be grateful if anyone who is responsible for Oracle database backups could take a quick (3 question) survey on their use or otherwise of the Fast (Flash) Recovery Area feature. http://www.surveymonkey.com/s/J93L7WT All responses are anonymous.

Possibly Related Posts:

• No Related Posts found

Yesterday I talked about some of the things to consider when adding space to a tablespace using auto-extend for datafiles. This of course isn’t the only way to add space to a tablespace. You could simply add more datafiles. Indeed nearly a decade ago (scary!) I wrote

The final reason for multiple datafiles (to spread IO) depends entirely on
how many disks you have which you don’t tell us, but in general small
datafiles spread across multiple disks will give better performance than
large datafiles on single disks.

At the time this sort of recommendation (use multiple datafiles, manually striped) was pretty common. These days of course with the widespread adoption of SAN technology, ASM, LVM and so on the micromanagement of disk I/O is a disappearing art (and mostly rightly so). The same underlying goal, to spread I/O, is achieved in hardware or o/s level volume management. Never the less, one might wonder how the exercise in growing a table would fare in a database where we added datafiles manually and in a controlled fashion, rather than just allowing auto-extend to do its job with a reasonable value for NEXT

The test script for this is even simpler than the one produced yesterday, because all that is in fact needed is a test that allocates N number of datafiles all of the same size. I reproduce my script for this below:

set timing off
set verify off

accept file_size prompt "Enter file size in MB [default 1)" default 1
accept files_to_add prompt "Enter #files to add [default 100]" default 100

create tablespace ts_&file_size.
datafile 'c:\app\oracle\oradata\niall\ts_&file_size..dbf' size &file_size.m autoextend off;

set timing on

begin
 for i in 1..&files_to_add. loop
   execute immediate 'alter tablespace  ts_&file_size. add datafile ''c:\app\oracle\oradata\niall\ts_&file_size.'||i||'.dbf'' size &file_size.m';
 end loop;
end;
/

drop tablespace ts_&file_size. including contents and datafiles;

This compares per operation with yesterday as follows :- The overhead column is just how much longer it takes to add a datafile.

However it is worth considering two more things before concluding that adding a datafile is a complete waste of time.

First, the session waiting on an add datafile event is the DBA session and not a user session. Consider an OLTP application where you know that you will need to add another 1gb of data per day (so we can reuse my figures). Imagine that you’ve decided that you don’t want any session to be delayed more than 1/2 a second by datafile extension. With my data from yesterday that would mean picking an autoextend size of about 10mb (0.52s per extension), all in all we’d waste about 52s of end-user time autoextending. If we were implementing multiple files per database then we’d almost certainly add a single 1gb datafile (in fact we’d likely have a standard size to add). This would end up having 2 advantages.

• The wait would fall on the administrator and not the user
• The time waited would be less

To illustrate the second point, and the fact that you can make objective predictions by gathering data like this, I’ve created an equivalent graph to yesterdays for file addition rather than file extension using the data from the table above. The bottom line is the quicker file extension, the top the time taken to add a single file of a specified size. I’ve also included the best fit linear trend line for the data as estimated by Excel.

If we plugin 1000mb for x to the formula given above we get an estimate for the time taken to add this datafile of 32.32 seconds.  When I reran my test script plugging in a single 1gb file, I got an actual time of

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.26

More Conclusions

For nearly all people the strategy of adopting an autoextend size of a few 10s of MB and not worrying about precise file sizes will be perfectly sensible. Bigfile tablespaces with autoextend may indeed be the way for many people who would otherwise have lots of 32gb datafiles, especially now RMAN can back these up in chunks. For some people however, either who have extremely tight performance requirements or who have excellent understanding of data growth patterns the old strategy of adding defined standard size datafiles in order to manage space may still be advantageous.

Possibly Related Posts:

• Proof by extension..
• Advice from the Internet
• Random Thoughts
• Distinctly Odd (Update)
• Distinctly Odd

I have got a offer from uCertify to review their Exam PrepKit. The terms of this were simply that I write an unbiased review (and you can be assured that no money is changing hands) so now I am reviewing Oracle 1Z0-052 PrepKit from uCertify which is the DBA 11G OCP Kit. I expect to post a full review in the next week or so.

Possibly Related Posts:

• No Related Posts found

Both an ORACLE-L discussion here and a Stack Exchange post raise essentially the same question which is, paraphrased:

What is the performance overhead of datafile autoextend events.

The conventional wisdom, with which I agree, is that the overhead is insignificant. However the poster in the stack exchange dialog asked how one would determine this empirically. A little bit of googling didn’t find anything so I thought I’d throw out some examples that can (with a little tweaking for file locations and sizes) be run on your system if you are interested.  The script I used is reproduced at the end of the post, but is described here for ease of understanding. The tests described were all conducted on a laptop using a single laptop HDD. As you’ll see from the results the actual hardware you have will impact this test significantly.

The script creates 2 tablespaces – one fixed at 1gb in size and one that starts small and is allowed to grow. The size and growth rate of the small tablespace are determined by the size of each extent in a test table that is created in each tablespace.  I ran sets of tests to determine the elapsed time taken to add a specific volume of data using different extent sizes for the table,(and hence different numbers of auto-extend operations), the setup is such that the growing tablespace should grow once for each time an extent is allocated to the table in the tablespace. The static tablespace is sized so that it does not need to grow at all by comparison.

I made tests at extent sizes (and autogrowth increments) of 1,10,25,50 and 100mb and data volumes from 100mb to 1000mb. Elapsed times are taken from sqlplus set timing on command.  My results are shown in the table below, All elapsed times are in seconds.

I then calculated the elapsed time per extension  and the elapsed time per extent allocation (by using the static tablespace timings). The second of these metrics varied a bit when very few extents were being allocated but was remarkably consistent at the 7-8ms mark. This suggests that the impact of allocating a new extent to an object is about equal to the impact of a single random disk I/O (or measurable but negligible). When it came to object extension then the average elapsed time per file system extension varied pretty much linearly with how much space was being allocated per file extension. On my system the average numbers (and the std deviation was very low though my sample size is small) came out as shown below.

It appears to me that what happens at file extension is pretty likely to be the same as what happens when a new datafile is added – namely space is allocated in the file system and the blocks zeroed out. The ‘overhead’ therefore will vary according to the capabilities of your I/O subsystem.  The data above for me graphs as follows:

Conclusions

There are 2 competing considerations when determining the optimal autoextend size for a datafile, and the balance between the 2 probably varies by I/O subsystem. In the greater cosmic scale of things neither is probably as important as the efficiency of your data import process or the SQL used to populate your tables. These 2 considerations are:

• What are the constraints on elapsed time that any individual transaction cannot exceed.
• Overall how efficient do we want to be.

In my case moving from a 25mb auto-extend size to a 50mb auto-extend size means that overall my dataload is 27% faster, but it does imply an increase in Worst case response time from 1.1 to 1.7s which may not be acceptable in an OLTP environment.

Script

The script I used is reproduced below. It will require editing if you use it for file locations.

set timing off
set verify off

accept extent_size prompt "Enter extent size in MB [default 1)" default 1
accept growth prompt "Enter desired growth in extents [default 100]" default 100
col min_size new_value min_file_size

select (&extent_size. + 1 ) min_size from dual;

drop tablespace ts_&extent_size. including contents and datafiles;
drop tablespace static_ts including contents and datafiles;

create tablespace ts_&extent_size.
datafile 'c:\app\oracle\oradata\niall\ts_&extent_size..dbf' size &min_file_size.m autoextend on next &extent_size.m
extent management local uniform size &extent_size.m;

create tablespace static_ts
datafile 'c:\app\oracle\oradata\niall\static_ts.dbf' size 1g autoextend off
;

create table growing_tab(n1 number, vc1 varchar2(100))
tablespace ts_&extent_size.;

create table static_tab(n1 number, vc1 varchar2(100))
tablespace static_ts;

set timing on

begin
for i in 1..&growth. loop
execute immediate 'alter table growing_tab allocate extent';
end loop;
end;
/

begin
for i in 1..&growth. loop
execute immediate 'alter table static_tab allocate extent';
end loop;
end;
/

drop tablespace ts_&extent_size. including contents and datafiles;
drop tablespace static_ts including contents and datafiles;

Possibly Related Posts:

• Advice from the Internet
• Distinctly Odd (Update)
• Distinctly Odd
• Instrumentation Overhead

We’ve now received a response to the issue described in the previous entry. In short oui updates at install time are broken (apparently fixed in 11.2.0.3, but no updates are available for that release yet to check.

Hi ,

The OUI looks for the metadata for the patches.
There is an unpublished bug 11709996 on 11.2.0.2 due to which ,the latest patches are not being downloaded.

Hence We’ll recommend you to check for the latest patches from My Oracle Support and apply the latest patch manually using opatch.

It would be interesting to know if the same issue affects Enterprise Manager patch recommendations as well (one would like to think that Oracle only have one “check for latest updates” routine, but you never know).

Possibly Related Posts:

• OUI Updates feature
• Oracle.com passwords
• October 2010 Security Patches

One of the nice new features of the 11.2 installer for the Database (and indeed the installer for Enterprise Manager 12c) is the ability to tell the setup software to download any post release recommended updates for the product and to apply them at install time. The installer first of all prompts you for your My Oracle Support details

It then presents you with a list of recommended updates. The screen below is for Oracle Database 11.2.0.2 on Windows Server x64.

This screen looks pretty good, allowing us to apply the recommended patch 12914926 which is Patch Bundle 11 as part of our install. This is much smoother than the old system of running an install, then downloading a patch manually, then running OPatch and applying the patch. In general I’m a fan of this feature. The only real problem that I can see is in that little disclaimer on the credential entry screen.

be aware that they may not include all patch updates to the software.

I had assumed that this meant that recommended updates, but not new patchsets (11.2.0.3 is out for example) would be included in this list. However if I use the My Oracle Support recommended patch feature for Oracle Database 11.2.0.2 on Windows Server x64 Patch 12914296 is not listed, Patch 13373446 is listed instead. If one looks directly for patch 12914296 Oracle’s patch search notes

This patch has been superseded. Please see the reason stated below.

Reason for Superseding
Patch 13038788 is a super set of Patch 12914926
Note: This patch was originally replaced by patch 13038788. The most recent replacement for this patch is 13373446. If you are downloading patch 12914926 because it is a prerequisite for another patch or patchset, you should verify whether or not 13373446 is suitable as a substitute prerequisite before downloading it.

In short the recommended patches listed by the installer may not be the recommended patches for your product.

Possibly Related Posts:

• OUI Updates Update
• Oracle.com passwords
• October 2010 Security Patches

Here is a spam comment that I thought deserved publishing in its entirety but without the spam links. I hope you all enjoy the lyricism as much as I did.

Incomparable Blog. I tot up this Blog to my bookmarks.Thanks for alluring the in the nick of time b soon to examine this, I lean to strongly yon it and fondness information more on this topic.

Possibly Related Posts:

• No Related Posts found

When 11.2 came out I posted about deferred segment creation at http://orawin.info/blog/2010/04/25/new-features-new-defaults-new-side-effects/ and a couple of odd side effects. Oracle published a Note  1050193.1 that makes the quite extraordinary claim that

Sequences are not guaranteed to generate all consecutive values starting with the ‘START WITH’ value.

It’s absolutely true that sequences don’t guarantee no gaps – but the fact that start with no longer specifies the starting value either is a bit rich in my view.  Anyway Martin Widlake ran into this issue and has blogged about it here today. I posted a quick comment and then was going to re-run his scripts with and without deferred_segment_creation=true on my 11.2 instance. My test script is exactly the same as his (copy and paste is wonderful) but includes an initial SHOW PARAMETER line and the addition of a set serveroutput on line. My results are not the same.

<pre>NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

Table dropped.

Sequence dropped.

Sequence dropped.

Table created.

Sequence created.

Sequence created.

1 row created.

I have just created male name number 1

PL/SQL procedure successfully completed.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

I created 10 men and last value was 10

PL/SQL procedure successfully completed.

1 row created.

I have just created female name number 1

PL/SQL procedure successfully completed.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

I created 10 women and last value was 10

PL/SQL procedure successfully completed.

S MIN(FORN_ID) MAX(FORN_ID)
- ------------ ------------
M            1           10
F            1           10

SQL>

The difference. This database has been upgraded to 11.2.0.3, however there doesn’t appear to be a reference to a fix for this behaviour in either the 11.2.0.3 bug fix list or that for 11.2.0.2 . I guess the lesson is the same old one, make sure you run your regression tests on all new releases including patch sets and be alert for silent behaviour changes.

Possibly Related Posts:

• New Features, New Defaults, New Side-Effects
• php long numbers
• Oracle.com passwords
• October 2010 Security Patches
• Metric Collection Error

As in previous years the UKOUG allows you to create a personalized agenda for the upcoming conference. To give you a flavour for some of the excellent presentations and to encourage you to register I’ve reproduced mine below, as with previous years this is a ideal wishlist, I’ll probably “die” halfway through various days and so take a break, or get caught up in the many networking and informal learning opportunities this conference gives.

Sunday

12:15 – 13:15      Tales From The OakTable – Mogens Nørgaard

Mogens is always entertaining and thought provoking, a great way to start the Oak Table Sunday.

13:25 – 14:10      Faster, Leaner, More Efficient databases – Index Organised Tables - Martin Widlake

If  you’ve been following Martin’s excellent IOT blog series (start here if not) you’ll know what I’m expecting.

14:35 – 15:20      Optimizer Statistics – a fresh approach - Connor McDonald

Connor is always worth listening to, for both content and style (not to mention slide count).

15:30 – 16:15       Statistics Gathering and Histograms – DOs and DON’Ts - Wolfgang Breitling

Wolfgang has huge experience specifically in this area and  a great clear style

16:40 – 17:30       Performance & High Availability Panel Session

Well the word beer is mentioned , I believe there will also be various giveaways including books that I and other Oakies have contributed to or written.

Monday

10:00 – 10:45       SQL Tuning - Kyle Hailey

I’ve never yet seen Kyle present, but his written material and design flair are both excellent, to this day he remains the only Oakie to be nominated and win for a fashion photography award!

11:05 – 12:05       Partitioning 101 - Connor McDonald

It’s that aussie again.

12:15 – 13:15       Going deeper into Real Application Testing – Learn how to make smoother migrations to 11g - Mariusz Piorkowski

RAT is a product with huge promise, hopefully Mariusz will let us know if the promise is fulfilled.

14:30 – 15:15       Automatic Diagnostic Repository – a look at how and when it can be used - John Hallas

John blogs here  and we share a number of interests, including performance management and Grid Control. Another first for me.

15:25 – 16:10       MySQL: the least an Oracle DBA needs to know - Patrick Hurley

Criminally Patrick doesn’t appear to have a blog so pester him to get one via twitter at @phurley.

16:35 – 17:35       Database I/O Performance: Measuring and Planning - Alex Gorbachev

Alex is the founder of Battle Against Any Guess, oh and CTO at Pythian, always well worth hearing.

17:45 – 18:30       Performance and Stability with Oracle 11g SQL Plan Management - Doug Burns

Yep, another Oakie and another favourite subject for me. Maybe the Cuddly Toys will yet see him present.

18:45 – 19:45       Learning about Life through Business and Software - Cary Millsap

A keynote I’ll actually attend – that’s because Cary speaks well, has clear insights to share and is an all-round nice guy.

Tuesday

09:00 – 18:35       RAC Attack workshop - Jeremy Schneider

Clearly I won’t be here all day, but I do intend to be there for at least some of the time.

10:10 – 10:55       First Things First: Getting the basics right - Niall Litchfield

This is a presentation I gave (and got positive online reviews!) a while back covering the things you really really shouldn’t get wrong.

11:15 – 12:15       Troubleshooting RAC - Julian Dyke

I’ve know Julian for a very long time now, but it’s been nearly 4 years since I last heard him talk about RAC. Time for an update methinks.

12:25 – 13:25       Instrumentation: Why You Should Care - Cary Millsap

Yep, Cary again.

14:40 – 15:25       ASH Outliers: Detecting Unusual Events in Active Session History - John Beresniewicz

JB always speaks well, played a core role in the engineering effort for the EM performance pages and improvements introduced in 10g and the subject is great.

15:35 – 16:20       Oracle on the Amazon Cloud – What should you consider - Stephen Priestnall

Over the next decade (maybe even 5 years) many databases will likely end up somewhere “in the cloud”, this is a must attend for me as a result.

16:40 – 17:40       My Case for Agile Methods - Cary Millsap

I’m not a fan of Agile – Cary is.

17:50 – 18:35       Challenges and Chances of the 11g Query Optimizer - Christian Antognini

Again another presentation on a personal hot spot by the author of the excellent Troubleshooting Oracle Performance

Wednesday

09:00 – 10:00    Redo - Jonathan Lewis

No UKOUG conference is complete without a Jonathan Lewis session, and redo is an often misunderstood area of the database.

10:10 – 10:55       Using Oracle GoldenGate to Minimize Database Upgrade Risk - Marc Fielding

Another excellent writer, who blogs here, whom I have yet to meet or see.

11:20 – 12:20       Oracle Database Smart Flash Cache Overview -Harald van Breederode

The advent of affordable reliable, enterprise ready SSD storage, for example FusionIO cards or SSD Arrays likeViolin,  is probably the biggest game changer for I/O intensive workloads such as database operations  for years. I’ll be interested particularly on whether and how the smart flash cache works compared to, say, locating datafiles directly on reliable low-latency flash storage.

12:30 – 13:30       Oracle RAC One-Node: What, Why, & How - Asif Momen

I’ve followed Asif through the OTN forums for some while now, so I’ll be interested to hear what he has to say on this new Oracle feature.

14:25 – 15:10       Six Easy Pieces: Essentials of Database Tuning for Packaged Applications - Mike Swing

This and the following presentation may not make the cut depending on the state of my mind regarding the final presentation of the day. Here I’m mostly interested in the message.

15:20 – 16:05       Simulating Workloads with RAT and Simora - James Morle

Those of you who can stay till my final session will realize that good workload simulation is a big part of getting performance design right.

16:15 – 17:00       What shape is your data? - Niall Litchfield

That nice Dawn R blog|twitter says she’ll be there. If you come perhaps they’ll be 2 people in the audience.

Possibly Related Posts:

• UKOUG Presentation
• UKOUG Conference Series 2010

Update — My attention has been drawn to this follow up. Had I read the follow up before the blog entry was written I’d have worded it somewhat differently or not published at all. 

There’s a rant against the document oriented database MongoDB doing the rounds. You can find it on PasteBin. You can also find a somewhat official response on YCombinator. I’ll leave you to assess which is the more reasonable and professional. Having read through the original rant and the response it seems to me that the root cause is listed in this early paragraph from the rant, emphasis mine.

Databases must be right, or as-right-as-possible, b/c database mistakes are so much more severe than almost every other variation of mistake. Not only does it have the largest impact on uptime, performance, expense, and value (the inherit value of the data), but data has *inertia*. Migrating TBs of data on-the-fly is a massive undertaking compared to changing drcses or fixing the average logic error in your code. Recovering TBs of data while down, limited by what spindles can do for you, is a helpless feeling. Databases are also complex systems that are effectively black boxes to the end developer. By adopting a database system, you place absolute trust in their ability to do the right thing with your data to keep it consistent and available.

Wikipedia defines Black Box in computing in the following 2 key ways:

• In computer programming and software engineering, black box testing is used to check that the output of a program is as expected, given certain inputs.^[4] The term “black box” is used because the actual program being executed is not examined.
• In computing in general, a black box program is one where the user cannot see its inner workings (perhaps because it is a closed source program) or one which has no side effects and the function of which need not be examined, a routine suitable for re-use.

Possibly Related Posts:

• No Related Posts found