Time Spent Using Cursors?
Jun 17, 2013
I have two methods using pro*c to execute a query SQL and read the results. The query is a "select from" a view, this view is a union between two tables. The first method creates a cursor and fetches the results line by line. A second method creates a cursor and fetches the results in parts.
Method 1)
CODEEXEC SQL FETCH Cursor1 INTO ...
Method 2)
CODEEXEC SQL FOR :numLines FETCH Cursor1 USING DESCRIPTOR areaDescSQL;
I had executed this methods in my development enviroment, using Oracle 11g, Suse linux. As I expected, the method 2 spent much less time than the method 1. But, in the "customer enviroment", using another database, Oracle 11g and HP-UX, these methods spent almost the same time.
Is there some Oracle's configuration or parameter's settings that maybe explain that? What configuration can be differente between the two databases? What can I do to improve the time spent?
View 1 Replies
ADVERTISEMENT
Jan 16, 2012
i'm keep on getting below error every morning in my Database.this indicate in Grid control every morning.
Metrics "Database Time Spent Waiting (%)" is at 34.336 for event class "Concurrency"
Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
Metrics "Database Time Spent Waiting (%)" is at 44.78 for event class "Other"
i need to know, this is something that i should consider as a critical warning and how can i solve this .
View 1 Replies
View Related
Dec 4, 2012
Most of my day to day work involves the support of DB2 on AIX and z/OS. I support the database infrastructure for one business system that runs on the Oracle RDBMS. The application is Oracle Transportation Management. The non-production environments don't get much traffic. If I sign into the TEST or DEV Enterprise Manager DB Control screen, the following warning is almost always listed on the main page:
Metrics "Database Time Spent Waiting (%)" is at 44.00384 for event class "Concurrency"
Enterprise Manager reports that this metric is continuously fluctuating above and below the warning threshold of 30% of db time when the environment is idle.To investigate(and I am no oracle expert, far from it), I ran the following:
SELECT * FROM v$session_event
WHERE WAIT_CLASS='Concurrency'
ORDER BY TOTAL_WAITS DESC;
By far, the top two entries in the result are the following:
SIDEVENTTOTAL_WAITSTIME_WAITEDAVERAGE_WAITMAX_WAIT
124os thread startup12041423733.5218
359os thread startup150653433.5521
[code]...
Does this indicate a possible problem with the operating system?
View 2 Replies
View Related
Jul 7, 2010
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;
Any way to do this?
View 4 Replies
View Related
Aug 13, 2013
an alternative to Oracle Cursors.My table has huge amount of data and is taking time in processing. I had thought of using BULK COLLECT but it can only be used to insert, delete or update data and will not allow me to select data.
View 33 Replies
View Related
Mar 20, 2013
declare
type ref_cur is ref cursor;
r ref_cur;
enam emp%rowtype;
dno dept.deptno%type;
begin
dbms_output.put_line('The Employee details are');
open r for select deptno from dept;
loop
fetch r into dno;
[code]....
Error at line 1
ORA-06550: line 12, column 28:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
. ( % ;
if i need to use ref cursor to send parameters, is it possible? if yes how to use it?
View 3 Replies
View Related
Feb 22, 2012
i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
View 4 Replies
View Related
Apr 19, 2012
My requirement is like as follows,
declare
v1str varchar2(100):='select empno,ename from emp';
v2str varchar2(100):='select empno,ename,sal from emp';
type t_array is varray(2) of varchar2(100);
[Code]....
So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.
View 21 Replies
View Related
Jun 18, 2013
How can we pass multiple parameters to cursors?
Ex: Cursor C_employees(C_empid number, C_cityname varchar2) is select emp_name, office_name from employee where employees where empid = c_empid and city = c_city_name;
I know we can pass one parameter to the cursor but I do not know how to pass multiple parameters.
View 8 Replies
View Related
Nov 16, 2011
I have a table table1 with 2 crore records.
select * from table1;
id code updateddatetime
------------------------------------
1 10001 2011-10-21 15:31:21.390
2 10001 2011-10-21 15:31:22.390
3 10001 2011-10-21 15:31:21.390
4 10001 2011-10-21 15:31:22.390
5 10002 2011-10-21 15:31:22.390
I want to delete records like id 2 which has odd updated time which is more than id 3 updated time.
is there any alternatives without using cursors as it taking so much time to process.
View 12 Replies
View Related
May 31, 2011
homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.
ASSIGNMENT:
1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table
where id=id on grade table)
Rule:
A
‐ LastName ends with a character between A‐F
B
‐ LastName ends with a character between G‐K
C
‐ LastName ends with a character between L‐P
D
‐ LastName ends with a character between Q‐T
E
‐ LastName ends with a character between U‐Z
Create TABLE Registration (RegistrationID number(10), SectionID number(10), CourseID number(10),
SectionNumber varchar2(10),
StudentID number(10), FirstName varchar2(20),
LastName varchar2(20), CourseNumber varchar2(20), CourseName varchar(20));
[code].....
View 20 Replies
View Related
Oct 3, 2011
DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0
I have the following four tables:
tab_main- which lists main projects
tab_sub_main - which lists sub projects
tab_budget - amounts per projects/subprojects
tab_total - I want to load the data here
The table script with data is attached.
I want to load data into tab_total fields for prj_type= 'J' as follows:
1. accn_no from tab_main table.
2. fy from tab_budget table
3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy
4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no
5. all the audit fields- date/user inserted/updated will come tab_budget table
how to create this procedure with cursors.
CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC
IS
CURSOR C IS
select distinct m.accn_no, a.control_no,m.prj_type,
b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod
from tab_main m,
tab_sub_main a,
[code]....
CREATE TABLE tab_main
(
ACCN_NO NUMBER(7) NOT NULL,
PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL
)
/
Insert into TAB_MAIN
(ACCN_NO, PRJ_TYPE)
[code]....
View 34 Replies
View Related
Mar 14, 2013
I am trying to write which will return cursors to front end program.The logic which I got stuck is while exiting the procedure.I am having some cursors in teh procedures which are read by the front end script.I have one condition which when satisfied I have to exit from the procedure.But the constraint I am facing is if I exit at that logic, oracle closes all the cursor and front end script is not able to read teh cursor and hence throws an error.Is there any way to exit the procedure without closing the REF cursors.
View 3 Replies
View Related
Apr 30, 2010
I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.
DECLARE
procedure DocInfoCur(myWhere IN varchar2) IS
init_dept_id number;
init_dept_name varchar2(40);
rcvg_dept_id number;
rcvg_dept_name varchar2(40);
CURSORCUR_DOCINFO IS
SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT,
I.DOCUMENTSUBJECT, I.DESTINATIONDEPT
FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D
[code]...
This displays all the records out there.
Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???
View 1 Replies
View Related
Oct 23, 2006
I think this is the scariest error I've encountered in oracle. After some thime of developing/debuggin I've always reached this error. This leaves me no choice but to restart the java app server, in that case I'll be affecting all the other applications deployed on it which is a very bad thing for me.
Is there anyway I could solve this, I've made it sure that I've handled all errors so that I could always close the connection. Simply put, for every termination case of my program I've placed a closing statement just to be sure that no connections are left open. But even after this, I still experience the ORA 0100 error.
View 8 Replies
View Related
May 27, 2011
how to print cursors? I mean like columnname : value. of couse curosr.columnname gives what I want. but I want genrally used way.
CREATE OR REPLACE PROCEDURE PROC_TEMP
IS
BEGIN
FOR C1 IN ( SELECT sbjct_id,sbjct_step
FROM RND.SBJCT_ORDR M) LOOP
DBMS_OUTPUT.PUT_LINE('c1 : '||c1);
END LOOP;
END;
View 3 Replies
View Related
Feb 17, 2012
Why exceptions does not raise in cursors declared by user like in the following program
create or replace function sal (p_id employees.department_id%Type) return number is
----sal1 employees.salary%type:=0;
cursor cx is
select * from employees where department_id=p_id;
begin
for i in cx
[code]...
View 13 Replies
View Related
Jul 21, 2011
I loop through the 1st cursor (account_csr), while in the 1st csr loop, based on some conditions being true, I want to loop through a 2nd cursor (acctper_csr) but I only want to retrieve data/rows in the 2nd cursor where the account_id column in 1st cursor = account_id column in the 2nd cursor. This will enable me to pull all the account_periods for each account I loop through in the first cursor.
I have attempted several different ways and cannot make this work. Thought I could somehow define a variable and store the account_id from 1st cursor and use on the 'where' clause in the 2nd cursor definition. Have not been able to make this work successfully.
Following is the sample of my
--First cursor (accounts)
CURSOR account_csr is
SELECT *
FROM s_dev_xref1.account A
WHERE a.source = 1
[Code]...
View 7 Replies
View Related
Apr 9, 2013
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is takinghere is the detailed program ... any body can tell why this is not taking and tell me how to pass the values through it..
declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
[code]....
View 4 Replies
View Related
Jan 26, 2012
I wanted to know how to find the links between cursors that have been created in v$sql after compiling a PLSQL procedure.
For example, if i compile the following procedure :
create or replace
PROCEDURE PROCEDURE1
AS
BEGIN
insert into t values(1,1);
END PROCEDURE1;
, the 2 following cursors are created in v$sql :
select SQL_ID, SQL_TEXT from v$sql
where sql_id in ('71pj8t5nz1d80','2s567zb6684sh');
"SQL_ID""SQL_TEXT"
"2s567zb6684sh""BEGIN PROCEDURE1; END;"
"71pj8t5nz1d80""INSERT INTO T VALUES(1,1)"
Thus, i would like to know how to find that the cursor 71pj8t5nz1d80 is called/linked by the cursor 2s567zb6684sh.
This would be useful for interpreting some sql statistics in v$sqlstats in where such cursors appear and related sql statistics are accounted twice.
View 1 Replies
View Related
Sep 4, 2010
I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.
cursor c1
is
select distinct roles
from table1
where flag = '1';
cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop
here im updating the roles im picking to to a suffix and role.
update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.
for j in c2
loop
here im deleting all the roles that are not part of my comparing table.
delete from table1
where role = 'something';
But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.
View 12 Replies
View Related
Mar 23, 2010
How to Close All open cursors in other session.
NAME TYPE VALUE
------------------------------------ ----------- ---------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 500
session_cached_cursors integer 50
Ex. as above we have 500 max cursor
seesion show 499 and now we need to flust all open cursors
View 9 Replies
View Related
Apr 26, 2013
My current database MAX OPEN CURSORS VALUE=300.
Applications "A","B","C" using same DB user (USER1) then connect to a database.
Last night I created dedicated DB user (USER2) then application "B" connect to DB using this new user.Starting traffic increase today morning "B" application receiving "ORA-01000: maximum open cursors exceeded" error from DB.When I change current DB user USER2 to USER1, application did not receiving above errors.
I don't understanding what happening in database?
View 5 Replies
View Related
Feb 7, 2011
I have a table by name DCODE.
I insert a row in it(TOAD) immediately after inserting it, if i try to delete it i am geeting an error saying ORA-01000: maximum open cursors exceeded.
I have tried to to solve this by increasing the value of OPEN_CURSORS. But still i get the same message.
View 12 Replies
View Related
Oct 6, 2011
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
View 7 Replies
View Related
Jun 23, 2011
I am working on SQL,PLSQL and my application is in Java. I have 2 schemas in same database.
In one schema, the DML operations are very slow while in other schema is fine.
Also I am getting an error ORA-01000 maximum open cursors exceeded frequently while running application in Java.
View 13 Replies
View Related
Jun 4, 2012
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
View 3 Replies
View Related
Jul 25, 2013
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
View 2 Replies
View Related
Dec 27, 2010
I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.
What does this exec and Fetch time is?
How to improve fetch time, (esp with sub-query) ?
View 2 Replies
View Related
Jun 16, 2013
I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......
View 5 Replies
View Related
