Home » RDBMS Server » Server Administration » Useful Tablespace Usage (11.2.0.1)
Useful Tablespace Usage [message #649020] Thu, 10 March 2016 00:52 Go to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
Dear all,

is this tablespace usage query useful?



col "Tablespace" for a22
col "Used MB" for 9,999,999.99
col "Free MB" for 9,999,999.99
col "Total MB" for 9,999,999.99
col free_space for 9,999,999.99
col "Pct. Free" for 999,999.99
col "maxspace" for 999,999.99


select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
nvl(fs.free_space,0) free_space
, round(maxspace,2) maxspace
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
,
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";



I found there'a lot of script out there calculating tablespace usage, some of them is incorrect. My script is mainly based on https://community.oracle.com/thread/507636?tstart=0 after testing. If the script above is incorrect please correct me.

The partial results is as follow:

Tablespace                   Used MB       Free MB      Total MB   Pct. Free    FREE_SPACE    MAXSPACE          
---------------------- ------------- ------------- ------------- ----------- ------------- -----------          
PERFSTAT                    1,202.00         61.00      1,263.00        4.83         60.19   32,767.98          
SYSAUX                        364.00         20.00        384.00        5.21         18.31      400.00          
SYSTEM                        412.00        213.00        625.00       34.08        211.06         .00   
......


If u look at the above result, one thing that come to mind is whether there's any required remedial action to be taken. Just because a tablesapce has 4.83 percentage of free doesn't means that remedial actions need to be taken. Further investigation is needed

so the first thing the come to my mind when percentage free space is less than 25%, is if the tablespace is extendable or not.

if it is not extendable,

then I need to make it extendable if there's still space in the current location
or I add another file.

So is my query correct and useful?
Re: Useful Tablespace Usage [message #649021 is a reply to message #649020] Thu, 10 March 2016 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't know what to do with the result of a query then it is useless.
The question is "why did you write your query?"

Re: Useful Tablespace Usage [message #649023 is a reply to message #649021] Thu, 10 March 2016 01:23 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
why did you write your query
=>
previously we have a query to detect tablespace problems, unfortunately it is flawed,



SELECT a.tablespace_name, 
       a.file_name, 
       a.bytes/1024/1024 allocated_mbytes,
       b.free_bytes/1024/1024 free_mbytes,
       b.free_bytes/a.bytes*100 free_pct,
       a.maxbytes/1024/1024 max,
       (a.bytes-b.free_bytes)/a.maxbytes*100 tot_use,
       case
       when (a.bytes-b.free_bytes)/a.maxbytes*100 < 75 then 0
       when (a.bytes-b.free_bytes)/a.maxbytes*100 < 90 then 1
       else 2
       end value
FROM dba_data_files a,
     (SELECT file_id, 
             SUM(bytes) free_bytes 
      FROM dba_free_space b 
      GROUP BY file_id) b 
WHERE a.file_id=b.file_id 
AND a.maxbytes > 0
ORDER BY tot_use desc



however when I check the alert file this problems occurs

ORA-1688: unable to extend table SYS.WRH$_SEG_STAT partition WRH$_SEG_ST_3684067556_3646 by 8 in                 tablespace SYSAUX 


so obviously the original query is wrong,

the problem is when you join dba_data_files and dba_free_space, dba_free_space will not return you any row if there's really no more free extents left in the file. It will not even be 0 bytes, there will not be any entry if there's no more free extents left in the file. Also dba_data_files.maxbytes will be zero if it is not extendable.

I also want to change the original free_space column to extendable_free_space to clarify that this is the amount of free space that is extendable. Not the real free space.


col "Tablespace" for a22
col "Used MB" for 9,999,999.99
col "Free MB" for 9,999,999.99
col "Total MB" for 9,999,999.99
col extendable_free_space for 9,999,999.99
col "Pct. Free" for 999,999.99
col "maxspace" for 999,999.99
set linesize 112

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace),2)
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name;

select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
nvl(fs.free_space,0) extendable_free_space
, round(maxspace,2) maxspace
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
,
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";



partial results

Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  
---------------------- ------------- ------------- ------------- ----------- --------------------- -----------  
PERFSTAT                    1,202.00         61.00      1,263.00        4.83                 60.19   32,767.98  
SYSAUX                        365.00         20.00        385.00        5.19                 19.25      400.00
SYSTEM                        412.00        213.00        625.00       34.08                211.06         .00  


thanks a lot

Re: Useful Tablespace Usage [message #649024 is a reply to message #649023] Thu, 10 March 2016 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
why did you write your query
=>
previously we have a query to detect tablespace problems, unfortunately it is flawed,


So the question is: "why did you write your previous query?".
Mean: what are you trying to get? to detect/solve which problem?

Re: Useful Tablespace Usage [message #649027 is a reply to message #649024] Thu, 10 March 2016 01:55 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
the flawed query is not written by me.

Mean: what are you trying to get? to detect/solve which problem?

yes to detect the problem early and to solve tablespace run out of space problems before ORA- error pops up.

so is my query correct and useful?

thanks a lot!
Re: Useful Tablespace Usage [message #649028 is a reply to message #649027] Thu, 10 March 2016 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does it give you a result that allows you to diagnose if there is a problem or not?

Note: I can't read not formatted query, so you have to answer many (basic) questions.

Re: Useful Tablespace Usage [message #649029 is a reply to message #649028] Thu, 10 March 2016 02:04 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
Yes it does give me the a clearer view of what is wrong.

how can I format the query to make u able to read the query?

I think useful is subjective, how about if the query is correct?

thanks a lot!
Re: Useful Tablespace Usage [message #649031 is a reply to message #649029] Thu, 10 March 2016 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use SQL Formatter.

A query is (functionally) correct if it provides a result that allows you to achieve your goal.

Re: Useful Tablespace Usage [message #649032 is a reply to message #649031] Thu, 10 March 2016 02:45 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
so after I format the query using http://www.dpriver.com/pp/sqlformat.htm

which output should I transfer? the normal output or html code?

how should I place html code?

thanks a lot!
Re: Useful Tablespace Usage [message #649035 is a reply to message #649032] Thu, 10 March 2016 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The normal/text output, using copy and paste and putting it here between code tags.

Re: Useful Tablespace Usage [message #649040 is a reply to message #649035] Thu, 10 March 2016 08:19 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member

SELECT df.tablespace_name                            "Tablespace", 
       Nvl(totalusedspace, 0)                        "Used MB", 
       ( df.totalspace - Nvl(tu.totalusedspace, 0) ) "Free MB", 
       df.totalspace                                 "Total MB", 
       Round(100 * ( ( df.totalspace - Nvl(tu.totalusedspace, 0) ) / 
       df.totalspace ), 2)                           "Pct. Free", 
       Nvl(fs.free_space, 0)                         extendable_free_space, 
       Round(maxspace, 2)                            maxspace 
FROM   (SELECT tablespace_name, 
               Round(SUM(bytes) / 1048576) TotalSpace, 
               SUM(maxbytes) / 1024 / 1024 maxspace 
        FROM   dba_data_files 
        GROUP  BY tablespace_name) df, 
       (SELECT Round(SUM(bytes) / ( 1024 * 1024 )) totalusedspace, 
               tablespace_name 
        FROM   dba_segments 
        GROUP  BY tablespace_name) tu, 
       (SELECT tablespace_name, 
               Round(SUM(bytes) / 1024 / 1024, 2) AS free_space 
        FROM   dba_free_space 
        GROUP  BY tablespace_name) fs 
WHERE  df.tablespace_name = tu.tablespace_name(+) 
       AND df.tablespace_name = fs.tablespace_name(+) 
ORDER  BY "pct. free" 



Also another question I want to ask how do you calculate free space in a tablespace, should I use the total allocated space - amount that segment used or used the free space from dba_extents.
i.e.


 ( df.totalspace - Nvl(tu.totalusedspace, 0) ) "Free MB", 



Nvl(fs.free_space, 0)                         extendable_free_space



What is the difference?

partial results as follow


Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  
---------------------- ------------- ------------- ------------- ----------- --------------------- -----------  
PERFSTAT                    1,202.00         61.00      1,263.00        4.83                 60.19   32,767.98  
SYSAUX                        365.00         20.00        385.00        5.19                 19.25      400.00  
SYSTEM                        412.00        213.00        625.00       34.08                211.06         .00  


EXTENDABLE_FREE_SPACE seems to be smaller than free MD.

thanks a lot!
Re: Useful Tablespace Usage [message #649041 is a reply to message #649040] Thu, 10 March 2016 08:27 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
Explain in detail how AUTOEXTEND affect each value reported.
Re: Useful Tablespace Usage [message #649053 is a reply to message #649041] Fri, 11 March 2016 00:20 Go to previous message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
I did a similar test in a post mentioned in http://gavinsoorma.com/2011/11/oem-tablespace-space-used-alerts-and-the-autoextend-effect/

so I'm going to do the following

first create non extendable tablespace
monitor tablespace usage
create table
monitor tablespace usage
insert table with a lot of numbers
monitor tablespace usage
auto extend unlimited
monitor tablespace usage
maxsize 500m
monitor tablespace usage

monitor tablespace usage is using the following query



SELECT df.tablespace_name                            "Tablespace", 
       Nvl(totalusedspace, 0)                        "Used MB", 
       ( df.totalspace - Nvl(tu.totalusedspace, 0) ) "Free MB", 
       df.totalspace                                 "Total MB", 
       Round(100 * ( ( df.totalspace - Nvl(tu.totalusedspace, 0) ) / 
       df.totalspace ), 2)                           "Pct. Free", 
       Nvl(fs.free_space, 0)                         extendable_free_space, 
       Round(maxspace, 2)                            maxspace 
FROM   (SELECT tablespace_name, 
               Round(SUM(bytes) / 1048576) TotalSpace, 
               SUM(maxbytes) / 1024 / 1024 maxspace 
        FROM   dba_data_files 
        GROUP  BY tablespace_name) df, 
       (SELECT Round(SUM(bytes) / ( 1024 * 1024 )) totalusedspace, 
               tablespace_name 
        FROM   dba_segments 
        GROUP  BY tablespace_name) tu, 
       (SELECT tablespace_name, 
               Round(SUM(bytes) / 1024 / 1024, 2) AS free_space 
        FROM   dba_free_space 
        GROUP  BY tablespace_name) fs 
WHERE  df.tablespace_name = tu.tablespace_name(+) 
       AND df.tablespace_name = fs.tablespace_name(+) 
ORDER  BY "pct. free" 




creating a non extendable tablespace and monitor it's usage
CREATE TABLESPACE TESTME DATAFILE '/u02/app/oracle2/oradata/ORCL/testme_01.dbf' SIZE 5M;



tablespace usage after creating tablespace

Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  
TESTME                           .00          5.00          5.00      100.00                  4.00         .00


=>extendable_free_space is not 5 Mhz, i.e. the allocated disk space but it is only 4M=>it probably take into account all the overhead and metadata that is used for creating the tablespace. extendable_Free_space is derived from dba_free_space.

creating a table


SYS@ORCL>conn OLAF/OLAF@ORCL
Connected.
OLAF@ORCL>
OLAF@ORCL>CREATE TABLE TESTME (
  2    id NUMBER,
  3    description VARCHAR2(1000),
  4    CONSTRAINT TESTME_pk PRIMARY KEY (id)
  5  ) tablespace TESTME;

Table created.




tablespace usage after creating table

Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  
TESTME                           .00          5.00          5.00      100.00                  3.88         .00  


=>since there's no change in Free MB after creating table, i guess dba_segments do not include usage for the metadata of the objects created. while the dba_Free_space.bytes do take this into consideration


populating table until the tablespace cannot extend

OLAF@ORCL>DECLARE
  2  count NUMBER;
  3  BEGIN
  4  	     FOR count in 1..1000000 LOOP
  5  		     NULL;
  6  		     INSERT INTO TESTME (id,description) VALUES (count, 'Description ' || count);
  7  			     COMMIT;
  8  	     END LOOP;
  9  
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-01653: unable to extend table OLAF.TESTME by 128 in tablespace TESTME 
ORA-06512: at line 6


tablespace usage after populating table until the tablespace cannot extend.


Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  
TESTME                          4.00          1.00          5.00       20.00                   .00         .00


Now there's really no more EXTENDABLE_FREE_SPACE as calculated from dba_free_space.bytes while the FREE MB derived from dba_Segments still mentioned that it have 1 Free MB left.

changing data file to autoextend unlimited

SYS@ORCL>ALTER DATABASE DATAFILE '/u02/app/oracle2/oradata/ORCL/testme_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Database altered.




tablespace usage after changing datafile to autoxtend unlimited

Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  

TESTME                          4.00          1.00          5.00       20.00                   .00   32,767.98




=>only MAXSPACE change to 32,767.98

changing datafile to autoextend up to 500M

SYS@ORCL>ALTER DATABASE DATAFILE '/u02/app/oracle2/oradata/ORCL/testme_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 500M;

Database altered.

SYS@ORCL>
SYS@ORCL>SET ECHO OFF


tablespace usage after changing datafile to autoextend up to 500M


Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  
TESTME                          4.00          1.00          5.00       20.00                   .00      500.00  
==================================================================================================================================



=>only MAXSPACE change to 500

so can I conclude that

dba_Segments does not include usage for metadata (for creating objects)?
we should not include any tablespace calcuation from dba_segments since it never take into account metadata that is used for creating objects.
given a nonextendable tablespace that has reach it allocated maximum limit and without further actions on the objects that are in the tablespace, the only thing that will change is maxspace, which is derived from dba_data_files.maxbytes.


now I continue to extend that tablepace from where I got stopped
OLAF@ORCL>SELECT max(id) FROM TESTME;

   MAX(ID)                                                                      
----------                                                                      
     63955                                                                      

OLAF@ORCL>
OLAF@ORCL>DECLARE
  2  count NUMBER;
  3  BEGIN
  4  	     FOR count in 63956..1000000 LOOP
  5  		     NULL;
  6  		     INSERT INTO TESTME (id,description) VALUES (count, 'Description ' || count);
  7  			     COMMIT;
  8  	     END LOOP;
  9  
 10  END;
 11  /

PL/SQL procedure successfully completed.






tablespace usage after populating tablespace with maxsize 500m


Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE
TESTME                         47.00         58.00        105.00       55.24                 57.00      500.00




=>practically every columns change mostly importantly maxspace has change to 500M from 0 since it is now extendable to 500M


SYS@ORCL>ALTER DATABASE DATAFILE '/u02/app/oracle2/oradata/ORCL/testme_01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Database altered.

Tablespace usage after changing tablespace to extend unlimited.


Tablespace                   Used MB       Free MB      Total MB   Pct. Free EXTENDABLE_FREE_SPACE    MAXSPACE  

TESTME                         47.00         58.00        105.00       55.24                 57.00   32,767.98  





=>down here only maxspace changes

thanks!

[Updated on: Fri, 11 March 2016 01:28]

Report message to a moderator

Previous Topic: Running a query from 20 sessions concurrently
Next Topic: Gathering Table Stats
Goto Forum:
  


Current Time: Fri Oct 19 02:05:21 CDT 2018