Database Monitoring
Oracle Hidden Parameters
Under normal circumstances you shouldn’t have any need to modify the Oracle hidden parameters, which all start with the underscore character. This is probably why you can’t see them very easily in the database with the normal SHOW command. If you would like to find out what hidden Oracle parameters are set within your database, […]
What’s in The Oracle Buffer Cache?
First of all, you can try running this query: SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS FROM V$DB_CACHE_ADVICE AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’) AND ADVICE_STATUS = ‘ON’; This many not return any rows depending upon what other initilisation parameters you have or have not set. You may find […]
ORA-30036: unable to extend segment by 4 in undo tablespace ‘UNDOTBS1’
One of our databases crashed recently because the disks were taken offline by mistake – fortunately, it was a development one! When I brought it back online and people started to use it, one of the developers got the following error message: ERROR at line 1: ORA-30036: unable to extend segment by 4 in undo […]
Control File Parallel Write Wait Event
Usually, you will find that high wait times on the control file parallel write wait event will be indicative of high log switching. The query below will show you which session performed control file transactions. select a.sid, decode(a.type, ‘BACKGROUND’, ‘BACKGROUND-‘||substr(a.program, instr(a.program,'(‘,1,1)), ‘FOREGROUND’) type, b.time_waited, round(b.time_waited/b.total_waits,4) average_wait, […]
ORA-00054: resource busy and acquire with NOWAIT specified
The ORA-00054: resource busy and acquire with NOWAIT specified error message usually happens when there are active transactions against an object in the database which mean other sessions cannot get the lock they require on the object to complete their transaction. There are two ways that a transaction can attempt to obtain a lock in […]
Monitor Oracle UNDO
Before I go into detail about how to check your UNDO tablespace in Oracle, I’d like to very briefly outline what UNDO is used for in the Oracle database. Quick Recap…Essential Info In short, the UNDO tablespace is used to store before images of database blocks which have had some kind of change made to […]
Monitoring TEMP Space Usage in Oracle
It’s a common problem encountered by many DBAs on a daily basis; developers writing queries which run out of TEMP space. It can come at the worst of times, too… For example, you’ve run in the scripts many times overnight into a development environment and they worked fine. They are signed off and run into […]
