statspack report hangs

Problem: Statspack report hangs 

Executing @?/rdbms/admin/spreport.sql report hangs in midway & unable to proceed further until cancelled

Even if we try to keep it for Hours after undo_retention limit it will throw "ORA-01555 snapshot too old" & come out

Such issue is observed in pre-10g databases where there is no other substitute to stats-pack & its due to huge db object count

Analysis:

Initial analysis,  i kept the report running for over night & it kicked me out with ORA-01555 error after 9 Hours

Further , PERFSTAT.STATS$SQL_SUMMARY table found to be the problem causing table which was doing "db file sequential read" infinitely 

SQL*Net message to dblink , SQL*Net message from dblink

This blog is written based on the issue faced due to huge response time of sql query waiting on dblink.

Issue:

Query responding in almost 5 min with session wait event of SQL*Net message to dblink / SQL*Net message from dblink

Analysis:

T3157 was a view calling one more view XXECMS_SM_DTLS_VL_ATS

Observing the DDL of XXECMS_SM_DTLS_VL_ATS it has been identified that it is formed with a complex query having remote references via db link.

Each table being referred over db link was holding more than 50 lac records & sizing over 4 GB each.

Improving FTS oracle

This blog is written after resolving sev1 performance issue post DB migration to new setup

Issue: Sql Full table Scan (FTS) Slowness due to change in execution plan post db migration

Background:

Recently we have migrated 11gr2 Single Node prod. DB (ASM/AIX) to 11gr2 RAC 2 node (Linux) using Data Pump, since then started facing the below issue