Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: SQL Tuning, any idea?

    Hello all,

    I need to tune the following statement. I'm a new dba. I tried to create
    some indexes on "cost_revenue" and "action_no" fields but it didnt do any effect. Oracle doesnt let me to create an index on grouping functions either.

    you can find the SQL statement and its plan below.

    help please.


    SQL:
    select cost_revenue.action_no, max(decode(cost_revenue.surcharge_type, 'SHIPMENT', cost_revenue.company_code, '')) supplier, sum(cost_revenue.le_amount) amount, max(cost_revenue.le_currency_code) currency
    from cost_revenue
    where cost_revenue.cost_revenue = 'C'
    group by cost_revenue.action_no;


    PLAN
    ------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    ------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 18793 | 513K| 347 (1)|
    | 1 | HASH GROUP BY | | 18793 | 513K| 347 (1)|
    | 2 | TABLE ACCESS FULL| COST_REVENUE | 25318 | 692K| 346 (1)|
    ------------------------------------------------------------------------

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Is the query running "slow" ?

    18k rows table, that's pretty small.

  3. #3
    Join Date
    Feb 2008
    Posts
    4
    Quote Originally Posted by JMartinez
    Is the query running "slow" ?

    18k rows table, that's pretty small.

    Martinez,
    this small query is used in a large query and this query makes the main one very expensive. even though it is small it consumes a lot of CPU.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How are the statistics on the cost_revenue table? Is the cardinality of the cost_revenue table sufficiently high to warrant an index? Lastly, how much diskspace is allocated to the cost_revenue table? If it has been recently had a large delete, then you may need to reorganize the data to be closer on disk. Hard to say.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps to lower a high water mark? Because, "small table" + "index on the only column in the WHERE clause" sounds like a "quite fast execution".

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As i was thinking. The Full Access may be unavoidable, if there are only a handful of values in the cost_revenue column. Still, even a Full Access of a table of 18,000 rows should be fairly quick.

Posting Permissions

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