Table Has No Rows - Select Taking Time
Aug 23, 2010
We were trying to insert approx 76 million records worth of approx 4 GB in a table when the insert operation failed with the archive log error.
The database was hanged, we re-started the database and currently there are no records in the table but when we are firing a select
select count(*)
from table
It's taking approx 2 mins to come out with the result.
We checked and found that there are no locks on the table currently.
what do we need to do to get the performance back
View 5 Replies
ADVERTISEMENT
Jan 24, 2013
My oracle database version is 11.2.0.3.0 where i am having one schema in that schema i am having 3 same tables with same structure same data but with different name.
but problem is in first table when i perform select query it takes 5 sec, in another table it is taking 0 sec and in third table it is taking 10 sec.
View 1 Replies
View Related
Sep 25, 2013
select
serialnumber from product where productid in
(select /*+ full parallel(producttask 16) */productid from producttask where
startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and producttasktypeid in
[code]....
Explain plan output:
Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |
[code]....
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)
[code]....
Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement
View 2 Replies
View Related
Sep 20, 2010
We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
View 5 Replies
View Related
Sep 13, 2012
I've a query like
update tab1
set col1 = ( select col2 from
tab2
where tab1.id = tab2.id) table 1 has arnd 10,000 rows
table 2 has arnd 1,700,000 rows and has a primary key on column id.This query is taking around 20 secs to execute. I checked the x-plan and most of time taken for table access by index rowid.I checked the stats for the tab2, its just three days old.
View 17 Replies
View Related
Mar 15, 2011
I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.
View 2 Replies
View Related
Feb 11, 2011
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
View 6 Replies
View Related
Mar 3, 2012
We are on a GRID DB and using Oracle 11g.
A external reporting application ( SQL SERVER REPORT SERVICES) sends in some comma separated parameter values, which has to be queried against a table with 6-10 million records.
Length of the comma separated value can go upto 5000-6000 in length based on user input in the front end application. This application sends this value in comma separated. i.e., like
'AA1-11101,AA2-34346,AA4-534399,.....' like this at a time the application can send upto 500 values each of length 10. i.e, maximum length can be upto 5000. I used CLOB to handle this because since the length can be 5000 and varchar2 can handle only 4000 long literals.
But the time taken by the CLOB to verify against the table using INSTR is more compared to VARCHAR2.
I found that VARCHAR2 does'nt take much time. Is it a good idea to have VARCHAR2 in the PLSQL procedure as parameter instead of CLOB, since PLSQL VARCHAR2 can handle upto 32000 long values.
View 10 Replies
View Related
Mar 2, 2013
How to find time log for query or any procedure like start time and end time and total time.
So that I can tune that queries properly.
Also how can we find estimated query running time.?
View -1 Replies
View Related
Jul 4, 2011
I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.
View 2 Replies
View Related
Feb 19, 2013
I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.
View 6 Replies
View Related
Nov 23, 2010
I have one query in my production which is taking more CPU time. when that statement executing the CUP is taking more than 90%
I am attaching the sql query and indexes on the table.
View 4 Replies
View Related
Mar 21, 2011
I have one big form where the first canvas (content) is taking nearly 20 seconds to display. this is in web mode using forms 10g.
i have tried using SHOW_VIEW(find_view('SAMPLE')) but still no improvement.
View 5 Replies
View Related
Sep 2, 2008
i want the 10 most recent mission date from mission table . i have tried using rownum but could not get it. TOP does not work
select * from
(select length(code_name) leng from missions
where length(code_name) >7
order by mission_date)
where rownum = 10
;
this does not work ::: no rows are selected and i do rownum = 1 then gives me one record and when i do rownum>=1 gives me all records
View 10 Replies
View Related
Jul 13, 2013
There are two tables like I posted below.I want a SQL query which selects all the rows from TABLE A which are not present in TABLE B. Also the select statement should pick all those rows which has updated value of COL_A2 in TABLE A.
TABLE A
COL_A1COL_A2
AAAMOBILE
BBBTABLET
CCCDESKTOP
DDDUNKNOWN
TABLE B
COL_B1COL_B2
AAAMOBILE
BBBUNKNOWN
CCCDESKTOP
The select statement should return following rows from TABLE A
COL_A1COL_A2
BBBTABLET
DDDUNKNOWN
View 2 Replies
View Related
Aug 20, 2012
select the rows from a table with the same particular field in PL/SQL. Actually I don't want to write two loops one inserted into another.
View 7 Replies
View Related
Feb 9, 2011
I have connected to the database from two sessions
Box 1 : i have executed a procedure.it took few seconds to get executed
Box 2 : I have executed a block of procedure.It is taking 1 min 40 secs to get executed.
why it is taking much time from Box2(for executing a block from procedure) , as the same code executed successfully in a procedure from Box 1.
View 2 Replies
View Related
Mar 31, 2010
Some of my forms taking to much time while loading on some machines but same forms working fine on other machines with same configuration.
Forms, version: 10.1.2.0.2, Clients: Win_XP_Pro, App Server: Window 2003 Server, IE7, Jinit 1.3.1.26
View 3 Replies
View Related
Feb 20, 2013
i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.
for example
update ps_po_lining b
set y.recv_req = 'N'
where recv_req in
[Code]....
this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.
View 5 Replies
View Related
Aug 3, 2010
I have a table with name SQL_STATEMENTS and it has 1 column, Column name is STATEMENT_DESC
The contents of rows in Table above are:
select * from emp;
select * from dept;
Can I write a PLSQL block that can select these 2 rows from the table and execute them?
View 9 Replies
View Related
Apr 21, 2011
I am trying to break down a row of data that has nine periods that I want to enter into a table one period at a time. I am trying to use "IF EXISTS ( SELECT * FROM TABLENAME WHERE FIELD1 IS NOT NULL) THEN" through all nine fields. It keeps returning lines that have a null column as well as not null columns. if there is a better way of doing this. Here is an example of what I am trying to do:
IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT9 IS NOT NULL) THEN
SELECT NUMBER, 1COL9, 2COL9, 3COL9, FIRST_DT9, LAST_DT9, 4COL9
FROM TABLE_NAME;
ELSE IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT8 IS NOT NULL) THEN
[code]...
View 1 Replies
View Related
Sep 14, 2010
I have upgraded oracle database from 9i to 11g using export and import utility. After migration we are facing performance issue in report generation, We have observed that First execution of report is taking very long time and when we generate the same report 2 -3 times there is considerable change in the execution time and it is more better than the first execution.
2 days back I have restarted the database and found the same issue. There are around 300 Reports and it is not possible to generate all the reports 2-3 times every time we restart the database.
View 5 Replies
View Related
Sep 19, 2013
My Database version as followsOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production We have datagaurd setup as well - Huge archive logs are generating in our primary database - Archive logs are shipping to standby with no dealy - But applying the archive logs are taking more in our physical standby database -why it was taking more time to apply archivlogs (sync) in standby ? - What could be possible reasons..? Note : Size of standby redo logs are same as redo log file of primary database - Also standby by redo one or more than online redo log primary. Since i need to report my higer leve stating this is cause for delay in applying archive logs.
View 32 Replies
View Related
May 28, 2012
I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
[code]...
But the case is I need to display the value in table format Horizontally as below
EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING
[code]...
View 14 Replies
View Related
Jan 20, 2011
We are inserting data using JDBC (Java program) in Oracle 11gR2 DB and Timesten to oracle (using AWT cache group) . In reality insertion in oracle is faster than Timesten Cache DB.
Timesten version - TimesTen Release 11.2.1.7.0 (64 bit Linux/x86_64)
1. It is Client/Server Model.
2. Cpu has 4 core and we are using 3 core for insert the Data.
3. Perm and Temp size is big enough compare to Data Size
4. auto commit=0
5. durable commit=0
6. PassThrough=1
7. LogBufParallelism=3
8. LogPurge=1
9. LockWait = 0.1
View 3 Replies
View Related
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
Sep 1, 2010
For an query, cost was 16Lakhs and was taking 30min, I brought down the cost to 1.5lakhs, but still it is taking 30min.
There were many outer joins and same table has been Used(FROM clause) 5 times in the query. I have introduced WITH clause, and brought down the cost.
View 7 Replies
View Related
Feb 4, 2013
I am working on oracle 11g...I have one normal insert proc
CREATE OR REPLACE PROCEDURE test2
AS
BEGIN
INSERT INTO first_table
(citiversion, financialcollectionid,
dataitemid, dataitemvalue,
[code]....
I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?
View 5 Replies
View Related
Feb 5, 2013
This is my stored procedure
I have below store procedure:
create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
View 12 Replies
View Related
May 23, 2011
Recently upgraded from Oracle 10.2.04 to 11g with a few bumps on the road most of which I've been able to resolve, but there's one that continues to confuse me.
Pretty vanilla INSERT statement in which the SELECT portion on its own runs in about 2 to 5 seconds (all results returned) on a facility by facility basis. When I try to combine this with an INSERT statement it ends up running for 12+ minutes per facility. The explain plan looks good and I've even tried emptying the target table prior to running the INSERT.
I've gathered schema/table statistics to no avail. I also tried using it as a CREATE TABLE AS statement and it still takes the 12 minutes per facility.
View 3 Replies
View Related
