Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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.
Eric Antunes
Honored Contributor

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

Hi Roma,

Just as a curiousity, what value do you get from the following sql:

select round( one_exec / more_one_exec, 2) "Bad/Good SQL Ratio"
from (select count(*) one_exec from v$sql where executions <= 1) bad_sqls
, (select count(*) more_one_exec from v$sql where executions > 1) good_sqls

I get [0.21;0.22]

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, the holidays are sacred.

So, if I understand you, in the select:

select count(*), sum( q.executions), q.module, q.sql_text
from v$sql q ...

I can see at the top the problematic querys, isn't it? Like this:

COUNT/SUM/MODULE/SQL TEXT
11 11 - SELECT cg1001||' '||cg2001||','||...
6 6 - SELECTnom001,cg1001,cg2001,dni001,...

In the second question I get the value 0,36, but only one value. What's mind this value?

Thank you.
TwoProc
Honored Contributor

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

Well, about that latch contention. I recognize your expertise in this area Eric, but I just want to add that I've seen latch contention go through the roof with just plain ol' bad code as well. It takes a lot of latches to run badly tuned code, a lot more latches than those needed to facilitate well-tuned code. So, in general I don't pay lots of attention to latch contention until I verify that running code is relatively tight. IMHO - You'll get more benefit from tuning two new pieces of junk than you do from fooling around freelists, freespace percentages to gain distribution across blocks, etc.
We are the people our parents warned us about --Jimmy Buffett
Eric Antunes
Honored Contributor

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

Hi Roma,

"I can see at the top the problematic querys, isn't it? Like this:

COUNT/SUM/MODULE/SQL TEXT
11 11 - SELECT cg1001||' '||cg2001||','||...
6 6 - SELECTnom001,cg1001,cg2001,dni001,..."

Exactly, those queries are being parsed each and every time they are executed, which consumes to much time and SGA: the purpose of the SGA is to SHARE SQL across the application.

Another and maybe more accurate way to check bad SQL's is the following query:

select substr( sql_text, 1, 40) "SQL", module "Module", count(*), sum (executions) "TotExecs"
from v$sqlarea
where executions < 3
group by substr( sql_text, 1, 40), module
having count(*) > 15

It's going to give you the SQL's that have the same first 40 characters but, for some reason, are parsed more than 15 times. Of course, not all of them are bad but some of them may be tuned.

For example, SQL's like this one:

select description
from system_items
where item_id = 123
and organization_id = 21

...should be changed by to this one:

select description
from system_items
where item_id = :w_item_id
and organization_id = :w_org_id

...so that it can be shared and excuted as needed. For example, to query the description of another item_id.

The purpose of the shared_pool is to maximize the sharing of SQL within the entire aplication (parse once and execute many).


"In the second question I get the value 0,36, but only one value. What's mind this value?"

It means that, for each 100 SQL's, you have 36 that have been executed only once.


Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Alzhy
Honored Contributor

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

Would very high semop() (sar -m sema/s) activity be indicative of something?:

HP-UX whs0001 B.11.11 U 9000/800 03/17/08

00:00:02 msg/s sema/s
...snipped..
05:50:00 1.06 91.83
05:55:00 1.07 224.09
06:00:00 1.07 4893.97
06:05:00 1.07 10383.00
06:10:00 1.07 9742.08
06:15:00 1.11 11279.94
06:20:00 1.09 11494.36
06:25:00 1.06 10490.05
06:30:00 1.07 10752.57
06:35:00 1.06 10388.84
06:40:00 1.06 11555.59
06:45:00 1.08 17057.53
06:50:00 1.08 16411.32
06:55:00 1.06 16708.09
07:00:01 1.07 18058.43
07:05:00 1.06 14262.85
07:10:00 1.06 11548.93
07:15:00 1.25 14025.12
07:20:00 1.06 17951.56
07:25:00 1.08 17510.05
07:30:01 1.06 16678.37
07:35:00 1.07 17297.22
07:40:01 1.07 17168.84
07:45:00 1.07 16704.87
07:50:01 1.06 16674.93
07:55:00 1.07 18221.17
08:00:00 1.07 19205.82
08:05:00 1.06 17085.05
08:10:00 1.06 18576.78
08:15:00 1.07 18373.55
08:20:00 1.08 16770.79
08:25:00 1.06 16520.42
08:30:00 1.06 19263.75
08:35:00 1.06 17286.54
08:40:00 1.06 18921.17
08:45:00 1.05 23206.16
08:50:00 1.07 23157.22
08:55:00 1.06 25037.89
09:00:00 1.06 21591.67
09:05:00 1.06 18972.01
09:10:00 1.06 19511.75
09:15:00 1.07 19251.45
09:20:01 1.08 19175.35
09:25:01 1.06 20274.44

Average 1.07 6204.48
Hakuna Matata.
Eric Antunes
Honored Contributor

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

Hi Nelson,

Maybe. What is your "PARSE COUNT (HARD) RATIO"?

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