How can Permissions affect Performance ?

[Paul Reddin]

Hi,

We have noticed some of our complex queries run much faster when
connected as
the instance owner, compared to a userA who has explcitly granted
privileges on all the required Tables & SQL Table Functions.

e.g 1 query runs in 10ms for the Instance Owner and 1.9s for the
user i.e greater than 100x faster!

If we add the instance owners (Linux) Primary group to userA's list of
(linux)groups, the query then runs in 10ms for him too.

Can anybody explain this as expected/reasonable behaviour ?

NB. We have run the query multiple times to eliminate compilation time
as a factor etc....

I have never seen query performance varying like this when using
different users/privileges.

Many thanks.

Paul.

[Serge Rielau]

Paul,

I take a wild guess here. If permissions need to be checked through a
group, DB2 needs to call out to the OS to establish group membership.
If that callout takes a while (domains?) then it explains for the
difference.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Sean McKeough]

Do you have any MQT's that only have access for the instance owner?

Paul Reddin wrote:

Hi,

We have noticed some of our complex queries run much faster when
connected as
the instance owner, compared to a userA who has explcitly granted
privileges on all the required Tables & SQL Table Functions.

e.g 1 query runs in 10ms for the Instance Owner and 1.9s for the
user i.e greater than 100x faster!

If we add the instance owners (Linux) Primary group to userA's list of
(linux)groups, the query then runs in 10ms for him too.

Can anybody explain this as expected/reasonable behaviour ?

NB. We have run the query multiple times to eliminate compilation time
as a factor etc....

I have never seen query performance varying like this when using
different users/privileges.

Many thanks.

Paul.

[Matt Emmerton]

"Paul Reddin" <pa**@abacus.co.uk> wrote in message
news:1f**************************@posting.google.c om...

Hi,

We have noticed some of our complex queries run much faster when
connected as
the instance owner, compared to a userA who has explcitly granted
privileges on all the required Tables & SQL Table Functions.

e.g 1 query runs in 10ms for the Instance Owner and 1.9s for the
user i.e greater than 100x faster!

If we add the instance owners (Linux) Primary group to userA's list of
(linux)groups, the query then runs in 10ms for him too.

Can anybody explain this as expected/reasonable behaviour ?

NB. We have run the query multiple times to eliminate compilation time
as a factor etc....

I have never seen query performance varying like this when using
different users/privileges.

Interesting problem.

The two experiments I'd try are:
1) Adding the instance owner's group to userA's group list [ you've already
done this, seems to fix the degradation ]
2) Changing SYSADM_GROUP (dbm parameter) to be userA's group and see if
queries run quickly.

If 2) works just as well as 1), then there's definitely a problem with DB2,
and a call to DB2 Support would be warranted.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

[Mark A]

"Paul Reddin" <pa**@abacus.co.uk> wrote in message
news:1f**************************@posting.google.c om...

Hi,

We have noticed some of our complex queries run much faster when
connected as
the instance owner, compared to a userA who has explcitly granted
privileges on all the required Tables & SQL Table Functions.

e.g 1 query runs in 10ms for the Instance Owner and 1.9s for the
user i.e greater than 100x faster!

If we add the instance owners (Linux) Primary group to userA's list of
(linux)groups, the query then runs in 10ms for him too.

Can anybody explain this as expected/reasonable behaviour ?

NB. We have run the query multiple times to eliminate compilation time
as a factor etc....

I have never seen query performance varying like this when using
different users/privileges.

Many thanks.

Paul.

When I first installed RH9, I noticed that the logon to Linux for non-root
users took a lot longer than root. This was true for the GUI desktop and
remote telnet sessions. The delay was several seconds for non-root users.

Since that time I have reinstalled RH9 and applied all the latest updates
and the problem seems to have gone away. I don't know if this has anything
to do with your DB2 problem on Linux, but that is the only thing I can think
of.

[Paul Reddin]

Thanks for the ideas.

- we don't use group permissions, so I guess the OS call isn't relevant.
- we don't use MQT's

I'll raise a PMR on this if/when fp6 doesn't fix it.

Thanks

Paul.