Slow Locally On Supersized Server

Apr 27, 2012

i have a nightly import ( about 20 tables ) and it takes up to 5 hours..we have one table of about 800,000 lines and the rest are between 1000 and 200,000 this is very slow when i monitor the import i see a very long amount of wait for the SQLnet from client ,

i run the import on the Database server itself .. if i check the current statement i see it's moving from one to one for instance i have

SELECT /*+ all_rows ordered */
"A".ROWID, 'REPORT', 'CONTRACT_LVL', 'SYS_C001329497'
FROM "REPORT"."CONTRACT_LVL" "A"
WHERE NOT (LENGTH (bonus_nat) <= 31)
then
SELECT /*+ all_rows ordered */
"A".ROWID, 'REPORT', 'CONTRACT_LVL', 'SYS_C001684584'
FROM "REPORT"."CONTRACT_LVL" "A"
WHERE NOT (LENGTH (outcome_cd) <= 1)

etc and it takes hours DB is on windows 2003 runnin oracle RDBMS 9.2.0.7 while the import screen show 185000 lines imported..I also see a lot of consistent gets for this sessions raising at that time..Would it be better to export import without statistics ?

I need also to mention that the dump file comes from a linux hosted Database don't think it will make the difference for a exp/imp.It's a peoplesoft Database there are a lot of tables more than 15000 and if i take the table mentioned above and i want to check its constraints it takes decade before toad can display them.I have seen that we have a incredible amount of constraints on those tables it might be the reason .

I just wonder if the system catalog needs to be tuned ? /* Update */ why but now the huge number of wait is no as "Library cache lock".

View 7 Replies


ADVERTISEMENT

Server Administration :: Locally Managed Tablespace

Feb 26, 2010

For a locally managed tabelspace, the dropped segment is changed to a Temp. Segment, to prevent bitmap from being updated.

View 2 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

I have one database which is recently upgraded from oracle 8.1.5 to oracle 10.2.0.4.The database is having around 300 tablespace and total size of the database is 1.5 TB.

The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.

This database is a mission critical database and very less downtime can be allowed.

View 6 Replies View Related

Connection Using Sqlplus Works Locally And Not From Other Servers

Jul 27, 2012

I have a two node RAC on 11.2.0.3. We are using SCAN Listener here. On my node 1, i can connect to the database using tns alias: The alias looks something like:

Prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x1200.prod.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cusms)
(UR = A)
)
)

sqlplus system/actdbasystem@cusms ----- works sucesfully on the prod server. Even the same prod alias copied to second tnsnames.ora in the cluster and run, it works sucesful too. But the issue is, when we copy the same alias onto any other servers and try to connect give's the following error message:

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 27 12:37:55 2012
Copyright © 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12519: TNS:no appropriate service handler found
[code]...

why is it my connection using sqlplus works locally and not from other servers?

View 2 Replies View Related

Server Utilities :: SELECT On External Is Very Slow?

Aug 10, 2013

I just did a 112G file migration of production data using oracle_datapump so I know this works in principle. When I tried it on my test instance I am seeing stuff like this

[oracle@aggs00.test for_test]$ ls -l aggs_day_conversion_agg_2419
-rw-r----- 1 oracle oracle 15917056 Aug 10 09:06 aggs_day_conversion_agg_2419
CREATE TABLE IMP_3251198_2419(
PARTITION_DATE DATE,
USER_ID NUMBER,
SID NUMBER,

[code]....

Executed in 1800.642 seconds

why it could be taking 1800 seconds to select one record from a not very big table? File corruption? Disc fragmentation? Oracle instance configuration?

View 29 Replies View Related

Server Utilities :: Slow Expdp After Upgrade

Oct 1, 2012

We had AIX OS on 570 machine and database 10.2.0.4. We took expdp and it took 2 and hour to complete every night.

Now we upgrade to 10.2.0.5 and 770 machine and now same command takes 6 hours to complete even database and hardware is upgraded

Command is

expdp T24SILK/oracle directory=backup dumpfile=exp_beod_T24_%U_$dt
.dmp logfile=exp_T24_$dt.log EXCLUDE=TABLE:"LIKE '%TRACE'" parallel=6

View 1 Replies View Related

DB Performance Is Slow Using Oracle 11gR2 On Windows Server

May 25, 2013

I am using 11gR2 on windows server. This is the query that runs many times a day and effect badly the performance of database. I don't have much idea about this query.

SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0 AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')

View 1 Replies View Related

Server Administration :: SLOW Login As User To DB Via Sqlplus?

Mar 2, 2012

I have a situation where when I login as a user to my DBvia sqlplus no service name it takes about 20 secs to connect.Yet when I login as a user with DBA privs it logs in immediately.

Is there something I can do to trace what is happneing behind scences to determine what the login delay may be..

View 9 Replies View Related

Performance Tuning :: DB Import Too Slow After Server Reboot

Jul 4, 2011

Few days ago, My database server no access to StorageBox then I reboot it then after works fine. But, know DB import process is too slow. Before 100GB DB import process completed within 10 hours when server normal running. Now 2 day working, but not complete

How to investigate this issue? Maybe I miss increase some parameters on the Server or Oracle?

Here is my server brief info:

RAM is 16GB,
SWAP size is 16GB,
CPU 12 cores

SQL> show sga;

Total System Global Area 4294967296 bytes
Fixed Size 1984144 bytes
Variable Size 369105264 bytes
Database Buffers 3909091328 bytes
Redo Buffers 14786560 bytes

View 11 Replies View Related

CPU Usage Is Ok On The Server - But Database Is Slow With High CPU Wait?

Jan 26, 2013

I have a RAC 48 cores on solaris. I check dbconsole when application performance is very slow and everyone complains, and I see that the main wait is cpu - also on the awr report. however when I check server cpu I see about 80% idle! so how can I make oracle use more cpu power instead of waiting for it? I don't think that parallel is an option here because I can't change the application code.

View 2 Replies View Related

Server Utilities :: Slow Exports Using Exp Command Due To Open Sessions

Nov 11, 2011

I have had the following problem open with Oracle support since March 2011 (8 months), and still no resolution.

When I export all our schema's on Sunday night it takes about 1 hour 50 minutes. When I export the same schema's on any other night it takes 7 hours. The only difference is that on Sunday at 4:00am we drop all connections in the connection pools and reestablish new connections. Then 19hours later on Sunday at 23:00 we perform the exports which only take 2 hours to complete.

I have also tried recreating the connections in the connection pools during the week, and the exports have then only taken 2 hours to complete. But the following night after the connections have been used during the day, the exports again take 7 hours. So it appears the export speed gets significantly slower when there are many open connections that have been used and not closed.

From the stats pack report I found 2 SQL statements internal to the export command, that had an order of magnitude in difference when looking at the elapsed execution time between the fast export, and the slow export (see below).

How to speed up the exports without having to drop and recreate the database connections in the connection pools each night.

FAST:
elapsed_time: 430.90
executions: 161,388
Module: exp@Oracle1 (TNS V1-V3)
SELECT COLNAME, COLNO, PROPERTY, NOLOG FROM SYS.EXU10CCL
WHERE CNO = :1 ORDER BY COLNO

elapsed_time: 264.29
executions: 50,349
Module: exp@Oracle1 (TNS V1-V3)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, 100 FROM SYS.EXU8COL
WHERE TOBJID = :1 ORDER BY INTCOLID

SLOW:
elapse_time: 8264.16
executions: 124,662
Module: exp@Oracle1 (TNS V1-V3)
SELECT COLNAME, COLNO, PROPERTY, NOLOG FROM SYS.EXU10CCL
WHERE CNO = :1 ORDER BY COLNO

elapsed_time: 3877.78
executions: 38,813
Module: exp@Oracle1 (TNS V1-V3)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, 100 FROM SYS.EXU8COL
WHERE TOBJID = :1 ORDER BY INTCOLID

I use the following export command for each schema:
$ORACLE_HOME/bin/exp user/pass file=somefile.dmp owner=$SCHEMA log=somelog.log buffer=9000000

I have an Oracle Standard edition 11.1.0.7 database on 64bit Linux with a 7GB SGA. I currently export (I use exp not datapump because datapump is a lot slower and we can't use parallel processing features of datapump on a standard edition database) approx 200 schema's each night. The export normally takes 1 hour 50 minutes which is approximately 2 schema's exported every minute. When the exports run slowly each export takes almost 2 minutes to complete.

The database has about 20 GB data and 50 GB indexes. The database has also approx 500 connections via toplink connection pools from 8 application servers.

View 2 Replies View Related

Server Administration :: Partitioned Tables For 30+ GB Table Which Load Very Slow In Front End GUI

May 13, 2013

that are my biggest tables

Users use front end (called ESS Console) and when they try to open one of those tables they wait very long (really bad performance). Sometimes the GUI even hanging without displaying results.

Does Partitioned Tables feature works for better performance?

View 3 Replies View Related

Slow Insertion In Particular TABLE

Apr 17, 2012

The issue is slow insertion in particular table(i.e A Table) it means insertion in all other tables(i.e B, C, D tables) in same schema is going properly but only when i am trying to insert in one particular table(i.e A table) in same schema it takes long time to complete insertion. Daily insertion is 6000 rows.

I have check all the details like Tablespace size, Analyzing of table, Analyzing of indexes and all. There is no any error alertlog file.

View 1 Replies View Related

10.2.0.3 SqlPlus DB Connection Slow?

May 16, 2007

We have two database instances on the same server. One was left at 9.2.0.7 and one was upgraded to 10.2.0.3. Connecting externally (sqlplus '/as sysdba') to the 9.2.0.7 database is lightning fast. Connecting externally to the 10.2.0.3 database is very slow, comparatively speakiing. This is on an IBM AIX-5L (64-bit) machine. We are using "tnsnames".

View 10 Replies View Related

Why Parametrized Cursor Is Slow

Aug 3, 2010

WHY PARAMETRIZED CURSOR IS SLOW

I have the following query which is very slow

CURSOR c_party_ids (p_party_name VARCHAR2, p_party_type VARCHAR2, p_eval_active NUMBER)
IS
SELECT a.party_id,
a.party_type,
b.cust_account_id,
b.attribute17
[code].......

View 3 Replies View Related

Slow Import For Oracle 11g R3 On IBM AIX 6.1

Sep 3, 2012

I am migrating a oracle 9i database to 11g r3. I can only use imp. As the database is huge, I have split the exp dump by schemas. In my recent test, i have split the schema into 4 seperate threads to be imported into the new oracle11g database. The 4 thread of imp consist of almost similar sizes of schema (Eg thread 1 - Schema 1, 2 ,3. Thread 2 - Schema 4,5,6 etc)

All the dump files are in the same mount point. When i execute the import (4 threads) together, the total import timing is each thread is between 2.5 days to 3.5 days.

Then i proceed to try only 1 thread, only 2 hrs. So could this be a IO issue or oracle memory problem?

Specs
IBM AIX 6.1, 32GB RAM

Oracle
24GB AMM
Number of processes 500

View 1 Replies View Related

ERP Is Running Slow After Cloning

Apr 22, 2012

We have done cloning of our ORACLE APPLICATION(11i),after that performance of ERP is getting slow (like fetching of data). What we can do to increase the performance.

View 1 Replies View Related

Slow Performance On New 11gr2 Linux DB?

Aug 27, 2013

we are busy updating one databasee from a windows platform 2003 oracle 10G to a linux and oracle 11r2

We exported/imported the data and it looks ok Explain plans look the same . but our heavy batches are twice slower than on the windows box ,the two top events are disk related, sequential and scattered reads there are 90% of the time of the batch job , i read some white paper and found that using ASM can be bad in some cases the same with the linux for this particular kind of scattered reads , i was just wondering if just changing the SGA to 10GB instead of 4GB to get more cache and speedup the things .

View 3 Replies View Related

Oracle Database Writing Slow?

Dec 31, 2011

We are using one software it is a test tool for verify the data base posting speed from server to client systems. In windows 2008 R2, database posting speed is very slow when compare to windows 2003 server .

Server configuration is same for both servers ( RAID 5 , RAM 4 GB) how we can improve writing performance in Oracle

View 1 Replies View Related

Character Report Generation Too Slow

Oct 12, 2010

I am using :

Database : Oracle 8i
Application Server: Oracle AS 9i
Developer Suite : Oracle 6i(forms & reports)

I have created some character reports in oracle reports 6i.. when reports used run from my ERP(oracle 6i oriented) ... report usually took time to create on server. Sometimes my ERP used to hang up due to busy reports generation. And then we have to kill some processes to finally create charater reports on emergency basis.What is the valid reason for slow generation of report(character file )?

View 1 Replies View Related

Insert Operation Very Slow While Import?

Feb 20, 2011

I am working on an SAP application migration project using Oracle 10.2.0.2 database. We are migrating the application from Windows to Solaris.

During the process we are facing problem with very slow insert operation on a particular table.The server's capacity is very good and so no resource bottleneck.

The table contains around 2,70,000 rows and inserting at around 100 rows per 10 seconds.

The table contains following data types.

SQL> desc SAPDATDB.CAF_GP_VALDEF;
Name Null? Type
----------------------------------------- -------- ----------------------------
VAL_UUID NOT NULL NVARCHAR2(34)
VAL_GUID NOT NULL NUMBER(10)
VAL_CLOB NCLOB

View 2 Replies View Related

SQL & PL/SQL :: Function Very Slow After Oracle Upgrade

Oct 11, 2012

We have a function that is called in various other PL/SQL packages, and performance has always been very good. On 29th Sept we upgraded our db to 10.2.0.5.0 and since then, a package that calls the function has gone from ~4mins, to ~2.5hrs to run.

In PL/SQL Developer, a simple select that calls the function has gone from ~0.5secs to retrieve the first 100 rows, to ~12secs. I ran a profile of the main package, which highlighted the where the bottleneck was (a fetch from an explicit cursor). Running an explain plan on the cursor SQL doesn't really show up anything untoward.

However, I found that if I subtly changed the cursor SQL, (so that it did the same thing, but was written differently), it fixed the performance problems.

where ade_start_date between cpDate-cpDays and cpDate-1
/*and ade_start_date < cpDate
and ade_start_date >= (cpDate-cpDays)*/

From this, we thought that there may have been a bad cached execution plan which the change of code forced a recalculation of. However, about 2 hours later, the changed code ran slowly again. So a further subtle change was made, which fixed the issue again. Until this morning, when it was running slowly again.

This feels like it is CBO/stats related potentially, but is out of my area of knowledge unfortunately. We have our DBA investigating this, but there may be things I can test to narrow down the possibilities in the meantime.

View 5 Replies View Related

SQL & PL/SQL :: Lexical Parameter Slow Down Query?

Feb 16, 2010

All my following query is running fine.

SELECT pd,
vr_date,
x.vr_no,
vr_sn,
cac,
ac,
nar,

[code]....

but problem is that when above query is used or oracle reports then and lexical parameter is used then i slows down more than 100 times

SELECT pd,
vr_date,
x.vr_no,
vr_sn,
cac,

[code]....

above query is copy of top query but differnce is that &P_Unt_Cls lexical parameter is used instead of and Unit in(29,34,35,36,37,38,44,45,60,70,71), I am unable to understand why it slow down query.

View 6 Replies View Related

Performance Tuning :: Delete Is Very Slow

Apr 26, 2013

I am running one simple delete statement in one table with rownum<10000 but it is taking nearly 10 to 15 mins.Table doesn't have any child table rows and triggers.

View 13 Replies View Related

Oracle 11G - Update Is Very Slow On View?

Dec 7, 2012

I have big trouble with some Update query on Oracle 11G.I have a set of tables (5) of identical structures and a view that consists in an UNION ALL of the 5 tables.None of this table contains more than 20 000 rows.Let's call the view V_INTE_NE. Each of the basic table has a PRIMARY KEY defined on 3 NUMBERS(10,0) -> INTE_REF / NE_REF / INSTANCE.

Now, I get 6 rows in another table and I want to update my view from the data of this small table (let's call it SMALL). This table has the 3 columns INTE_REF / NE_REF / INSTANCE.

When I try to join the two tables :

SELECT * FROM T_INTE_NE T2
WHERE EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )

I get the 6 lines in 0.037 seconds

When I try to update the view (I have an INSTEAD OF trigger that does nothing (just return for testing even without modifying anything), I execute the following query :

UPDATE T_INTE_NE T2
SET INTE_STATE = -11 WHERE
EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
The 6 rows are updated (at least TRIGGER is called) in 20 seconds.
However, in the execution plan, I can't see where Oracle takes time to achieve the query :
Plan hash value: 907176690

[code]....

Predicate Information (identified by operation id):

2 - access("T2"."INTE_REF"="T1"."INTEREF" AND "T2"."NE_REF"="T1"."NEREF" AND
"T2"."INTE_INST"="T1"."INSTANCE")

Note- dynamic sampling used for this statement (level=2)

Statistics
-----------------------------------------------------------
3 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin

[code]....

I get exactly the same execution plan (when autotrace is ON).Furthermore, if I try to do the same update on each of the basic tables, I get the rows updated instantaneously.

View 10 Replies View Related

After Conversion 11G Very Slow - Power Builder Application?

Dec 19, 2011

We converted our Power builder application from Oracle 10g to Oracle 11g. We are getting very slow to retrieveing data from the server.Basically we have noticed the following system internal sqls taking more time during our sql calls.

My schema Name : CMATISPROD
Oracle Version : 11.2.0.2.0

Indexes are moved separte table space.how to resolve this problem.

SELECT index_name, uniqueness
FROM SYS.all_indexes
WHERE table_owner = 'CMATISPROD' AND table_name = 'EXPRESSIONS';
* Formatted on 2011/12/15 14:19 (Formatter Plus v4.8.0) */

[code]...

View 3 Replies View Related

Expdp Slow With Error ORA-01555 - Snapshot Too Old On 11g?

May 15, 2013

data pump export is very slow. For 50GB export has taken more than 24Hrs with one below error:

Database Version:11.2.0.2.0
OS: Windows server 2008 r2
Increased 10GB RAM and CPU 6 to 8 then also same issue

Error:
ORA-31693: Table data object "BNCSDB"."MS_DATA_PTORE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4037596720$" too small

Export log:
Export: Release 11.2.0.2.0 - Production on Tue May 14 20:03:25 2013

Copyright � 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl dumpfile=BCSDB04_19.dmp logfile=BCSDB04_19.log

[code]...

View 12 Replies View Related

Performance Very Slow Inserting Data Into Table

Jun 25, 2013

I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query

INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ;
COMMIT;

Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.

View 4 Replies View Related

SQL & PL/SQL :: Materialized View Makes System Very Slow?

Apr 15, 2010

I have created a MV joining 10 tables with FAST REFRESH ON COMMIT It made my sytem very slow.

View 18 Replies View Related

Client Tools :: Sql Developer Startup Is Slow?

Apr 15, 2013

I am finding Oracle sql developer start up to be very slow.It takes more than 5 - 10 mins to startup.It seems for long startup.Its installed on Windows 7 machine in c: drive. Want to know what is causing it to be too slow.virus scanning, JVM startup.... ?

FYI
---------
Oracle Sql developer Version -- 3.2.20.09
Java(TM) Platform -- 1.6.0_43

View 4 Replies View Related







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