Toad Script execution very slow

Toad for Oracle
1

I'm running a SQL script that extracts 5 columns from three tables joined together,- max 10,000 rows. Version 16.1.
When I run it in SQL Plus by Oracle, it completes quickly and spools the files.

When I run it in Toad as a script - it is very slow - taking 20 minutes to spool 3,000 rows so far. I
like this feature because it generates data grids for each sql run in the script.

Is there a way to change options either in the script or in toad to allow the script spool operation happen faster?

Any help would be greatly appreciated!

Eric

2

That is very slow. For me, using 16.1, I can select all 3000ish rows out of DBA_TABLES, as a script with grids enabled, in about 20 seconds. Of course, if I run with F9, it's nearly instant. Do you see similar behavior with dba_tables?

Do you have large values (clobs or big varchar2's) in those 5 columns?

There aren't really any options that will make it go faster, except maybe to disable the grids, but you already said you like the grid, so you probably don't want to turn it off.

3

Thanks, John. Yea I use the data grids to do a rowcount and capture screen shot of the rowcount dialog box for compliance purposes.

I also noticed that the speed slows down as more records are being retrieved, When I started the script - it was extracting about 20-30 records per second. This morning - it was doing 1-2 per second.

The SQL is really simple - its Oracle EBS. varchar2 data. Here's the query. It has a very low resource cost. I also tried doing an extract in Oracle SQL Developer and it worked instantly - very quick.

Last year around the same time I ran this same extract (I think it was under Toad 15) and it ran really quickly.

SPOOL output.txt;

SELECT
( (((A.APPLICATION_ID || '|') || RESPONSIBILITY_ID) || '|')
|| CONCURRENT_PROGRAM_ID)
|| '|'
FROM APPS.FND_RESPONSIBILITY_VL D,
APPSRO.FND_REQUEST_GROUPS B,
APPSRO.FND_REQUEST_GROUP_UNITS C,
APPS.FND_CONCURRENT_PROGRAMS_VL A
WHERE (NVL (START_DATE, SYSDATE) <= SYSDATE)
AND (NVL (END_DATE, SYSDATE) >= SYSDATE)
AND B.REQUEST_GROUP_ID = D.REQUEST_GROUP_ID
AND B.REQUEST_GROUP_ID = C.REQUEST_GROUP_ID
AND REQUEST_UNIT_ID = CONCURRENT_PROGRAM_ID
AND UNIT_APPLICATION_ID = A.APPLICATION_ID;

If I run the query (not in script mode) and use the export function - it too is really quick. it does all the rows in under 5 seconds!

Is there a tool or a log we can see to determine what Toad is doing during the extracts?

I disabled grids and reran query - no difference - BTW it looks like Toad doesn't actually create the grid until the SQL statement is done (The data grid is not shown while Processing Script dialog is running)

Thanks!

4

Hi Eric,

Oh, ok. I didn't realize that it slows down as it runs. I tried to reproduce that too with a "select * from dba_source", but it ran fast through the 30,000 row mark. Then I cancelled.

So, I have a few more questions:

  1. Is this script very long?
  2. Has Toad been running for very long before you start the script?

If either of these is a "yes", does it perform better if you restart Toad, then put that statement all by itself in a script and run it?

You might want to try the beta version, if you are able. We recently solved a memory leak related to script execution. I don't know if that is related to the slowness (I believe that the leak existed in version 15 as well), but it is worth a try.

You can get it here.

-John