Very slow to open the Tablespace View

Toad for Oracle
1

When connecting to a brand new Oracle 12.1.0.2.0, opening the tablespace view is really slow (1min) but the rest is fine.

  • the slow tablespace view is opened either by clicking Database-> Administer -> Tablespace or SchemaBrowser-> Tablespace

  • It is the same problem with Toad 11.5.1.2 with OraClient 11g on Win7 and with Toad 12.1 with OraClient 12c on Win7

  • Toad Advisor only interesting remark is “Auto-detect Oracle utilities is off” but I am not sure what it means.

  • The problem does not exist with Oracle9i, Oracle10g or Oracle11g.

  • The Oracle12c database does not use the new feature CDB/PDB => no container / no pluggable db

  • I have no other Oracle12c database to test this behavior

Any idea why it is slow? Do I need the latest Toad 12.6?

thanks

Vincent

2

Updating your Toad isn’t going to make any difference. The query hasn’t changed in recent versions of Toad.

Here’s the query that we run when Database -> Administer -> Tablespaces opens. I’m an guessing that it is just running slowly on that database. On my test DB, the query runs in less than a second, but I have seen it take a little longer.

select a.tablespace_name,

round(a.bytes_alloc / 1024 / 1024) megs_alloc,

round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

round(maxbytes/1048576) Max,

c.status, c.contents

from ( select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible, ‘YES’,f.maxbytes,‘NO’, f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

( select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b,

dba_tablespaces c

where a.tablespace_name = b.tablespace_name (+)

and a.tablespace_name = c.tablespace_name

union all

select h.tablespace_name,

round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

round(sum(decode(f.autoextensible, ‘YES’, f.maxbytes, ‘NO’, f.bytes) / 1048576)) max,

c.status, c.contents

from sys.v_$TEMP_SPACE_HEADER h,

sys.v_$Temp_extent_pool p,

dba_temp_files f,

dba_tablespaces c

where p.file_id(+) = h.file_id

and p.tablespace_name(+) = h.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = h.tablespace_name

and f.tablespace_name = c.tablespace_name

group by h.tablespace_name, c.status, c.contents

ORDER BY 1

As a possible remedy, you could check to see that your DB’s statistics are up to date. These two pl/sql blocks will update them:

BEGIN

SYS.DBMS_STATS.GATHER_DICTIONARY_STATS (

Granularity => ‘DEFAULT’

,Options => ‘GATHER AUTO’

,Estimate_Percent => 10

,Method_Opt => ‘FOR ALL COLUMNS SIZE 1’

,Degree => 4

,Cascade => TRUE

,No_Invalidate => FALSE);

END;

/

BEGIN

SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (

No_Invalidate => FALSE);

END;

/

3

Thanks for prompt answer!!

Unfortunately updating statistics did not make any difference.

The provided SQL is slow as well so nothing to do with the graphical part.

I’ve played a little bit with the SQL. The slow part seems to be in the following join of the first part of the UNION:

from ( select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible, ‘YES’,f.maxbytes,‘NO’, f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

( select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b,

dba_tablespaces c

where a.tablespace_name = b.tablespace_name (+)

and a.tablespace_name = c.tablespace_name

Also no slowness running the following queries:

select * from dba_data_files => fast

select * from dba_free_space => fast

select * from dba_tablespaces => fast

thx

Vince

4

I’ve sometimes seen better performance when moving the subqueries up to a WITH clause, but for me at least, it generates the same execution plan.

with

a as (select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible, ‘YES’,f.maxbytes,‘NO’, f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name),

b as

(select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name)

select a.tablespace_name,

round(a.bytes_alloc / 1024 / 1024) megs_alloc,

round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

round(maxbytes/1048576) Max,

c.status, c.contents

from a, b, dba_tablespaces c

where a.tablespace_name = b.tablespace_name (+)

and a.tablespace_name = c.tablespace_name

5

I’ve found the solution.

  1. in the slow join, the slow part was on the view dba_free_space:

select f.tablespace_name, sum(f.bytes) bytes_free

from dba_free_space f group by tablespace_name

  1. I updated the stats on all schema SYS objects but it did not make any difference:

EXEC dbms_stats.gather_schema_stats(‘SYS’, cascade=>TRUE);

  1. In MOS, I found the Oracle Doc ID 1292253.1 and Doc ID 1327378.1.

It reports a problem with this view with the recycle bin.

I checked the number of objects in the recyclebin:

SQL> select count(*) from recyclebin;

COUNT(*)

17

Not a big deal but I decided to purge the recycle bin then it works!

SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from recyclebin;

COUNT(*)

0

=> The Query is fast

=> the tablespace view is fast

Thanks for your help! It is very much appreciated!

6

Hey, there’s our query right there in metalink! I’m glad you found a solution. Thanks for the follow up.

7

I had exactly the same situation with one of my 12.1.0.2 DBs. The stats didn’t make any discernible difference but after reading the above I tried the recycle bin trick and the Database/Administer Tablespaces window went from minutes to less than a second.

Thanks for the tip.

Best Regards,

Brian Keegan

8

I had the same issue until I read this note from Oracle Support:

Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang (Doc ID 1292253.1)

This is what I did:

SQL> select count(*) from recyclebin;

SQL> purge recyclebin;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>‘SYS’, TABNAME => ‘$KTFBUE’,ESTIMATE_PERCENT=>100);

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

Problem Solved!!!

9

Thank you so much for the recyclebin tip! We were monitoring a long-running job that was consuming space quickly, and then the tablespace view started to hang. It was after 22:00 and we were wiped, so having your reply at hand saved us some serious aggravation. The tablespace view started behaving again after the purge completed.

Laura

10

Even after the recyclebin purging, we were experiencing delays populating the tablespace details using almost all clients (SQL, SQL Developer etc). Our further reading about the purge recyclebin for 11g landed us on http://www.dba-oracle.com/t_oracle_purge_recycle_bin.htm & following the suggestions, purged dba_recyclebin

That was the solution, the tablespace details population was instant once after the dba_recyclebin purging! Hope it helps few others out there

11

The last few versions of Toad (maybe 12.9 or so and newer) has a checkbox at the top of that window window to use DBA_LMT_FREE_SPACE, instead of DBA_FREE_SPACE, which is what that window has traditionally used. Here is some info on the differences.

  • DBA_LMT_FREE_SPACE is faster than DBA_FREE_SPACE, but includes objects in the recyclebin as used space.
  • DBA_FREE_SPACE is slower than DBA_LMT_FREE_SPACE and does not count objects in the recyclebin as used space.
  • DBA_FREE_SPACE and DBA_LMT_FREE_SPACE will give the same result when recyclebin is empty.
  • DBA_LMT_FREE_SPACE will be fast even when recyclebin is not empty.
  • As others have noted, in some oracle vesions of oracle, DBA_FREE_SPACE is dreadfully slow when the recyclebin is not empty.
    If you have a “full” tablespace, but some of its objects are in the recyclebin, oracle will automatically purge the recyclebin objects as space is needed in the tablespace for non-recycled objects.

So in most cases, DBA_LMT_FREE_SPACE will quickly give you a fairly accurate picture of free space in your tablespaces. If you want to know exactly how much space you have, switch to DBA_FREE_SPACE.