Dear all,
I am looking to convert a very complex SQL in my application to PL/SQL using pipelined functions. I then came across parallel pipelined functions and wondering if it would be a good fit. Some questions on my mind are :
1. Can I parallelize if the parameters are as below?
2. My REF_CURSOR is going to be a query with atleast 2 joins. Can I still parallelize? Do hints work when other parameters are turned off and are hints possible when there are joins?
3. REF_CURSOR gets atleast a "Soft Parse" - how do I decide if static cursors with serial pipe Vs parallel pipe with REF CURSOR?
4. Is it efficient to call a parallel pipeline function inside another pipelined function?
Step 1: My client code passes only a parameter to pipelined function.
Step 2: Do a few calculations
Step 3: Actual CURSOR to be executed and further calculations to be done
For the above steps, I am thinking of calling a pipelined function within which a parallel pipelined function to implement Step 3 (this is where bulk of processing happens and am thinking parallelization will help)
Details of DB Version & parallel parameters in my installation (please note that I cannot change these parameters):
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 1280
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 512
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
Thanks,
Srini