Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2005
    Posts
    25

    Unanswered: Is there a more efficient total technique other than SUM?

    I just want to count up some software usage. Below is the smaple code, and although my file sizes or enormous, this code is not efficient. Looking for clues.

    Sample:

    SELECT
    mySite,
    myDomain,
    mySWDisc,
    myInv1,
    myInv2,
    myDaysIn,
    myMnfr,
    myName,
    myVer,
    mySWSN,
    myLogin,
    myAsset,
    myMach,
    myHWSN,
    myWSID,
    myProdID,
    myCompID,
    SUM(swu.startupcount) AS myStarts,
    SUM(swu.runtimeseconds) / 3600 AS myRunHrs,
    SUM(swu.activeseconds) / 3600 AS myActHrs
    FROM (
    SELECT
    mySite,
    myDomain,
    mySWDisc,
    myInv1,
    myInv2,
    myInv2 - mySWDisc AS myDaysIn,
    myMnfr,
    myName,
    myVer,
    mySWSN,
    myLogin,
    myAsset,
    myMach,
    myHWSN,
    myWSID,
    myProdID,
    myCompID
    FROM (
    SELECT
    wkst.site AS mySite,
    cols.collservername AS myCSN,
    wkst.wkstndomain AS myDomain,
    cols.createdate AS mySWDisc,
    wkst.createdate AS myInv1,
    wkst.LastSuccessfulLoadDate AS myInv2,
    prod.manufacturer AS myMnfr,
    prod.name AS myName,
    prod.version AS myVer,
    comp.serialnumber AS mySWSN,
    wkst.loginname AS myLogin,
    wkst.assettag AS myAsset,
    wkst.machinename AS myMach,
    wkst.serialnumber AS myHWSN,
    comp.workstationoid AS myWSID,
    prod.productoid AS myProdID,
    comp.componentoid AS myCompID
    FROM
    product prod, -- Each SW or HW description including Mfr & Serial
    component comp, -- Components include SW and HW on Workstation
    workstation wkst, -- 32,000 Workstations
    collserver cols
    WHERE INSTR(UPPER(prod.name), 'DREAMWEAVER') <> 0
    AND comp.isdeleted IS NULL
    AND comp.workstationoid = wkst.workstationoid
    AND wkst.isdeleted IS NULL
    AND wkst.collserveroid = cols.collserveroid
    ),
    software s
    WHERE s.componentoid = myCompID
    AND NOT ( INSTR(UPPER(s.path), 'TRANSFER') <> 0
    OR INSTR(UPPER(s.path), 'OLD C') <> 0
    OR INSTR(UPPER(s.path), 'OLD D') <> 0 ),
    softwareusagehistory swu -- 57 Million records at last count
    WHERE swu.componentoid = myCompID (+)
    GROUP BY mySite, -- This looks like a memory hog. All I want to
    myDomain, -- know is how much the product is being used
    mySWDisc, -- on a particular PC. I'd rather select a
    myInv1, -- particular PC, then run the totals for that
    myInv2, -- one product, spool that line unbuffered, then,
    myDaysIn, -- grab the next workstation/product and count
    myMnfr, -- it's usage. Even with large file sizes, this
    myName, -- should take 4-6 hours like it does.
    myVer,
    mySWSN,
    myLogin,
    myAsset,
    myMach,
    myHWSN,
    myWSID,
    myProdID,
    myCompID;


    As always, Thanks,

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) Enable SQL_TRACE & run results thru TKPROF
    2) Post the EXPLAIN_PLAN back here
    3) Read the sticky post (at the top of the list) & learn how to use "code" tags
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2005
    Posts
    25

    I'd like to do that...

    But, have no idea how. I was given an Oracle 9i SQL Plus Worksheet with which to work. Not even a pocket referrence in the deal. I actually changed my question and put in in a new thread.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Not even a pocket referrence in the deal.
    It appears that you are unwilling or capable of reading, comprehending & following the advice POSTED clearly in TOP post in this forum.
    " Sticky: Guidance and resources for posters"
    There are MORE resources & KNOWLEDGE listed about Oracle than you could consume this decade.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2005
    Posts
    22
    One thing which may help:

    if you use

    select ...
    from
    (select ... from .. where ...) prod,
    ...
    where ...;

    and put the largest tables first in the from clause

    you should get a substantial spped up.

    Bill

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >and put the largest tables first in the from clause you should get a substantial speed up.
    I would like to see any reproducable test case that supports your assertion.
    I seriously doubt that this is true more often than not.
    Even if it were true, why hasn't Oracle included this in the optimizer so the the person writing the SQL does not have to do it & still obtain the best performance?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2005
    Posts
    22
    Try it and see

    I cannot guarantee it will work for every database but I have had substantial speedup doing this.

    Bill

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I cannot guarantee it will work for every database but I have had substantial speedup doing this.

    THIS?
    This is what?
    Provide data.
    Provide SQL.
    Provide verifiable evidence & reproducable cases.
    Provide a single, measurable, reproducable case; with Oracle version.
    Quantify "substantial".
    Even if it were true, why hasn't Oracle included this in the optimizer so the the person writing the SQL does not have to do it & still obtain the best performance?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Sep 2005
    Posts
    22
    To anacedent:

    It is mentioned you read about the RBO optimzer.
    eg Oracle SQL Tuning pocket reference (M Gurry - OReilly) pages 7-11 "What the RBO rules don't tell you".

    This gives the optimiser a hint about which table to use as the driving table.

    Speedup varies 2-4 times depending. Of course the other part of the optimisation suggested is a large part of it as it reduces the number of records to search.

    Bill

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    A minor point but I dont think we know which optimizer is being used though I would be surprised if he was using the RBO.

    Alan

  11. #11
    Join Date
    Oct 2005
    Posts
    25

    Thanks for the help, anacedent

    Quote Originally Posted by anacedent
    >Not even a pocket referrence in the deal.
    It appears that you are unwilling or capable of reading, comprehending & following the advice POSTED clearly in TOP post in this forum.
    " Sticky: Guidance and resources for posters"
    There are MORE resources & KNOWLEDGE listed about Oracle than you could consume this decade.
    Nice attitude. Really helpful. anacedent, I am very sorry to have wasted your time with my newbie questions. Please feel free to never respond to anymore of my posts.

    I did actually attempt to go read "Sticky" notes, but, when I select it, I don't see anything. In this regard, anacedent is correct, I was unable, not incapable, of reading the information. I think it would be appropriate that I request you withhold judgement on my comprehension skills until such time that I have actually located this information. Perhaps I am not going to the right place, perhaps I just don't understand how to navigate vBulletins as well as I should, but, for whatever reason, I didn't see anything.

    If there is anyone who would like to have a civil, rational conversation concerning how I can access the elusive "Sticky" notes posted so clearly at the top of this forum, and it wouldn't be to huge a nusiance to perhaps point me in the right direction, your help would be much appreciated.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Why the outer join (+) here? :-

    WHERE swu.componentoid = myCompID (+)

    Since the purpose of the whole query is to get data grouped by myCompID and its related data, it seems that all this does is slow the query down.

    I can't really follow what the query is trying to do, but it seems overly complex. Why not just something like:
    Code:
    SELECT wkst.site,
           wkst.wkstndomain,
           ...etc.
           comp.componentoid,
           ...etc.       
           SUM(swu.startupcount) AS myStarts,
           SUM(swu.runtimeseconds) / 3600 AS myRunHrs,
           SUM(swu.activeseconds) / 3600 AS myActHrs
    FROM   workstation wkst,
           softwareusagehistory swu,
           component comp,
           ...etc.
    WHERE  swu.componentoid = comp.componentoid
    AND    ...etc.
    GROUP BY 
           wkst.site
           wkst.wkstndomain
           ...
           comp.componentoid
           ...;

  13. #13
    Join Date
    Oct 2005
    Posts
    25

    Overly complex, yes, I am trying to correct

    The SELECT is overly complex. I'm looking for ways to correct it. When working with progress, I would always avoid using any of the ACCUM Statements (SUM, AVG, COUNT, etc...) because of the memory load. Especially when a report was running through large volumes of data.

    Let me rationalize teh outer join. Each workstation has components. Components are HW or SW. I work for a firly large corporation which has upwards of 30,000 Workstations. Each workstation may have 100 (HW & SW) components. All I am concerned with here is SW. Everytime a SW is run on the PC, a usage record is created based on Date. Typically, McAfee has one entry each day on each workstation with runsecs = 86399. However, if the application isn't run, the usage record is not created. In the grand scheme of things, that is what I want. Where are we wasting a lincense on someone who has never used the SW.

    Someone may request a license for a particular SW package. It may be granted and installed. Six months later, I may see that they have only started it 10 times with total active usage of 1.5 hours. I may want to recover that license and use it for someone else.

    Worst scenario, user has never accessed the SW application. We have installed it, but a usage record has never been created. Therefore, I report every machine where SW is loaded, but, I may not have any usage records, so OUTER JOIN. I started another thread http://www.dbforums.com/t1198061.html
    which just explains what I want to do without the code. Posting the code was likely a mistake...confusing.

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Book
    The SELECT is overly complex. I'm looking for ways to correct it. When working with progress, I would always avoid using any of the ACCUM Statements (SUM, AVG, COUNT, etc...) because of the memory load. Especially when a report was running through large volumes of data.
    Well I don't know whether that is good practice for Progress, but I do know that it is very inappropriate for Oracle. Aggregate functions like these are the best and most efficient way to aggregate data in Oracle and do not impose an unacceptable "memory load".

    Quote Originally Posted by Book
    Let me rationalize teh outer join. Each workstation has components. Components are HW or SW. I work for a firly large corporation which has upwards of 30,000 Workstations. Each workstation may have 100 (HW & SW) components. All I am concerned with here is SW. Everytime a SW is run on the PC, a usage record is created based on Date. Typically, McAfee has one entry each day on each workstation with runsecs = 86399. However, if the application isn't run, the usage record is not created. In the grand scheme of things, that is what I want. Where are we wasting a lincense on someone who has never used the SW.
    In which case, you have written the outer join the wrong way round. It should be:
    Code:
    WHERE swu.componentoid (+) = myCompID
    That might also perform better (maybe).

    I still feel that your SQL can be simplified to something more like:
    Code:
    SELECT wkst.site,
           wkst.wkstndomain,
           ...etc.
           comp.componentoid,
           ...etc.       
           SUM(swu.startupcount) AS myStarts,
           SUM(swu.runtimeseconds) / 3600 AS myRunHrs,
           SUM(swu.activeseconds) / 3600 AS myActHrs
    FROM   workstation wkst,
           softwareusagehistory swu,
           component comp,
           ...etc.
    WHERE  swu.componentoid (+) = comp.componentoid
    AND    ...etc.
    GROUP BY 
           wkst.site
           wkst.wkstndomain
           ...
           comp.componentoid
           ...;

  15. #15
    Join Date
    Oct 2005
    Posts
    25

    I stand corrected...

    Tony,

    Rewritten as you suggested, the query runs mush more efficiently. Returns in minutes. My biggest problem was trying to exclude SW entries which were discovered in areas like "TRANSFER" or the like. My command there was something to the effect of

    AND NOT (INSTR(UPPER(sw.path), 'TRANSFER') <> 0
    OR INSTR(UPPER(sw.path), 'OLD C') <> 0
    OR INSTR(UPPER(sw.path), 'OLD D') <> 0 )

    Decided to simply report sw.path and then cull out invalid entries in Excel.

    sw.path is a 255 character field. This is where all the problems began.

    Thanks again,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •