DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Parallel Query

  1. #11
    Join Date
    Sep 2000
    Posts
    128
    Thanks, I'll probably wait for the 8i upgrade then. Although I'm fairly sure CPU usage is a lot lower at night when our app is active.

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Since the disks are stripped, definitely PQO will improve performance, whenever the optimizer chooses full table scan.



  3. #13
    Join Date
    Sep 2000
    Posts
    128
    Have been playing around with PQO a little (using the /*+ PARALLEL (TABLE, DEGREE) */ Hint).

    On the migration machine, it made little difference...

    On production.... Oh yes!..

    Without PQO
    -----------------------
    select count(cartype)
    from t_entity

    Tkprof totals
    CPU: 50.57
    Elapsed: 51.11

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

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    3165654 SORT (AGGREGATE)


    With PQO
    -----------------------
    select /*+ PARALLEL(T_ENTITY, 25) */
    count(cartype)
    from t_entity

    tkprof totals
    cpu 0.03
    elapsed 6.49

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

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    24 SORT (AGGREGATE)
    0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T_ENTITY' [:Q21000]
    SELECT /*+ ROWID(A1) PIV_SSF */ COUNT(A1."CARTYPE") FROM
    "T_ENTITY" A1 WHERE ROWID BETWEEN :B1 AND :B2

    ***************************************************

    In fact, everything I've run on a Full Table scan is in a magnitude of 5-10 times quicker.

    So my question now - is it sufficient to just change the degree on the tables to have parallel query running?
    (I am still reading up on it btw... just that if experience is always better than manuals - and I can't really test on my other servers since is has little impact on them).

    Thanks for all the input so far....

    Terry



  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Good Result, Terryd.

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