Re: 8.1.7.4 + 2 patches hangs on imp, strange waits

"Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote in message news:<HmWPa.2247$3G5.61_at_nwrdny03.gnilink.net>...
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0307111033.469d6c41_at_posting.google.com...
> Those waits are common. Most are idle waits. If you open a sql*plus session
> you will see sql*net message from client. The waits don't seem to be
> pointing to
> any obvious problem.

Yeah, my brain was in exp land when I looked at that, had to slap my forehead after posting. Always a risk in using fake addresses with google posting, no easy cancel. :-)

>
> Try this:
> Run a trace on the session doing the import (using oradebug or
> dbms_system.set_sql_session_in_trace).
> If using oradebug try event 10046 level 12. See what kind of waits that
> session is seeing in real time.

Will try. I'm really starting to think those patches didn't fix what 8.1.7.4 broke, though.

>
> and since you have set commit = y ... check on that table: are rows
> increasing in that table? or is the session
> just hung? You can also check v$transaction (used_ublk) for more clues.

I've tried several things, the table always gets stuck with 2168324 rows imported. I tried importing just that table (from the next nights export) into an instance running 8.1.7.2, imported 2981011 rows just fine. Now I'm trying just that table from the file being used on 8.1.7.4 to see if it hangs, I expect it to... it did.

I think if imp is just spinning the cpu and nothing else is happening, that would be considered hung? :-) For the heck of it, I let it go Friday to Monday, no change.

  1* select used_ublk from v$transaction SQL> / no rows selected

Not sure what I'm looking for there? If imp is hung after commit, there wouldn't be a transaction?

One of the things I tried was changing optimizer_mode from rule to all_rows, since imp does that odd /*+NESTED_TABLE_SET_REFS+*/ hint on everything, I figured, what the heck (I saw a metalink forum posting that inferred a performance issue there, but it didn't really say). No difference.

> Also: What kind of table is that? IOT? normal?
> Since the listener is down .. I guess you are doing the import
> locally.
> Make doubly sure that you don't have the archiver stuck (though
> you should have seen it in the alert log)

Normal table, has a couple indexes, but the import doesn't get that far, get's stuck on the data.

noarchivelog mode at this time. (Test machine will be turned into standby db soon)

>
> Then try bigger buffer size if its a normal table. Also, try following
> faster methods of import (like building
> indexes later and after increasing sort area size). I think Connor's website
> has some tips on that (www.oracledba.co.uk -> Tuning).

Somewhere in the bug or patch description it mentions the workaround is to use _smaller_ buffers. I habitually use 20000000, I was going to try taking that out...

sort_area_size is tuned for normal operations, probably could be enlarged for this. Useless to spend too much time on it though, since it's not going to be done a lot, and the production box is big and fast enough to just do it overnight if necessary, and hopefully it won't be necessary for quite a while.

The most important thing is that imp works, and right now I can't say that it does in 8.1.7.4.

Thanks for mentioning Connor's site, I've been doing this so long it always helps to be reminded of the tricks. One needs to avoid tricks at this particular customer though, I have trouble convincing them not to be dependent on exp for backups! :-O

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/sun/currents/news_1c13potcar.html