I found myself in a situation where OpsView, a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.
Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:
SQL> set timing on SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS'; NVL(SUM(DFS.BYTES)/1024/1024,0) ------------------------------- 70.75 Elapsed: 00:00:10.98
There are 60 tablespaces in this pluggable database, which the time varied querying each tablespace, but was by far where most the time was spent.
I wrote a PL/SQL block to mimic Opsview as I didn’t want to create an object (procedure) in this customer’s database:
SET SERVEROUTPUT ON SET TIMING ON DECLARE cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY'; sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string'''; sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string'''; sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string'''; num_out NUMBER; BEGIN FOR ts_name in ts_names LOOP --sql for used space EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out; dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name)); dbms_output.put_line(num_out); --sql for free space EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out; dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name)); dbms_output.put_line(num_out); --sql for max EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out; dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name)); dbms_output.put_line(num_out); END LOOP; END; /
I ran this and the total time was shocking 😐 :
SQL> --SET SERVEROUTPUT ON
SQL> SET TIMING ON
SQL> DECLARE
2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
6 num_out NUMBER;
7 BEGIN
8 FOR ts_name in ts_names
9 LOOP
10 --sql for used space
11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
13 dbms_output.put_line(num_out);
14 --sql for free space
15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
17 dbms_output.put_line(num_out);
--sql for max
18 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
21 dbms_output.put_line(num_out);
22 END LOOP;
END;
23 24 /
PL/SQL procedure successfully completed.
Elapsed: 00:21:30.94
SQL>
So I searched My Oracle Support (MOS) and found the following MOS note:
Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)
Which states:
“1) In release 10g, the view dba_free_space was modified to also include objects in the recycle bin.
2) Large number of objects in the recyclebin can slow down queries on dba_free_space.
3) This is a normal behaviour.
4) For release 11g, the view dba_free_space doesn’t contain a hint which in case when there is only few objects in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.”
The database indeed did have a lot of objects in the recycle bin (in the pluggable database):
SQL> SELECT count(*) from dba_recyclebin; COUNT(*) ---------- 27615 SQL>
With most of them drop recently:
SQL> select trunc(to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS')), count(*) from dba_recyclebin group by trunc(to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS')) 2 order by 1 3 / TRUNC(TO_ COUNT(*) --------- ---------- 24-SEP-16 2 ... 19-JAN-18 2506 20-JAN-18 4322 21-JAN-18 4321 22-JAN-18 4320 23-JAN-18 4321 24-JAN-18 4321 25-JAN-18 2446 421 rows selected. SQL>
So I purged the recycle bin (with customers permission) and re-ran the check:
SQL> purge dba_recyclebin; DBA Recyclebin purged. Elapsed: 00:06:30.39 SQL> --SET SERVEROUTPUT ON SET TIMING ON SQL> SQL> DECLARE 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY'; 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string'''; 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string'''; 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string'''; 6 num_out NUMBER; 7 BEGIN 8 FOR ts_name in ts_names 9 LOOP 10 --sql for used space 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out; 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name)); 13 dbms_output.put_line(num_out); 14 --sql for free space 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out; 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name)); 17 dbms_output.put_line(num_out); 18 --sql for max 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out; 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name)); 21 dbms_output.put_line(num_out); 22 END LOOP; 23 END; 24 / PL/SQL procedure successfully completed. Elapsed: 00:02:46.25 SQL>
Result, the duration of the PL/SQL block went from 21 minutes to just under 3 minutes. However I need it to go under 2 minutes as this was the timeout for OpsView.
So I proceed with the next recommendation in the MOS note of gather dictionary and fixed table stats (with customers permission) using MOS note:
How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects (Doc ID 457926.1)
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. Elapsed: 00:00:20.49 SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed. Elapsed: 00:04:28.07 SQL> --SET SERVEROUTPUT ON SET TIMING ON SQL> SQL> DECLARE 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY'; 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string'''; 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string'''; 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string'''; 6 num_out NUMBER; 7 BEGIN 8 FOR ts_name in ts_names 9 LOOP 10 --sql for used space 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out; 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name)); 13 dbms_output.put_line(num_out); 14 --sql for free space 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out; 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name)); 17 dbms_output.put_line(num_out); 18 --sql for max 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out; 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name)); 21 dbms_output.put_line(num_out); 22 END LOOP; 23 END; 24 / PL/SQL procedure successfully completed. Elapsed: 00:00:04.53 SQL>
Bingo! the duration of the PL/SQL block went down to 4 seconds 🙂
PLEASE NOTE: This still effects non-pluggable databases, however in pluggable databases, you need to purge the recycle bin for where the dropped objects are, the container database and the pluggable databases require independent purge.
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)
I have no doubts that empty a recycle bin may sufficiently speed up your system – empty Undo helps to speed up processing. However the second part of the article when you re-running some statements twice contains very questionable proof. Remember: when you run your statement the second time Oracle checking his cache first, and if he finds a perfect match – he returns the output very fast – much faster than original statement was running.
Practically the timing of the second run is quite faster for the most of our queries which we run them sequentially in a short period of time. Since the result are taken from the cash.
LikeLike
Hi Norman,
Thanks for your comments. I would ordinarily agree with you to some degree, however in this instance I know the timings are correct for 2 reason:
1. Although I didn’t show in the post and perhaps I should have, I ran the PL/SQL block several times and got more a less consistent timings
2. OpsView was still running in the background running the queries every 5 minutes and I could see the time taken around:
a) 15-25 minutes before purging recycling bin
b) 3 minutes after purging recycling bin
c) few seconds after gather stats
With regards to caching, there 2 thing you could be referring to:
1. Result Cache, which store the result of a query in a result cache in the SGA and if the result doesn’t become stale i.e. underlying data changes, it can be served from result cache instead of running the query. However, I don’t believe result cache is applicable on dictionary table but I could be wrong. I certainly didn’t see result cache in execution plan and even if it was in the result cache, I would believe it would get stale very quickly.
2. Blocks in the buffer cache, thus consistent gets instead of physical reads, however autotrace showed mostly if not all consistent get i.e. from memory not disk. And the running of the query multiple times by OpsView pretty much had all the blocks in the buffer cache.
The main factor here is by purging the recyclebin the underlying query for DBA_FREE_SPACE doesn’t need to go through the x$ tables to get data for objects in the recyclebin and the second factor was gather dictionary stats, so the optimiser had better stats about it’s own dictionary tables, so to create more optimal execution plan.
Apologies if I wasn’t clear about this.
Kind Regards
ZedDBA
LikeLike
Nice work-around!
However, what about addressing the fact that (a) your customer had nearly 28000 objects in the database’s recycle bin and (b) didn’t even know about that, apparently, until you ran an unrelated query?
Seems to me to be an incredible waste of usable space, especially since they had no problem letting you blow away all those RECYCLEd objects without a fight …
LikeLike
Hi Jim,
Thanks for your comments 🙂
Oh yes, don’t worry we are on the case! We have advised the client about the issue and it was discovered the application vendor using this database was deploying releases and dropping them, repeatedly (this is a test database). So we have advised them to:
1. Use the drop with purge option if they are not intending “flashback table to before drop”
2. Purge recycle bin after their test cycle
3. Us disable the recycling bin if not in use
So this problem doesn’t keep reoccurring 🙂
Kind Regards
ZedDBA
LikeLike
Pingback: How to fix queries on DBA_FREE_SPACE that are slow - SSWUG.ORG
Thank you! Useful.
LikeLiked by 1 person
Welcome, glad you found useful 🙂
LikeLike
Thank you, very useful. Went from 500sec to 5sec when monitoring tablespaces
LikeLiked by 1 person
You’re most welcome 🙂
Glad this blog post came in use for others.
LikeLike
Thanks Mr. Zahid,
I really appreciate your knowledge, I faced the same issue, it was very slow to get the tablespace view in Toad ( around 30 minutes ) , but once I ran your script ( gather stats ) it took 2 ~ 3 seconds.
Thanks again
LikeLike
You’re welcome Omer 🙂
LikeLike
Hi Zahid, this article is very good and helped me with the same problem you have mentioned, after following the steps, my query ran within 2 seconds as compared to 1 hour +. So thank you very much.
LikeLike
You’re most welcome 🙂
LikeLike
you save me!!!!!
LikeLike
You’re welcome 🙂
LikeLike