Operating System - HP-UX
1748156 Members
3862 Online
108758 Solutions
New Discussion юеВ

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

 
SOLVED
Go to solution
Pedro Lucas
Advisor

HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Hello,

I have a server N4000 with 4 CPU's and 16Gb RAM, patched. Normally the server works fine, but sometimes the efficiency of oracle DB down. In this case, I see the uptime of server that is normally (more or less 2), but when I execute a sar -m I see that de sema/s is about 1500, when is a normal situation is about 200.

I have clients who work locally and other that work via listener. How can I know which sentences are affecting the efficiency of the DB? If I list the process (ps -ef) I only see the processes but no the sentences. I need to know what sentence (select, insert, delete, ...) is affecting the server.

Thank you in advance.
15 REPLIES 15
Steven E. Protter
Exalted Contributor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Shalom,

Most of the time this comes down to two things:
1) Bad application code from developers (Have the dba review all new sql for efficiency)
2) Database bugs (good luck with this one your db is so way far out of support as is your OS)

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
Pedro Lucas
Advisor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Just. I think that developer's code is affecting the system. Then I want to know which process is making a lot of semop() calls. With these PID, I can know the sentence.

It's possible to know which process is making theses semop() calls?

Thanks.
Dennis Handly
Acclaimed Contributor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

>which process is making theses semop() calls?

Glance would show you the counts.
tusc would also show it but would be harder.
Pedro Lucas
Advisor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Really? Which option? It's possible to see a list of processes and it's semop system calls?

Thank you very much.
Eric Antunes
Honored Contributor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Hi Roma,

"Normally the server works fine, but sometimes the efficiency of oracle DB down." Humm, it sounds familiar to me...

Can you post the following (attach an excel or post the first 5 entries) results:

select *
from v$system_event
where event not like 'rdbms%'
and event not like 'SQL%'
and event not like '%time%'
and event not like '%pipe get%'
and event not like '%dle%'
and event not like 'queue%'
and event not like 'jobq%'
order by time_waited desc

Best Regards,

Eric
Each and every day is a good day to learn.
Pedro Lucas
Advisor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Hi Eric, thank you for your response.

Here is the XLS file with result of the select.

We stop every night the DB to make the backup, so I think that the select should be launched after the DB has the problem and before the shutdown.Isn't it?
Eric Antunes
Honored Contributor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Hi Roma,

Yes, after the DB has the problem and before the shutdown it's the right time.

From the excel you attached, you may be facing some latch contention. What kind of application are you runing?

Can you post an excel for the following:

select pc.name stat_name, pc.value*100/decode(ec.value,0,1,ec.value) "PARSE COUNT (HARD) RATIO < 3%"
from v$sysstat ec, v$sysstat pc
where ec.name='execute count'
and pc.name like '%parse count%'

Best Regards,

Eric
Each and every day is a good day to learn.
Pedro Lucas
Advisor

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Hi Eric, thousand thanks (like we say in Spain).

The result of the select is:

STAT_NAME PARSE COUNT (HARD) RATIO < 3%
parse count 24,0558122357901

The DB is used from application servers with JBOSS and from a local application developed with Forms, aproximately 500 users locally and 1500 via listener.

A lot of thanks.
Eric Antunes
Honored Contributor
Solution

Re: HP-UX 11.00 with Oracle 7.3.4 - High sema/s from sar -m

Hi Roma,

Sorry for the delay but it was holiday here in Portugal. ;-)

As I said before, your application(s) may have a lot of literal SQLs (SQL that don't use bind variables):

select count(*), sum( q.executions), q.module, q.sql_text
from v$sql q
where q.executions between 0 and 1
group by q.module, q.sql_text
order by 1 desc

Those in the top are the problematic ones.

If you have Metalink access, check also Note 16347.1 that is specific for Oracle 7.

Best Regards,

Eric Antunes

Each and every day is a good day to learn.