You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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:
===
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):
===
* 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:
===
-- trace log does NOT contain line with 'USER_PRIV_COPY' in statistics per table section. Only 'RDB$ROLES' can be seen there.
The text was updated successfully, but these errors were encountered: