Oracle TABLESPACE Allocation / Usage Performance Impact

Question

TLDR? Skip to bullet points.

I've recently deployed a solution with an Oracle 11g DB at the backend to a test environment and performance is TERRIBLE - the system is inoperable. Performance was better in the lower-spec'd dev. environment. I appreciate this could be down to many, many things but the Oracle setup is currently in the firing line since its the component I'm least familiar with (and I'm running out of ideas).

The DB was created using dbca. Simple SELECTS made directly against the DB in SQL Developer are OK, but going through our internal data access drivers which involve complex queries with lots of joins yields the poor performance. There are no network latency issues and the data access code is fine* elsewhere.

*albeit a bit chatty - a story for another day.

To assist, I'd like info. on the following:

SQL Developer shows that tablespace usage of SYSTEM / SYSAUX is 99% (of 710Mb / 600Mb respectively). Could this have a performance impact?
SQL Developer shows that the TEMP tablespace has usage NULL, allocated NULL, used NULL, free 0, datafiles NULL. I ran queries against the DB to show that the temp file exists and is online... I even dropped / recreated it, but still it appears NULL in the Manage Database screen. Is this normal behaviour? If not, could this have a performance impact?

Thanks in advance.

You might want to ask this question on dba.stackexchange.com. — Robin Green
What edition of Oracle (enterprise? standard? XE?) Are you licensed to use the AWR? Is Statspack installed? Oracle can provide you a wealth of information about why performance is poor-- looking at a Statspack/ AWR report would let you zero in on the actual performance problem(s) much more quickly than looking at things like tablespace allocation reports. — Justin Cave

Jon Heller Jon Heller · Accepted Answer · 2013-11-09T21:02:38

TLDR - space isn't your problem.

Your SYSTEM and SYSAUX tablespaces are probably fine.

Your reports are probably misleading. Your data files are probably using 99% of their currently allocated space. But I bet if you look at the data files you will find they can auto-extend to much larger values. For example, on my mostly-default-settings installation:

select tablespace_name, round(bytes/1024/1024) current_mb,
    round(maxbytes/1024/1024) max_mb, autoextensible
from dba_data_files
where tablespace_name in ('SYSTEM', 'SYSAUX');

TABLESPACE_NAME  CURRENT_MB   MAX_MB  AUTOEXTENSIBLE
SYSAUX           2050         32768   YES
SYSTEM           810          32768   YES

Chances are you have lots of space left, unless your hard drive is full. Plus, if you were really out of SYSTEM or SYSAUX space it would throw lots of errors. For example, you wouldn't be able to log in because sys.aud$ was full, or statistics gathering would throw exceptions if it couldn't write data.

The 12c version of DBCA doesn't even give an option of setting the SYSAUX and SYSTEM tablespace.

Your temporary tablespace is probably fine.

A completely empty temporary tablespace is unusual. But that's not a bad thing. Temporary tablespace is used for things like sorting and hashing if there's not enough space in memory. If a system runs small OLTP queries and has enough memory then temporary tablespace isn't needed.

Again, DBCA wouldn't let you under-size the temporary tablespace even if you wanted to.

Oracle TABLESPACE Allocation / Usage Performance Impact

2 Answers