RE: Export w/ rows=N taking hours......

Well, as usual, I found the solution right after posting my question.....  

Sorry for the spam.  

For those who are interested:
if you have optimizer_mode=FIRST_ROWS and do NOT have stats on SYS schema, export performance suffers. The solution (which I have not yet tested) is to analyze the SYS schema.  

-Mark
 

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"Exception: Some dividends may be reported as qualified dividends but are not qualified dividends. These include:

• Dividends you received on any share of stock that you held for less than 61 days during the 121-day period that began 60 days before the ex-dividend date. The ex-dividend date is the first date following the declaration of a dividend on which the purchaser of a stock is not entitled to receive the next dividend payment. When counting the number of days you held the stock, include the day you disposed of the stock but not the day you acquired it. See the examples below. Also, when counting the number of days you held the stock, you cannot count certain days during which your risk of loss was diminished. See Pub. 550 for more details."

  --IRS, Form 1040-A Instruction Booklet, Line 9b: Qualified Dividends  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Friday, March 17, 2006 10:50 AM
To: oracle-l_at_freelists.org
Cc: Consul, Mamta
Subject: Export w/ rows=N taking hours......

Does anyone have any insight as to why taking an export w/ rows=n would take many, many hours?

Details are as follows:
Solaris 8, running Oracle 9.2.0.6, 64 bit Schema being exported has 2,451 tables.

Parameter file being used is:
userid=system/<password removed>
file=proddta_jdeprd_nodata.dmp
log=exp_proddta_jdeprd_nodata.log
rows=n
owner=proddta
direct=y

Yes, I'm aware that direct=y won't do anything w/ rows=n, but it ought to be harmless.

Export session has been running for 18 hours. V$SESSION_WAIT shows db file sequential read waits, and some of the queries are:

SELECT COLNAME, COLNO, PROPERTY      FROM   SYS.EXU9CCL      WHERE  CNO
= :1      ORDER  BY COLNO; 

SELECT SYNNAM, SYNNAM2, SYNTAB, TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO

FROM   SYS.EXU9PTS      WHERE  SYNOBJNO IN (          SELECT SYNOBJNO
FROM SYS.EXU9TYPT          WHERE TABOBJNO = :1 )      

ORDER BY SYNTIME Anyone have any ideas or clues why it would take 18+ hours to do a rows=n export of less than 2,500 tables?

Thanks,

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 17 2006 - 10:08:18 CST