Oracle query is very slow

Have your DBA index both Tables
should speed it back up :)

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.

What sort of additional information do you need (Oracle scripts, query text, etc)?

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.

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?

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.

How do you specify 'chunk' size?

you can specify chunk size with the help of row id, row count....

how you are query in a program, sql or plsql ?

Querying in VB code using SQL

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.

I don't know how to specify chunk size  (see previous response above)

can you share the code and query ?

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'

Select allOpen in new window

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

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?

Yes, I need all 300,00 at once.

ALTER SESSION SET ARRAYSIZE 500 returns an 'invalid option' error on ARRAYSIZE.

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

slightwv

I am doing all of my testing with Toad.  Can I run the above script in Toad?

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.

I will give it a try on the Oracle server (black screen) and let you know.

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

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.

Reminder on the explain plan from 37802817 above.

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.

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?

After you get 'Explained', you just need to query it:

select * from table(dbms_xplan.display);

Thanks.
Image of plan screen output is attached.
ExplainPlan.jpg

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.

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.

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

Select allOpen in new window

Thanks for all the help.  I will investigate Statspack.