Coskan’s Approach to Oracle

May 27, 2010

Flashback log generation with select statements

Filed under: Basics, Tips — coskan @ 3:50 pm

There is a question on OTN Forums about excessive flashback log generates with select statement . Basically OP says there are flashback logs generated when they do select only.

there are different answers in the thread which can be true or false,  but when I first read the question I immediately think if delayed block cleanout have the similar effect on flashback log generation as well, so I tested.

I initially thought v$sesstats counters might reveal something but no luck and then I checked v$flashback_database_stat for the generated flashback_data_size and I think I hit the jackpot.

HR@ORACOS> select * from v$flashback_database_stat;

BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
20100527 15:32:53 20100527 15:50:16      875266048 1207132160 2038729728                        0
20100527 14:32:50 20100527 15:32:53      248160256  127295488  450139648               1.3215E+10
20100527 13:32:48 20100527 14:32:50       10452992   15646720    4400640               1.5549E+10
20100527 12:32:43 20100527 13:32:48      745693184  948461568 1311620608               2.2789E+10
20100527 11:25:56 20100527 12:32:43     1262026752 1984741376 2358546432               2.7212E+10

HR@ORACOS> set autotrace traceonly statistics
HR@ORACOS>  update base_table_np set y='INVALID';
 commit;

4021808 rows updated.

Statistics
----------------------------------------------------------
       2512  recursive calls
    8341430  db block gets
    4069140  consistent gets
     120569  physical reads
 1908471980  redo size
        848  bytes sent via SQL*Net to client
        793  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    4021808  rows processed

HR@ORACOS> set autotrace off;
HR@ORACOS> select * from v$flashback_database_stat;

HR@ORACOS>
BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
20100527 15:32:53 20100527 16:00:36     1236664320 2021974016 4019910656                        0
20100527 14:32:50 20100527 15:32:53      248160256  127295488  450139648               1.3215E+10
20100527 13:32:48 20100527 14:32:50       10452992   15646720    4400640               1.5549E+10
20100527 12:32:43 20100527 13:32:48      745693184  948461568 1311620608               2.2789E+10
20100527 11:25:56 20100527 12:32:43     1262026752 1984741376 2358546432               2.7212E+10

HR@ORACOS> set autotrace traceonly statistics
HR@ORACOS> select * from base_table_np;
4021808 rows selected.

Statistics
----------------------------------------------------------
        139  recursive calls
          0  db block gets
      53908  consistent gets
       4404  physical reads
    1652384  redo size							------->DELAYED BLOCKS CLEANOUTS
  175008833  bytes sent via SQL*Net to client
      88996  bytes received via SQL*Net from client
       8045  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
    4021808  rows processed

HR@ORACOS> set autotrace off
HR@ORACOS> select * from v$flashback_database_stat;    ------>CHECK THE increase in FLASHBACK_DATA
HR@ORACOS>
BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
20100527 15:32:53 20100527 16:01:11     1305264128 2054594560 4021728256                        0
20100527 14:32:50 20100527 15:32:53      248160256  127295488  450139648               1.3215E+10
20100527 13:32:48 20100527 14:32:50       10452992   15646720    4400640               1.5549E+10
20100527 12:32:43 20100527 13:32:48      745693184  948461568 1311620608               2.2789E+10
20100527 11:25:56 20100527 12:32:43     1262026752 1984741376 2358546432               2.7212E+10

I am still not %100 convinced because I have no idea what is going on behind the scenes and I don’t have Julian Dyke (ppt file ) wisdom/time to go more deep into the issue and  I stopped here.

If you have something to say about the issue  comments are welcomed.

Tested on 11.2.0.1 with single active instance

May 23, 2010

Blogroll Report 26/03 /2010 – 02/04/2010

Filed under: Basics, Blogroll Report — coskan @ 10:21 pm

<—- Blogroll Report 19/03/2010 – 26/03/2010

1-How, adding another index can cause performance problem ? (AllEqRange can cause wrong index choice)
Vivek Sharma-Cost Based Optimization ! Query Performance Issue after creating a new Index…

2-How to extract sql statements from 10046 tracefile via tkprof?
Hemant K Chitale-Extracting Application / User SQLs from a TraceFile

3-Which one to choose ? Memory Target vs Huge Pages
Ronny Egner-MEMORY_TARGET (SGA_TARGET) or HugePages – which to choose?

4-How does heap block compress statistics increase and what happens when there is space but not at the top of the free space heap?
Jonathan Lewis– Heap Block Compress

5-Where are the database logs located in 10GR2 RAC?
Murali Vallath-Logs in 10GR2 RAC

6-Why to use DBMS_STATS instead of analyze ?
Jonathan Lewis-Analyze this
Jonathan Lewis-Analyze this 2

7-Ways to transfer Oracle files between database when using ASM
Emre Baransel-Transferring Oracle Files Between Databases In ASM

8-How to reclaim unused space in datafiles?
Tim Hall-Reclaiming Unused Space in Datafiles

9-Available Opatch methods in RAC environment
Mohammad Arju-OPatch supported patch methods in RAC environment

10-Performance impact of ATOMIC_REFRESH parameter value for materialized view refreshes
Kubilay Kara-Materialized View Refresh and the ATOMIC_REFRESH parameter

11-How to check whether or not Java privilege attacks have occurred in your database ?
Paul M Wright-Java Forensics in Oracle

12-How to rotate alert logs via ADRCI
James Koopmann-Rotating Oracle Database’s Alert Log with ADRCI

Blogroll Report 02/04 /2010 – 09/04/2010 –>

May 3, 2010

Blogroll Report 19/03 /2010 – 26/03/2010

Filed under: Basics, Blogroll Report — coskan @ 2:39 am

<—- Blogroll Report 12/03/2010 – 19/03/2010

1-Tanel Poder’s Session Snapper v3.10
Tanel Poder-Session Snapper

2-Function Based Index behaviour with cursor_sharing similar or force?
Yu Sun-Demonstrate a function-based index is unable to be used due to cursor_sharing setting

3-How to upgrade database using a Rolling Upgrade With an Existing Physical Standby Database feature of 11G (transient logical standby)
Gavin Soorma-11g Release 2 Rolling Upgrade using Transient Logical Standby database

4-Extra filter in CBO to do short circuit
Alex Fatkulin-Where did the filter came from?

5-What happens when you delete a row where there are more than 2 index and you reach via 1?
Gary Myers-Turning things on their head

6-How to clone a database on the same server using rman duplicate from active database ?
Alejandro Vargas-Cloning A Database On The Same Server Using Rman Duplicate From Active Database

7-Java Vulnaribility that lets hacker to use orapwd to change sys password
Paul M Wright-CREATE SESSION to SYSDBA via Java and orapwd

8-What is the difference between database sessions and processs?
Arup Nanda-Difference between Session and Process

9-What does Parse Calls statistic mean?
Jonathan Lewis-Nutshell-2

10-Difference between deletion from index and table
Jonathan Lewis-Index too big

11-How to create fake height balanced histograms?
Jonathan Lewis-Fake Histograms

12-How does oracle store unique bitmap indexes
Richard Foote-Unique Bitmap Indexes Part I (Unnatural Selection)

13-Using Mapping tables instead of decode or case
Robert Vollman-DECODE/CASE vs. Mapping Tables

14-How to build ASM Clustered file system
Jim Czuprynski-Building an ASM Clustered File System

15-How does dynamic remastering work in Oracle RAC
Riyaj Shamsudeen-RAC object remastering ( Dynamic remastering )

Blogroll Report 26/03 /2010 – 02/04/2010–>

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers