| 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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
