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

Thread: dbms_stats with parallelism stats

  1. #1
    Join Date
    Oct 2001

    dbms_stats with parallelism stats

    I used the package DBMS_STATS to gather statistics for a schema (with 8 Go).

    - without the parallelism clause (degree with the default value),
    to compute statistics (100%), execution time is about : 45 mn

    - with a parallelism of 4 (there are 4 CPUs on this box),
    to compute statistics (100%), execution time is about : 42 mn.

    I waited for an important ratio between the first and second execution). An explanation, idea ?

  2. #2
    Join Date
    Jun 2000
    ratio? Are you expecting 45minutes/4 for your 2nd case? No.

    Is you system already CPU bound?

  3. #3
    Join Date
    Oct 2001

    In the 2 cases, we have an execution time aroud 45mn (45mn without degree clause and 42mn with degree of 4).

    Our system is not CPU bound.

  4. #4
    Join Date
    Apr 2001
    Bangalore, India
    Oracle Says

    Oracle’s parallel-query architecture is unique in its ability to dynamically determine the degree of parallelism for every query. Unlike other database architectures, in which the degree of parallelism is solely determined by the partitioning scheme of the underlying tables, query parallelism within Oracle is intelligently determined, based on the size of the tables, the number of CPU’s, the number of files to be accessed, and other variables.
    Eventhough this is about parallelism on query, definitely it will applies to the other areas too. It is true that you specified degree of parallism 4 but, the database objects may not be the candidates for that parallism.

    In Oracle 10g comes with a new option for DEGREE in DBMS_STATS. You can specify DEGREE=auto_degree so that Oracle will calcualte the degree of parallelism on execution hence it will be optimal. Its decision will be based on the number of CPUs and other factors considering optimal performance.
    Last edited by Thomasps; 12-05-2003 at 06:15 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)


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