Forms :: Wait Event During Execution Simple Select Statement

Jul 19, 2013

Yesterday i got wait event when executed simple select from table.This select was like:

SELECT emp_number from employer where subs_id = 111

I got one row, select is very fast.In our Core Bank System we have package with function which returns such information. I tested this select on test DB, and nothing wrong. But when I executed such select and package on Production DB, DB Admin saw that 88 sessions waits when my session release the resource. But what can happen, it was simple select? I used PL/SQL developer to get information from table:

1) SELECT emp_number from employer where subs_id = 111 then
2) Package with this function

Another users used Oracle Forms screen to execute package. How simple select statement could stop all DB?

BANNER
1Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
2PL/SQL Release 10.2.0.5.0 - Production
3CORE 10.2.0.5.0 Production
4TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
5NLSRTL Version 10.2.0.5.0 - Production
[code]...

Forgot to say that after succeful execution on Prod DB I disconnected, and in EM my session was INACTIVE.

View 3 Replies


ADVERTISEMENT

PL/SQL :: Wait Event Starts With SQL*Net Message From Client - Time Wait 178577 Units

Dec 11, 2012

I am trying to look at wait events for a long running query in TOAD.I start the query on one instance of TOAD and open the Session Browser on another instance.But I am surprised to find that in "TOtal Waits" on the RHS-> SQL*Net message from client is the longest time taking and is already -> 178577 units whereas I have just started the query.

Whereas in the Current Waits it shows DB File Scattered Read currectly as some seconds.

View 5 Replies View Related

ODP.NET :: Ora-00936 With Simple Select Statement?

Dec 19, 2012

I get "ORA-00936: missing expression" when I issue the following SQL:

select 4 - .2 from dual

from the following VB.NET code (3.5 framework):

Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn)
conn.Open()
Dim da As Oracle.DataAccess.Client.OracleDataAdapter
Dim ds As New DataSet
da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)
da.Fill(ds)
MsgBox(ds.Tables(0).Rows(0)(0))

I have the following installed when connecting to 10.2.0.3 database:
11.2.0.1 client installed
11.2.0.3.20 ODP installed

This is really strange behaviour as the following SQL variants work:
select 4 - 0.2 from dual
select 4 - '.2' from dual
select -.2 + 4 from dual

This is a big problem as I have a complex application deployed in a number of sites so won't be able to rollout ODP 11

View 11 Replies View Related

SQL*Net More Data From Client Wait Event?

Oct 8, 2013

DB 11.2.0.2AIX 6 

I am getting following two top wait events from AWR report

1)SQL*Net more data from client
2)log file sync 

Does it hints towards network latency and hardware configuration?what should i do for first wait event?

View 11 Replies View Related

PL/SQL Timer Wait Event Analysis

Jun 22, 2013

One of our Job running long than usual time. I checked the wtait event for which its waiting. its PL/SQL Timer wait. But i noticed the total waits is 179 and timed out is also 179 for PL/SQL Timer. I checked that job that particular procedure calls dbms_lock.sleep in it. I want to know why this wait event is getting timed out ?

View 1 Replies View Related

SQL & PL/SQL :: Error Occurred During Execution Of Simple JDBC Program In Eclipse

Jun 27, 2013

java.sql.SQLException: ORA-04054: database link GMAIL.COM does not exist

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1615)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1580)
at com.jdbc.pack.Lab1.main(Lab1.java:31)

View 7 Replies View Related

SQL & PL/SQL :: How To Commit After 1000 Records In Simple Insert Statement

Oct 10, 2011

I am using Oracle 11g Release 11.2.0.1.0

OS: windows

I am taking an example of EMP table in a scott schema.

EMP_1 is also a same structure of EMP.

Here is the Insert statement used.(i might get where clauses etc in the real scenario i am taking a simple example here)

INSERT INTO SCOTT.EMP_1(JOB,ENAME,EMPNO) SELECT JOB,ENAME,EMPNO FROM SCOTT.EMP

table EMP is having around 25 million records.

i would like to do a commit after every 1000 records. as my redo log is getting full if i use a single commit at the end.

Quote:pls note: Increasing of redo log is not possible as of now

is there anyway i can acheive this without cursor bulk collect.

View 7 Replies View Related

Concurrency Wait On Insert Statement?

Feb 19, 2013

I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.

All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?

View 3 Replies View Related

PL/SQL :: SELECT - How To Wait Until Result Appears

Mar 28, 2013

SELECT * from TABLE_1 where a=b;

Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?

Something like this

cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked;
row c%rowtype;
begin
open c;

[Code].....

Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?

View 29 Replies View Related

Workflow :: No Event Subscriptions Exist For Event Error

Mar 6, 2013

we are running Oracle R12.1.3 on DB version 11.2.0.3. I just migrated a created a custom subscription for the oracle.apps.ap.supplier.event Oracle Event. I execute a custom package when this event fires. My package is working fine and I'm getting the expected results. My problem is I keep getting a Workflow notification saying:

Sent: Monday, March 04, 2013 9:14 PM
To: SYSADMIN
Subject: Action Required: Local Event UNEXPECTED : oracle.apps.ap.supplier.event / 36422

To               SYSADMIN
Sent               04-MAR-2013 21:13:40
ID               1659929
    
An Error occurred in the following Event Subscription: Event Subscription

Event Error Name:
Event Error Message: No Event Subscriptions exist for this Event
Event Error Stack:
Event Data: Event Data URL
Event Details
Event Field     Value
[code]...

I saw an earlier post about this and have tried changing the Source Type to "External" but that didn't change anything. why Workflow is telling me a subscription doesn't exist when my subscription is executing with no problems.

View 5 Replies View Related

Application Express :: Capture On Change Event For Select List Using JavaScript

Nov 15, 2012

Is there any way I can capture on change event for the Select List (already created on the page) using java script and then refresh chart region?

View 2 Replies View Related

Forms :: How To Handle Multi Row Select Statement

Feb 3, 2011

I am doing simple project, where i need to check date(form which we give in run time) with database column date.problem i have written the code for program now i found that select statement is return multi rows.

code is

when_button_pressed trigger.

declare
fdcode dmas.dcode%type;
fcodno dmas.codno%type;
famtrs dmas.amtrs%type;
fdate donres.rdate%type;
begin
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Confirmation Of Statement Execution In Block

Jul 12, 2010

I have a set of sql statements which i have to execute inside a pl/sql block. But i need to know the response of each statement and confirmation whether it is executed successfully.

Practically i need to get info as such in sqlplus status msg for each sql statement

E.g. :
SQL> insert into test values(1);

1 row created.

or similar to this.

View 7 Replies View Related

Forms :: Dynamic Variable In Place Of Username In Each Select Statement Throughout Application

Jul 25, 2010

I have a problem that i have hard coded the username.tablename in each select statement of all forms of my application. Now i want to use a dynamic variable in place of username in each select statement throughout the application. The example is:

select * from scott.emp
and i want to write it as:
select * from variable.emp

But at compilation of the form the compiler should know the above variable name.

I have tried to use following select statement but it does not work.

select user into :global.username from user_users

I think perhaps my problem would be solved with Dynamic SQL Statement but i have no experience by using this statement.

View 4 Replies View Related

Performance Tuning :: Execution Plan Of SQL Statement

Mar 25, 2012

I have queries on the execution plan of a sql statement

Following is the example

create table t1 as select s1.nextval id,a.* from dba_objects a;
create table t2 as select s2.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
commit;

create index i1 on t1(id);
create index i2 on t2(id);
create index i11 on t1(object_type);

exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

select count(*) from t1 where object_type='VIEW';

COUNT(*)
----------
8934

set autotrace traceonly explain

Can we say in the following case, that,

(1) First index on object_type is accessed to get rowids - t1.object_type='VIEW'
(2) Then the filter on owner is applied - t1.owner='SYS'
(3) Then the table T1 is accessed to fetch data from the rowids returned by the index I11 and filer application - TABLE ACCESS BY INDEX ROWID

Though I am unable to understand how filter can be applied to the rowids retrieved from index, we can see from the plan below that The rows accessed have reduced from 8550 to 1221 before we access the table...Thus filter "t1.owner='SYS'" is applied in between. Right?

another question is

Case 1 - do we retrieve a rowid from index for a given value, then retrieve required values from table for that rowid
Thus row at a time in both ... in loop
OR
Case 2 - we first fetch all rowids from index and then retrieve values from table one row at a time from the collection of rowids fetched?

Suppose Case 1 is what is happening then can we say, both the steps mentioned by IDS 2,3 in plan below are executed exactly equal number of times and the filter "t1.owner='SYS'" is applied at some later stage? Of course in this case the values in ROWS stand misleading then

select * from t1,t2 where t1.id = t2.id and t1.object_type='VIEW' and t1.owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 26873579
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 1 | HASH JOIN | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1221 | 116K| 381 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | I11 | 8550 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 161K| 15M| 533 (1)| 00:00:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."OWNER"='SYS')
3 - access("T1"."OBJECT_TYPE"='VIEW')

View 7 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

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

Performance Tuning :: How To Change Execution Plan Of Currently Executing Statement

Feb 8, 2011

refer following sql statements and code

Session 1
create table tab1 as select * from dba_objects where object_id is not null;
alter session set events '10046 trace name context forever, level 12';
declare
x number;
begin
for i in 1..4
loop

[code]....

Session 2

after "starting" the above pl/sql block from Session 1, I keep on querying tab2 from Session 2 And as soon as 2 records are inserted in tab2, I create index from Session 2

select * from tab2;
select * from tab2;
select * from tab2;
N
----------
1
2
create index i on tab1(object_id);

As I have tested from a single session (just before this test) such index is used for the sql statement

select count(1) into x from tab1 where object_id=2331;

However when I checked the trace file I am not geeting results as expected

I am expecting 4 execution plans - 2 FTS and 2 Index Access scans and for this I am issuing following command

tkprof dst1_ora_7369.trc dst1_ora_7369.txt aggregate=no sys=no

But unfortunately I am getting following output

SELECT COUNT(1)
FROM
TAB1 WHERE OBJECT_ID=2331
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 4 0.00 0.00 0 2 0 0

[code]....

1) Why I am unable to see 4 execution plans - 2 with FTS and 2 with Index access when I mentioned 'aggregate=no'?

2) Whether the index i will be used for last 2 iterations after first 2 iterations of FTS?

If answer to above question 2) is 'No'

By which method I can force an ongoing sql statement in loop to take different execution path? Of course I can't hard parse sql in 'that' current session Will flushing Shared pool work in above case?

View 6 Replies View Related

Performance Tuning :: Same Execution Plan For Create Table Statement When Name Changes?

May 18, 2010

Can we have same execution plan for a create table statement where the name of the table changes every time as follows:

create table test
as
select * from t1

Here table name changes from test to another table name next time

View 6 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

PL/SQL :: DML Query Execution - Length Exceeds 4000 Characters With Execute Immediate Statement

Jun 18, 2013

I want to execute a DML query with execute immediate statement. That DML query length exceeds 4000 characters. This query has Xquery related conditions, i can not split the query. when i tried execute it is giving "string literal too long".  I tried with DBMS_SQL.Parse() and DBMS_SQL.Execute also, but it is giving same error. I have to execute this DML query inside a Procedure. We are using oracle 10g version

View 13 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

Forms :: How To Design A Simple Graph

May 23, 2011

How to design a Simple Graph?

View 1 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

Forms :: Canvas / Wait Property In 6i?

Nov 28, 2011

I am using Forms 6i in Oracle Applications, In the button click event i used set_canvas_proeprty() twice. First time it executes these two statements, second time it does not. If i use any message() statement before these two statements, it executes.

Do we have wait() statement in Forms ?in apps we have app_item_property.set_property(), can we use same command to set canvas property also.

View 1 Replies View Related

Forms :: Simple Data Block - Serial Number Generation

Jun 2, 2010

I have created simple data block by wizard and taken tabular layout 10 rows and 6 columns

i want to generate serial number on first column .....

what need to use..?

View 11 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

AWR Report Shows Wait Time Is High But OS Shows Wait Time As Normal

Oct 2, 2012

We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.

Service Time : 15.36%
Wait Time : 84.64%

This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.

As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.

View 17 Replies View Related

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

Select Statement Errors

May 5, 2010

I have a question about select statements, as I am new to them and don't know how to work all the commands yet.

I'm making a select statement that is about half right... it is shown below:

select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);

I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.

View 2 Replies View Related







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