Home » RDBMS Server » Server Administration » query of 'dba_free_space' is too slow??!!
query of 'dba_free_space' is too slow??!! [message #50488] Mon, 18 March 2002 06:39 Go to next message
JZ
Messages: 8
Registered: March 2002
Junior Member
Oracle 8.1.6.0.0 standard edition for solaris 8

database has about 190GB. When I run the following query to find out the space usage for every tablespaces, it took 47 minutes. Meanwhile about 100 rows are inserted into database per second. Is the slow normal? Otherwise how can I improve?

select a.tablespace_name name,
b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

After ask Oracle tech support, they suggest I use 'select /*+ use_hash(a b) */', but I didn't see any improvment.

Any idea?

Thnx a lot!
Re: query of 'dba_free_space' is too slow??!! [message #50489 is a reply to message #50488] Mon, 18 March 2002 07:28 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
I would start by issueing "select table_name, LAST_ANALYZED from dba_tables where owner='SYS';" and "select index_name, LAST_ANALYZED from dba_indexes where owner='SYS';" to be sure SYS does not have any objects analyzed. There should be nothing in the LAST_ANALYZED column. If they do enter "exec dbms_utility.analyze_schema('SYS','DELETE');"

I use the following scripts I got somewhere. It uses a view. You are welcome to try it and see if it works better.

rem
rem create_space_views.sql
rem
rem Purpose:
rem This script will create required views for space reporting
rem This script should be run as "SYS"
rem
rem Author: Jeff Tarnok
rem Date: Jan. 1999
rem Version History:
rem Version 1.00.00
rem
create or replace view space_total
as select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name;
create or replace view space_used
as select tablespace_name, sum(bytes) bytes
from dba_extents group by tablespace_name
/

rem
rem space.sql
rem
rem Purpose:
rem This script reports tabespace utilization using views created with
rem create_space_views.sql.
rem
rem Author: Jeff Tarnok
rem Date: Dec. 1998
rem Version History:
rem Version 1.00.00
rem
set feedback off
set pagesize 10000

column total heading "Total MB" format 99999
column used heading "Used MB" format 99999
column percent heading "% Utilization" format 999

select
d.tablespace_name,
round(d.bytes/1048576) total,
nvl(round(f.bytes/1048576), 0) used,
nvl(f.bytes/d.bytes*100, 0) percent
from
sys.space_total d, sys.space_used f
where
d.tablespace_name=f.tablespace_name (+)
order by
tablespace_name
/
Re: query of 'dba_free_space' is too slow??!! [message #50533 is a reply to message #50488] Wed, 20 March 2002 06:29 Go to previous messageGo to next message
JZ
Messages: 8
Registered: March 2002
Junior Member
Thanks a lot!
I tested your script on two big databases, one is 150GB, the other one is 185 GB. It works fine. It took just several seconds. Why does the 'dba_free_space' cause the performance problem? Since in your script, you don't use this view, so that's why it's much faster.
Re: query of 'dba_free_space' is too slow??!! [message #50536 is a reply to message #50488] Wed, 20 March 2002 07:32 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
I have no idea. Maybe someone else has an idea on this one.
Re: query of 'dba_free_space' is too slow??!! [message #643147 is a reply to message #50536] Wed, 30 September 2015 08:45 Go to previous messageGo to next message
Frank Naude
Messages: 4557
Registered: April 1998
Senior Member
See MOS Doc ID 271169.1: Queries on DBA_FREE_SPACE are Slow.

The solution is to purge the recyclebin:
SQL> purge recyclebin;

Re: query of 'dba_free_space' is too slow??!! [message #643148 is a reply to message #643147] Wed, 30 September 2015 09:01 Go to previous messageGo to next message
cookiemonster
Messages: 13344
Registered: September 2008
Location: Rainy Manchester
Senior Member
Frank, you do realize this thread is 13 years old?
Re: query of 'dba_free_space' is too slow??!! [message #643149 is a reply to message #643148] Wed, 30 September 2015 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 26289
Registered: January 2009
Location: SoCal
Senior Member
& was for V8.1.6
Re: query of 'dba_free_space' is too slow??!! [message #643151 is a reply to message #643149] Wed, 30 September 2015 10:27 Go to previous message
Frank Naude
Messages: 4557
Registered: April 1998
Senior Member
I do, but I've decided to modernize it a bit.
If you search Google for DBA_FREE_SPACE and SLOW, this page is (still!) one of the top hits.
Recycle Bin wasn't available back in the day, but since Oracle 10g it is causing this view to perform spectacularly badly.
Previous Topic: Error ORA-47306 causing error for Checkpoint
Next Topic: ORA errors reflected in Apps not registering in the database alert log
Goto Forum:
  


Current Time: Wed Nov 21 09:44:45 CST 2018