Friday, March 28, 2008

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!

No comments:

Custom Search