Home » RDBMS Server » Performance Tuning » parallel query performance
parallel query performance [message #65359] Wed, 18 August 2004 05:44
pier
Messages: 1
Registered: August 2004
Junior Member
Partion and Parallel problem

A very strange situation....
I try to explain:
I have a partition table (range) with 2.000.000.000 of records (Datawarehouse telecom italia).
We have theese queries and the following problem:

1) select * from tab partition(p_167)
elapsed: 10 seconds
rows: 100.000

2) select * from tab where "partition col" = '167'
elapsed: 10 seconds (the same)
rows: 100.000

3) select /*+ parallel (tab,16) */ * from tab partition(p_167)
elapsed: 10 seconds (the same)
rows: 100.000

4) select /*+ parallel (tab,16) */ * from tab
where "partition col" = '167'
elapsed: 5 minutes !!! with pruning (exact)
and many waits on "library cache pin"
rows: 100.000

Please, could you help me to understand why the case number 4 take 5 Minutes with "library cache pin"??
Previous Topic: Sql Trace in VB
Next Topic: different plan output
Goto Forum:
  


Current Time: Fri Mar 29 05:19:06 CDT 2024