
If you were logged in you would be able to see more operations.
|
|
|
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.
|
Description
|
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.
|
Show » |
There are no comments yet on this issue.
|
|