Sure...your trash will drag you!!!

If you have figured out what happened, you may be wondering why Oracle implemented the free space view in that way, don't you?

In case you are lost in the darkness follow the next real-life case:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from dba_free_space;

 COUNT(*)
----------
   193822
                              _
SQL> select count(*) from all_objects where object_name like '%BIN%';

 COUNT(*)
----------
   175249

SQL> set timing on
SQL> run
 1  select count(*) from all_objects where object_name like '%BIN%'
 2*

 COUNT(*)
----------
   175249

Elapsed: 00:00:27.00
SQL> select count(*) from dba_free_space;

 COUNT(*)
----------
   193821

Elapsed: 00:00:45.71

That is a long waiting time for a free space report, don't you think? ... I'm not a lazy and careless DBA, but you can't take my word for granted, therefore I will check if statistics are more-or-less accurate and later refresh them, anyway.

SQL> select num_rows, blocks from all_Tables where table_name = 'RECYCLEBIN$';

 NUM_ROWS     BLOCKS
---------- ----------
   185640       2308

Elapsed: 00:00:00.05
SQL> exec dbms_stats.gather_table_stats(ownname=> 'SYS',
2: tabname=> 'RECYCLEBIN$', partname=> NULL);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.70
SQL> select count(*) from dba_free_space;

 COUNT(*)
----------
   193820

Elapsed: 00:00:46.02
SQL> run
 1* select count(*) from dba_free_space

 COUNT(*)
----------
   193787

Elapsed: 00:00:45.82
SQL> set autotrace on explain statistics
SQL> run
 1* select count(*) from dba_free_space

 COUNT(*)
----------
   193787

Elapsed: 00:00:46.20

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |       |       |  1563  (53)|
|   1 |  SORT AGGREGATE                |                  |     1 |       |       |            |
|   2 |   VIEW                         | DBA_FREE_SPACE   |   190 |       |       |  1563  (53)|
|   3 |    UNION-ALL                   |                  |       |       |       |            |
|   4 |     NESTED LOOPS               |                  |     1 |    39 |       |     4   (0)|
|   5 |      NESTED LOOPS              |                  |     1 |    32 |       |     3   (0)|
|   6 |       TABLE ACCESS FULL        | FET$             |     1 |    26 |       |     3   (0)|
|   7 |       INDEX UNIQUE SCAN        | I_FILE2          |     1 |     6 |       |     0   (0)|
|   8 |      TABLE ACCESS CLUSTER      | TS$              |     1 |     7 |       |     1   (0)|
|   9 |     NESTED LOOPS               |                  |    90 |  4050 |       |    12   (9)|
|  10 |      NESTED LOOPS              |                  |    90 |  3510 |       |    12   (9)|
|  11 |       TABLE ACCESS FULL        | TS$              |    36 |   468 |       |    11   (0)|
|  12 |       FIXED TABLE FIXED INDEX  | X$KTFBFE (ind:1) |     3 |    78 |       |     0   (0)|
|  13 |      INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |       |     0   (0)|
|  14 |     NESTED LOOPS               |                  |    98 |  6762 |       |  1530  (54)|
|  15 |      NESTED LOOPS              |                  |    98 |  6174 |       |  1530  (54)|
|  16 |       HASH JOIN                |                  |   169K|  3983K|  3920K|   840  (16)|
|  17 |        TABLE ACCESS FULL       | RECYCLEBIN$      |   174K|  1871K|       |   585  (14)|
|  18 |        TABLE ACCESS FULL       | TS$              |    36 |   468 |       |    11   (0)|
|  19 |       FIXED TABLE FIXED INDEX  | X$KTFBUE (ind:1) |     1 |    39 |       |     0   (0)|
|  20 |      INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |       |     0   (0)|
|  21 |     TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |     1 |    11 |       |     2   (0)|
|  22 |      NESTED LOOPS              |                  |     1 |    63 |       |    17   (0)|
|  23 |       NESTED LOOPS             |                  |     1 |    52 |       |    15   (0)|
|  24 |        NESTED LOOPS            |                  |     1 |    45 |       |    14   (0)|
|  25 |         TABLE ACCESS FULL      | UET$             |     1 |    39 |       |    14   (0)|
|  26 |         INDEX UNIQUE SCAN      | I_FILE2          |     1 |     6 |       |     0   (0)|
|  27 |        TABLE ACCESS CLUSTER    | TS$              |     1 |     7 |       |     1   (0)|
|  28 |         INDEX UNIQUE SCAN      | I_TS#            |     1 |       |       |     0   (0)|
|  29 |       INDEX RANGE SCAN         | RECYCLEBIN$_TS   |  7961 |       |       |     2   (0)|
------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
   4027917  recursive calls
       129  db block gets
    891718  consistent gets
    175114  physical reads
         0  redo size
       517  bytes sent via SQL*Net to client
       469  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> set autotrace off

Let's see what happen if we get rid off all that garbage... be pacient, it'll take 2 or 3 hours to purge the recycle bin.

SQL> purge dbarecycle_bin;

Recyclebin purged

Our timings will improve by the order of thousands, getting results in results in fractions of seconds. That will make easier and faster your space management tasks... say good bye to those long chats with your peers between tablespace resize (if you are using Enterprise Manager is even worse!).

SQL> run
 1* select count(*) from all_objects where object_name like '%BIN%'

 COUNT(*)
----------
      103

Elapsed: 00:00:00.20

SQL> set linesize 255
SQL> set autotrace on explain statistics
SQL> run
 1* select count(*) from dba_free_space

 COUNT(*)
----------
     1140

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |       |      |  1563  (53)|
|   1 |  SORT AGGREGATE                |                  |     1 |       |      |             |
|   2 |   VIEW                         | DBA_FREE_SPACE   |   190 |       |      |  1563  (53)|
|   3 |    UNION-ALL                   |                  |       |       |      |             |
|   4 |     NESTED LOOPS               |                  |     1 |    39 |      |      4   (0)|
|   5 |      NESTED LOOPS              |                  |     1 |    32 |      |      3   (0)|
|   6 |       TABLE ACCESS FULL        | FET$             |     1 |    26 |      |      3   (0)|
|   7 |       INDEX UNIQUE SCAN        | I_FILE2          |     1 |     6 |      |      0   (0)|
|   8 |      TABLE ACCESS CLUSTER      | TS$              |     1 |     7 |      |      1   (0)|
|   9 |     NESTED LOOPS               |                  |    90 |  4050 |      |    12   (9)|
|  10 |      NESTED LOOPS              |                  |    90 |  3510 |      |    12   (9)|
|  11 |       TABLE ACCESS FULL        | TS$              |    36 |   468 |      |    11   (0)|
|  12 |       FIXED TABLE FIXED INDEX  | X$KTFBFE (ind:1) |     3 |    78 |      |      0   (0)|
|  13 |      INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |      |      0   (0)|
|  14 |     NESTED LOOPS               |                  |    98 |  6762 |      |  1530  (54)|
|  15 |      NESTED LOOPS              |                  |    98 |  6174 |      |  1530  (54)|
|  16 |       HASH JOIN                |                  |   169K|  3983K|  3920K|   840  (16)|
|  17 |        TABLE ACCESS FULL       | RECYCLEBIN$      |   174K|  1871K|      |   585  (14)|
|  18 |        TABLE ACCESS FULL       | TS$              |    36 |   468 |      |    11   (0)|
|  19 |       FIXED TABLE FIXED INDEX  | X$KTFBUE (ind:1) |     1 |    39 |      |      0   (0)|
|  20 |      INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |      |      0   (0)|
|  21 |     TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |     1 |    11 |      |      2   (0)|
|  22 |      NESTED LOOPS              |                  |     1 |    63 |      |    17   (0)|
|  23 |       NESTED LOOPS             |                  |     1 |    52 |      |    15   (0)|
|  24 |        NESTED LOOPS            |                  |     1 |    45 |      |    14   (0)|
|  25 |         TABLE ACCESS FULL      | UET$             |     1 |    39 |      |    14   (0)|
|  26 |         INDEX UNIQUE SCAN      | I_FILE2          |     1 |     6 |      |      0   (0)|
|  27 |        TABLE ACCESS CLUSTER    | TS$              |     1 |     7 |      |      1   (0)|
|  28 |         INDEX UNIQUE SCAN      | I_TS#            |     1 |       |      |      0   (0)|
|  29 |       INDEX RANGE SCAN         | RECYCLEBIN$_TS   |  7961 |       |      |      2   (0)|
------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       295  recursive calls
       129  db block gets
     12437  consistent gets
         0  physical reads
         0  redo size
       516  bytes sent via SQL*Net to client
       469  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

Please, before you go, don't forget to vote the poll regarding the content of this blog, thank you!

Your trash will drag you

If you think that Oracle recycle bin (feature available since 10g1) has no direct impact on your performance, you better think deep about it... seriously.

Try the following (if possible) : turn on statement timing, then issue a simple SELECT on DBA_FREE_SPACE and keep record of elapsed time; create a couple of hundreds of thousands tables in a 10g database, and then delete it (the DB recycle bin feature must be turned on) ... now you have a full-to-the-top recycle bin, then repeat the SELECT on DBA_FREE_SPACE, you'll see it will take a longer time to complete, and finally will get a huge number of rows. You may think that suddenly the free space has been multiplied... but your response time divided.

I don't know ... I'll let you think about this puzzling situation here and come back later to see how you doing.

Question: Can you figure out what is happening?

Subscribe to Oracle Database Disected by Email

2 minute guide for Statspack Installation

Ver este articulo en Español

Statspack is an Oracle database tool that is both powerful and underused, it's the first reporting resource you have (it's free, comes in the box) to see what is happening in your DB at a glance.

Ingredients
1 Oracle sql*plus session
1 Tablespace with at least 200Mb (9i) or 400Mb (10g) of free space*
1 user with DBA power

*Recommend you create a brand new tablespace

Preparation
You need to install Statspack previous to any use, then you have to run just one script... yes, it's that easy.
Login with your powerful user, then at the prompt type:

SQL> @?/rdbms/admin/spcreate

...you've started the Statspack setup, that will create the owner for the Statspack objects, which username is fixed to PERFSTAT. In order to acomplish that, asks for the tablespace where all objects will be stored, the temporary tablespace Statspack will use for sorting data and the user password.

You'll end with a message like this, and a result listing at the place you executed sqlplus.

It's recommended to establish a baseline for your reporting, then you must connect as PERFSTAT and take your first snapshot:

SQL> exec statspack.snap;

It will run with detail level 5, which is the default ... and enough for 90% of the cases. Taking samples 2 to 5 minutes apart is the period recommended, you may take samples with longer intervals but some results will get their formatting exceded and show # marks.

You may be interested on scheduling execution of statspack.snap, please read 2 minute guide for Statspack sampling

Further ... and detailed reading at the Oracle(R) Database Performance Tuning Guide.

Subscribe to Oracle Database Disected by Email

Your own DB 'Big Brother'

How many times you have been in the unfortunate situation where something catastrophic has happened, caused by an OSI 'Layer-8' error, you've been asked whom did that... and you don't have a clue.

Well, for those cases Oracle provides a very useful auditing feature, that is very simple to enable.

Usually you start enabling auditing on users that have the privilege to do the activity you are interested on, after you may enable auditing on users suspicious of trying to do something they don't have the privilege to.

Let's give a short example, given the case I want to track who drops or modifies any user.

SQL> alter system set audit_trail=DB scope=spfile;

or edit your pfile to reflect above setting
SQL> shutdown
SQL> startup
SQL> audit drop user by admin;


SQL> audit alter user by admin;

SQL> alter user scott identified by tigre;
User Altered
SQL> select count(*) sys.aud$;

How do you check what is fasible to audit or what is beeing audited, you must check these views:
* SYSTEM_PRIVILEGE_MAP
* DBA_PRIV_AUDIT_OPTS
* DBA_AUDIT_OBJECT

If those views aren't available then you must create them running the cataudit.sql script from $ORACLE_HOME/rdbms/admin.

Further explanation you may find in the Oracle Database Administration Guide.

Subscribe to Oracle Database Disected by Email