| query of 'dba_free_space' is too slow??!! [message #50488] |
Mon, 18 March 2002 06:39  |
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  |
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 |
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 #643151 is a reply to message #643149] |
Wed, 30 September 2015 10:27 |
Frank Naude
Messages: 4589
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
