SQL & PL/SQL :: Oracle 11g - Parallel Execution Of Procedure

Jul 28, 2010

Oracle 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.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Parallel Execution Of A Procedure?

Jul 22, 2010

Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)

I am using Oracle 9i.

View 2 Replies View Related

How To Avoid Parallel Execution In Database

Dec 5, 2012

In 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

View 3 Replies View Related

SQL & PL/SQL :: ORACLE Error 6550 In FDPSTP - Run Parallel All Procedure

Feb 15, 2012

I 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"

View 11 Replies View Related

SQL & PL/SQL :: ORA-12842 / Cursor Invalidated During Parallel Execution

Sep 17, 2010

I 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?

View 4 Replies View Related

SQL & PL/SQL :: Parallel Degree Proportionate To Number Of CPUs Used During Execution

Nov 30, 2011

In 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 ?

View 9 Replies View Related

Server Utilities :: Execution Of Oracle 11g Wrapped Procedure Gives Ora-00900 Error

Apr 4, 2013

I 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.

View 4 Replies View Related

SQL & PL/SQL :: How To Run Procedure In Parallel

Dec 28, 2012

we 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.

View 11 Replies View Related

Function And Procedure Execution

Jul 7, 2010

I 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.

View 3 Replies View Related

SQL & PL/SQL :: To Generate Procedure Execution LOG

Sep 3, 2013

I 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 Related

SQL & PL/SQL :: Procedure And Function Execution

Jul 8, 2010

I 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 Related

SQL & PL/SQL :: Dropping A Procedure During Execution

Apr 26, 2012

When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .

View 5 Replies View Related

SQL & PL/SQL :: Write Log When Procedure Execution Is Successful

Aug 10, 2011

My 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".

View 12 Replies View Related

SQL & PL/SQL :: Stored Procedure Execution Jump To End

Mar 13, 2013

I 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].......

View 7 Replies View Related

PL/SQL :: Return Results Before Procedure Execution Completes

May 21, 2013

Is 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.

View 4 Replies View Related

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

If 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?

View 5 Replies View Related

Server Administration :: Are Parallel Queries Supported In Oracle XE

Nov 18, 2010

I'm not sure is parallel query supported in xe ?

View 2 Replies View Related

Forms :: Communication Form Serial / Parallel Port Via Oracle?

Jun 14, 2005

I want to connect a biometric device with my system, through serial port / parallel port. And want to permit only valid users to the system.

View 18 Replies View Related

SQL & PL/SQL :: Incorporate Parallel Loading To Load Data Into Database - Oracle Locking Not Working?

Aug 3, 2011

At the moment, we were loading the file in our system serially. This is a very old and established system.We would like to incorporate parallel loading for our loaders to load data into the database.

Most of the issues would be due to multiple inserts happening due to the files being loaded in parallel. For some reasons, we cannot give regular commits untill the entire batch of items is processed in case the process needs to rollback. A file can contain different set of batch of items clubbed together for loading.

The issue here is untill the first file finishes loading and commits, the second file would just hang. In fact, mulitiple files might hang for the first file to finish. what can I do to overcome this?I tried to used "lock table t1 in SHARE ROW EXCLUSIVE mode nowait". When the leading process is doing inserts, the failing process will fail with a resource busy and acquire with NOWAIT specified. We would catch this exception and redirect that batch to an error file to be reloaded at a later date.

View 15 Replies View Related

SQL & PL/SQL :: Execution Of Blocks In Oracle

Sep 26, 2010

I have a block of code that looks something like this, I'll write it in pseudo code to avoid pasting 100's of lines of

IF <condition> THEN
FOR record in (select query here..)
LOOP
--add data
END LOOP
END IF;

IF <condition> THEN
--do soemthing
END IF;

IF <condiiton> THEN
--do soemthing
END IF;

Now, in the 1st IF statement there is a for loop which basically builds up a tab type array - this takes at least an hour to execute based on the select query which returns 1000's of records.

The strange thing is, that my log files shows that at the time the procedure was executed, the stuff in the 2nd IF statement was executed almost straight away.. is this even possible?

If the loop in the 1st IF statement takes over an hour to finish, how is it possible for the 2nd IF statement stuff to process straight away? My log files show me that the loop in the 1st IF statement was going on for a good hour.. yet the 2nd IF statement was executed straight away.

View 7 Replies View Related

Performance Tuning :: Oracle 11g Alternates Between Two Execution Plans?

Apr 3, 2012

We have a query which makes Oracle behave very strangely. It is a straight-forward join between four tables of about 30.000 rows each, with some simple comparisons and some NOT LIKE:s.

When we run this query, it either takes about 1 second or more than 1.000 seconds to run and return the approximately 5.000 rows of the result. If we run the same query over and over again, it fluctuates back and forth between two different execution plans, apparently at random, 3 times out of 4 selecting the 1.000 second version and 1 time out of 4 the 1 second version.

There are no other connections to the database, the schema is not modified, the data is identical, the query is identical, and the response is identical, but the execution time alternates between 1 second and 1.000 seconds.On the same database instance we have another schema which is identical, but with slightly less data, which is used for development. The 1.000 second run times did not happen in that schema, but only in the test system's database.

Therefore we would REALLY like to understand what happens and why, so that we can avoid triggering this in the future. We could try locking the 1 second execution plan, but then we're afraid of doing the same thing wrong again in the future.

Here are the two execution plans that Oracle switches between, more or less at random:

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15663 pr=10536 pw=0 time=855673 us cost=82273 size=2707430769293 card=14028138701)
79272 79272 79272 TABLE ACCESS FULL GROUPS (cr=1008 pr=0 pw=0 time=22154 us cost=277 size=10693 card=289)

[code]...

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15664 pr=0 pw=0 time=778178696 us cost=30838477 size=741611997206725 card=3842549208325)
375411 375411 375411 TABLE ACCESS FULL GROUP_GROUPS_FLAT (cr=3782 pr=0 pw=0 time=51533 us cost=1029 size=25152738 card=375414)

[code]...

The query:

select g.ucid, a.ucid
from account a, groups g, group_members gm, group_groups_flat ggf
where a.ucid = gm.ucid_member
and gm.ucid_group = ggf.ucid_member

[code]...

And excerpts from the schema:
CREATE TABLE "PDB"."GROUPS"
(
"UCID" VARCHAR2(256 BYTE),
"UNIX_GID" NUMBER(*,0),
[...]

[code]...

View 4 Replies View Related

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

Feb 6, 2013

Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.

View 2 Replies View Related

Server Utilities :: Execution Series Of Application Through Oracle Trace Events

Feb 20, 2013

Would like to know the execution series of an application(From a particular screen) through Oracle trace events.

View 1 Replies View Related

Performance Tuning :: Query Taking High CPU And Execution Time In Oracle 11gR2

Dec 24, 2012

The below query is taking high CPU almost 98% and longer time to execute.

SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....

View 15 Replies View Related

Export/Import/SQL Loader :: Server Connecting Oracle DB Using Linked Server Execution Delay

Jul 24, 2012

I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay?  I have set RPC out (true).

Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds._

Another case when I have both servers on the same machine it executes in milliseconds. I have tried Following methods in SQL SERVER.

*1, Using OPENQUERY:*
SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)

*2, Using Exec:*
DECLARE @sql NVARCHAR(MAX);
SET @sql =(’Select * from OracleTableName ‘);
EXEC (@sql) AT Linked Server Name ;

How to reduce the time delay caused for the execution?

View 0 Replies View Related

Reports & Discoverer :: Automate Oracle Reports Execution

Feb 10, 2012

Any way to automate a job to execute several Oracle Reports to run at the end of every month and either email them to a list of people or possibly save them to a location on a network drive.

At first, I started out with 2 avenues of thought:

1) The following link raised the option of an Oracle Scheduler, but from my research, the scheduler only runs database jobs, shell scripts, batch files, etc.:

[URL]......

2) I was thinking about possibly creating a database job of some sort to call the reports, but from the following link, I've determined that one cannot create+call a stored/database procedure to run Oracle Report files. So there goes my second hope.

[URL].......

So, it appears that the scheduler won't meet my requirements and now I think I'm almost out of options.

My last resort is creating a database job that queries required information and spooling the data + various formatting "stuff" into a txt file. Then, that txt file would be my report. This seems a little savage and I would like to think that there's a ay to automatically run an .REP against a database and save an electronic copy of the report somewhere.

Is it possible to automate the execution of Oracle Reports and then saving the report in a particular format (xls/pdf/rtf/etc.)?

View 1 Replies View Related

SQL & PL/SQL :: Union Run In Parallel

Sep 30, 2010

I 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 Related

Process Queries Parallel?

Feb 2, 2005

On 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 ?

View 1 Replies View Related

SQL & PL/SQL :: Partition Truncation Parallel?

Dec 15, 2011

I 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?

View 1 Replies View Related

SQL & PL/SQL :: How To Find Degree For Parallel DML

Feb 12, 2013

I 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.

View 24 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved