This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

What’s the Current Size of AWR in the Oracle Database?

If you have to size a new Oracle 10g database, or you just want to increase the AWR snapshot interval, you may want to know how much database space is being used by the current AWR setup.

This is where V$SYSAUX_OCCUPANTS comes in.

SQL> set linesize 300
SQL> col OCCUPANT_NAME format A7
SQL> SELECT OCCUPANT_NAME,
           OCCUPANT_DESC,
           SPACE_USAGE_KBYTES
           FROM v$sysaux_occupants where OCCUPANT_NAME ='SM/AWR';

OCCUPAN OCCUPANT_DESC                                         SPACE_USAGE_KBYTES
------- ----------------------------------------------------- ------------------
SM/AWR  Server Manageability - Automatic Workload Repository  107008

The above was taken from a system with a default AWR setup (retention of 7 days, snapping every hour).
This is approximately 14MB per day.

If you were thinking you were going to keep 60 days, you would expect about 900MB of data in SYSAUX.

Checking Segment High Water Mark (HWM) in Oracle 10g

The High Water Mark is equal to “Total Blocks” – “Unused Blocks”.

Substitute “<<<A USER>>>” and “<<<A TABLE>>>” with the real values.
Thanks to this post here.

set serveroutput on size 100000;
DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM dba_tables where table_name = ‘<<<A TABLE>>>’) LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => ‘<<<A USER>>>’ ,
segment_name => c1_row.table_name ,
segment_type => ‘TABLE’ ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( ‘Data for ‘|| c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD(‘*’,LENGTH(c1_row.table_name) + 10,’*’));
DBMS_OUTPUT.PUT_LINE( ‘Total Blocks……………..’|| alc_bks );
DBMS_OUTPUT.PUT_LINE( ‘Total Bytes………………’|| alc_bts );
DBMS_OUTPUT.PUT_LINE( ‘Unused Blocks…………….’|| unsd_bks );
DBMS_OUTPUT.PUT_LINE( ‘Unused Bytes……………..’|| unsd_bts );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext File Id……..’|| luefi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext Block Id…….’|| luebi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Block…………..’|| lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

Oracle Index Rows Per Leaf Block

The query below can be used to obtain the number of rows per index leaf block.
You will need to know the index name, the table name that the index references, the pk column in the index and the object id for the index:

SELECT object_id
from user_objects
WHERE object_name = 'MGMT_VERSIONS_PK';

SELECT rows_per_block, count(*) blocks
FROM (
      SELECT /*+ cursor_sharing_exact
                 dynamic_sampling(0)
                 no_monitoring
                 no_expand
                 index_ffs(serv_inst,ix_serv_inst)
                 noparallel_index(serv_inst,ix_serv_inst)
              */
             sys_op_lbid(349440,              -- << INDEX OBJECT ID HERE
                                 'L',rowid) block_id,
             count(*) rows_per_block
        FROM MGMT_VERSIONS                    -- << TABLE NAME HERE
       WHERE COMPONENT_NAME IS NOT NULL       -- << INDEX COL NAME HERE
      GROUP BY sys_op_lbid(349440,            -- << INDEX OBJECT ID HERE
                           'L',rowid)

     )
GROUP BY rows_per_block;

This is useful for determining sparse index blocks:
https://www.dba-oracle.com/t_sys_op_lbid_index_node_density.htm

It could be combined with this script (https://jonathanlewis.wordpress.com/segment-scans/) which helps to detect full table scans and index fast full scans:

select
       owner,

       object_type,
       object_name,
       obj#,
       subobject_name,

       tablespace_name,
       value scans
from
       V$segment_statistics
where
       statistic_name = 'segment scans'
  and  value != 0
order by owner, value;

(When I get time, I’ll combine it and post it here).

Running the first SQL statement, then checking if the table has “high” segment scans would then give a good indication if the index is used frequently for large multi-block operations and is very sparse (block wise) and potentially up for a re-build.

DBUA “You do not have OS authentication” – Fat Fingers

The other day, I was upgrading an Oracle 10.2.0.2 database to 10.2.0.5 using DBUA (it has it’s benefits and provides a nice consistent approach when under pressure, providing it’s used with care).

I constantly got the request to enter the password for a DB user with SYSDBA privileges.
I didn’t have the SYS or SYSTEM user passwords (really!), but the OS user I was using definitely was a member of the group that is compiled into the config.o library (see MYOS note “SYSDBA and SYSOPER Privileges in Oracle [ID 50507.1]”):

For reference, you check on UNIX by:

cd $ORACLE_HOME/rdbms/lib
cat config.[c|s]

Checking the values for SYS_DBA and SYS_OPER against the UNIX groups your user is a member of (use the “groups” command).

The solution: this was simple, the Oracle home had been manually added into the /etc/oratab file and had been misspelled.  If only it didn’t take 45 minutes to find it!
Correcting /etc/oratab and restarting DBUA fixed the problem.

I guess DBUA was just plain lying and it didn’t have the heart to tell me that I was about to upgrade a database when it couldn’t find the Oracle home.  Shame on you.

Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE

Have you got some large indexes creeping around?
Some of my systems have 10GB indexes.
These take valuable resources away from the Oracle database:
– DML time (INSERT, UPDATE etc).
– Stats generation time.
– Segment space (hard disk).
– Structure validation time.
– Recovery time.

Removing the index may not be an option.  But what if it’s not actually used!!

How can we tell if it’s used?
Well you could monitor all SQL in the shared pool using the AWR capture script https://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm, or you could use Jonathan’s script https://jonathanlewis.wordpress.com/segment-scans/ to see if the index segment(s) has been scanned (full scan).  But these don’t comprehensively give you a definitive answer.
There could be holes in your monitoring.

Instead, you could use the V$OBJECT_USAGE view to monitor index usage.
Although a very basic method, if you only want to know definitively if an index has been used or not, then it gives you the answer.
It is described in great detail here: https://wiki.oracle.com/page/Monitor+Index+Usage

Turn on:
alter index <INDEX> monitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" monitoring usage;

Turn off:
alter index <INDEX> nomonitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" nomonitoring usage;

Check usage (must be as owner of monitored objects):
select count(1) from v$object_usage;

Or use query below to see all monitored objects:

SELECT
u.name owner ,
io.name index_name ,
t.name table_name ,
DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring ,
DECODE(bitand(ou.flags, 1), 0, 'NO', 'YES') used ,
ou.start_monitoring start_monitoring ,
ou.end_monitoring END_MONITORING
FROM
sys.user$ u ,
sys.obj$ io ,
sys.obj$ t ,
sys.ind$ i ,
sys.object_usage ou
WHERE
i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#
AND u.user# = io.owner#;