Re: simple SELECT scales in a multi-processor env?

There are a couple of errors in the query, but I assume this is just an indication of the sort of thing you are trying to do.

In principle, such a query can run parallel (the analytic functions are generally parallel-enabled) if you configure the instance to allow parallel execution slaves (parallel_max_servers = 5 by default - you may want to increase it) and invoke parallelism in the query e.g. by making the table inherently parallel

    alter table transaction parallel (degree 2);

or put in a parallel hint

    select /*+ parallel (transaction 2) */ from

When using hints, especially with analytic functions, make sure you put the hint in the right block - i.e. the one that does the select from the table, not one in an in-line view a couple of layers out.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Giovanni Azua wrote in message ...


>Hello all,

>

>Imagine you make a costly SELECT statement like this

>over more than 4 million transactions:

>

>select sum(to_number(amount)) over

>       (

>             partition by transaction_date

>             order by rowid

>             range between interval '10' day preceding and interval

'10' day


>following

>       ) as highly_smoothed_amount

>from transaction

>

>Oracle implementation of this query is able to transparently spam

>several threads to execute it and hence use as many processors as

>possible or this is just executed in the session/connection "primary

>thread" and the scaling is given by several sessions/connections

>issuing statements like that...?

>

>My point is: is it paralelism achieved transparently or is something

>we have to configure and explicitly make happen e.g. creating

transaction


>table as a partitioned table.

>

>Thanks in advance,

>Best Regards,

>Giovanni

>

>