Top 10 Memory Abusers

The following query will show the top 10 SQL statements using memory as a percentage of the entire system.

SQL> select sum(pct_bufgets)
from ( select rank() over ( order by buffer_gets desc ) as rank_bufgets,
to_char(100 * ratio_to_report(buffer_gets) over (), '999.99') pct_bufgets
from v$sqlarea )
where rank_bufgets < 11;

If the percentage is

< 5% then good performance
5 - 20% then average performance
> 25% then bad performance

Thanks

Top 10 Disk-Read Abusers

The following query will show the top 10 heaviest disk reading statements as a percentage of the entire system.

SQL> select sum(pct_bufgets)
from ( select rank() over ( order by disk_reads desc ) as rank_bufgets,
to_char(100 * ratio_to_report(disk_reads) over (), '999.99') pct_bufgets
from v$sqlarea )
where rank_bufgets < 11;

If the percentage is

< 5% then good performance
5 - 20% then average performance
>25% then bad performance

Thanks

Segment Shrink Restrictions

Init.ora Parameter compatible >= 10.0

The tablespace must be Locally Managed with Automatic Segment Space Management (ASSM) enabled.

You cannot shrink:

• UNDO segments
• Temporary segments
• Clustered tables and Compressed tables
• Tables with a colmn of datatype LONG
• LOB segments (belwo 10.2)
• Tables with Function based Indexes or Bitmap Join Indexes
• IOT mapping tables and IOT overflow segments
• Tables with domain Indexes (below 11.1)
• Tables with MVIEWS with ON COMMIT
• Tables with MVIEWS which are based on ROWIDs

Incase of tables with function based indexes or bitmap join indexes, drop the index the shrink and then recreate the index.

The following query will identify the segments that are shrinkable and non-shrinkable...


SELECT dt.owner, dt.table_name,
(CASE WHEN NVL(ind.cnt, 0) < 1 THEN 'Y' ELSE 'N'END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type in ( 'FUNCTION-BASED NORMAL','FUNCTION-BASED DOMAIN','DOMAIN')
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = '&owner'
ORDER BY 1, 2;

Note: Change the values of index type according to the version you are running...

Thanks

Segment Shrinking

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use Automatic Segment Space Management and locally managed.

Steps to perform segment shrink.

Step 1: Enable Row movement for the segment

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Step 2: Recover space and amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE;

Step 2a: Recover space, but don't amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

Step 3: Recover space for the object and all dependant objects.

SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Note: The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any dependant SQL statements will need to be reparsed.

Thanks

ADDM Views

The following views are useful for ADDM analysis.

DBA_ADVISOR_TASKS - Basic information about existing tasks
DBA_ADVISOR_LOG - Status information about existing tasks
DBA_ADVISOR_FINDINGS - Findings identified for an existing tasks
DBA_ADVISOR_RECOMMENDATIONS – Reco for problems identified by tasks
DBA_ADVISOR_ACTIONS - Actions to be taken for the existing tasks
DBA_ADVISOR_RATIONALE - Rationale for ADDM recommendations.

Thanks

ADDM Findings, Recos and Actions

Query to find ADDM Findings, Recommendations and Actions

SQL> Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'b.message MESSAGE,
'Command To correct:'c.command COMMAND,
'Action Message:'c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='12/06/2009'
Order by 3 desc;

Thanks