Bug 445711 - Errata ID Index speeds up Oracle CBO 10x
Errata ID Index speeds up Oracle CBO 10x
Status: CLOSED RAWHIDE
Product: Red Hat Satellite 5
Classification: Red Hat
Component: New Feature (Show other bugs)
510
All Linux
low Severity low
: ---
: ---
Assigned To: James M. Leddy
Brandon Perkins
:
Depends On:
Blocks: 444766
  Show dependency treegraph
 
Reported: 2008-05-08 13:52 EDT by James M. Leddy
Modified: 2014-08-11 01:42 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-05-08 13:57:45 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:


Attachments (Terms of Use)
patch against Goldman custom svn branch that adds the eid index (601 bytes, patch)
2008-05-08 13:52 EDT, James M. Leddy
no flags Details | Diff

  None (edit)
Description James M. Leddy 2008-05-08 13:52:28 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 
% >section.
% >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 ->
2s)
 :)
Comment 1 James M. Leddy 2008-05-08 13:52:28 EDT
Created attachment 304878 [details]
patch against Goldman custom svn branch that adds the eid index
Comment 2 James M. Leddy 2008-05-08 13:55:24 EDT
Just making a note that this seems to reverse the decision of BZ 117597

Note You need to log in before you can comment on or make changes to this bug.