Response time

besco · Post by **besco** » Tue Jun 15, 2004 7:25 am

Hi,
With the stage ORAOCI8, my request takes more than 10 minutes. I seen in the monitor that DataStage draft 100 rows/sec. Is it normal ?

kcbland · Post by **kcbland** » Tue Jun 15, 2004 7:33 am

Are you selecting or loading? If you're selecting, what kind of query are you doing, is it a table dump or a complex join? Are there a lot of bytes of data to be returned? 1000 columns will spool at a different rate than 10 columns because the measurement of performance is not row based but chars/sec. Is your query doing large scans with dead spots on the table where no data returns because of filtering WHERE clauses? Have you tuned the query, checked the explain plan, any parallel query going on?

If your loading, are you inserting, updating, or both? What are the indexes on the table, are you bogged down with loading lots of columns and updating lots of indexes? What about updates, are you naming the primary key or doing wildcard WHERE clauses? How many rows, what's the commit count?

What about database load? Have you checked server utilization? What about load balancing, are you restricted in any way? What about parallel DML, is that interfering?

Your question is like going to the doctor and saying you don't feel well. We need a little bit more information if we're to help you.

besco · Post by **besco** » Tue Jun 15, 2004 9:26 am

You ask too many questions. Are you an expert Oracle ?
my request is just a join between two table. This request bring back 80000 rows. There is also a select in a select.

Code: Select all

SELECT m.email 
FROM erreur e, #NomClient#_member m 
WHERE (e.member_id = m.member_id) 
AND (e.client_id = m.client_id) 
AND (e.client_id = (select client_id from client where upper(name)='#NomClient#')) 
AND (e.code_type>0)

In table member, there's 1800000 rows and 55000000 rows for table erreur. You see better!

ogmios · Post by **ogmios** » Tue Jun 15, 2004 9:57 am

Check whether there's a function based index on upper(name) on table client for starters.

Then run the query in SQL*Plus and see how long that takes: if the query there also takes "long" you've already pinpointed your bottle-neck. (hint: use tkprof to see what's happening)

If you would execute this job a lot of times I would rethink your design and make the client name a binded parameter for Oracle, else Oracle will have to do a lot of hard parsing. Anyway you will have to do this if you want to have your data for a lot of customers within 24 hours

.

My gut feeling is that your speed is the maximum you will get if m.email is a big field, DataStage needs a lot of time moving large columns e.g. 80000 rows x 4Kb = +- 300Mb

Ogmios

kcbland · Post by **kcbland** » Tue Jun 15, 2004 10:12 am

Just remember, it's not how long it takes to get the first row in a query, but how long it takes to get the last row. You need to run this query and spool the entire results to get the full appreciation of its runtime. You will need to tune your 3 table join. Ogmios has given you a headstart, and yes, I'm somewhat good with Oracle.

Your query has multiple things wrong with it, starting with what probably is a full table scan on client. If you're querying a 55 million row table (erreur) hopefully you're using a parallel query with some ability to prune partitions (hopefully your table is partitioned and you have some means to limit the partition ranging).

tonystark622 · Post by **tonystark622** » Tue Jun 15, 2004 10:40 am

Some other tiny thing that may make a difference is to change the array size on the Oracle OCI stage. I would suggest starting at 1000 and going up or down from there as you re-run the job to see if you get any better retrieval speed.

Tony

tonystark622 · Post by **tonystark622** » Tue Jun 15, 2004 10:41 am

Some other tiny thing that may make a difference is to change the array size on the Oracle OCI stage. I would suggest starting at 1000 and going up or down from there as you re-run the job to see if you get any better retrieval speed.

Tony

besco · Post by **besco** » Wed Jun 16, 2004 2:07 am

The request (select client_id from client where upper(name)='#NomClient#') is instantaneous with SQL+. The big request takes about 11 minutes when I execute it with SQL+.

you think that cannot go more quickly ogmios ?
Can you explain me your idea about a redesign ?

ogmios · Post by **ogmios** » Wed Jun 16, 2004 9:58 am

besco wrote: you think that cannot go more quickly ogmios ?

Give a describe of your input tables and output table and an overview of your transformation (if they're anything else than a straight move).

About redesigning, it depends on the exact requirements E.g. for how many customers are you going to execute the query. You could e.g. make a table with the customers to be processed, add a customer name in the output table and do all of the customers at the same time (without arguments in the SQL).

In a last instance I would suggest using external tables/sqlloader to load data.

Ogmios

ray.wurlod · Post by **ray.wurlod** » Wed Jun 16, 2004 4:09 pm

Indexes on constrained and joining columns will help the SELECT but hinder the update of these tables. You may need an index on the virtual column UPPER(name) to assist the inner query, though you say this is quick.

ogmios · Post by **ogmios** » Thu Jun 17, 2004 11:04 am

So I guess the email field is pretty big, this is probably why you get so slow throughput.

How many customers do you process.

For the redesign, maybe I'm mixing two questions, there seem to be 2 different people having similar performance problems. what I thought was happening is that you execute your query for a lot of customers by physically running the jobs one after the other. In such a case it usually is faster to "process" all customers at the same time in one query.

Anyway get rid of the hashed file.

Ogmios