| Import stalled with 'SQL*Net message from client' wait event [message #426523] |
Fri, 16 October 2009 05:19  |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hi,
I am tring to import certain schemas from export taken from other database with same version
(Schema import)
I have copied the dump files to the target database and started the import
my par files is as below
file=(e:\export\dump1.dmp,e:\export\dump2.dmp,e:\export\dump3.dmp,e:\export\dump4.dmp,e:\export\dump5.dmp,e:\export\dump6.dmp,e:\export\dump7.dmp,e:\export\dump8.dmp,e:\export\dump9.dmp)
log=e:\export\db1_imp_121009.log
fromuser=(user1,user2,user3,user4,user5,user6)
touser=(user1,user2,user3,user4,user5,user6)
buffer=2000000
commit=y
grants=n
analyze=n
userid=system/password
Now the import started well which imported hundreds of table in first schema but since lats 2 days it stalled a particular table
Apart from the import session and another session from from which i am checking the wait event there is no other session opened on the database
I checked the wait event for the import session and since yesterday it is showing as 'SQL*Net message from client'
Also status of the session is 'INACTIVE'
The alert.log is not updated since yesterday morning and same is the import log file, which is not updated since yesterday morning
I have checked that all *.dmp files are at approprate location
I tried to trace it with 10046 but no trace file is getting generated
Could anybody help me on this?
Thanks and Regards,
Chetana
|
|
|
|
|
|
|
|
| Re: Import stalled with 'SQL*Net message from client' wait event [message #426528 is a reply to message #426523] |
Fri, 16 October 2009 05:58  |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Thanks ThomasG
Following are the last lines of import log
before you scroll down a quick update
I created a table in system schema and dropped the table but none of the datafile, logfile got updated with timestamp !!
SQL> conn system
Enter password:
Connected.
SQL> create table a(n number);
Table created.
SQL> drop table a;
Table dropped.
Here is the import log
. . importing table "PG_ACCESSORY_PRICE_GUIDES" 165885 rows imported
. . importing table "PG_ACCESSORY_REQUIREMENTS" 408 rows imported
. . importing table "PG_BATCH_SCHEDULES" 1 rows imported
. . importing table "PG_BATCH_SCHEDULE_DETAILS" 29 rows imported
. . importing table "PG_BENCHMARK_HISTORIES" 0 rows imported
. . importing table "PG_CALCULATION_TIMES" 140 rows imported
. . importing table "PG_CORRECTION_COLUMNS" 19 rows imported
. . importing table "PG_CRITERIA" 93662 rows imported
. . importing table "PG_DAILY_REQUESTS" 0 rows imported
. . importing table "PG_DAILY_REQUEST_HISTORIES" 440 rows imported
. . importing table "PG_DAILY_TEMP" 440 rows imported
. . importing table "PG_MANUFACTURER_OUTPUTS" 0 rows imported
. . importing table "PG_MANUFACTURER_REQUIREMENTS" 100417 rows imported
. . importing table "PG_MODEL_OUTPUTS" 0 rows imported
. . importing table "PG_MODEL_REQUIREMENTS" 112860 rows imported
. . importing table "PG_PAA_INTERMEDIATE" 0 rows imported
. . importing table "PG_PAC_INTERMEDIATE" 0 rows imported
. . importing table "PG_PGD_INTERMEDIATE" 0 rows imported
. . importing table "PG_PMO_INTERMEDIATE" 0 rows imported
. . importing table "PG_PNO_INTERMEDIATE" 0 rows imported
. . importing table "PG_POO_INTERMEDIATE" 0 rows imported
. . importing table "PG_PPD_INTERMEDIATE" 0 rows imported
. . importing table "PG_PRICE_GUIDES" 4203662 rows imported
. . importing table "PG_PRICE_GUIDES_BAK" 277811 rows imported
however on screen I can see that it has gone beyond
as following
. . importing table "PG_PNO_INTERMEDIATE" 0 rows imported
. . importing table "PG_POO_INTERMEDIATE" 0 rows imported
. . importing table "PG_PPD_INTERMEDIATE" 0 rows imported
. . importing table "PG_PRICE_GUIDES" 4203662 rows imported
. . importing table "PG_PRICE_GUIDES_BAK" 277811 rows imported
. . importing table "PG_PRICE_GUIDE_HISTORIES" 12410 rows imported
. . importing table "PG_PTL_DETAIL" 161182 rows imported
. . importing table "PG_RANGE_OUTPUTS" 0 rows imported
. . importing table "PG_RANGE_REQUIREMENTS" 58163 rows imported
. . importing table "PG_REFRESH_HISTORIES" 10 rows imported
. . importing table "PG_REQUESTS" 22050 rows imported
. . importing table "PG_REQUEST_DEFERRED_HISTORIES" 0 rows imported
. . importing table "PG_TEMP" 0 rows imported
. . importing table "PG_TEMPLATES" 41017 rows imported
. . importing table "PG_TERM_REQUIREMENTS" 59071 rows imported
. . importing table "PLANNED_PAYMENTS" 2128925 rows imported
. . importing table "PLANNED_TRANSACTIONS" 105957704 rows imported
Regards,
Cheatana
|
|
|
|
| Re: Import stalled with 'SQL*Net message from client' wait event [message #426530 is a reply to message #426528] |
Fri, 16 October 2009 06:42 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Ok. That's quite a lot of rows, and when there are a lot of indexes that need to be re-build it is possible that it takes this long, depending on how fast the disk are.
Can you have a look at the result of this:
select DISTINCT s.sid,s. SERIAL#, event, p1text, p1, p2text, p2, p3text, p3,
wait_time, seconds_in_wait, state,
osuser, program,client_info, s.MODULE, sql_text
from v$session s
join v$sql q ON q.hash_value = s.sql_hash_value
where event not in ('SQL*Net message from client',
'SQL*Net message to client', 'jobq slave wait')
AND event not like 'PX Deq Credit%';
|
|
|
|
| Re: Import stalled with 'SQL*Net message from client' wait event [message #426536 is a reply to message #426523] |
Fri, 16 October 2009 07:11 |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hi ThomasG,
Good news is that the import suddenly got pushed by 10-12 tables just minutes before
However I would like to know the reason
Though after 1.5 days the import moved a little evrything was standstill till now including alert.log file
I tweaked your query little and the result file is attched herewith
It will be great if yiu can suggest me something after finding the attached file since this problem may happen again
Many thanks for your help
Regards,
Chetana
|
|
|
|
| Re: Import stalled with 'SQL*Net message from client' wait event [message #426542 is a reply to message #426523] |
Fri, 16 October 2009 07:22 |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hi ThomasG,
a quick update
the import is now stalled again at another table 80598233 rows
but this time alert.log is showing current time and import.log is showing time 1 hour back
This time the wait event is 'direct path read'
Thats why the session was not captured in last file I sent to you
Thanks and Regards,
Chetana
|
|
|
|
| Re: Import stalled with 'SQL*Net message from client' wait event [message #426545 is a reply to message #426542] |
Fri, 16 October 2009 07:33 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
"Direkt Path Read" means Oracle is busy reading from database files.
Which it usually is during an import when the indexes are rebuild.
As are rule of thumb, that's usually what takes the longest during an import. So you can expect that once all rows are loaded into a table the log will seem to "hang" at that point while the indexes on the table are re-build.
There is a way to do the import without creating the indexes, and then create the indexes afterwards. Have a look at the "Delaying Index Creation" in the Import documentation.
But so far I have found that it's not that much of an overall speed improvement to justify the three import steps it takes over the one-shot import including the indexes.
|
|
|
|
| Re: Import stalled with 'SQL*Net message from client' wait event [message #426785 is a reply to message #426523] |
Mon, 19 October 2009 11:22 |
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
|
Senior Member |
|
|
Hi ThomasG,
Regarding 'Direct Path Read' your observation is correct since index rebuild will cause this
As far as 'Sql net message from client', I realized what was happening which is strange
Since i executed the import command from manually and the session was executing for long time, 3-4 days, after spending long time importing large tables, my session was getting idele thu causing this wait event and status as 'INACTIVE' in v$session
I realized that 'Clicking' on 'PressingEnter' on the screen could not make it push further, but to my surprise whenever I right clicked it the session moved forward in such stalled conditions
It appears strange but I had done this 3-4 times during the import and everytime it worked
I was doing this by Remote Desktop to windows server, may be this has caused the issue
Many thnks for your help on this
Thanks and Regards,
Chatana
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
