Performance Issue

We have a Report and execute the same from SQL*Plus
Check the statistics
       3728  recursive calls                                                                                              
      81355  db block gets                                                                                        
     493836  consistent gets                                                                                      
     493579  physical reads                                                                                        
       1548  redo size                                                                                            
      23243  bytes sent via SQL*Net to client                                                                      
       9003  bytes received via SQL*Net from client                                                                
         16  SQL*Net roundtrips to/from client                                                                    
          2  sorts (memory)                                                                                        
          0  sorts (disk)                                                                                        
        215  rows processed

I want to reduce physical reads  and consistent gets  (if required).Pls suggest me how to do this..and tell me the cause fr this

Please also let me know is we need to reduce recursive call..As per parameter setup cursor_sharing is EXACT

I have using WITH clause in the query
Who is Participating?

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

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.

slightwv (䄆 Netminder) Commented:
>> (if required)

The 'if required' concerns me.  What makes you think the recursive calls is 'bad'?

Please post the execution plan (or plans) for the SQL.
gajmpAuthor Commented:
Thanks for your reply. we can reduce the logical read also (if possible) thats y i have mentioned "if required". I am sure, if we run the same query again then consistent gets  will be reduced.

I am not sure recursive call will do Parsing or not. thats y i want to reduce that. if the recursive call wont do parsing again then im ok with that recursive call.
Start by checking the basics, like that indexes exist on all columns involved in criteria or joins. High reads and gets can be due to full scans due to missing indexes. You can do it manually by identifying all of the joins in the query, or by looking at the plan.

As Steve says, post your plan so he can help more with the specific query.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
To add to what mrjoltcola said:

You really don't tune from the metrics.  They are a part of the overall tuning process but you don't start the process with: How do I reduce disk reads.  The answer to that is easy: Get more RAM.

You start off by identifying a 'slow' process.  You then look at that is making it slow.  This is where the plan comes in.  It will show you if you can fix the problem with more or less indexes.

If you want to jump right in to the nuts and bolts: Trace the session and run TKPROF against it.
gajmpAuthor Commented:
     I can't create Index now. If i suggest to create index they wnt allow me. bcz client using the application for the last two years. now only they are facing performance issue. It may be because of volume of data increased. but they are fearing to create index because it will affect DML. So please suggest me some other option

    "Get more RAM" currently DB box have 15GB RAM. but they have allocated 1GB for SGA, sort area size is 64K and db_block_size is 8k.I feel if we increase SGA and sort_area_size then it will gv fruit full result. but they have set workarea_size_policy is AUTO and pga_aggregate_target is 0. Is it do auto mem mgt?
slightwv (䄆 Netminder) Commented:
>> Is it do auto mem mgt?

Not by itself.  I suggest you refer to the docs on setting up Automatic Memory Management (AMM) for your version of Oracle.

>> "Get more RAM"

The reason for that comment:  The more database blocks you can have in memory, the faster Oracle is.

What I was getting at is:  Just doing that might not increase performance.  You need to define what is 'slow' then go through the entire tuning process for that item.

There is no: reduce disk reads = faster overall database.  The number of disk reads you see might be the correct amount for what you are doing.

>> have 15GB RAM. but they have allocated 1GB for SGA,

Are you running 64Bit Oracle?  If so and you have the free memory to allocate to Oracle, crank it up!
>>If i suggest to create index they wnt allow me. bcz client using the application for the last two years. now only they are facing performance issue

That's like a patient telling a doctor what to prescribe. In that case, the doctor should dismiss the patient.

The client needs to be educated, and needs to trust you as the expert. If they don't, there is a larger non-technical problem that needs to be addressed first.
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.