Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Simple Oracle Queries

SOLVED
Go to solution
Tony Williams
Regular Advisor

Simple Oracle Queries

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
5 REPLIES
Steven E. Protter
Exalted Contributor
Solution

Re: Simple Oracle Queries

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Tony Williams
Regular Advisor

Re: Simple Oracle Queries

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
Exalted Contributor

Re: Simple Oracle Queries

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 E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
steven Burgess_2
Honored Contributor

Re: Simple Oracle Queries

if I had a Â
take your time and think things through
Yogeeraj_1
Honored Contributor

Re: Simple Oracle Queries

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)