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

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