We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

instance response time

nobleit
nobleit asked
on
Medium Priority
405 Views
Last Modified: 2012-08-14
Is there any simple method to fix slow instance response time using the package from DBMS_ADVANCED_REWRITE or any other method..
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Tuning tuning tuning.


People make entire careers doing what you asked.  There are many ways to go about this.  First is define what is slow, define objectives, then see what you can do to achieve them.

'Slow app' isn't good enough. What makes it 'slow'?  Memory, disk, ???  Then see what is using that resource.
Top Expert 2009

Commented:
Is the CPU taxed? Is the network congested? Make sure it is Oracle. Could be OS, network, unlrealistic expectations, etc.

Otherwise, I usually start with the slowest, most often run query and work down from there.

Yes, query rewrite lets you fix bad SQL. I think it should be used as a last resort, after attempting to get the code fixed first.

Author

Commented:
slow instance response could be taken care of using the package DBMS_ADVANCED_REWRITE
or not..Actually how we can implement this....
Top Expert 2009

Commented:
>>slow instance response could be taken care of using the package DBMS_ADVANCED_REWRITE
or not..Actually how we can implement this....

Yes, it can help. The point is, it is just one of many tools you have. It isn't your first line of attack, though.

The use of the advanced rewrite module is to fix / improve application SQL from behind the scenes as a DBA. This assumes you have slow queries. Have you determined that you have slow queries?

Author

Commented:
I used this script to find any slow running queries..
SQL> select  sql_text, disk_reads, loads, optimizer_cost,
  2  parsing_user_id, serializable_aborts, au.username
  3  from  v$sql, all_users au
  4  where  disk_reads > 10000
  5  and  parsing_user_id = au.user_id
  6  order by disk_reads desc;

no rows selected

Also I heard that  slow query log to find slow queries..
where is it located..How can we access..
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Haven't I posted links to the performance tuning guide in some of your previous questions?  You should really spend some time in the docs.

If there is a log, that is really a separate question.  This one is: is there a simple way to fix slow response and what about DBMS_ADVANCED_REWRITE.

Both have been answered.  There is no 'easy' way to fix performance problems and if that package can help.

Back to the latest extra question:  are you thinking v$longops?  You should really ask a new question with specifics and not general like: How do I make it run fast.  Remember: people make careers out of that question.  There really isn't a way to anser that one here.

Commented:
@slightvw

>> Back to the latest extra question:  are you thinking v$longops? <<

Or rather V$SESSION_LONGOPS :)

@nobleit

Read that Tuning Guide we've been suggesting you since several posts ! Nobody can guess why you 'think' your database is 'slow'. There is so many possibilities...

Read the docs, search the problem and then we might help you with some advice.

P

Author

Commented:
I queried
select * from elapsed_second from v$session_longops;
it is zero...

That means no slow queries...right..
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
no,  it only means there are no queries using dbms_application_info  to write to the long ops tracking
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
nobliet,

Can you explain why you accepted the answer you did?  It seems a few posts helped you answer the question asked and a split should be in order.

Please clarify.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.