PL/SQL :: Execution Time Spread Across Various Procedures Within A Package

Jul 6, 2012

I have huge package having numerous procedures. Therein there is one wrapper procedure which is invoked from front end and which in turn calls other stored procedures within the package. The stored procedures called return the result to the wrapper procedure which in turn calls other stored procedures.

The ultimate result is returned to the calling environment by the wrapper procedure itself.I need to know the time taken in totality by the wrapper procedure along with the individual execution time of each procedure called by the wrapper procedure. I am not allowed to modify the code for adding timestamp capturing though.

View 1 Replies


ADVERTISEMENT

Executing WHOLE Package (of Procedures) At Once?

Aug 30, 2007

I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??

Its not possible but i could call each procedure from ONE procedure

View 2 Replies View Related

PL/SQL :: How To Create A Package With Procedures

Aug 15, 2013

By referring through net I have written the following PCK file. I have installed my Oracle 11g database in VirtualBox(Win XP). I can able to select the tables using query from the host(Windows 7) 

CREATE OR REPLACE PACKAGE APEX_SYS_PCK  AS    TYPE refcursortype IS REF cursor  PROCEDURE GET_USER_DETAILS(  D1 OUT refcursortype,  P_USER IN VARCHAR2,  P_PASS IN VARCHAR2);END APEX_SYS_PCK; / CREATE OR REPLACE PACKAGE BODY APEX_SYS_PCKAS PROCEDURE GET_USER_DETAILS(  D1 OUT refcursortype,  P_USER IN VARCHAR2,  P_PASS IN VARCHAR2) IS BEGIN   OPEN D1 FOR SELECT * FROM APEX.PERSONS; END GET_USER_DETAILS;  END APEX_SYS_PCK;/

 When I try to run the script I am getting the following error. How to solve this problem?

Project: sqldev.temp:/IdeConnections%23vAPEX.jprF:PACKAGESAPEX_SYS_PCK.sqlError: PL/SQL: Compilation unit analysis terminatedError(1,14): PLS-00905: object APEX.APEX_SYS_PCK is invalidError(1,14): PLS-00304: cannot compile body of 'APEX_SYS_PCK' without its specification

View 9 Replies View Related

SQL & PL/SQL :: How Many Procedures And Functions Can Be Written In Package

May 4, 2010

I think there is no limit for number of procedures and functions can be written in a package, but little much confusion.

What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?

View 4 Replies View Related

SQL & PL/SQL :: How Many Functions And Procedures Exist In Package

Apr 6, 2011

how many functions and Procedures are exist in Package. Is it possible to identify through query.

Eg: I have one Package:- ABC. This ABC package having 2 functions and 3 procedures. I want, total no.of functions and total no.of procedures through query.

View 1 Replies View Related

SQL & PL/SQL :: Retrieve Private Package Procedures Information?

Apr 16, 2013

how i cant get the private package procedures(the procedures that are defined only in the pcakage body) information?Specially the name and arguments??

Every simple Oracle client presents this type of information, so I think there is a way to get it?

View 2 Replies View Related

Client Tools :: How To See Specs / Body Of Package Procedures

Jan 27, 2011

1- I do not have access to TOAD for Oracle yet
2- I can connect to SQLPLUS: Release 9.2.0.1.0
3- We have many stored procedures in packages that are available from the TOAD for Oracle interface
4- I need to be able to see the specs/body of some packages containing some procedures.
5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?

For example: SchemaName1.PackageName1.ProcedureName1

View 39 Replies View Related

SQL & PL/SQL :: Package Variables And Execution Error

Sep 27, 2013

I have a package with pipelined function in it. using the below stmt to execute the function in Oracle 11g.

select * FROM table(Test_PKG.Test_PIPELINED ('A','--N/A--','1/1/2010','1/1/2011'));

Throwing an error: invalid month error.

CREATE TYPE WBS_ROW IS OBJECT
(
Product VARCHAR2 (100),
DESC VARCHAR (1000),
RecDATE DATE);

[code]....

View 12 Replies View Related

Longer SP Execution Time

May 5, 2011

In our production environment some SP's are executing longer duration, but when same SP is executed from PLSQL Developer client it is executing vary quickly.

View 3 Replies View Related

SQL & PL/SQL :: Execution Time Of A Query

Mar 16, 2010

I want to know that is there any way to know the execution time of a sql query.

View 1 Replies View Related

SQL & PL/SQL :: Know Execution Time Without Running Query?

Jul 13, 2008

Is there a way to know how much time will a query take to execute without running it, just like using the autotrace (traceonly) and explain plan utility.

View 16 Replies View Related

Unusual High Query Execution Time?

Aug 25, 2010

I am facing a very strange issue with one of our Oracle query. The query is usually completes in a minute or two. Even the execution plan of the query is good and it works perfect most of the times, as expected. The query fetches about 1000-2000 records each day.

But on a given day, the query takes about 30-40 mins to execute completely. Upon checking the load on DB server, there are no other processes running which can impact the run time of this query. Moreover, the record counts fetched are almost same as compared to other days. There is no pattern observed as that this phenomenon occurs. it all happens once in a while.

Configuration is Oracle 10g with RAC environment on LINUX

View 5 Replies View Related

Performance Tuning :: Avg Time Taken By Execution Plan

Apr 12, 2013

How can i check the avg time taken by an execution plan. Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.

View 7 Replies View Related

Performance Tuning :: Lost Index From Second Execution Time?

Jul 11, 2013

The problem was describe:

- First time to execute: Using all indexes on 2 tables

- Second time to execute: Using only indexes on first table, full table scan on the other

- Third time to execute: Do FTS on both of tables.

Now, I show the objects and relate information here:

The Tables:

system@dbwap> select count(*) from my_wap.news_relation;

COUNT(*)
----------
272708

system@dbwap> select count(*) from my_wap.news_content;

COUNT(*)
----------
95092

system@dbwap> desc my_wap.news_content;
Name Null? Type
----------------------------------------------------- -------- ----------------
ID NOT NULL NUMBER(11)
SUBJECT NOT NULL VARCHAR2(500)
TITLE VARCHAR2(4000)
STATE NUMBER(1)
IMGPATH VARCHAR2(500)
ALIGN VARCHAR2(10)

[Code]....

View 7 Replies View Related

Performance Tuning :: Delete Statement Is Taking More Time For Execution?

Mar 9, 2010

In my code I am using delete statement which is taking too much time to execute.

Statement is as follow:

DELETE FROM TRADE_ORDER_EMP_ALLOCATION T
WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT)
IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT
FROM LOAD_TRADE_ORDER
WHERE IND_IS_BAD_RECORD='N');

Tables Used:
oTRADE_ORDER_EMP_ALLOCATION Row count (329525880)
oLOAD_TRADE_ORDER Row count (29281)

Every column in "IN" clause and select clause is containing index on it

Every time no of rows which to be deleted is vary (May be in hundred ,thousand or hundred thousand )so that I am Unable to use "BITMAP" index on the table "LOAD_TRADE_ORDER" column "IND_IS_BAD_RECORD" though it is containing distinct record in it.

Even table "TRADE_ORDER_EMP_ALLOCATION" is containing "RANGE" PARTITION over it on the column "ARTEMIS_SOURCE_SYSTEM_ID". With this I am enclosing table scripts with Indexes and Partitions over it.

way for fast execution in of above delete statement?

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

Performance Tuning :: Increased LOGFILE Capacity Not Decrease Execution Time?

Aug 6, 2012

I'm planning to decrease the time taken to execute data by managing the redo log file but I'm kinda stuck in some aspect : > Why is my OPTIMAL_LOGFILE_SIZE is showing NULL ? > I'm trying to resize the LOGFILE capacity from 100M to 200M and I'm also adding 1 more LOG GROUP with 200M capacity too but turned out that didn't decrease my execution time.

View 12 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

Performance Tuning :: Why Elapsed Time Changed While Execution Plan And Stats Remains The Same

Jun 4, 2010

attached query giving consistent execution plan but different timings across run

SELECT /*+ INDEX (CRT CRT_CUN_FK_I)*/
DISTINCT odr.dve_id
FROM company_requirements crt, orders odr, lelo_products la_pct
WHERE crt.qtn_cun_id = 10035637--10000021--10035667
AND crt.ID = odr.crt_id_quote_implemented
AND NVL (odr.cancellation_date, '31-Dec-9999') = '31-Dec-9999'

[code]....

we have 4 databases, 2 on each servers, such that db1 and db2 on server1 and db3 and db4 on server2

refer count of the records for column of biggest table in the query, taken on all 4 databases (The column is nullable)

select count(*) from company_requirements crt WHERE crt.qtn_cun_id = 10035637
db1 = 73335
db2 = 89073
db3 = 81182
db4 = 82936

First I executed the query on db1 and db2 while there wasn't any user logged on to the system

db1
**********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 17.47 473.39 85704 1508102 0 0

[code]...

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 85704 0.31 460.55
latch free 1 0.00 0.00
SQL*Net message from client 1 14.98 14.98

[code]...

Why the elasped time changed when data and plan hasn't changed at all? Also why the plan has different stats for round 1 and 2 on db1 and db2?

I ran it 2 times each round each database so hard parsing shall not be issue.Also why the number of rows accessed are different in db1,db2 and db3,db4 especially for step1 when count of crt.qtn_cun_id is similar?

In fact when the query was taking long I was the only user on the system Also I used hard coded value (no bind variables at all)

I checked num_rows, distinct keys as well which are quite similar across all 4 databases Also no stats where gather during the query execution

What I should have checked or monitored?

View 10 Replies View Related

Forms :: Oracle Spread Table OCX

Nov 17, 2005

Any example of how to load data into an oracle spread table OCX control

I have successfully inserted the the oracle spread control in a form and also created the program units form the control but unable to load data into the it at run time.

View 10 Replies View Related

Forms :: How To Connect Query With Spread Table

Nov 2, 2010

how to connect query with Spread Table or load table data in spreadtable by using forms 6i

View 6 Replies View Related

Server Utilities :: Impdp - Package Body Import Taking Huge Time?

Sep 13, 2012

I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).

I have tried both parallel and non-parallel.how to improve speed of the package body import.

Details about the Schema & Import No. of objects in Schema

SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);

OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1

ORA-00933: SQL command not properly ended

View 3 Replies View Related

PL/SQL :: Find Out In Database Package Where Package Is Installed

May 28, 2013

I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').

What I would need is something like $$PLSQL_UNIT

View 8 Replies View Related

SQL & PL/SQL :: Global Exception In Package Available Outside Package?

Jan 15, 2012

I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:

e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);

Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?

View 4 Replies View Related

How To Use Member Procedures

Dec 13, 2009

create type employee is object(
name varchar2(30),
member procedure change_name(new_name varchar2)
)

[Code]....

Now how to use the change_name procedure to change the name of the employees in the table?

View 1 Replies View Related

PL/SQL :: Drop All Procedures At One Go?

Aug 29, 2012

I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.

ORACLE 11G
I am using sql developer 3.2 version tool

View 6 Replies View Related

Monitoring Runtime Of Procedures

May 23, 2013

runtime monitoring procedures....need a sample script

View 1 Replies View Related

Procedures Are Not Running Paralleled?

Mar 17, 2011

I have a job that runs two procedure parallely . The code is as follows:

DECLARE
jobno1 NUMBER;
jobno2 NUMBER;
flag NUMBER;
BEGIN
dbms_job.submit(jobno1,'pc;', next_date=>SYSDATE);
dbms_job.submit(jobno2, 'pc2;', next_date=>SYSDATE);

[code]....

And the stored procedure (in SQL File) is as follows:

procedure pc:

create or replace procedure pc
as
begin
for idx in 1..100 loop
dbms_output.put_line(idx);
end loop;
end;
/

procedure pc2:

create or replace procedure pc2
as
begin
for idx in 1..1000 loop
dbms_output.put_line(idx);
end loop;
end;
/

However, when I execute the job , I get the following output:

1
2
3
.
.
.
.
100

[code]....

Which indicates the job 1 got executed 1st and then job2. But this is not what I want.

View 4 Replies View Related

Modifying Table Through A Procedures?

Aug 28, 2007

whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob

how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?

View 6 Replies View Related

SQL & PL/SQL :: List All Procedures Running On DB?

May 4, 2010

we are doing a performance tuning on a large database. Our main aim is to capture all the packages/procedures/view that are running on database. Below given is the format we have prepared

Eg:
SPIdSP_Name CPU TimeI/OLine Executed
1Emp_AllDetails221
1Emp_AllDetails582
1Emp_AllDetails25103

[code]...

Requirement:Write an Procedure that captures the above given information in the background of database and stores the information in a table.write the procedure to capture the above given information.

View 17 Replies View Related







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