Red Hat Bugzilla – Bug 445711
Errata ID Index speeds up Oracle CBO 10x
Last modified: 2014-08-11 01:42:30 EDT
Description of problem:
With an Oracle 10g CBO database, the YourRhn page registers much to slowly for a
database with many systems.
Analysis from conversation on satellite email list:
% >2.) Looking at the SQL below, the buffer IO seems to be close between
% >9i and 10g. But,
% >the CPU time for this query in 9i was 0.28(s) and the CPU time for 10g
% >was 2.51(s). That's
% >seems to be a big difference in time. The elapsed time for 9i is
% >0.28 and the elapsed time
% >for 10g is 62.69. Which means that it took 1.2 minutes for this query
% >to run.
% >first query under QL ordered by Gets DB/Inst: TENG/teng Snaps: 12-13
% >SELECT SECURITY_ERRATA, BUG_ERRATA, ENHANCEMENT_ERRATA,
% > GO.GROUP_ID AS ID, GROUP_NAME AS NAME, GROUP_ADMINS, SERVER_C
% >OUNT, NOTE_COUNT, GO.MODIFIED, GO.MAX_MEMBERS,
% >(SELECT DECODE( MAX(DECODE(PS.state, 'OK', 1
% >, 'PENDING', 2, 'UNKNOWN', 3, 'WARNING', 4, 'CRITICAL', 5)),
% >Let's try and get an explain plan on this query in 9i and 10g.
% I had suspected this query because it was only after I added 5k systems
% that the problem cropped up.
I created index on RHNSAT.RHNSERVERNEEDEDPACKAGECACHE("ERRATA_ID") which is
in the top of recomendations and YourRHN.do renders much faster now. (16s ->
Created attachment 304878 [details]
patch against Goldman custom svn branch that adds the eid index
Just making a note that this seems to reverse the decision of BZ 117597