Highlighted
Absent Member.. Absent Member..
Absent Member..
941 views

Monitoring multiple tablespaces

Jump to solution

Hi Experts,

 

I would like to monitor tablespace %free space for all tablespaces on a specific instance.

I created a new "Database Query Monitor"  with the below query:

select df.tablespace_name "tb_name", totalusedspace "used" ,round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))  "pct" 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

 

Now I would like to alert if any table space %usage is  90% or above.

I tried configuring Match content and column labels but I cannot find it on "Threshold Settings" even though I have matched it.

See results below:

"

Status Summary: matched: SYSAUX, 658, 34
ATERNITY_IDX, 79, 85
ATERNITY_DATA, 573, 43
ATERNITY_HD_IDX, 1657, 53
UNDOTBS, 350, 30
SYSTEM, 490, 51
ATERNITY_HD, 2589, 49
TOOLS, 154, 56
LOBLARGE, 2.89 sec, 9 rows, SYSAUX, 658, 34
ATERNITY_IDX, 79, 85
ATERNITY_DATA, 573, 43
ATERNITY_HD_IDX, 1657, 53
UNDOTBS, 350, 30
SYSTEM, 490, 51
ATERNITY_HD, 2589, 49
TOOLS, 154, 56
LOBLARGE, 4, 99

"

 

Please advise what else should I check? Sitescope version is 11.21 (HP SiteScope 11.21 64-bit JVM, Build SS1121130301)

 

Thanks,

Tomer.

Labels (1)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Hello

 

I create an example, with the same query, with a Database Counter Monitor. Please take a look into the doc file attached in this post. (Monitoring Multiple tablespaces.docx)

 

Using your query, I detect an interesting fact. The % you are getting with this query, is the % free, not used

 

Please take a look into the other document attach in this post. (Monitoring Multiple TableSpaces_Results.docx)

"HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution."

View solution in original post

8 Replies
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

While I can't verify/test your query right now - I would simply

*Sort it by pct descending

*Match on result column 3(which should then be the first line, 3rd column) >= 90

 

That means the monitor should always hit aslong as there is one table >=90, it just won't show you how many there are.

 

I've done a similar thing here:

SELECT sdb.Name AS DatabaseName, COALESCE(DATEDIFF(hour,MAX(bus.backup_finish_date),getdate()),'9999') AS LastBackUpTime  FROM sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name  WHERE sdb.Name Not in ('tempdb') GROUP BY sdb.Name  HAVING COALESCE(DATEDIFF(hour,MAX(bus.backup_finish_date),getdate()),'9999') > 24 ORDER by LastBackUpTime desc

 

Where I get myself backuptimes and return a table if any of them is > 24 hours.

See screenshot attached for Thresholds

(The rows line is amiss there, I'm aware of that 🙂

 

hope that helps

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Hi Tomer,

 

if the result of your query can be adjust it to be display it in two columns, where the first one is the table space and the second column is for the value you are trying to monitor, you can use Database Counter monitor. 

 

Here is the monitor description about this monitor from SiteScope Monitor referecence located unedr SiteScope\sisdocs\pdfs

 

 

"Use the Database Counter monitor to make SQL queries for performance metrics from any JDBCaccessible
database. This monitor provides optional support for calculating deltas and rates for
metrics between monitor runs. You can monitor multiple counters with a single monitor instance.
This enables you to watch server loading for performance, availability, and capacity planning. The
error and warning thresholds for the monitor can be set on one or more database server performance
statistics"

David Molina
HP Support

If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Hello

 

I create an example, with the same query, with a Database Counter Monitor. Please take a look into the doc file attached in this post. (Monitoring Multiple tablespaces.docx)

 

Using your query, I detect an interesting fact. The % you are getting with this query, is the % free, not used

 

Please take a look into the other document attach in this post. (Monitoring Multiple TableSpaces_Results.docx)

"HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution."

View solution in original post

Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..
Have to say, this version(with database counter) is really neat Battushi.
But there is no way to dynamically add a threshold, or set a default threshold should a new counter come in, or is there? (or atleast get a notification).
Highlighted
Absent Member.. Absent Member..
Absent Member..

Thanks a lot for the help !

I used  Battushi and DavidMolina solution.

 

bkserver, are you sure that your solution will create an error for each table and not only the first one ?

 

 

 

 

 

 

0 Likes
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..
I'll throw in a screenshot how my result looks like tomorrow from work.

My solution alarms if any table returned has a value higher than the threshold, it still returns all hits of course.
Since any violation of the threshold is bad I'm not picky on the individual returns lines, they are all bad after all 🙂

In your case, you could probably rewrite your query to(approx)
HAVING round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) > 90
then your query would only return tables that are actually over the threshold instead of all tables.

Your result will then be either
0 rows - which means that all your tables are fine or
1+ rows - which means that some of your tables are indeed near their max

I assume you're using Oracle, so I'll grab one of ours tomorrow and see.

regards
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

There you go.

As you can see it returns all lines in question(whereas the example does not have any different backup times sadly).

So from the GUI you can always see all of them

 

hope that helps

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Thanks a lot!

 

So I guess both soltutions can work !

Sitescope will not show labels but instead "results columns" to be used.

 

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.