Description
Submitted by: @pavel-zotov
Attachments:
improve-DDL-of-rdb_user_privileges.7z
Every connect to current 4.0 databases leads to auto-run following query by system attachment:
with recursive role\_tree as \(
select rdb$relation\_name as nm, 0 as ur
from rdb$user\_privileges
where
rdb$privilege = 'M'
and rdb$field\_name = 'D'
and rdb$user = ? \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- is substituted by current user
and rdb$user\_type = 8
UNION ALL
select rdb$role\_name as nm, 1 as ur
from rdb$roles
where rdb$role\_name = ? \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- is substituted by current role
UNION ALL
select p\.rdb$relation\_name as nm, t\.ur
from rdb$user\_privileges p
join role\_tree t on t\.nm = p\.rdb$user
where
p\.rdb$privilege = 'M'
and \(p\.rdb$field\_name = 'D' or t\.ur = 1\)
\)
select r\.rdb$role\_name, r\.rdb$system\_privileges
from role\_tree t
join rdb$roles r on t\.nm = r\.rdb$role\_name;
===
For SYSDBA with role = NONE on empty DB this query requires ~615 indexed reads of RDB$USER_PRIVILEGES table.
The more objects in DB (users/roles, tables, etc) - the more IRs will be done. This can lead to perormance problems when lot of users make connect / disconnect frequently.
This is because table RDB$USER_PRIVILEGES has _no_ index with key: (rdb$user,rdb$privilege,rdb$field_name)
But it HAS index with starting part of this expr: (rdb$user).
So, my suggestion is: ADD two fields to this index, i.e. make its key = (rdb$user,rdb$privilege,rdb$field_name).
#################
One may to estimate number of unnecessary IRs:
* download scripts from attach;
* create empty DB in 4.0, make its FW = OFF (for sped); assign alias 'e40' for this DB;
* run: C:\FB\40SS\isql /:e40 -i roles-ddl.sql 2>roles-ddl.err
(it can take 3-4 minute; ensure that file with errors "roles-ddl.err " is empty after finish)
NOTE that script 'roles-ddl.sql' has following tail:
recreate table user_priv_copy ( ------------- this table is used for comparison, see script 'roles-run.sql'
< ... DDL as in rdb$user_privileges ... >
);
insert into user_priv_copy select * from rdb$user_privileges;
create index up_rel_name on user_priv_copy (rdb$relation_name);
create index up_user_priv_fldname on user_priv_copy (rdb$user,rdb$privilege,rdb$field_name); -- modified key here: add two fields.
...
* launch trace session with following config (it's also in attached .7z):
database
\{
#⁠ Do we trace database events or not
enabled = true
#⁠ Put sql statement execution finish\\fetch to eof records
log\_statement\_finish = true
#⁠ Print access path \(plan\) with sql statement
print\_plan = true
#⁠ Use legacy \(false\) or explained \(true\) plan format
explain\_plan = true
#⁠ Print detailed performance info when applicable
print\_perf = true
#⁠ Put xxx\_finish record only if its timing exceeds this number of milliseconds
time\_threshold = 0
#⁠ Maximum length of SQL string logged
#⁠ Beware when adjusting max\_xxx parameters\! Maximum length of log record
#⁠ for one event should never exceed 64K\.
max\_sql\_length = 16384
#⁠ Maximum length of blr request logged
#⁠max\_blr\_length = 500
#⁠ Maximum length of dyn request logged
#⁠max\_dyn\_length = 500
#⁠ Maximum length of individual string argument we log
#⁠max\_arg\_length = 80
#⁠ Maximum number of query arguments to put in log
max\_arg\_count = 50
\}
===
* run file 'roles-run.sql' from attach. It will make connect to alias 'e30' and then imitate the same query as ATT_0 does.
* open trace log. You will see that ATT_0 did
param0 = varchar(252), "U_1999"
param1 = varchar(252), "R_19"
1 records fetched
11 ms, 15233 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$USER_PRIVILEGES 15021
RDB$ROLES 2
* Compare with possible statistics when use index on table with increased key
1 records fetched
0 ms, 3 read(s), 12 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$ROLES 2
PS.
Interesting that for query from roles-run.sql:
with recursive role\_tree as \(
select rdb$relation\_name as nm, 0 as ur
from USER\_PRIV\_COPY
\.\.\. \.\.\. \.\.\.
\)
\.\.\.\.
===
-- trace log does NOT contain line with 'USER_PRIV_COPY' in statistics per table section. Only 'RDB$ROLES' can be seen there.