ORA-07445 log file Oracle Error :: SP2-1503 vMaterialized View: ORA-22818 unused index

ORA-07445: exception encountered: core dump [skgfr_reap64()+235] [SIGSEGV] [Address not mapped to object] [0x2AEE7443A188] [] []

In the database alert log there is continuous message "WARNING: Oracle process running out of OS kernel I/O resources". In the trace file you will notice following message.
WARNING:io_submit failed due to kernel limitations MAXAIO for process=128 pending aio=81
WARNING:asynch I/O kernel limits is set at AIO-MAX-NR=1048576 AIO-NR=67968
WARNING:Oracle process running out of OS kernel I/O resources

Cause of the Problem
This is Oracle bug. Oracle named this bug as Bug 6687381 or Bug 7523755. This problem only occurs on platforms where the kernel cannot dynamically allocate Async IO descriptors.

Solution of the Problem
Workaround you can disable the asynchronous IO by setting disk_asynch_io to FALSE but that is not recommended because if disk_asynch_io=FALSE, then asynchronous I/O is not enabled, which may have a detrimental effect on performance.

This bug is fixed in following versions.
- 11.1.0.7 (Server Patch Set)
- 10.2.0.5 (Server Patch Set)
- 10.2.0.4.2 (Patch Set Update)
- 10.2.0.4 Patch 26 on Windows Platforms
- 11.2g

So upgrading to your oracle database is the viable solution.

Wednesday, 11 July 2012

log file switch (private strand flush incomplete)/Private Redo Strands( Private Redo )

Earlier there was a single redo allocation latch available for the entire log buffer. This was a major contention point as till the time one won’t be able to pin the latch, he wont be able to write his changed redo vectors in the log buffer. From 10.1 onwards, the log buffer is broken in to smaller sub-buffers called Private Redo Strands(shown in the x$kcrfstrand). These are also allocated in the shared pool. These all are written by redo allocation latches. There are allocation latches now for each private strand. The data when changed now, doesn’t contend to go into the log buffer as like before but goes into the private redo strands and from there, it goes into the log buffer when the buffer pools become full or when the transaction is committed. We can see the private strands shown in the shared pool from the V$sgastat

SQL> select name,pool from V$sgastat
2 where name like ‘%private%’;

NAME POOL
————————– ————
private strands shared pool
KCB private handles shared pool
At log file switch , when the data is still is in the Private redo strands, lgwr may wait to get the flush done by DBWR. Hence the event, log file switch (private strand flush incomplete) may occur

log file switch (private strand flush incomplete)
New wait 10g
Like a “log file switch Completion”
Wait for lgwr to switch log files when generating redo
Solution:
Increase redo log file size
Reduces frequency of switches


What happens when a log file switch occurs:
Get next log file from control file
Get Redo Copy and Redo Allocation latch
Flush redo
Close File
Update Controlfile
Set new file to Current
Set old file to Active
If in Archivelog mode add file to archive list
Open all members of new logfile group
Write the SCN to the headers
Enable redo log generation
DBWR makes a list of blocks that need to be written out in order to over write the Redo log file a list of blocks that need to be written out in order to over write the Redo log file

Monday, 9 July 2012

Oracle Error :: SP2-1503 Unable to initialize Oracle call interface SP2-0152: ORACLE may not be functioning properly

Oracle Error :: SP2-1503 Unable to initialize Oracle call interface
Cause

Indicates a library used by SQL*Plus to communicate with the database failed to initialize correctly.
Action

1)  Check that the Oracle environment variable are set properly .
2) If using the SQL*Plus Instant Client make sure the SQL*Plus and Oracle libraries are from the same release
3) Make sure you have read access to the libraries.

In my case there are both 10g and 11g are installed on same server . and 11g is default home .now i want to connect with oracle 10g home on the server

I have export the  ORACLE_BASE, ORACLE_HOME, ORACLE_SID

$export ORACLE_BASE=/home/oracle/apps
$ export ORACLE_HOME=$ORACLE_BASE/product/db_1/10.2.0
$ export ORACLE_SID=DB01

and run sqlplus and got below error

$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Then i have export the path  with above environment variables which i set earlier

$ export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
$ PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/bin:/usr/lib64/:$ORACLE_HOME/OPatch

and it sucessfully connected


$ sqlplus / as sysdba
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

Materialized View: ORA-22818 subquery expressions not allowed here

CREATE MATERIALIZED VIEW mv1 AS
(SELECT s.id AS sport_no
, c.rollno id
, (SELECT NAME FROM class c WHERE id = sport_no ) AS stu_name
from sport s);

Error: ORA-22818 subquery expressions not allowed here

This is a documented restriction of Materialized view.
Solution: rewrite the query replacing scalar subquery with outer join


create meterialized view mv1 as
select s.id AS sport_no
, c.rollno id
from sport s , class c
where s.id = c.rollno(+);

A simpler solution: creating MV on top of the view

CREATE or REPLACE VIEW view_sport_stu_name AS
(SELECT s.id AS sport_no
, c.rollno id
, (SELECT NAME FROM class c WHERE id = sport_no ) AS stu_name
from sport s);

create MATERIALIZED VIEW mview_sport_stu_name AS
select * from view_sport_stu_name;

How to find the unused index in database

Query to find the unused  index in database

SELECT owner, table_name ,index_name,index_type ,LAST_ANALYZED FROM DBA_INDEXES WHERE index_name IN (SELECT index_name
FROM
(SELECT
owner,
index_name
FROM
DBA_INDEXES di
WHERE
di.index_type != 'LOB'
AND
owner   =&owner
MINUS
SELECT
index_owner owner,
index_name
FROM
DBA_CONSTRAINTS dc
WHERE
index_owner   =&owner
MINUS
SELECT
p.object_owner owner,
p.object_name index_name
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p
WHERE
sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND
p.object_type = 'INDEX'
))
and owner =&owner
ORDER BY 1


Query to find the frequency  of  index usage

SELECT
p.object_name search_columns,
ROUND (COUNT(*)/15 ,2 ) COUNT
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p,
DBA_HIST_SQLSTAT st
WHERE
st.sql_id = p.sql_id
AND
sn.snap_id = st.snap_id
AND
p.object_type = 'INDEX'
AND sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND p.object_owner =&owner
GROUP BY
p.object_name ORDER BY 2 DESC, 1

ORA-00257:archiver error

ORA-00257:	archiver error. Connect internal only, until freed.
Cause:	The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action:	Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving. In My case the device associated with ARCHIVE_LOG_DEST  was 100 % full

Tuesday, 26 June 2012

Life Cycle of a Cursor

Having a good understanding of life cycle of cursors is required knowledge for optimizing application
that execute SQL statements. The following are the steps carried out during the processing of a cursor:
Open cursor: A memory structure for the cursor is allocated in the server-side private memory
of the server process associates with the session,a user global area (UGA).Note that no SQL statement is associated with the cursor yet
Parse cursor: A SQL statement is associate with the cursor It's parse representation that include
the execution plan (which describe how the SQl engine will execute the SQl statement)is loaded in the shared pool ,specifically,in the literary cache. The structure in the uga is updated to store a pointer to the location of the sharable cursor in the library cache
Define output variables:  If the SQL statements returns a data , the variable receiving must be declare .This is not necessary not only for quires but also for DELETE,INSERT,UPDATE statement that use the returning clause
Bind input variables : If the SQL statement use bind variables, their value must be provided .
No check is performed during the binding.If invalid data is passed , a runtime error will be raised during the execution.
Execute cursor: The SQL statements is executed.But be careful , becuase the database engine doesn't always do anything significant during this phase. In fact, for many types of queries,

the real processing is usually delayed to the fetch phase.
Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries,
this step is where most of the processing is performed. In the case of queries, rows might
be partially fetched. In other words, the cursor might be closed before fetching all the rows.
Close cursor: The resources associated with the cursor in the UGA are freed and consequently
made available for other cursors.The shareable cursor in the library cache is not
removed. It remains there in the hope of being reused in the future.