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

[Pedro Lucas]

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.

[Steven E. Protter]

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

[Pedro Lucas]

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]

>which process is making theses semop() calls?

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

[Pedro Lucas]

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

Thank you very much.

[Eric Antunes]

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

[Pedro Lucas]

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]

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

[Pedro Lucas]

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 · Accepted solution]

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

[Eric Antunes]

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

[Pedro Lucas]

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]

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.

[Eric Antunes]

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

[Alzhy]

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

[Eric Antunes]

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%'