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

Thread: query runs slow in the prod and fast in the test

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    query runs slow in the prod and fast in the test

    Hi guys,

    I have a wird problem.
    A query runs fast in the test (6 sec.) environment and slow in the production environment (27 sec.).
    Both of the envs are similar regarding data. I copied the prod into the test before i examined the query.
    Both hosts: HP-UX B.11.00 U 9000/800
    Oracle version: 8.1.7.3

    Following the tkprof output results in the production env:
    ==========================================================
    SELECT DEPT_ID,DEPT_NAME,ORDER_ID,SITE_NAME,START_DATE,
    START_TIME,END_DATE, END_TIME,WORK_DESC,CONTINUOUS,PRIORITY_NAME,
    STATUS_ID,STATUS_NAME,WORK_RISK,
    MATE,PARALLEL
    FROM
    ORDERS_LIST WHERE DEPT_ID>=0 AND ORDER_ID>0
    order by ORDERS_LIST.ORDER_ID
    DESC

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.35 0.40 0 0 0 0
    Execute 1 0.01 0.00 0 0 0 0
    Fetch 1103 1.26 27.10 1642 775 228 16516
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 1105 1.62 27.50 1642 775 228 16516

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 225 (WEB_HAZ)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    16516 SORT ORDER BY
    16516 VIEW ORDERS_LIST
    16516 UNION-ALL
    6268 HASH JOIN
    39 TABLE ACCESS FULL TDEPTS
    6268 HASH JOIN
    23 TABLE ACCESS FULL TSTATUS
    6268 HASH JOIN
    2 TABLE ACCESS FULL TPRIORITYS
    6268 HASH JOIN OUTER
    6268 HASH JOIN OUTER
    6268 HASH JOIN
    1854 TABLE ACCESS FULL TWORKS
    16516 TABLE ACCESS FULL ORDERS
    321 INDEX FULL SCAN (object id 74900)
    282 TABLE ACCESS FULL TSITES
    10248 HASH JOIN
    39 TABLE ACCESS FULL TDEPTS
    10248 HASH JOIN
    23 TABLE ACCESS FULL TSTATUS
    10248 HASH JOIN
    2 TABLE ACCESS FULL TPRIORITYS
    10248 HASH JOIN OUTER
    10248 HASH JOIN OUTER
    10248 TABLE ACCESS FULL ORDERS
    282 TABLE ACCESS FULL TSITES
    321 INDEX FULL SCAN (object id 74900)


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    16516 SORT (ORDER BY)
    16516 VIEW OF 'ORDERS_LIST'
    16516 UNION-ALL
    6268 HASH JOIN
    39 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TDEPTS'
    6268 HASH JOIN
    23 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSTATUS'
    6268 HASH JOIN
    2 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'TPRIORITYS'
    6268 HASH JOIN (OUTER)
    6268 HASH JOIN (OUTER)
    6268 HASH JOIN
    1854 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'TWORKS'
    16516 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'ORDERS'
    321 INDEX GOAL: ANALYZED (FULL SCAN) OF
    'ORDERS_CONNECT_PK' (UNIQUE)
    282 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSITES'
    10248 HASH JOIN
    39 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TDEPTS'
    10248 HASH JOIN
    23 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSTATUS'
    10248 HASH JOIN
    2 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'TPRIORITYS'
    10248 HASH JOIN (OUTER)
    10248 HASH JOIN (OUTER)
    10248 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'ORDERS'
    282 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'TSITES'
    321 INDEX GOAL: ANALYZED (FULL SCAN) OF
    'ORDERS_CONNECT_PK' (UNIQUE)

    prod init*.ora parameters
    ==========================
    db_name = "prod3"
    open_cursors = 300
    max_enabled_roles = 30
    db_block_buffers = 2048
    shared_pool_size = 52428800
    large_pool_size = 614400
    java_pool_size = 20971520
    log_checkpoint_interval = 10000
    log_checkpoint_timeout = 1800
    processes = 150
    log_buffer = 163840
    db_block_size = 8192
    job_queue_processes = 20
    job_queue_interval = 60
    distributed_transactions = 10
    open_links = 4
    compatible = "8.1.7"
    sort_area_size = 65536
    sort_area_retained_size = 65536


    Following the results of the tkprof output in test env:
    =======================================================
    SELECT DEPT_ID,DEPT_NAME,ORDER_ID,SITE_NAME,START_DATE,
    START_TIME,END_DATE, END_TIME,WORK_DESC,CONTINUOUS,PRIORITY_NAME,
    STATUS_ID,STATUS_NAME,WORK_RISK,
    MATE,PARALLEL
    FROM
    ORDERS_LIST WHERE DEPT_ID>=0 AND ORDER_ID>0
    order by ORDERS_LIST.ORDER_ID
    DESC

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.56 0.62 0 2 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1101 1.70 5.56 2378 741 156 16498
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 1103 2.26 6.18 2378 743 156 16498

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 512

    Rows Row Source Operation
    ------- ---------------------------------------------------
    16498 SORT ORDER BY
    16498 VIEW ORDERS_LIST
    16498 UNION-ALL
    6260 HASH JOIN
    39 TABLE ACCESS FULL TDEPTS
    6260 HASH JOIN
    23 TABLE ACCESS FULL TSTATUS
    6260 HASH JOIN
    2 TABLE ACCESS FULL TPRIORITYS
    6260 HASH JOIN OUTER
    6260 HASH JOIN OUTER
    6260 HASH JOIN
    1851 TABLE ACCESS FULL TWORKS
    16498 TABLE ACCESS FULL ORDERS
    323 INDEX FULL SCAN (object id 262169)
    282 TABLE ACCESS FULL TSITES
    10238 HASH JOIN
    39 TABLE ACCESS FULL TDEPTS
    10238 HASH JOIN
    23 TABLE ACCESS FULL TSTATUS
    10238 HASH JOIN
    2 TABLE ACCESS FULL TPRIORITYS
    10238 HASH JOIN OUTER
    10238 HASH JOIN OUTER
    10238 TABLE ACCESS FULL ORDERS
    282 TABLE ACCESS FULL TSITES
    323 INDEX FULL SCAN (object id 262169)

    test init*.ora parameters:
    ======================
    db_name = "test1"
    open_cursors = 1000
    max_enabled_roles = 140
    db_block_buffers = 2048
    shared_pool_size = 52428800
    large_pool_size = 614400
    java_pool_size = 20971520
    log_checkpoint_interval = 10000
    log_checkpoint_timeout = 1800
    processes = 150
    log_buffer = 163840
    db_block_size = 8192
    job_queue_processes = 4
    job_queue_interval = 60
    distributed_transactions = 10
    open_links = 4
    compatible = "8.1.7"
    sort_area_size = 65536
    sort_area_retained_size = 65536
    query_rewrite_enabled = true
    query_rewrite_integrity = trusted


    Any recommendations?

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Dec 2001
    Posts
    141
    Do you have the same number of extents in both databases ?
    Maybe the segments in the "test" database were reorganized during import/export for example ...
    Hope this help !

  3. #3
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    How busy is your production?
    Do you have a lot of read waits on prod compare to test?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #4
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi TomazZ,

    Following the situation of the wait events in the databases:

    production:
    =============
    prod3>
    SELECT time, count, class
    2 FROM V$WAITSTAT
    3 ORDER BY time,count
    4 ;

    TIME COUNT CLASS
    ---------- ---------- ------------------
    0 0 sort block
    0 0 save undo block
    0 0 save undo header
    0 0 free list
    0 0 bitmap block
    0 0 unused
    0 0 system undo block
    0 0 system undo header
    0 0 bitmap index block
    0 0 extent map
    10 9 segment header
    207 3142 undo header
    1351 7896 undo block
    447550 88476 data block

    14 rows selected.

    htamga,
    There are no extents problem. The bigest segment in the prod db has 4 extents.

    test
    =====
    test1>
    SELECT time, count, class
    2 FROM V$WAITSTAT
    3 ORDER BY time,count
    4 ;

    TIME COUNT CLASS
    ---------- ---------- ------------------
    0 0 sort block
    0 0 save undo block
    0 0 save undo header
    0 0 free list
    0 0 bitmap block
    0 0 unused
    0 0 system undo block
    0 0 undo block
    0 0 system undo header
    0 0 bitmap index block
    0 0 extent map
    0 1 segment header
    48 5 undo header
    355 50 data block

    14 rows selected.

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    Those the tables in both environnement has about the same number of rows?

    Analyse the tables in the environment that is slow.

  6. #6
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi Steve,

    All the tables in both of the environment have the same rows.
    In addition,all the tables are analyzed everynight at 24:00.

    Nir

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Sorry, I was thinking more of session waits or statistics.

    Well, actually I was thinking of you production being very busy (especially on physical reads), hence the slower query execution.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    Jan 2001
    Posts
    642
    Contension? check the undo header- if this value to the total request is more than 1% then try to increase the number of rbs

    Badrinath
    There is always a better way to do the things.

  9. #9
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi guys,

    Eventually, i've solved the problem by increasing the parameter SORT_AREA_SIZE from 65536 (default) to 1048576.
    After the increasing,the query ran fast.

    Thanks anyway,

    Nir

  10. #10
    Join Date
    Jan 2001
    Posts
    642
    How can it happen? Your environments are the same and you had to increase the sort area

    Then probably there were other users using the system and also there were a lot of disk i/o happening to your temp tablespace
    There is always a better way to do the things.

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