orawin.info

So we recently reviewed a table with no fewer than 23 indexes on it. A combination of alter index… monitoring usage and reviewing DBMS_HIST_SQL_PLAN for plans that referenced the indexes found 8 indexes that were never used. As this table is also heavily updated we removed the indexes identified as unused. This was, fortunately, a QA system under change control. See if you can spot when the change was made.

Possibly Related Posts:

• No Related Posts found

This is known in the DBA trade as being “in the brown stuff”

Possibly Related Posts:

• No Related Posts found

… Read an email I received today from Oracle Corporation

Hello Niall,

I hope you are well.

I would like to introduce myself as your Oracle Linux and Oracle Virtual Machine Account representative for <name of employer I left in 2008>

I would like to set up a 5 minutes introduction call with you to discuss the following benefits of Oracle’s support for Red hat Linux:

• 60% cheaper than Red Hat Support.
• No Migration required from Red Hat to Oracle Linux
• Indemnification
• Free Enterprise Manager (Equivalent to Satellite Server)
• Free Cluster Ware
• Life time support
• 24*7 support
• Zero downtime on updates. No reboot needed.

· Backporting, with the help of which you can stay the OS version! of your own choice their won’t be any forced upgrade or update sent from us.

· Our support cost is based on the number of Physical servers and Physical Processors, we support all the virtual environments created on top of a Physical machine unlike Red hat where you have to pay extra for Virtual environments.

I would really appreciate if you can please let me know the best time when we can have a discussion.

Once again thanks

Warm Regards,

I’ll not be bothering to schedule the call because:

• You can’t write in English
• You have hideously out of date contact info
• You wish to discuss 10 things in 5 minutes

Fortunately you didn’t mass mail this spam to anyone with a public blog.

Possibly Related Posts:

• No Related Posts found

New to me in 11g is the view DBA_USERS_WITH_DEFPWD which checks both for users with default passwords and those whose password is equal to their username. The query below might be a useful  addition to your support toolbox.

select u.username,u.account_status
from dba_users u inner join dba_users_with_defpwd p
on (u.username = p.username);

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
OUTLN                          LOCKED
DIP                            LOCKED
ORACLE_OCM                     LOCKED
WMSYS                          LOCKED
CTXSYS                         LOCKED
XXXX                           OPEN

Possibly Related Posts:

• No Related Posts found

A while ago I said that I’d be posting a product review here. Unfortunately work got in the way of me posting the review as quickly as I would have liked. The nice people at Ucertify sent me a review kit for the OCA Oracle Database 11g: Administration 1 exam.

I very much like the structure of the software which provides a wealth of study aids as shown below.

The notes are generally clearly written and well supplemented with links to, for example, sections from the Oracle 11g Concepts Guide. Each section allows you to add your own notes, discuss with other users online and tag for later review. Overall then the product provides a wealth of different sources of relevant study material and provides tools that allow you to use that material in conjunction with the way you learn.

In addition the product allows you to assess how you are doing against the exam objectives via multiple scheduled tests that can be taken in either learning or timed mode.

Intelligent use of the test system in conjunction with good source material ought therefore to ensure that you arrive at the test prepared properly for the exam. In principle then I think that this is a fine product for preparing for the OCP exams. I do however have some concerns. These are largely to do with content. I didn’t do a thorough walk through all of the hundreds of notes, but I saw enough examples of dubious content to be a little concerned, for example the note “What is the Shared Pool” contains the following statement.

The shared pool contains the following three components:

• Library Cache
• Data Dictionary Cache
• User Global Area

The thing is that the UGA ought really to live in the large pool (and will do so unless it isn’t configured ) Indeed a later note correctly states that the UGA can live in the SGA if so required.

Similarly the note on direct path load states correctly that the buffer cache is not involved and then adds

It does not generate UNDO data and generation of redo can also be switched off

this is not quite correct, and is in  fact a common misunderstanding. It is true that undo and redo generation requirements are reduced, but any recursive SQL required (for example extent allocation) will be protected in the normal way.

Overall then the product probably deserves an 8 out of 10 from me. The structure and approach to test preparation are excellent, but I’d want to review the study notes pretty carefully for accuracy and completeness. That said I’d expect the target audience (those wishing to pass the exam) to get full value from the product.

Possibly Related Posts:

• No Related Posts found

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