Cut 'em off at the pass! Or, why is my piped export file truncated?

Sometimes you need to conserve space on a file system, and using exp with a named pipe to the compress utility is a nice way to export large databases and consume the minimal amount of disk space. But sometimes things don’t happen the way you plan them. Sometimes the Sys Admin goes through the process of creating/allocating file systems and neglects the all-important task of asking if the intended user needs large file support. Oops. [To be completely fair, though, for years exp and imp would not read/generate a .dmp file larger than 2GB, and most operating systems wouldn't allow a single file to be larger than 2 GB, period. It has only been recently that these limits changed. So, a 'standard build' of the operating system may still be using configurations left over from byegone days. I'd check with your system admistrator regarding how these UNIX systems are configured.] Using exp through a named pipe to compress then exhibits some ‘interesting’ behaviour, as evidenced below:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Direct Path ...

[usual list of users/tables here]

. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.

Notice that exp displays no errors – this is because it could successfully write to the named pipe even though the destination file written by compress isn’t complete; errors from any utility at the end of a named pipe don’t traverse the named pipe when using redirection and thus aren’t ‘registered’ by the originating application. [Since both ends of a named pipe must be open at the same time to write to it/read from it the flow goes 'one way', from the source to the destination.] We don’t discover this fact until we invoke imp on the newly created file, again through a named pipe. This time, though, imp encounters an incomplete dump file and complains, rather loudly:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via direct path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)

[usual imp output here]

IMP-00009: abnormal end of export file
IMP-00018: partial import of previous table completed: 7405342 rows imported
Import terminated successfully with warnings.

The destination file system allowed a maximum file size of 2 GB, causing compress to produce an incomplete archive exactly 2 GB in size. It is rare, indeed, that such a dump file will consume exactly 2 GB of disk space (although it can occur). Double oops. Again note that since exp was using a named pipe it reported successful completion even though the destination file had been truncated.

So how in the name of Soupy Sales do you discover this before you fail on import? Use imp to verify the file, but use a ‘non-invasive’ option to prevent imp from doing any more work than simply reading through the file. The show=y or indexfile options are suitable for such checks:

imp user/pass file=… show=y log=mycheck.log

or

imp user/pass file=… indexfile=mycheck.log

would verify a complete file was written. Of course you’ll need to use a named pipe, so the complete command would be:

mknod /tmp/imp_pipe p

uncompress< /oradba/exports/ora_db.dmp.Z > /tmp/imp_pipe &

imp parfile=parfile_imp_test

(Of course there would be other ‘bits and pieces’ of success checking and exits on error status, but this isn’t intended to be a complete script.) The parfile_imp_test file contains the import parameters in effect, including the use of /tmp/imp_pipe as the source ‘file’. It should look like this:

userid=
log=
file=/tmp/imp_pipe
show=y

Another parameter option would be:

userid=

file=/tmp/imp_pipe

indexfile=

Either set of instructions would direct imp to simply read through the file until the end; errors generated due to a truncated file would be displayed. If, perchance, you receive the dreaded IMP-00009: abnormal end of export file error contact the System Administrator for the machine in question and bring this to his or her attention. Possibly a different file system on the same machine is configured to contain files larger than 2 GB allowing you to redirect the output to a more ‘receptive’ location. If not you’ll need to be notified when the problem is corrected; at that point you can run a new export and generate a usable compressed archive.

The BCHR Follies (my two cents)

I should be surprised, although I'm not, at how many people still believe that the Buffer Cache Hit Ratio (henceforth referred to as the BCHR) is a reliable indicator of database performance. This, for the most part, is a myth from the Oracle 6/Oracle 7 days; as the BCHR is an aggregate it can't pinpoint areas of concern. It may provide a clue as to how well the application SQL is written (although there are other, more suitable metrics for such investigations, such as parsing statistics), but it's, at best, merely an indicator of possible performance issues. It isn't the ubiquitous 'red flag' most seem to think it is.

The BCHR is computed as follows:

Buffer cache hit ratio = [1 - (physical reads / (db block gets + consistent gets))] * 100

where

physical reads == number of data blocks read from disk into the buffer cache
db block gets == number of times a current block was requested from the cache
consistent gets == number of times a consistent read was requested for a block from the buffer cache

Obviously the fewer physical reads we make (these load data into the cache) the higher the BCHR becomes because the db block gets and consistent gets increase (since the data is already in the cache and doesn't need to be loaded). Of course, we can also 'jack up' the BCHR if it isn't to our liking by simply increasing the number of consistent gets, and this is surprisingly easy to do. Connor McDonald provided a script (actually a procedure) to allow a DBA to actually pick the desired hit ratio; this script is found on his website (http://www.oracledba.co.uk/) and I'll provide it here as well:

create or replace
procedure choose_a_hit_ratio(p_ratio number default 99,p_show_only boolean default false) is
v_phy number;
v_db number;
v_con number;
v_count number;
v_additional_congets number;
v_hit number;
procedure show_hit is
begin
select p.value, d.value, c.value
into v_phy, v_db, v_con
from
( select value from v$sysstat where name = 'physical reads' ) p,
( select value from v$sysstat where name = 'db block gets' ) d,
( select value from v$sysstat where name = 'consistent gets' ) c;
v_hit := 1-(v_phy/(v_db+v_con));
dbms_output.put_line('Current ratio is: 'round(v_hit*100,5));
end;
begin
--
-- First we work out the ratio in the normal fashion
--
show_hit;
if p_ratio/100 <> 99.9999999 then
dbms_output.put_line('Sorry - I cannot help you');
return;
end if;
--
-- Flipping the formula we can work out how many more consistent gets
-- we need to increase the hit ratio
--
v_additional_congets := trunc(v_phy/(1-p_ratio/100)-v_db - v_con);
dbms_output.put_line('Another 'v_additional_congets' consistent gets needed...');
if p_show_only then return; end if;
--
-- Create a simple table to hold 200 rows in a single block
--
begin
execute immediate 'drop table dummy';
exception
when others then null;
end;
execute immediate 'create table dummy (n primary key) organization index as '
'select rownum n from all_objects where rownum <= 200'; -- -- Turn off any new 9i connect-by features to ensure we still do lots of -- logical IO -- begin execute immediate 'alter session set "_old_connect_by_enabled" = true'; exception when others then null; end; -- -- Grind away until we do all those additional gets -- execute immediate ' select count(*) from ( select n from dummy connect by n > prior n
start with n = 1 )
where rownum < :v_additional_congets' into v_count using v_additional_congets; show_hit; end; / 
/*
And some output to keep the hit ratio fanatics happy!
SQL> exec choose_a_hit_ratio(85,true);
Current ratio is: 82.30833
Another 29385 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(85);
Current ratio is: 82.30833
Another 29385 consistent gets needed...
Current ratio is: 86.24548

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90,true);
Current ratio is: 86.24731
Another 79053 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90);
Current ratio is: 86.24731
Another 79053 consistent gets needed...
Current ratio is: 90.5702

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98,true);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...
Current ratio is: 98.02386

PL/SQL procedure successfully completed.
*/

Connor has taken a page (or two or three) from the practices of mathematicians and scientists and has re-worked the BCHR equation to solve for the missing 'consistent gets' value one would need to provide the desired result:

(physical reads/(1 - (BCHR/100))) - db block gets = consistent gets

however we want to use a different BCHR than was calculated, so we need the difference in consistent gets. The equation is modified to:

(physical reads/(1 - (desired BCHR/100))) - db block gets - current consistent gets = additional consistent gets required

Of course this doesn't provide the exact BCHR requested (it's likely a little high) because in solving for the additional consistent gets value his formula doesn't take into account the increase in the db block gets and the slight increase in the physical reads (because the data we will use to generate the consistent gets needs to be loaded into the buffer cache before we can generate those consistent gets), but, gee, it's darned close. Since the number of blocks can vary (it's dependent upon the db_block_size for the database in question) and, for a 200 row single-column table the number of blocks is small compared to the number of blocks already accounted for in the physical reads figure, it can be (relatively safely) ignored. To prove this let's presume we have a database with an 8k block size, and use the default settings of PCTFREE 10 and PTCUSED 40. A single-column table containing 200 rows of data type number will require roughly one (1) data block; usually the physical reads value is MUCH greater than 1 block, so that overhead can be discarded. Really, what's one more block added to the million or so already logged in the statistics? That being said Connor has devised a way to 'adjust' your BCHR up (and there isn't anyone I know who would want to adjust it in the opposite direction) to just about any value you desire.

So, we see that simply by 'fiddling' with the consistent gets by using an innocuous table and repeated queries against it we can produce pretty much any BCHR we want. Howard J. Rogers brought up some interesting points in a recent discussion on comp.databases.oracle.server:

* A high BCHR could be indicating problems caused by contention, insufficient UNDO or poor DBA maintenance practices.
* A high BCHR could be indicating that absolutely nothing is wrong.

Simply because the indicator is blinking only tells you ... it's blinking. It blinks when things are bad, it blinks when things are good. The only reliable information the indicator provides is it's blinking. That being the case, just how reliable is the BCHR at diagnosing performance problems or reporting stellar performance?

Apparently, not very reliable at all.

Cary Millsap has pointed out that a high BCHR could be hiding any number of problems involving the key components of the BCHR calculation. You can read the full paper here.

I'd be hesitant to rely upon the BCHR as anything more than a test of your mathematical prowess.