Welcome to Rakesh Jayappa's Blog

SQL> select RESETLOGS_CHANGE#, THREAD#, min(SEQUENCE#) mins, max(SEQUENCE#) maxs from v$log_history group by RESETLOGS_CHANGE#, THREAD#;

RESETLOGS_CHANGE# THREAD# MINS MAXS
—————– ———- ———- ———-
602821 2 2585 3608
602821 1 8040 8965

SQL>

SQL> select RESETLOGS_CHANGE#, THREAD#, min(SEQUENCE#) mins, max(SEQUENCE#) maxs from v$log_history group by RESETLOGS_CHANGE#, THREAD#;

RESETLOGS_CHANGE# THREAD# MINS MAXS
—————– ———- ———- ———-
602821 1 8040 8965
602821 2 2585 3608

SQL> select RESETLOGS_CHANGE#, THREAD#, min(SEQUENCE#) mins, max(SEQUENCE#) maxs from v$log_history group by RESETLOGS_CHANGE#, THREAD#;

RESETLOGS_CHANGE# THREAD# MINS MAXS
—————– ———- ———- ———-
602821 1 8040 8965
602821 2 2584 3607

SQL>

How to resolve ORA-16792 [ID 966472.1]

standby:

SQL> set numf 99999999999
SQL> select current_scn from v$database;

CURRENT_SCN
————
84154314190

SQL> select current_scn from v$database;

CURRENT_SCN
————
84154314190

o to primary and check:

SQL> set numf 99999999999
SQL> select to_char(scn_to_timestamp(84153282076 ),’MM/DD/YYYY HH24:MI’) from dual;

TO_CHAR(SCN_TO_T
—————-
08/19/2011 09:40

SQL> select to_char(scn_to_timestamp(84154314190),’MM/DD/YYYY HH24:MI’) from dual;

TO_CHAR(SCN_TO_T
—————-
08/19/2011 13:27

SQL> select to_char(scn_to_timestamp(84154314190),’MM/DD/YYYY HH24:MI’) from dual;

TO_CHAR(SCN_TO_T
—————-
08/19/2011 13:27

What is db file parallel read and possible solution to reduce the wait events?

These occur when database processes perform special types of multiblock I/Os
between the disk and process PGA memory, thus bypassing the Buffer Cache.
Such I/Os may be performed both synchronously and asynchronously.
Examples where they may be used are:
o Sort I/Os when memory Sort areas are exhausted and temporary tablespaces
are used to perform the sort
o Parallel Execution (Query and DML)
o Readahead operations (buffer prefetching)
o Direct Load operations
o I/O to LOB segments (which are not cached in the Buffer Cache)
Due to the way in which time for these waits is recorded (it does not measure
the time taken to perform the I/O), their relative position in listings such
as Statspack’s “Top 5 Wait/Timed Events” cannot be used to evaluate their
true impact.
Guidelines for tuning:
o Usage of Asynchronous I/O is recommended where available.
o In Oracle8i, minimize the number of I/O requests by setting the
DB_FILE_DIRECT_IO_COUNT instance parameter so that
DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system
In Oracle8i the default for this is 64 blocks.
(In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs
the size of direct I/Os in BYTES (not blocks). The default is 1Mb but
will be sized down if the max_io_size of the system is smaller.)

Tune memory Sort areas so that disk I/O for Sorting is minimized:
In 9i and above use Automated SQL Execution Memory Management.
In 8i tune the various Sort areas manually.
Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
o For LOB segments, store them on filesystems where an Operating System File
Buffer Cache can provide some memory caching.
o Identify sessions performing direct I/Os by querying V$SESSION_EVENT
for these Wait Events or V$SESSTAT for statistics
‘physical reads direct’, ‘physical reads direct (lob)’,
‘physical writes direct’ & ‘physical writes direct (lob)’
and tune their SQL statements.
o Identify datafiles on bottlenecked disk storage and move elsewhere
using V$FILESTAT or Statspack’s “File IO Statistics” section.

Bind Peeking and Related bugs

    Note 387394.1
    Title: Query using Bind Variables is suddenly slow
RESEARCH
============
Bug 4112254 
Abstract:           CBO may not choose best index for single table access with
unpeeked bind/function
Fixed-Releases:     9207 A105 A201

Details:           
  The cheapest index is not always selected for single table access if the
  cost determined for the index is based on an unpeeked bind variable or a
  function whose value is not know at compile time.
  Note: This fix requires Event 38068 to be set to be activated.
        The event level should be set to a figure to be used as a
        percentage to determine the cutoff at which we should use
        a guess cost in preference to a non-guess cost.
  Workaround:
    Hint the query to use the preferred index.
bsram79:
Also set the follwing events:
38044 to enable fix to Bug 3290358
38056 to enable fix to Bug 3668224.
To implement the solution set in init.ora the following events and restart the
database.:

event=”38044 trace name context forever, level 1″
event=”38056 trace name context forever, level 1″
event=”38068 trace name context forever, level 100″

Bug 4618615  – DDR info BugDesc 4618615
Affects:            RDBMS (A2-B0)
NB:                    FIXED
Abstract:           Query with bind variable against column with histogram has wrong selectivity
Fixed-Releases:     A202 B000
Tags:               BADCARD CBO QPERF RA105 RA201 REGRESSION
Details:           
  This problem is introduced in 10.1.0.5 / 10.2.0.1 by the fix for bug 3536027.
  A query with a bind variable against a column with a histogram
  can have the wrong selectivity leading to a suboptimal plan.
  This can occur when:
   – The original fix for bug 3536027 is applied/included
   – There is a height-balanced histogram on the column
   – There are some popular values in the histogram. 
     (a popular value is a value that appears more than one endpoint).

Bug.4618615
Bug.5263572
Bug.5364143
Bug.5547895
Bug.5082178
Bug.4652100
Bug.5519856
Bug.5910187

What to do when oracle database hang?

Hang Analyze
1- Using SQL*Plus connect as “/ AS SYSDBA”
SQL> oradebug setmypid

2- Execute the following commands:
SQL> oradebug hanganalyze 3
   … Wait at least 2 minutes to give time to identify process state changes.
   SQL> oradebug hanganalyze 3
SQL> oradebug setmypid
3)

3- Open a separate SQL session and immediately generate a system state dump.
  System StateDump

  Using SQL*Plus connect as “/ AS SYSDBA”
      select * from dual;                   
      oradebug setmypid
      oradebug unlimit
      oradebug dump systemstate 266
      … Wait at least 5 minutes
      oradebug dump systemstate 266
      … Wait at least 5 minutes
      oradebug dump systemstate 266
      exit