SQL & PL/SQL :: Parallel Execution Of A Procedure?
Jul 22, 2010Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)
I am using Oracle 9i.
ADVERTISEMENT
SQL & PL/SQL :: Oracle 11g - Parallel Execution Of Procedure
Jul 28, 2010Oracle Version is 11g.
I need to call the same procedure with different parameter multiple time in parallel.
I have job_control Table
CREATE TABLE JOB_CONTROL
(
JOB_CONTROL_ID NUMBER NOT NULL,
JOB_SEQ_NO NUMBER NOT NULL,
MODULE_NAME VARCHAR2(32 BYTE) NOT NULL,
JOB_STATUS VARCHAR2(15 BYTE),
NO_OF_RECORDS NUMBER,
PROCESSED_RECORDS NUMBER
);
Insert into JOB_CONTROL
(JOB_CONTROL_ID, JOB_SEQ_NO, MODULE_NAME, JOB_STATUS, NO_OF_RECORDS)
Values
(20, 1, 'SALES', NULL, 5);
Insert into JOB_CONTROL
[code]........
Now the table look like this.
JOB_CONTROL_IDJOB_SEQ_NOMODULE_NAMEJOB_STATUSNO_OF_RECORDSPROCESSED_RECORDS
201SALES5
212SALES5
223SALES5
234SALES5
245SALES5
256SALES3
based on the data available in table i wanted to execute a procedure 6 times
create table job_table
(now date, seq_no number)
the procedure will have one parameter as job_seq_no
create of replace procedure job_call (i_job_seq_no number)
is
begin
for i in 1.. i_job_seq_no
loop
insert into job_table
values
[code]......
the problem is i want to do this activity in parallel , i.e. all six calls to the procedure would be parallel. and this will be one time activity.
How To Avoid Parallel Execution In Database
Dec 5, 2012In my database is present undesirable parallelization of the query. All involved tables has degree=1,
if I right understood there is no method to avoid this except reducing of degree of indexes ? I'm using 11.2.0.3
SQL & PL/SQL :: ORA-12842 / Cursor Invalidated During Parallel Execution
Sep 17, 2010I have received ORA-12842: Cursor invalidated during parallel execution error in my Pl/sql script. In pl/SQL 4MVs are used those are refreshed/refreshing while Pl/sql script start executing
Script is also dropping and creating table and index before actual processing starts.
Is that error coming because MVs are refreshing and they are not ready for use or some thing happened between actual processing and dropping/creating the table?
SQL & PL/SQL :: Parallel Degree Proportionate To Number Of CPUs Used During Execution
Nov 30, 2011In my recent performance improvement tasks I was experimenting with Parallel hints. I have been getting good results with them, however the question arises "How is Parallel degree proportionate to number of CPUs used during execution?
/*+ parallel(emp_tab,8) */
Oracle Version: Oracle 10g
OS : UNIX
Client OS: Windows XP
V$PARAMETER VARIABLES as follows:
Total CPUs available: 10
No of CPUs per core: 2
Max parallel : 100
Min Parallel : 5
Question 1: So how do I map the degree of 8 with CPUs. I believe that degree of 8 does not mean its going to use 8 CPUs. If so, how do I prove it. I tried SQL_TRACE and cpu comes 0. And I believe its not the CPU count.
Question2: How do I know how many CPUs were used to execute a SQL query ?
SQL & PL/SQL :: How To Run Procedure In Parallel
Dec 28, 2012we have many tables in Cursor For Loop :
CREATE OR REPLACE PROCEDURE EBILL_BULK_UPDATE_SERVICE(in_cycle VARCHAR2)
AS
v_cnt NUMBER; -----Variable used for checking table is partitioned or not partitioned
CURSOR cur_update -----Cursor defined for Updating EBILL tables for service_id
is
SELECT table_name
, cycle_name
FROM NNP_EBILL_UPDATE
[code]....
As our requirement that Execute Immediate should work for 5 or more tables updation parallely at a time.If one table get completed then it should take next table from loop and then start the code till completion of all tables.
SQL & PL/SQL :: ORACLE Error 6550 In FDPSTP - Run Parallel All Procedure
Feb 15, 2012I am creating a package. In case my package, i am want to run parallel all procedure(FillAndSend_IM1,FillAndSend_IM2,FillAndSend_IM3,FillAndSend_IM4 ) .
in package i using dbms_job.submit,
dbms_job.submit( l_job, 'FillAndSend_IM1;' );
dbms_job.submit( l_job, 'FillAndSend_IM2;' );
dbms_job.submit( l_job, 'FillAndSend_IM3;' );
dbms_job.submit( l_job, 'FillAndSend_IM4;' );
when procedure is runing , found error "ORACLE error 6550 in FDPSTP
Cause: FDPSTP failed due to ORA-06550: line 1, column 93:
PLS-00201: identifier 'FILLANDSEND_IM1' must be declared"
Function And Procedure Execution
Jul 7, 2010I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.
how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.
SQL & PL/SQL :: To Generate Procedure Execution LOG
Sep 3, 2013I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
View 21 Replies View RelatedSQL & PL/SQL :: Procedure And Function Execution
Jul 8, 2010I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.
View 2 Replies View RelatedSQL & PL/SQL :: Dropping A Procedure During Execution
Apr 26, 2012When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies View RelatedSQL & PL/SQL :: Write Log When Procedure Execution Is Successful
Aug 10, 2011My need is to check whether procedure execution is successful and write sysdate and procedure name into log table.
Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".
SQL & PL/SQL :: Stored Procedure Execution Jump To End
Mar 13, 2013I created the following stored procedure which I am calling from a script. I compiled my Stored Procedure with Debug Info. For some reason Execution jumps from the second BEGIN to the END statement.
Since the SP compiles w/o any errors, I suspect I have a logic error.
Stored Procedure:
CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
VALIDATED OUT int,
LAST_NAME IN VARCHAR2 DEFAULT NULL,
FIRST_NAME IN VARCHAR2 DEFAULT NULL,
DOB date DEFAULT NULL,
PAT_NUMBER OUT int,
FACILITY_KEY OUT CHAR
)
AS
BEGIN
/* SELECT * */
BEGIN
[code].......
PL/SQL :: Return Results Before Procedure Execution Completes
May 21, 2013Is there any way of returning output parameter values to calling environment before completion of procedure execution. I may achieve it by using GTTs, looking for any other way (because calling environment again need to issue select statement to retrieve data from GTT).
Example case:
Procedure have multiple ref cursors as out parameters.
....
...
if exp1=exp2
then
open v_ref_var1 for select ...from ... ;
end;
[code]..........
If the first if condition satisfies, ref cursor - v_ref_var1 data should be immediately available for the calling environment.
Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?
Apr 30, 2012If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?
In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?
Server Utilities :: Execution Of Oracle 11g Wrapped Procedure Gives Ora-00900 Error
Apr 4, 2013I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
SQL & PL/SQL :: Union Run In Parallel
Sep 30, 2010I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs, Is there any way I can make these clause to run in parallel?
View 13 Replies View RelatedProcess Queries Parallel?
Feb 2, 2005On a tab page should be displayed the result of four indifferent queries, each based on a stored procedure.At the moment, the queries are processed serially, by the statements:
GO_BLOCK('one');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
GO_BLOCK('two');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
Is there a way to processes the queries parallel ?
SQL & PL/SQL :: Partition Truncation Parallel?
Dec 15, 2011I have to truncate two partitions from the same table parallelly at a time.
ALTER TABLE tablename TRUNCATE PARTITION partitioname1;
ALTER TABLE tablename TRUNCATE PARTITION partitioname2;
Huge data is present in each partition.is this possible, means it does not lock table for another alter truncate partition statement?
SQL & PL/SQL :: How To Find Degree For Parallel DML
Feb 12, 2013I am inserting 50 million records into a table MAIL_LOG. I am using the hint /*+ append parallel (MAIL_LOG, 12) */. But for my table degree is 1.
SELECT table_name, degree
FROM user_tables
WHERE table_name = 'MAIL_LOG';
I have following clarifications.
1) What degree I should use.
2) On what basis I have to give the degree.
3) Have we use constant degree all the times.
4) How to check my insert statement is using parallel degree.
5) How to find the degree at session level.
Parallel Keyword In Expdp
Dec 17, 2012i am trying to export table using datapump in oracle 10g, this expdp takes 5 hours time, so i want use use parallel keyword in expdp,
my question is how should i know number of parallels can i use...?
PL/SQL :: How To Run DBMS_JOB In Parallel And Serial
Jul 26, 2012I have total 8 procedure to run in parallel . and after that my 9th procedure should run.
below is my job submission procedure
create or replace procedure DURATION_ALARM_WEEKLY as
l_job number ;
begin
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_OUT ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_IN ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_DURATIN_OUT ; end;');
[code].......
what is the syntax I have to do in my FINALE procedure . using DBMS_ALERT.REGISTER , DBMS_ALERT.WAITANY .....?
Get A Query To Run In Parallel Using Index
Jun 21, 2012oracle: 10.2.0.5.7...I can get this to work, but not the way the docs seem to say. I am wondering if I am reading the docs wrong or missing something.
The docs seem to say to get a query to run in parallel using an index you use the PARALLEL_INDEX hint. This doesn't seem to work for me. I have to do one of the following
1. change the parallel degree with an alter index, then use the PARALLEL hint (parallel index hint does nothing in this case)
2. use both the parallel_index and parallel hint
Parallel Query Option
Oct 23, 2013Just a general query on parallel query. My customer having 4 cpus and running the database in 11.2.0.3 in AIX 5.3(One is in AIX 6.1). Under which circumstances, we can propose to user parallel query options.
View 2 Replies View RelatedStarting A Function In Parallel Using DBMS_JOB
Jun 30, 2011We have Data Migration for our application coded in PL/SQL. The DB server has 64 Cores available (Solaris 10 OS) however running the migration code written as a function, utilizes very little CPU and CPU utilization is to max 2%. To utilize CPU power available to increase the speed of migration, we are using DBMS_JOB to schedule this function multiple times.
However scheduling the function 10 times, we are seeing that at any moment only 4/5 oracle processes are active and utilizing the CPU and CPU utilization has gone up to 5-6%. The speed of migration is increased but not to a great extend which I feel would work if we could utilize more CPU.
I see a parameter job_queue_processes is set to 10 currently in the database and am planning to increase this (currently to 25 as I don't have exact count of how many other jobs may be running in the database).
Running Parallel / Concurrent Scripts?
Feb 24, 2011I am trying to execute two scripts at the same time (concurrent) in Oracle SQL Developer. I know we can schedule a job using DBMS_job package and define the job. But is there any other way of doing it using Threads ?
View 3 Replies View RelatedParallel Query Block Of Union All Set
Sep 24, 2010We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.
Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.
We have to retrieve data from all these tables as follows:
select a1, a2, a3, a4, a5, a6
from A
where < logical filter condition>
union all
select b1, b2, b3, b4, b5, b6
[code].....
We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.
How does this above query able to execute each query block in parallel?
Parallel Insert In Subpartitioned Table
Dec 29, 2010At work we have a linux server with Oracle 10.2.0.5 g, and we have the need, to save time, to use parallel process launched in background (&) or (at now).
The table that we are going to insert is partitioned for date and subpartitioned for name of file_source, and we are having a lot of problem with this.
Our DBA staff say that is impossible to work in this way but for me, after ten year of oracle-work, sounds strange , sure may be that we don'r use really really correctly this procedure but i suppose that Oracle is able to recevie 20 ask of insert on the same table.
The situation is that we usede this procedure in other server and it's work but the answer of our DBA-staff is that we have less data storage and so on. In the end some times this proceduers have a low time of execution and some times no, this goes out of my ability to find out the problem.
We have a DWH with STAGING AREA, ODS Level, DDS Level and DM Level, the Staing area is paritionend for file_source, the ODS Area is partitioned by range (date) and subpartitioned for name (file_source), the DDS area is partitioned by range(date) and subpartitioned for Origin system(that include all the file_source) and the DM area is only partitioned by date.
So the most big problems that we meet are between the Staging are and ODS area, and between the Ods area and the DDS Area, the most important thing is that this table (DDS) is a monster of near 500.000.000 of rows (ITr of data) but we look only at the date to elaborate.
The solution is clear, divide this table in two, one online and one of storage as usually and correctly a normal situation require, but unfortunatly is a situation that we Erhedit from an old system and at the moment is not approoved the change request on this site.
The really strange thing is that sometimes work and some times not, without understand the cause of this. My opinion on this is that the DB is not correctly configured but the System Staff say that everything is correct and there are no problem. My first problem is to understand, if possible, wich is the limit of this way to operate, can i insert in a subpartition in the same time with twenty parallel process that write on same partition and different subpartition? Is correct to act in this way to save time about the data-load or better doing it one by one? On my experience i realized that Oracle can manage(is his work) a lot of request in the same time, but in this DB that we are using i continue to see problems that sounds like if we are usuing a tool that is not working in the correct way...
May be we went beyond some limit but in the end are less then 5.000.000 records per day that we move i think that a DWH have to support more than this...
How To Check If Parallel Enable In Session
Aug 18, 2010I need to know if PARALLEL is enabled in my session. Would this be a session parameter or something else? Is there a view I have to query or some SQL*Plus command to execute?
P.S. Is there a way to correct the title spelling after submitting.
How To Control File Parallel Write
Mar 2, 2011We have a database that is accessed by ArcSDE, a product to modify maps. It uses BLOBs to store those maps.
We ran a load on the server and the response time was slow. By running the following query:
select event, total_waits, time_waited, avg_ms, round(ratio_to_report(time_waited) over () * 100) percent
from (select substr(event, 1, 30) event, total_waits, time_waited, round(time_waited_micro / total_waits / 1000, 2) avg_ms
from v$system_event
where wait_class in ('System I/O') union
select 'CPU' event, NULL, value, NULL
from v$sysstat
where statistic# = 12
order by 3 desc)
where rownum <= 10;
I get
EVENT TOTAL_WAITS TIME_WAITED AVG_MS PERCENT
--------------------------- -------------- ------------- -------- ---------
control file parallel write 127187 6354909 499.65 70
CPU 988274 11
db file parallel write 20461 886442 433.23 10
log file parallel write 14987 870672 580.95 10
log archive I/O 1557 18094 116.21 0
control file single write 149 10590 710.71 0
control file sequential read 136502 5219 .38 0
log file single write 56 2511 448.41 0
log file sequential read 489 492 10.05 0
BUG: 733426 says to change the event="10359 trace name context forever, level 1"