Simple Oracle Queries

[Tony Williams]

We are using the HP OV SPI for Databases and we recently found that a child process of the SPI with a username of daemon consumes 100% of a Superdome CPU. In working with HP it turns out that it is 2 metrics that are causing the problems. The SPI is working OK its the size of the database that is the problem. Is there a few simple queries that a non-DBA (Me) can execute that will tell me the size of the database? How many tables are in the database? The size of the tablespaces? How many segements are in each tablespace?

Thanks

[Steven E. Protter · Accepted solution]

This code will help with usage of the the allocated tablespaces.

ttitle left ' Free Space By Tablespace' skip 1
prompt
set wrap off
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column "Tablespace Name" format a16
column bytes format 9,999,999,999,999 heading "Bytes"
column used format 99,999,999,999 heading "Used"
column free format 999,999,999,999 heading "Free"
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report
select a.tablespace_name "Tablespace Name",
b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
clear break
clear compute
clear column

That sql code is oracle only, tested oin 8.1.7.4.0

You should be ablt to cut and paste it.

With regards to the 100% CPU use on a Superdome. That's quit an achievment. Do you have all the latest Gold Packs/OS Patches recommended installed for roacle.

SEP

[Tony Williams]

Thanks for the code. Recent June 2003 Gold patches not the latest. Metric 3, the # of tablespaces with free low space, and metric 16, the # of segments that cannot extend are the culprits. According to HP the size of our databae (1.5 TB) and the number of tablespaces and objects cause these two metrics to hammer on the database without mercy. The SPI is supposed to run for 5 minutes and actually runs for 25 minutes with 100% CPU. We didn't see this problem on smaller databases that the SPI is deployed on.

[Steven E. Protter]

I'm attaching some performance data collection scripts.

They run background and will at least show you where the bottleneck is.

It might be the process is waiting on resources, not sure without more data.

You also might want to fire up glance plus and drill into the process and see whats going on there.

Good Luck,

SEP

[steven Burgess_2]

if I had a Â

[Yogeeraj_1]

hi,

if you want to do monitoring, statspack will be the best way to go!

You should be using statspack on a constant basis.

Every morning, you should take a snapshot, every afternoon another, every evening, yet another.

Now you have a history. You can compare a statspack from today (bad performance) with last weeks at the same time (good performance) and look for major differences.

Also, people must "quantify" things. Eg: Screen 1 typically takes less then 1 second, today it is taking 60 seconds. -- Ah ha, maybe we lost an index on some of the tables surrounding screen 1, lets look at that. Are there specific components "going slow" or is the entire thing going slow.

Statspack will help you identify the top sql, the big wait events, contention points, bad performance metric.

hope this helps too!

regards
Yogeeraj