Direct path load operations incur waits for "control file sequential read" event

Usual select generates a lot of wait events. In Enterprise Manager it looks like:

System I/O    control file sequential read    file#    0    block#    18    blocks    1    0
System I/O    control file sequential read    file#    0    block#    16    blocks    1    0
System I/O    control file sequential read    file#    3    block#    1    blocks    1    0
System I/O    control file sequential read    file#    2    block#    1    blocks    1    0
System I/O    control file sequential read    file#    1    block#    1    blocks    1    0
System I/O    control file sequential read    file#    0    block#    1    blocks    1    0
System I/O    control file sequential read    file#    0    block#    18    blocks    1    10
System I/O    control file sequential read    file#    0    block#    16    blocks    1    0
System I/O    control file sequential read    file#    3    block#    1    blocks    1    0
System I/O    control file sequential read    file#    2    block#    1    blocks    1    0 

Following Metalink:

It takes a long time to query v$log_history in rman session

In rman session hangs for a long time while executing:
SELECT recid, stamp, thread#, sequence#, first_change# low_scn,
       first_time low_time, next_change# next_scn, resetlogs_change#,
       resetlogs_time
    FROM v$log_history
    WHERE recid BETWEEN :b1 AND :b2
      AND resetlogs_time IS NOT NULL
      AND stamp >= :b3
    ORDER BY recid

It occurred at full resync time. It suppose to be another appearance of BUG:5620640 - LONG WAIT ON OPEN DB RESETLOGS WHEN USING FLASHBACK DB GUARANTEED RECOVERY POINT

Oracle's Block Browser and Editor tool

Featured Database Articles

September 23, 2009
Oracle's Block Browser and Editor tool

By Steve Callan
In a prior article on Oracle’s Block Browser and Editor tool (BBED), the installation of the tool was covered. In this article, we’ll look at starting a session using a simple database and see how to view data. The database used in this example is minimal in terms of structure. Use dbca to create a general purpose database and pick a sample schema such as SCOTT to practice on. This simplifies what you have to find.

The schema contains four tables (EMP, DEPT, BONUS, and SALGRADE) and two indexes, all contained in the USERS tablespace and further, only one database file to contend with (users01). We don’t care about the other two segment names (PK_EMP and PK_DEPT) because they represent indexes, and really, why would you want to use bbed on an index? Remember, use of bbed is a last resort measure, and there are so many other ways of fixing or rescuing indexes that are much safer.

As an example, let’s get the

How to set trace for others sessions, for your own session and at instance level

a very interesting article which I found at src

This short paper is not a polished white paper (so please do not expect lots of explanatory text) but is intended to be a good example to show the many ways to set Oracle trace in your own session or in another session. let's get on with it:

Tools to analyse trace files

Up to and including Oracle 10g the tool that is generally used to analyse trace files is called tkprof. This tool formats the trace files that have been generated into a more readable format. Understanding the trace file format seems daunting on first inspection. A good source for details on the trace file format is a metalink note 39817.1. In 10g there is a new tool for formatting trace files called trcsess. This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module / action. This allows trace to be completed even if connection pooling and multi-threading is used. An individual client in these circumstances could share many different sessions.

Find out where the trace file will be written to

If the user you are using is not a DBA or to be more specific has not been granted access to the data dictionary view V$PARAMETER then you will need to use this technique to find out where your trace files are written to:

    SQL> set serveroutput on size 1000000 for wra
    SQL> declare
      2  paramname varchar2(256);
      3  integerval binary_integer;

Oracle Open World: OOW 2010: We Go Green and healthy ... oh ... sort of ...

By Mike Dietrich:

OOW 2010: We Go Green and healthy ... oh ... sort of ...

The healthy food provided in the breaks ... :-(

src

library cache pin & library cache lock

Once there was an error on one of our production database. I saw some process awaiting for "library cache lock". One process was in "library cache pin' state. This situation was expected during table partition movement operation.

As I understood it was a consequences of invalid package which became invalid due to "alter table XXX move partition". Session 388 was getting library cache pin when he or some system process had been tried to recompile invalid object during partition movement. The others were waiting for 388 process to get a lock. After partition had been moved successfully package was recompiled and the queue disappeared.

b-tree index structure (Non-Unique)

First name kind of field based index. This is Non-Unique index.

How b-tree database indexes work and how to tell if they are efficient (100' level)


src