Parallel query option

[ora_inf]

O.S. => Windows NT
CPU => 8
Oracle => 8.1.7.3.0

I want to use Parallel query option to utilize more CPU to improve the performance for table which is not partitioned and SQL uses index scan.

Does Parallel query option is going to improve the performance?

Do I need to set up parallelizm for related Index along with the Table?

Any suggestion?

Thanks.

[pando]

parallel query doesnt mean "good" always, in fact it can slow you queries if your database is OLTP, i.e index access

parallelism is at table level not index

[ora_inf]

What does it mean by index parallelize ?

Alter the table (or index) to indicate that Oracle should try to parallelize operations performed against it

ALTER TABLE TAB_XXX PARALLEL (DEGREE 8);

[Orca777]

Hi,
Could it be that the degree-col in dba_indexes
counts when the optimzizer wants to do a fast full index-scan?
Orca

Description of dba_indexes :

DEGREE VARCHAR2(40) Number of threads per instance for scanning the index 

[ora_inf]

If it is a index scan, do I need to set up a parallel just for the index or table or both(index and table)?

Thanks.

[ora_inf]

I am going to use hint for the SQL.

Following are the steps. If I am missing something, let me know.
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL >INSERT /*+ PARALLEL (TABLE_NAME,8) */
INTO TABLE_NAME


Do I need to setup the following init.ora parameter in oracle 8i?I think these parameters do not need to set for oracle 8i.

parallel_min_server
parallel_max_server
parallel_server_idle_time
parallel_min_percent

Thanks.

[pando]

FYI Parallel DML unlike Parallel Query, it only works for partitioned tables

[ora_inf]

I think it is true for only delete and update but not for insert..

[pando]

well insert is DML, check Oracle concepts guide

but seems right that it applies to delete and update

[ora_inf]

Hi:

I am doing select on the table. Select statement is generated and every time it generates the diff. way so I don't have much control over it. If I set the parallelizm at DDL level, will it use parallel?
Is there any other way to find out beside query plan that this query is using parallelizm?

Any other suggestion to improve the performance for gererate select statement query?


Thanks.