SEO

Dave Thacker wrote:
> We've recently migrated from 7.31UC5 to 9.30UC5. Our platform is AIX 4.3.3.
> The hardware is an S7A with twelve CPU's. KAIO is enabled. The machine runs
> both the database and 4gl, c, and perl applications that connect to the
> database. There are also many remote applications that connect to the
> database via tcp/ip. The application is heavily OLTP, there is little DSS
> work running on the box. After the upgrade, we've experienced a 50% increase
> in the response time to one of our key applications. We'd been running 7.3x
> for a long time and had our tuning down pretty well, we kept database
> structure and applications static while the upgrade took place, so my feeling
> is that I'm just not tuned properly. I'll throw this before the group with as
> many onstat commands as I can remember being requested on previous cases.
> Please take a look and see if you can see any fatal flaws.
>
> onstat -p
>

snip for brevity ....

>
> If there is any other information I can provide, please let me know.
>
> TIA
>
> Dave Thacker


Dave - on any 32 bit AIX system, make sure SHMVIRTSIZE is an integral
multiple of 256 MBytes (or slightly less). Set SHMADD to 256 MB (or
slightly less). This will fully utilize the small number of available
256MByte AIX shared memory segments. This likely won't solve your
immediate problem, though someday it will save you a call in the middle
of the night.

9.40 is the first release advertized as faster then 7.x. I'd expect
9.30 to be broadly slightly slower than 7.31.xxx. If I was within say
5-10% I'd call it close enough.

23 million sequential scans seems large. I'd get with the app developers
& try to home in on the SQL that is sluggish, capture it, EXPLAIN it &
tune it. The optimizer is no doubt different & you may have been bit by
changes. I'll bet there are a couple of SQL's that are scanning you to
death, particularly with such a high buffer hit ratio - they're just
camped out in memory, thrashing away, driving up the hit ratio and
stretching out response times.

Check below the DBMS - see if a physical disk is a bottleneck - an
optimizer change may be pounding on a disk where 7.3x didn't. You'll
probably find one or two SQL's changed for the worse & are the ones
eating you alive. The good news is it usually doesn't take long to fix
a couple of bad SQL's....

Are you getting any funky messages in the log file? If so, please post
- there may be great clues in there.

Is the 50% slowdown consistent? If not, the culprit is probably
variable competition for resources. If it is consistent, its more
likely that app itself.

I'd also set LRU's to 127 & Cleaners to 127. No point in being stingy.
RA_PAGES & RA_THRESHOLD could be increased, you're currently using
proctically everything you read in ahead. Say 64 & 48.

Here's some SQL I have used at different times to "fish" for Bad
(expensive) SQL (which is most likely the problem):

database sysmaster;
select s.username,
s.pid unix_pid,
s.hostname,
s.tty,
q.sqx_estcost,
q.sqx_estrows,
q.sqx_sqlstatement
from syssessions s,
syssqexplain q
where s.sid = q.sqx_sessionid
and q.sqx_estcost > 500
order by q.sqx_estcost desc;


select avg(sqx_estcost), count(*) num, sqx_statement
from syssqexplain
group by 3
order by 1 desc;

select * from syssqexplain
where sqx_seqscan = 1 or
sqx_tempfile = 1 or
sqx_autoindex = 1 or
sqx_srtscan = 1
order by sqx_estcost desc;

Grab any offensive SQL & explain it - tune accordingly.

Good Luck.... Greg

Greg <reply@publicly.com> wrote in message

Greg's other recommendations snipped, we'll try most of them

I've got some questions about the SQL statements you gave me.
>
> Here's some SQL I have used at different times to "fish" for Bad
> (expensive) SQL (which is most likely the problem):
>
> database sysmaster;
> select s.username,
> s.pid unix_pid,
> s.hostname,
> s.tty,
> q.sqx_estcost,
> q.sqx_estrows,
> q.sqx_sqlstatement
> from syssessions s,
> syssqexplain q
> where s.sid = q.sqx_sessionid
> and q.sqx_estcost > 500
> order by q.sqx_estcost desc;

This one seems pretty straightforward, find all the queries that have
a cost over 500. I've already identified 2 culprits.

>
>
> select avg(sqx_estcost), count(*) num, sqx_sqlstatement
> from syssqexplain
> group by 3
> order by 1 desc;

I'm not sure what we're focusing on in this query. High cost queries
that execute many times? (hence the average?) I get a lot of results
on this one but they all have a count of 1.

>
> select * from syssqexplain
> where sqx_seqscan = 1 or
> sqx_tempfile = 1 or
> sqx_autoindex = 1 or
> sqx_srtscan = 1
> order by sqx_estcost desc;

Could you verify why we are querying on these values?

sqx_seqscan=1 the query used a sequential scan
sqx_tempfile=1 the query generated a temp table? (and we didn't
tell it to)
sqx_autoindex=1 the query built it's own temporary index?
sql_srtscan=1 no idea. What's the problem with this value?

>
> Grab any offensive SQL & explain it - tune accordingly.
>
> Good Luck.... Greg

Thanks for your earlier feedback. I'll post a followup after we've
changed some of the values you mentioned.

Dave Thacker