Re: Flashback log file sync

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 12 May 2012 19:46:21 +0000 (UTC)
Message-ID: <pan.2012.05.12.19.46.20_at_gmail.com>

On Sat, 12 May 2012 19:49:36 +0100, Jonathan Lewis wrote:

> "Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message
> news:pan.2012.05.12.18.08.31_at_gmail.com...
> | On Sat, 12 May 2012 07:26:46 +0100, Jonathan Lewis wrote:
> |
> | > What's the full DDL for the table (use dbms_metadata.get_ddl) - are
> the | > LOBs nocache nologging, for example, and can this affect the
> flashback | > rate.
> |
> | Jonathan, no need for DBMS_METADATA. I have the DDL:
> |
>
> When puzzled I always look at the output from dbms_metadata in case
> something I had forgotten about has sneaked into the definition.
>
> LOB ("CONTENT") STORE AS BASICFILE (
> | TABLESPACE "TEST_LOB" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION |
> NOCACHE NOLOGGING | STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
> MAXEXTENTS 2147483645 | PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 |
> BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
> |
>
> As a test, create the LOB as CACHE.
>
> Did you check the session stats to see what your reads were ?

The plot thickens. I re-created the LOB as cache and flashback waits went away:

EVENT TIME_WAITED AVERAGE_WAIT

------------------------------ ----------- ------------
Disk file operations I/O		 0	    .02
log buffer space			 0	      0
log file sync				 1	    .77
SQL*Net message to client		 1	      0
log file switch completion		23	   5.77
SQL*Net message from client	       523	   1.02
SQL*Net more data from client	      1323	    .01
db file sequential read 	     33915	    .14

The DDL is now this:

CREATE TABLE "SCOTT"."BIN_FILES"

   (	"FILENO" NUMBER(4,0), 
	"FILENAME" VARCHAR2(256), 
	"CONTENT" BLOB

   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "TEST_TBS"
 LOB ("CONTENT") STORE AS BASICFILE "MUSIC"(   TABLESPACE "TEST_LOB" DISABLE STORAGE IN ROW CHUNK 32768 RETENTION   CACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ; It seems that flashback operations are synchronous only for the "direct" operations, which bypass the SGA. When I come to think of that, it is a logical behavior of a sort. Also, Oracle prevents me from specifying "NOLOGGING" with CACHE, which is sort of strange. The script now completes in less than 9 minutes, which is excellent, after having started with 20 minutes.

mgogala_at_medo tmp]$ time ./test_lob

real	8m59.722s
user	0m6.372s
sys	0m7.414s

Here is the entire script, if someone else wants to get involved. One only needs to modify the directory and the maximum LOB size in the connection (LongReadLen) if the files to be loaded can exceed 10M in size.

#!/usr/bin/env perl
use warnings;
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);

my $dbh = db_connect("scott", "tiger");
$dbh->do("alter session set events='10046 trace name context forever, level 12'");
my _at_files = glob("/home/mgogala/mp3/misc/*");

my $cnt   = 0;
my $ins   = "insert into bin_files values(?,?,?)";
my $sth=$dbh->prepare($ins);

foreach my $f (_at_files) {

    my $cont=slurp($f);

    $sth->bind_param(1,$cnt++);
    $sth->bind_param(2,$f);
    $sth->bind_param(3,$cont,{ora_type=>SQLT_BIN});
    $sth->execute();

}

$dbh->commit();

END {
    $dbh->disconnect() if defined($dbh); }

sub db_connect {

    my ( $username, $passwd, $db ) = ( _at__, $ENV{"TWO_TASK"} );     if ( $username =~ m|/| ) {

        my _at_tmp = split( /[\/\@]+/, $username );
        $username = $tmp[0];
        $passwd   = $tmp[1];
        $db       = $tmp[2] || $ENV{"TWO_TASK"} ";

    }

    $db ||= "";
    my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )

      || die( $DBI::errstr . " \n " );
    $dbh->{AutoCommit}           = 0;
    $dbh->{RaiseError}           = 1;
    $dbh->{ora_check_sql}        = 0;
    $dbh->{RowCacheSize}         = 1024;
    $dbh->{ora_array_chunk_size} = 1024;
    $dbh->{LongReadLen}          = 10000000;

    return ($dbh);
}

sub slurp {

    my $name=shift;
    local $/=undef;
    my $fd;
    open($fd," < ",$name) or die(" cannot open $name for reading : $! \n ");

    my $cont=<$fd>;
    close($fd);
    return($cont);
}

-- 
http://mgogala.byethost5.com

Received on Sat May 12 2012 - 14:46:21 CDT