|
Replies:
42
-
Pages:
3
[
1
2
3
| Next
]
-
Last Post:
Aug 3, 2008 11:14 AM
Last Post By: riyaj
|
|
|
Posts:
153
Registered:
03/20/08
|
|
|
|
Import running Very Slow!!!
Posted:
Jul 30, 2008 11:15 PM
|
|
|
Hi Guys,
This is in sequence with my first two posts...i'lll explain the whole scenario....
I have a 44 GB table on an Oracle DB 9.2.0.8. I first tried to truncate it but it was taking huge amount of time...
So, i truncated the table with:-
SQL> Truncate table TABLENB REUSE STORAGE;
After this i tried to Drop the table and it took approx 16-18 hrs to drop it. Now, i have started a import of the table using following command:-
imp schema/passwd
file=/oracle/XX/97/imp/TABLENB.1.exp,/oracle/XX/98/imp/TABLENB.2.exp,/oracle/XX/99/imp/TABLENB.3.exp log=/oracle/XX/97/exp/TABLENB.imp.log tables=TABLENB feedback=10000 indexes=N ignore=Y
It has taken 16 hrs till now and when i run the following query the output is as follows:-
SQL> select extents from dba_segments where segment_name='TABLENB';
EXTENTS
12450
Initially the table had about 1 million extents....
Can somebody tell, is dere any way i can speed up the import process or is dere any lock on the table which is making the import slow????
Regards,
Nick.
Message was edited by:
Nick-- wud b DBA |
|
|
Posts:
1,296
Registered:
10/27/06
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 30, 2008 11:21 PM
in response to: Nick-- wud b DBA
|
|
|
|
Just to confirm, are you importing back in the same database ?
Also are there any other users logged in to the database ?
As you said, it has been running for last 16 hours but it hasn't almost done anything. There seems to be some issue ?
Check long ops view to see what is there ?
Amardeep Sidhu
|
|
|
Posts:
4,974
Registered:
11/06/98
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 30, 2008 11:36 PM
in response to: Nick-- wud b DBA
|
|
|
|
You've not used BUFFER and COMMIT=Y so the import might well fail on Undo space.
To monitor the INSERT that the import is executing you could also query USED_UREC and USED_UBLK in V$TRANSACTION.
USED_UBLK will also tell you how many Undo Blocks are in use -- so that will help you decide when you need to extend / add a file to the Undo Tablespace.
If you have Indexes on the table, then the import will be that much slower (index entries, too, will be reflected in USED_UREC).
|
|
|
Posts:
153
Registered:
03/20/08
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 30, 2008 11:39 PM
in response to: Amardeep Sidhu
|
|
|
|
Hi Amardeep,
Yeah, i'am importing back 2 da same DB.
Yes, users are logged into the DB...
Amarjeet, can u please tell, wat all do i need to check in the v$session_longops view?????
|
|
|
Posts:
153
Registered:
03/20/08
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 30, 2008 11:54 PM
in response to: hkchital
|
|
|
|
Hi Hemant,
We do not have any indexes on the table. Also, we have sufficient undo tablespace.
Can u please give the query wat i have to fire on v$transaction......
Regards,
Nick.
|
|
|
Posts:
24,284
Registered:
10/11/99
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 30, 2008 11:56 PM
in response to: Nick-- wud b DBA
|
|
|
|
If I'm recalling correctly, you were using dictionary managed tablespaces (shudder) when we were discussing the performance of the TRUNCATE and/or DROP operations, right? Are you importing the data back into another dictionary managed tablespace?
12,000 extents is probably way more than you'd like in a dictionary managed tablespace. It would almost certainly be more efficient to pre-create the table with more appropriate extent sizes and then to import the data into that existing table. Of course, if you're going to the effort of dropping and re-loading a table (and I'm not sure why you would be doing that), you should probably be moving it to a locally managed tablespace on general principle.
Justin
|
|
|
Posts:
4,974
Registered:
11/06/98
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 12:20 AM
in response to: Justin Cave
|
|
|
|
hm.. didn't recall that other thread. yes, he had issues with a DROP.
He didn't say he was doing a DROP only to Re-IMPORT ?!
I would have adviced him to rebuild with a MOVE, using PARALLEL, NOLOGGING and APPEND and to an LMT, UNIFORM.
Incomplete information is what we started with if the DROP and this IMPORT are two sides of the same coin.
|
|
|
Posts:
153
Registered:
03/20/08
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 12:23 AM
in response to: Justin Cave
|
|
|
Yeah Justin, u've recalled correctly.........
Unfortunately, as per the Pre-plan (Assigned to us by someone else), we are importing back into the Dictionary Managed Tablespace....... 
Do u have some suggestion for us so that the import can be made faster if possible... or simply we have to sit and wait......
Regards,
Nick. |
|
|
Posts:
1,296
Registered:
10/27/06
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 12:27 AM
in response to: Nick-- wud b DBA
|
|
|
|
Sorry. As you are doing INDEXES=no, so no use of looking in long ops.
I said that to see the progress of some sorting while creating indexes.
Amardeep Sidhu
Message was edited by:
Amardeep Sidhu
|
|
|
Posts:
24,284
Registered:
10/11/99
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 12:44 AM
in response to: Nick-- wud b DBA
|
|
|
Yeah Justin, u've recalled correctly.........
Unfortunately, as per the Pre-plan (Assigned to us by
someone else), we are importing back into the
Dictionary Managed Tablespace.......
I'd suggest talking to that "someone else" and figuring out why this is being done. Dropping a 1 million extent table from a DMT in 9.2 only to import that same table back into a DMT without even fixing the underlying extent allocation issue is a rather unfortunate plan. At some point, the right answer has to be to fix the plan rather than continuing to slog forward with a poorly conceived plan.
Do u have some suggestion for us so that the import
can be made faster if possible... or simply we have
to sit and wait......
Quoting from my earlier reply-- "It would almost certainly be more efficient to pre-create the table with more appropriate extent sizes and then to import the data into that existing table"
Justin |
|
|
Posts:
153
Registered:
03/20/08
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 12:48 AM
in response to: Amardeep Sidhu
|
|
|
It's okay Amardeep......
Atleast, i got to know the use of long ops.
Regards,
Nick. |
|
|
Posts:
443
Registered:
01/20/08
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 1:02 AM
in response to: Nick-- wud b DBA
|
|
|
|
As Justin have mentioned, please firstly create the table and allocate that much extents and import the data in that table. Would be much more faster.
|
|
|
Posts:
339
Registered:
03/28/99
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 1:06 AM
in response to: Justin Cave
|
|
|
I'd suggest talking to that "someone else" and
figuring out why this is being done. Dropping a 1
million extent table from a DMT in 9.2 only to import
that same table back into a DMT without even fixing
the underlying extent allocation issue is a rather
unfortunate plan.
Actually, it is not unfortunate. It is the plan that will easily get its deserved place on Oracle daily WTF and Computerworld's "Shark Tank". Can you imagine how much joy and happiness would such a story bring to a small group of experienced Oracle DBA geeks on a tech conference, over a pint? |
|
|
Posts:
153
Registered:
03/20/08
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 2:43 AM
in response to: mgogala
|
|
|
|
Hi Mgogola,
With due respect, i want to advise u dat this sarcasm won't lead u anywhere.....
Try to be as humble as u can be..... jus like Justin Sir.... Learn from him.......
Regards,
Nick.
|
|
|
Posts:
38
Registered:
05/24/07
|
|
|
|
Re: Import running Very Slow!!!
Posted:
Jul 31, 2008 3:37 AM
in response to: Nick-- wud b DBA
|
|
|
|
Hi everyone,
I am new to Oracle and know very very little about tablespace and so on. I remeber last time when I tried to import a table into a dbs with the same table name, oracle said there is a table with the same name. import cannot be done. so i dropped the table then imported.
May someone help me?
|
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
