| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: What to check?
David,
That looks like a pretty basic script. There is no ordering, no functions, nothing at all really except for some basic select statements (assuming these are all tables and not views). I don't know what is in dumptables.sql, but presumably it sets the linesize and pagesize and defines the column separator as a comma.
My first suggestion would be to put in some logging messages between each table. Even if you just add a "set timing on" to see the elapsed time of each query. Or at the very least, look at the timestamp on each spool file - I believe it will be the time the file began to be created. You're looking to see if the "problem" is isolated to a single table or not.
Hundreds of thousands of records isn't real big for a table - even when spooling to a file.
If you are considering changing the script you could try redirecting the output of sqlplus to a file (esp if on unix) rather than using spool - it is sometimes faster. Whatever you do, you want to make sure it isn't displaying the output to a screen - this always seems to slow things down.
You could try copying files of a similar size to the destination directory on each disk - just to see if there is a noticeable difference in write speed. Like wise you could do something like "select /*+ full */ count(*) from <blah>" on each table on each machine to get an idea of full table scans.
Regards,
Mark.
"Nguyen, David
M" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<david.m.nguye cc:
n_at_xo.com> Subject: RE: What to check?
Sent by:
root_at_fatcity.c
om
04/03/2003
07:49
Please respond
to ORACLE-L
All three machines having the same amount of CPUs and memory, and he uses the same script to run on three machines, some of tables containing hundred thousands of records. Below is a portion of his script. Is there a way to improve his SQL command?
@Dumptables.sql
spool lata.csv
select * from lata;
spool off
spool subscriber.csv
select * from subscriber;
spool off
spool non_subscriber.csv
select * from non_subscriber;
spool off
spool numbering_plan.csv
select * from numbering_plan;
spool off;
spool service_area.csv
select * from service_area;
spool off
spool carrier.csv
select * from carrier;
spool off
spool escaped_number.csv
select * from escaped_number;
spool off
exit;
Regards,
David
-----Original Message-----
Sent: Sunday, March 02, 2003 8:09 PM
To: Multiple recipients of list ORACLE-L
David,
Have you checked the most obvious... The number of rows in the table?
Also, you say "three different Solaris8 machines"... How "different" are they? Are the disk subsystems the same on all three? How identical are their configurations? How similar are the init.ora files on all three machines (perhaps sort_area_size or something similar depending on the query to dump the table)? Unfortunately there's a lot of possibilities about what might cause the slow down.
The other suggestions of wait events and execution plans are valid ideas. Hopefully one of these will give a hint about where to look for the cause.
Regards,
Mark.
"Nguyen, David
M" To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
<david.m.nguye cc:
n_at_xo.com> Subject: What to check?
Sent by:
root_at_fatcity.c
om
01/03/2003
08:09
Please respond
to ORACLE-L
Someone writes a SQL script to dump a table on three different database on three different Solaris8 machines, he complaines one of machines took 6 hours to dump a table while other two's only take one hour. He asks me to investigate why. I log into the machine in question to check I/O statistic, memory, CPU usage and found no problem. What else should I check here?
Regards,
David
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Mar 03 2003 - 21:18:45 CST
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |