Re: Flashback log file sync

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message 
news: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