The DBAs have highlighted the following:
We discovered that the following statement is used in every request:
SELECT DISTINCT a.assetid, l.majorid, a.type_code, a.status, a.name, a.short_name, pt.path, l.sort_order
FROM sq_ast a
INNER JOIN sq_ast_lnk l ON a.assetid = l.minorid
INNER JOIN sq_ast_path pt ON a.assetid = pt.assetid
INNER JOIN sq_ast_perm p ON a.assetid = p.assetid
LEFT JOIN sq_vw_ast_role r ON p.userid = r.roleid
WHERE
l.majorid IN ('123') AND
BITAND (l.link_type, 1) > 0 AND
(
p.userid IN ('7', '7') OR
r.userid IN ('7', '7')
)
AND
(
(p.permission = '1' AND
( p.userid <> '7' OR
r.userid <> '7' OR
(p.userid = '7' AND p.granted = '1') OR
(r.userid = '7' AND p.granted = '1')
)
) OR
( p.permission > '1' AND p.granted = '1' )
)
GROUP BY a.assetid, l.majorid, a.type_code, a.status, a.name, a.short_name, pt.path, l.sort_order, p.assetid
HAVING MIN(p.granted) <> '0' ORDER BY l.majorid, l.sort_order
It is not efficient as the part of "l.majorid IN ('123')" is a hardwired statement rather than bind variable.
This causes Oracle build up share pool for every statement with different id.
It will also cause a scalability issue as more and more web sites come to the system.
Could you please ask the supplier to change their code so that a prepared statement is used.
userid 7 is the public user, so it means every access to a public page generates this request to the Oracle database. I am not a DBA and I am told Oracle is intelligent enough to know what queries it has been looking at. And therefore changing the above query to use a prepared statement will add much needed efficiency.
Matrix Efficiency
In 3.18.0+, most of these values are bind vars. However, the IN clause is not because it generally changes and you will always have a different number of bind vars within it.
The only way I can really see this working with bind vars is to either do the code where each IN value gets a new bind var dynamically (used elsewhere in Matrix at times) and/or have a special case where the IN becomes a straight comparison if there is only one value to compare to.
Either way, it is logged in the tracker so we wont forget about it. If you want to fund the development to get it more quickly, contact Squiz.
Oracle:
ORA-01795: maximum number of expressions in a list is 1000.
bug report: http://bugs.matrix.squiz.net/view_bug.php?bug_id=3146
To replicate the problem link an asset to many places (keep on adding until you get an error). And then try to acquire a lock on the linked asset. The following query is run:
(SELECT DISTINCT(ll.majorid), ll.type_code, ll.treeid, length(ll.treeid)/4 as depth FROM
(
SELECT l.majorid, t.treeid, a.type_code, a.name FROM sq_ast_lnk_tree t
INNER JOIN sq_ast_lnk l ON t.linkid = l.linkid
INNER JOIN sq_ast a ON l.majorid = a.assetid WHERE t.treeid IN (…)
) AND a.type_code = ‘bodycopy’) ll UNION ALL SELECT sl.majorid, null, null as treeid, null as depth FROM sq_shdw_ast_lnk sl WHERE sl.minorid = ‘10973’ )ORDER BY depth DESC
if the number of items in the list ‘WHERE t.treeid IN (…)’ exceeds 1000 items, the error ORA-01795: maximum number of expressions in a list is 1000. is eturned.
Jus letting you know just in case you come across the problem.
Suggested fixes (from the DBAs, I am not a DBA):
- Rewrite the list as a subquery
- WHERE t.treeid IN ( … ) or t.treeid IN ( … ) or t.treeid IN ( … ). Alternatively you could insert the values into a global temporary table and do something like WHERE t.treeid IN ( select treeid from temp_table ).
Ensure the values in the list are unique reduces the size a bit, for our case 1072 items to 636, which will make the problem go away as long as we do add any more links
I've commented on the bug report as well. This was reported previously and fixed in our dev version. It was then back-ported to 3.18 once testing found it to be working consistently. However, it does remove code that was specifically added to improve Oracle performance when getting parents, so it was not backported to earlier versions. The last thing I want to to slow down systems during a minor version upgrade.