DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Slow sorting

Hybrid View

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    Slow sorting

    DB - Oracle 10g (10.2.0.4)
    OS - HP UNIX
    db_file_multiblock_read_count=64

    I have a query which fetches around 6 million records and do order by. Please find the query below (I have changed the table / column names)

    SELECT /*+ PARALLEL(TBL 8) */
    COLA,
    COLB,
    COLC,
    COLD,
    COLE,
    COLF,
    COLG,
    COLH,
    COLI,
    COLJ,
    COLK,
    COLL
    FROM
    TBL
    ORDER BY
    COLA,
    COLB,
    COLC,
    COLD,
    COLE,
    COLF,
    COLG,
    COLH,
    COLI,
    COLJ,
    COLK,
    COLL
    Please find the auto trace output below.

    Statistics
    ----------------------------------------------------------
    4001 recursive calls
    18 db block gets
    276723 consistent gets
    525087 physical reads
    1480 redo size
    789593219 bytes sent via SQL*Net to client
    4448243 bytes received via SQL*Net from client
    404343 SQL*Net roundtrips to/from client
    9 sorts (memory)
    1 sorts (disk)
    6065116 rows processed
    Thou explain plan showing 12 minutes for this query, actually it is taking close to 2 hours to complete. I am trying to find why it is taking 2 hours and couple of questing flashing in my mind.

    is this because of (disk) sort?

    is this because of more recursive calls / consistent gets?

    As this is simple query, not much options in my hand. Could some one drop some hints to me so that I can tune this query in a better way?



    Thanks,
    Last edited by vnktummala; 02-22-2011 at 10:32 AM.
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Is there any index on order by column? Moreover, I cannot see the entire query.
    lucky

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Akhil,

    what ever I posted is the complete query. Select few columns from a table and order by... that's it...no filters at all.

    yes, two Indexes on the table. One is on COLA and the other one is on other column which is not in the select list.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by vnktummala View Post
    Hi Akhil,

    what ever I posted is the complete query. Select few columns from a table and order by... that's it...no filters at all.

    yes, two Indexes on the table. One is on COLA and the other one is on other column which is not in the select list.

    Thanks,


    I am sorry, I thought that it is only column list after SELECT, I just saw the entire query.

    Does the execution plan use the index on COLA? Also, please post the execution plan.
    lucky

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    It is doing FTS in parallel. As there is no filter condition no chances of using Index.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by vnktummala View Post
    It is doing FTS in parallel. As there is no filter condition no chances of using Index.

    Thanks,
    If there is an index on order by column, it is used by the optimizer and the rows are fetched using the index, and hence a sort should not be required. I think that I am correct. Am I?
    lucky

  7. #7
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    It is a report, but who is firing this query and does he really need all those 6 million records?
    If he needs what is frequency of this report once in month.
    Derive this way and then find out is it worth tuning it?

    However for the time being, you can reove that PARALLEL hint or reduce 8 to some other value if you I/O wait is high? BTW how many cpu cores availble in DB.
    http://www.perf-engg.com
    A performance engineering forum

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks Biswal. I have 24 dual core CPUs in the server. Please find the init parameter values below.

    parallel_adaptive_multi_user boolean TRUE
    parallel_automatic_tuning boolean FALSE
    parallel_execution_message_size integer 8192
    parallel_instance_group string
    parallel_max_servers integer 48
    parallel_min_percent integer 0
    parallel_min_servers integer 8
    parallel_server boolean FALSE
    parallel_server_instances integer 1
    parallel_threads_per_cpu integer 2
    Informatica session fires this job and yes it needs all 6 million order by. This session is to build persistent cache and it is part of daily job.

    I have executed this query with parallel 4 hint and traced with 10046 evebt. As parallel_threads_per_cpu value is 2, total 8 parallel slaves assigned work.

    Due to formatting issue, I have attached the tkprof output from PX & QC processes. Please have a look.

    Thanks,
    Attached Files Attached Files
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  9. #9
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    adding to my last post ...

    threads P0 - P3 are doing sorting. All the wait events for these threads (listed below) are considered idle wait events.

    PX Deq: Execution Msg
    PX Deq Credit: send blkd
    PX Deq: Table Q Sample
    PX Deq: Table Q Normal
    threads P4 - P7 are doing FTS (1.5 million rows each). For these threads I have seen two interested wait events(listed below) along with idle wait events.

    PX Deq Credit: need buffer
    PX qref latch
    but total wait time for the above two events is very less ( average 0.50 for need buffer and average 0.03 for latch).

    I will trace this query again with reduced parallel servers and post my findings. Mean time any expert advise is highly appreciated.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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