CPU bound and distinct select tuning (10g)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: CPU bound and distinct select tuning (10g)

  1. #1
    Join Date
    Feb 2008
    Posts
    31

    CPU bound and distinct select tuning (10g)

    Hi,

    I think that my database is CPU bound :


    Top 5 Timed Events Avg %Total
    ~~~~~~~~~~~~~~~~~~ wait Call
    Event Waits Time (s) (ms) Time
    ----------------------------------------- ------------ ----------- ------ ------
    CPU time 662 71.8
    db file sequential read 11,082 66 6 7.2
    control file sequential read 51,677 41 1 4.4
    db file scattered read 8,133 39 5 4.3
    SQL*Net more data to client 113,379 32 0 3.5
    -------------------------------------------------------------

    ================
    ==================

    And such stmt's probably cause this issue :

    SQL ordered by CPU

    CPU CPU per Elapsd Old
    Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
    ---------- ------------ ---------- ------ ---------- --------------- ----------
    101.49 160 0.63 15.9 102.55 252,160 2426247326
    Module: dirdld.exe
    SELECT DISTINCT(APPNM) FROM APPLICATION ORDER BY APPNM

    ===============================================

    (SQL ordered by Gets)

    Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
    713,149 33,280 21.4 13.3 39.67 42.70 2402103054
    Module: ORACLE.EXE
    INSERT INTO "PARAMETER" ("FAMNM","APPNM","PARTID","PARNAMELOC",
    "DLDTYPE","VALUE","FLAG","SEQINFO") VALUES (:B8,:B7,:B6,TRIM(:B5
    ),:B4,TRIM(:B3),:B2,:B1)

    489,906 114 4,297.4 9.1 20.16 29.46 2145980626
    Module: ORACLE.EXE
    DELETE FROM "PARAMETER" "A1" WHERE "A1"."PARTID"=:B1

    ============================
    ============================

    So is there any possiblility to tune this query ? :
    (it is already in keep buffer)

    SELECT DISTINCT(APPNM) FROM APPLICATION ORDER BY APPNM

    ---
    (
    Plan
    SELECT STATEMENT ALL_ROWSCost: 1,008 Bytes: 859,080 Cardinality: 85,908
    3 SORT ORDER BY Cost: 1,008 Bytes: 859,080 Cardinality: 85,908
    2 HASH UNIQUE Cost: 659 Bytes: 859,080 Cardinality: 85,908
    1 TABLE ACCESS FULL TABLE VCESERVICE.APPLICATION Cost: 307 Bytes: 869,150 Cardinality: 86,915
    )
    ---

    Best Regards Arkadiusz Masny

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by amasny
    I think that my database is CPU bound
    I don't think so...


    Quote Originally Posted by amasny
    So is there any possiblility to tune this query ?
    Your query hits a single table with no predicate, isn't that a book case study for Full Table Scan?

    Do you have an index on APPNM column?
    If yes... what happens if you take out the ORDER BY clause?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2008
    Posts
    31

    Cool

    Quote Originally Posted by PAVB
    I don't think so...




    Your query hits a single table with no predicate, isn't that a book case study for Full Table Scan?

    Do you have an index on APPNM column?
    If yes... what happens if you take out the ORDER BY clause?
    -----------

    PAVB, You are mystery man

    If order by is thrown away cost is 1/3 less.
    I know that it is case study for FTS, so i don't know if it is possible to make that query to run faster.

    Best Regards Arek Masny

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    hi

    Pavb also asked you if you an index on APPNM column?

    Can you also post the explain plan for that query

    regards
    Hrishy

  5. #5
    Join Date
    Feb 2008
    Posts
    31
    Quote Originally Posted by hrishy
    hi

    Pavb also asked you if you an index on APPNM column?

    Can you also post the explain plan for that query

    regards
    Hrishy

    -----------------

    Index is created on column : application.appnm

    --

    Plan for query :

    SELECT DISTINCT(APPNM) FROM APPLICATION ORDER BY APPNM;

    is below:
    --
    Plan
    SELECT STATEMENT ALL_ROWSCost: 1,008 Bytes: 859,080 Cardinality: 85,908
    3 SORT ORDER BY Cost: 1,008 Bytes: 859,080 Cardinality: 85,908
    2 HASH UNIQUE Cost: 659 Bytes: 859,080 Cardinality: 85,908
    1 TABLE ACCESS FULL TABLE VCESERVICE.APPLICATION Cost: 307 Bytes: 869,150 Cardinality: 86,915

    -------

    and for query :

    SELECT DISTINCT(APPNM) FROM APPLICATION

    is below :

    --

    Plan
    SELECT STATEMENT ALL_ROWSCost: 656 Bytes: 859,080 Cardinality: 85,908
    2 HASH UNIQUE Cost: 656 Bytes: 859,080 Cardinality: 85,908
    1 TABLE ACCESS FULL TABLE VCESERVICE.APPLICATION Cost: 307 Bytes: 869,150 Cardinality: 86,915

    ---------

    Thank You.
    Best Regards Arek Masny

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Okay... so you have about 87K rows on your table and 86K unique keys on your candidate index on APPNM column; is that correct?

    If that's correct the cheaper execution plan you can get is the one you have, FTS on your table.

    If your row/key counts are substantially different please gather fresh stats and start again.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Feb 2008
    Posts
    31
    Quote Originally Posted by PAVB
    Okay... so you have about 87K rows on your table and 86K unique keys on your candidate index on APPNM column; is that correct?

    If that's correct the cheaper execution plan you can get is the one you have, FTS on your table.

    If your row/key counts are substantially different please gather fresh stats and start again.
    ------
    Thank You PAVB.
    Sorry for bothering You but only quick question regarding this.
    Someone has adviced me that creating materialized view and setting query rewrite may be good solution (this query is hardcoded in application - i can't change it).
    What do you think about this ?

    Best Regards Arek Masny

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    let me see... you are advised to create a 86K rows MV on top of a 87K table... providing you do not have a real huge row lenght in your table which I have reasons to believe is about 100 bytes per row - that would be bad advice; Materialized Views and Query Rewrite are a great solution for a very different problem.

    What's your average row lenght on that table?
    How often you insert/delete row on that table?

    By the way... if you knew the query was hard-coded and you had no chance in hell to modify it... why where you asking for ways of finetuning it? most finetuning involves touching the query.
    Last edited by PAVB; 10-08-2008 at 08:41 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    How long does the query take to execute.

    I wouldnt go through the materialized query route based on just the information provided.

    I really doubt you have a performance problem

    regards
    Hrishy

Posting Permissions

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



Click Here to Expand Forum to Full Width