Home » RDBMS Server » Server Administration » Useful Tablespace Usage (11.2.0.1)
| Useful Tablespace Usage [message #649020] |
Thu, 10 March 2016 00:52  |
 |
juniordbanewbie
Messages: 250
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 #649023 is a reply to message #649021] |
Thu, 10 March 2016 01:23  |
|
juniordbanewbie
Messages: 250
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 #649040 is a reply to message #649035] |
Thu, 10 March 2016 08:19 |
|
juniordbanewbie
Messages: 250
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 #649053 is a reply to message #649041] |
Fri, 11 March 2016 00:20 |
|
juniordbanewbie
Messages: 250
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 |
|
|
|
Goto Forum:
Current Time: Thu Nov 28 18:48:48 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
