Home » RDBMS Server » Server Utilities » Import stalled with 'SQL*Net message from client' wait event (Oracle 9.2.0.6.0 on Windows)
Import stalled with 'SQL*Net message from client' wait event [message #426523] Fri, 16 October 2009 05:19 Go to next message
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 #426524 is a reply to message #426523] Fri, 16 October 2009 05:32 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Apologies

I should have posted this topic in 'Server Utilities' Forum

Moderators, Please help me by moving this to 'Server Utilities' forum

One update on the topic

None of the database file is updated since yestrday morning including temp and undo

Thanks and Regards,
Chetana
Re: Import stalled with 'SQL*Net message from client' wait event [message #426526 is a reply to message #426524] Fri, 16 October 2009 05:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What are the last few lines of the import log?
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous message
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
Previous Topic: Import errors
Next Topic: Import Schema in different tablepaces (merged 3)
Goto Forum:
  


Current Time: Sat Dec 03 08:16:19 CST 2016

Total time taken to generate the page: 0.05051 seconds