Bug 445711 - Errata ID Index speeds up Oracle CBO 10x
Summary: Errata ID Index speeds up Oracle CBO 10x
Status: CLOSED RAWHIDE
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: New Feature
Version: 510
Hardware: All
OS: Linux
low
low
Target Milestone: ---
Assignee: James M. Leddy
QA Contact: Brandon Perkins
URL:
Whiteboard:
Keywords:
Depends On:
Blocks: 444766
TreeView+ depends on / blocked
 
Reported: 2008-05-08 17:52 UTC by James M. Leddy
Modified: 2014-08-11 05:42 UTC (History)
1 user (show)

(edit)
Clone Of:
(edit)
Last Closed: 2008-05-08 17:57:45 UTC


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

Description James M. Leddy 2008-05-08 17:52:28 UTC
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 17:52:28 UTC
Created attachment 304878 [details]
patch against Goldman custom svn branch that adds the eid index

Comment 2 James M. Leddy 2008-05-08 17:55:24 UTC
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.