Oracle query is very slow

I have a query that extracts from two INNER joined tables.  The tables contain approximately 30 million rows and 400,000 rows.  The expected results should return around 300,000 rows.  The query runs very vast (less than 1 second) through the first 200,000 rows returned, and then it starts to crawl returning at a rate of about 1000/sec for the remaining rows.

What is causing the apparent slowdown, and what can I do to speed it up?

I have tried adding an index hint as well as 'Cardinality' in the hint line.  Nothing seems to help much.
mdreedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
Have your DBA index both Tables
should speed it back up :)
gplanaCommented:
Probably first records are on cache (RAM) so they are going fast, but after this, a lot of disk reads should be done, so performance is slowing down.

Maybe if you put your query and the structure of the tables involved then we can investigate a little more if it can be optimized.
mdreedAuthor Commented:
What sort of additional information do you need (Oracle scripts, query text, etc)?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DavidSenior Oracle Database AdministratorCommented:
Do an explain plan on the statement and post the results.  There are many prior E-E threads that document how to interpret the plan.

Indexes and RAM notwithstanding, the optimizer requires current table & index statistics.  

Can the query be moved to a materialized view (if the data is not OLTP) or a partition (if most of the data is historic)?

What version of the database, and what are your system constraints (RAM and I/O correctly sized)?  Do you have access to built-in analysis such as AWR in EM.
mdreedAuthor Commented:
Oracle version is 9.
Materialized view is not an option as the data is too dynamic.
I don't know how to run or interpret an explain plan.
The query is running from a computer other than the server.

If the first part of the fetch is fast because it is in RAM, is there a way to increase the available memory for the query?
expert9Commented:
you can get data in chunks to by passing it chunk size.

and if you are using toad, then use the 3rd menu for explain plan.
mdreedAuthor Commented:
How do you specify 'chunk' size?
expert9Commented:
you can specify chunk size with the help of row id, row count....

how you are query in a program, sql or plsql ?
mdreedAuthor Commented:
Querying in VB code using SQL
expert9Commented:
ok then define a chunk size, it depends on the query. if it works fine for 200000 then make chunk of 200000 and execute query again.
mdreedAuthor Commented:
I don't know how to specify chunk size  (see previous response above)
expert9Commented:
can you share the code and query ?
mdreedAuthor Commented:
The query is simply:
.Open sQRY, cn, adOpenStatic, adLockOptimistic

And the sQRY string contains :

SELECT /*+ INDEX(GM_PRC GM_PRC2) */
	   B.sku_num, A.dept_cd, A.des1, B.ret_prc, B.beg_dt, B.end_dt, B.pc_num, A.ve_cd, A.returns 
FROM gm_inv.gm_itm A, prc.gm_prc B 
WHERE (A.itm_cd = b.itm_cd) 
AND (A.inc_plu = 'Y') 
AND b.prc_zone_cd = 'GEN' 
AND (B.prc_grp_cd = '901') 
AND b.end_dt IS NULL 
AND b.pc_tp != 'TMD'

Open in new window

DavidSenior Oracle Database AdministratorCommented:
One other option, preface your query with an increase to the number of rows fetched, default is 15.  Use:
sql> ALTER SESSION SET ARRAYSIZE 500;

In ref to 37802509 above:
sql> EXPLAIN PLAN for SELECT ....... ;
See also http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm
slightwv (䄆 Netminder) Commented:
I think what expert9 is saying is can you paginate the results and only bring back what you need at the time you need it?

Do you need all 300,000 rows brought back at once?
mdreedAuthor Commented:
Yes, I need all 300,00 at once.

ALTER SESSION SET ARRAYSIZE 500 returns an 'invalid option' error on ARRAYSIZE.
slightwv (䄆 Netminder) Commented:
Are you sure the performance slow down is at the databsae layer and not the VB layer?

Do you have sqlplus?  Try spooling the results of the query to get a database time.  Create a script with the following:
set termout off
set pages 0
set timing on
spool myfile.txt
select ... --your select statement
;

spool off


Then execute the script:
SQL> @myscript.sql

check the timing at the bottom oy myfile.txt
mdreedAuthor Commented:
slightwv

I am doing all of my testing with Toad.  Can I run the above script in Toad?
slightwv (䄆 Netminder) Commented:
I beleive Toad accepts sqlplus commands from the the worksheet and hitting F5 but I also believe Toad has it's own buffering built into it.  I'm not a Toad user so cannot say for sure.

Do you have a login to the database server itself?  If so, not only could you run sqlplus from there, it would help eliminate the network as part of the performance issue.
mdreedAuthor Commented:
I will give it a try on the Oracle server (black screen) and let you know.
DavidSenior Oracle Database AdministratorCommented:
Agreeing with 37803003, the more you can do to deconstruct (simplify) the root cause, the better.  

My bad on the syntax, it's SET ARRAYSIZE 500
mdreedAuthor Commented:
I ran the recommended script from the Oracle server via PL/SQL and the results were the same as in Toad and VB.  It retrieved pretty fast for about half of the records then started to crawl and finally finished after 35 minutes.
DavidSenior Oracle Database AdministratorCommented:
Reminder on the explain plan from 37802817 above.
slightwv (䄆 Netminder) Commented:
While we wait for the explain plan, looks like it is time for good old-fashioned tuning.

Mentioned above:  Are you licensed for ADDM and AWR?

If not, try statspack:
http://www.dba-oracle.com/art_statspack.htm

First make sure statistics are up to date.
Then: create a statspack snapshot.
Start the quuery.
about the time it stats slowing down: take a snapshot
At the end take a snapshot.

Then sprreport from 1 and 2, 2 and 3 and 1 and 3.  See if you can see where the problem is.
mdreedAuthor Commented:
When I run the script with EXPAIN PLAN FOR the Toad status bar says 'Explained' indicating to me that it ran.  But when I select View/Explain plan it gives an error saying 'Table or view does not exist'.  
Then I tried to run it on the Oracle server and got :
 explain plan for
  2  SELECT /*+ INDEX(GM_PRC GM_PRC2) */
  3  B.sku_num, A.dept_cd, A.des1, B.ret_prc, B.beg_dt, B.end_dt, B.pc_num, A.ve
_cd, A.returns
  4  FROM gm_inv.gm_itm A, prc.gm_prc B
  5  WHERE (A.itm_cd = b.itm_cd)
  6  AND (A.inc_plu = 'Y')
  7  AND b.prc_zone_cd = 'GEN'
  8  AND (B.prc_grp_cd = '901')
  9  AND b.end_dt IS NULL
 10  AND b.pc_tp != 'TMD';

Explained.

But, I don't know where it put the explanation.  Can you tell that I am an SQL Server guy and not an Oracle guy?
slightwv (䄆 Netminder) Commented:
After you get 'Explained', you just need to query it:

select * from table(dbms_xplan.display);
mdreedAuthor Commented:
Thanks.
Image of plan screen output is attached.
ExplainPlan.jpg
slightwv (䄆 Netminder) Commented:
In the future, do not post screen shots.  You can spool out the output and post it as text.

Select itself looks fine.

No we are down to a database tuning issue.  Since you confirmed that capturing the data does slow down over time by running and spooling the output on the databsae server:  The issue alsmost has to be database/server tuning related.

ADDM/AWR or Statspack will greatly assist in this.

At a high level you can monitor things like memory, CPU and disk I/o while it is running to see if you can observe what is causing the slowdown.

Another high-level approach is from sqlplus on the server again:
SQL> set autotrace traceonly
SQL> Select...

Setting autotrace on will also provide the actual statistics for the query.  This should help narrow down where to look.
mdreedAuthor Commented:
We don't have ADDM/AWR or Statpack. Can they analyze a query or are they for general system health?

The TRACEONLY process is running.  At least I think it's running.  It has created the output text file of zero file size and appears to be running.  Maybe I'll know in another 35 minutes if it runs as long as before.
mdreedAuthor Commented:
Trace output of query.                                  

cgesprod> SELECT /*+ INDEX(GM_PRC GM_PRC2) */
  2             B.sku_num, A.dept_cd, A.des1, B.ret_prc, B.beg_dt, B.end_dt, B.pc_num, A.ve_cd, A.returns
  3  FROM gm_inv.gm_itm A, prc.gm_prc B
  4  WHERE (A.itm_cd = b.itm_cd)
  5  AND (A.inc_plu = 'Y')
  6  AND b.prc_zone_cd = 'GEN'
  7  AND (B.prc_grp_cd = '901')
  8  AND b.end_dt IS NULL
  9  AND b.pc_tp != 'TMD';

480000 rows selected.

Elapsed: 00:17:54.61

Execution Plan
----------------------------------------------------------                      
              0                                                                 
SELECT STATEMENT Optimizer=RULE (Cost=2 Card=1 Bytes=96)                        
                                                                                
              1                  0                                              
  NESTED LOOPS (Cost=2 Card=1 Bytes=96)                                         
                                                                                
              2                  1                                              
    TABLE ACCESS (BY INDEX ROWID) OF 'GM_PRC' (Cost=1 Card=1 Bytes=62)          
                                                                                
              3                  2                                              
      INDEX (RANGE SCAN) OF 'CUSTOM2GM_PRC2' (NON-UNIQUE) (Cost=2 Card=1)       
                                                                                
              4                  1                                              
    TABLE ACCESS (BY INDEX ROWID) OF 'GM_ITM' (Cost=1 Card=1 Bytes=34)          
                                                                                
              5                  4                                              
      INDEX (UNIQUE SCAN) OF 'GM_ITM_PK' (UNIQUE)                               
                                                                                




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
    1808169  consistent gets                                                    
     130640  physical reads                                                     
        156  redo size                                                          
   25360442  bytes sent via SQL*Net to client                                   
     352641  bytes received via SQL*Net from client                             
      32001  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
     480000  rows processed                                                     

cgesprod> spool OFF

Open in new window

slightwv (䄆 Netminder) Commented:
>>We don't have ADDM/AWR or Statpack.

Statspack is free.

>>Can they analyze a query or are they for general system health?

They are at the database level.  From everyting you posted, the query itself is fine.  If there is a performance issue, the next thing to look at is overall database performance.

>>Trace output of query

Honestly, that doesn't look that bad.  Most of the data was retrieved from memory (consistent gets).  A fraction was read from disk (physical reads).

As I said before:  Next place to look is at the database as a whole.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdreedAuthor Commented:
Thanks for all the help.  I will investigate Statspack.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.