Description of problem: When rendering systems overview page in webUI and selecting 250 systems to be displayed the SQL takes ~14s to finish and render the page, but when the systems are 500+ the SQL takes up to 4 minutes to render the page. This query is using bind params and Oracle is having an issue with the bind peek. Adding a hint to the Oracle optimizer to improve performance. spacewalk-java/spacewalk-java-1.2.39/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml @@ -1132,7 +1132,7 @@ ORDER BY S.ID </query> <query name="system_overview" params=""> - SELECT /*+ RULE */ SERVER_ID AS ID, OUTDATED_PACKAGES, SERVER_NAME, security_errata, bug_errata, enhancement_errata, + SELECT /*+ opt_param('_OPTIM_PEEK_USER_BINDS ',FALSE) RULE */ SERVER_ID AS ID, OUTDATED_PACKAGES, SERVER_NAME, security_errata, bug_errata, enhancement_errata, SERVER_ADMINS, GROUP_COUNT, NOTE_COUNT, MODIFIED, CHANNEL_LABELS, CHANNEL_ID, Version-Release number of selected component (if applicable): How reproducible: Steps to Reproduce: 1. 2. 3. Actual results: Expected results: Additional info:
65e6c8a..777d83d master -> master
I've reverted the change now with 94a0c8a3cb8e7b02cd6f722b47bbaecac7697e78.
Committed to spacewalk master: 60d188b797fd3bac125bc735ec88bf3fd33c2bd7 I removed the rule in the offending query that was forcing the query to operate sub-optimally. I also added a new query to significantly improved the performance of the system.listActiveSystems and system.listInactiveSystems API methods when dealing with large numbers of registered systems.
Stephen, is the DISTINCT really needed? Also, can't we just ORDER BY that id? In XMLRPC, I'd much rather see stable order (by numeric id) than profile name there which can change.
(In reply to comment #5) > Stephen, is the DISTINCT really needed? > > Also, can't we just ORDER BY that id? In XMLRPC, I'd much rather see stable > order (by numeric id) than profile name there which can change. Hi Jan, That would be fine with me, but not consistent with the way we handle other XMLRPC system list requests. You'll notice that the new queries are identical to the xmlrpc_visible_to_user query, with an additional where clause that filters by last checking date. If we want to change this behaviour in the new queries we should change it in the existing one to, and I saw no reason to mess with (and possibly introduce regressions in) that query.
Many customers have been complaining of the speed of loading the Systems page since I have taken out the RULE hint. Some customers only see fast query times if the default cost-based optimizer is used, some only see fast query times if the RULE based optimizer is used, and some only see fast query times with the RULE optimizer and hint provided in comment 0. I have been unable to find a way to make this fast for everyone, but judging by the speed and size of the response since I took out the RULE based optimizer I am reverting that portion of my earlier change so that the query will once again use the RULE optimizer. I highly recommend that someone with more in-depth knowledge of the internal operations of databases take a look at this issue. Ideally everyone would see fast query results, not just a subset of people. Spacewalk master commit: 568a009f342df13818a8a6371805f58bd7b10a86
The problem seems to be (at least on the reproducing system I have access to) the config file differences information. It seems that extra query adds enough complexity that the Oracle can't figure out an efficient way to run the query without the RULE hint (on this system). Separating the config diff query out into its own elaborator allows both to run quickly with no hints to Oracle. Hopefully this will be a general solution on all systems with this issue. Committed to Spacewalk master: b7fc95df2bc41d7786da0bd5e1c9d2e00ed5a8ac
Removing the config elaborator from a few queries where it's not needed. 0578253e95c488928e5d486e665eead2a7aaf034
Moving ON_QA. Packages that address this bugzilla should now be available in yum repos at http://yum.spacewalkproject.org/nightly/
Spacewalk 1.8 has been released: https://fedorahosted.org/spacewalk/wiki/ReleaseNotes18