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

Thread: Parallel Query

  1. #1
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Howdy!... Hey, I'm doing some testing on Parallel Queries and received some interesting results. As anyone got any advice for me.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> set timi on
    SQL> select count(*) from crp_sio;

    COUNT(*)
    ----------
    658768

    Elapsed: 00:00:01.26

    SQL> alter session force parallel query;

    Session altered.

    Elapsed: 00:00:00.00

    SQL> select count(*) from crp_sio;

    COUNT(*)
    ----------
    658768

    Elapsed: 00:00:05.91

    I mean a parallel count(*) took 5 seconds over a normal unparallel query of 1 seconds... it this a normal thing.

    Thanks party people!
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hi

    how many CPUs do you have?

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Hey Pando,

    The server in which this test was performed has 10 CPU's.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Did u get explain plan of both queries?
    Seems like in a first case cbo used fast scan index but
    in a second case it used n-flow full scan table with
    io contention between disk operations.

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Well... now I get a different result from yesterday...

    SQL> alter session disable parallel query;

    Session altered.

    Elapsed: 00:00:00.04
    SQL> select count(*) from crp_sio;


    COUNT(*)
    ----------
    658768

    Elapsed: 00:00:07.05

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=599 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'CRP_SIO' (Cost=599 Card=691601)

    SQL> alter session force parallel query;

    Session altered.

    Elapsed: 00:00:00.01

    SQL> select count(*) from crp_sio;

    COUNT(*)
    ----------
    658768

    Elapsed: 00:00:04.26

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 SORT* (AGGREGATE) :Q19000
    3 2 TABLE ACCESS* (FULL) OF 'CRP_SIO' (Cost=67 Card=691601 :Q19000
    )
    2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
    ROM (SELECT /*+ NO_EXPAND ROWID(A2)

    3 PARALLEL_COMBINED_WITH_PARENT

    [Edited by grjohnson on 10-03-2002 at 12:14 AM]
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    At what point of time did u execute this query.Means did u exeucte this query when the activity on the database was high.If so you would have got very low response time, on the other had if the activity on the database is low you would get a fast response time.

    regards
    anandkl
    anandkl

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    A couple of thoughts:

    - When timing queries, there are so many variables - one of the most important of which is I/O. Run any statement twice and it will run faster the second time because data was read from disk the first time and put in cache. Therefore, the second run did not have to read that data from disk and ran much faster. Any time you are using actualy time to compare the efficiency of statements, you need to run *each* version enough times to eliminate caching as a variable. You must run each several times because, since they probably have different execution plans, they will be caching different data.

    - Setting something to parallel is no silver bullet. A lot of variables are involved here as well. What are your configuration options? How many threads are attacking the problem? What is your I/O spread? How much did the plan change? Is this a statement that *should* be parallel? A good analogy I've heard is home repair: If you try to use 40 contractors to put in a bathroom sink, it's going to take longer than if you have just one. You don't actually want the 40 contractors until you're building an addition.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong

    - Setting something to parallel is no silver bullet. A lot of variables are involved here as well. What are your configuration options? How many threads are attacking the problem? What is your I/O spread? How much did the plan change? Is this a statement that *should* be parallel? A good analogy I've heard is home repair: If you try to use 40 contractors to put in a bathroom sink, it's going to take longer than if you have just one. You don't actually want the 40 contractors until you're building an addition.
    This is kind of what I was thinking as well. There's a certain amount of overhead when setting up a parallel query. In cases where your query runs for 5 seconds, that overhead is a significant portion. When your query takes 4 hours, that overhead is insignificant.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by chrisrlong
    A couple of thoughts:

    - When timing queries, there are so many variables - one of the most important of which is I/O. Run any statement twice and it will run faster the second time because data was read from disk the first time and put in cache. Therefore, the second run did not have to read that data from disk and ran much faster. Any time you are using actualy time to compare the efficiency of statements, you need to run *each* version enough times to eliminate caching as a variable. You must run each several times because, since they probably have different execution plans, they will be caching different data.

    - Chris
    Thanks Chris, yeah, I realise this, I actually ran the query a number of times after each other to take into account the caching from disk.

    Anyway, I agree about parallel not being a silver bullet, I actually thik it can cause serious performance decreases in some issues. I parrallel should only be used in certain processes. I have people around here thinking that Parallel processing will automatically improve performance on the database. Unformtunatly, on our server we have 6 database instances, so if one instance starts to consume excess resources via parallel processing, it'll affect the other instances.


    Also good point Jeff, I'll take that onboard.

    Thanks people...
    OCP 8i, 9i DBA
    Brisbane Australia

  10. #10
    Join Date
    Jan 2000
    Location
    HOUSTON, TX
    Posts
    75
    I am also, 100% agreement with following statement:
    "Setting something to parallel is no silver bullet", every thing depends on CPU, number of instances, Memory, and more over it depends on free server resources at time of your execution.

    Also, how you setting up your Parallelism: instances or object level.

    On the parallel issue, I was talking to an Oracle guru about the Oracle Parallel processes, and he was advocating to have 4-10 (based on degree of PARALLEL) datafiles for each tablespace, so when any one write or query to that tablespace, it will write/access simultaneously 4-10 datafiles as parallel processes.

    Now, I am looking for any oracle documentation to verify his statement. He could not guide me to any doc, and saying it is from his experience. Does any one in this forum have any clue about this?

    Thanks in advance,

    OraMan


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