Solved

Oracle query is very slow

Posted on 2012-04-03
32
520 Views
Last Modified: 2012-04-05
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.
0
Comment
Question by:mdreed
  • 15
  • 7
  • 4
  • +3
32 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 37802457
Have your DBA index both Tables
should speed it back up :)
0
 
LVL 15

Expert Comment

by:gplana
ID: 37802458
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.
0
 

Author Comment

by:mdreed
ID: 37802471
What sort of additional information do you need (Oracle scripts, query text, etc)?
0
 
LVL 23

Expert Comment

by:David
ID: 37802509
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.
0
 

Author Comment

by:mdreed
ID: 37802574
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?
0
 

Expert Comment

by:expert9
ID: 37802660
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.
0
 

Author Comment

by:mdreed
ID: 37802719
How do you specify 'chunk' size?
0
 

Expert Comment

by:expert9
ID: 37802732
you can specify chunk size with the help of row id, row count....

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

Author Comment

by:mdreed
ID: 37802755
Querying in VB code using SQL
0
 

Expert Comment

by:expert9
ID: 37802766
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.
0
 

Author Comment

by:mdreed
ID: 37802776
I don't know how to specify chunk size  (see previous response above)
0
 

Expert Comment

by:expert9
ID: 37802785
can you share the code and query ?
0
 

Author Comment

by:mdreed
ID: 37802802
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

0
 
LVL 23

Expert Comment

by:David
ID: 37802817
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802849
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?
0
 

Author Comment

by:mdreed
ID: 37802865
Yes, I need all 300,00 at once.

ALTER SESSION SET ARRAYSIZE 500 returns an 'invalid option' error on ARRAYSIZE.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802903
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
0
 

Author Comment

by:mdreed
ID: 37802990
slightwv

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37803003
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.
0
 

Author Comment

by:mdreed
ID: 37803043
I will give it a try on the Oracle server (black screen) and let you know.
0
 
LVL 23

Expert Comment

by:David
ID: 37803145
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
0
 

Author Comment

by:mdreed
ID: 37806682
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.
0
 
LVL 23

Expert Comment

by:David
ID: 37806736
Reminder on the explain plan from 37802817 above.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37806779
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.
0
 

Author Comment

by:mdreed
ID: 37807191
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37807219
After you get 'Explained', you just need to query it:

select * from table(dbms_xplan.display);
0
 

Author Comment

by:mdreed
ID: 37807272
Thanks.
Image of plan screen output is attached.
ExplainPlan.jpg
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37807318
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.
0
 

Author Comment

by:mdreed
ID: 37807587
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.
0
 

Author Comment

by:mdreed
ID: 37807914
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

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37810666
>>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.
0
 

Author Closing Comment

by:mdreed
ID: 37811511
Thanks for all the help.  I will investigate Statspack.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Getting network error using utl tcp package 7 83
oracle query 15 63
Cannot open form error 6 49
Pfile and SPfile - Oracle 2 35
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now