?
Solved

instance response time

Posted on 2011-04-28
11
Medium Priority
?
394 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..
0
Comment
Question by:nobleit
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 78

Expert Comment

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

Expert Comment

by:mrjoltcola
ID: 35488610
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.

0
 

Author Comment

by:nobleit
ID: 35488630
slow instance response could be taken care of using the package DBMS_ADVANCED_REWRITE
or not..Actually how we can implement this....
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35488644
>>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?
0
 

Author Comment

by:nobleit
ID: 35488672
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..
0
 
LVL 78

Expert Comment

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

Expert Comment

by:Piloute
ID: 35490046
@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
0
 

Author Comment

by:nobleit
ID: 35494885
I queried
select * from elapsed_second from v$session_longops;
it is zero...

That means no slow queries...right..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35494998
no,  it only means there are no queries using dbms_application_info  to write to the long ops tracking
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35495018
Maybe maybe not.

The docs explain what that view shows:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_3022.htm#REFRN30227

It depends.

There is no single magic wand to tuning.  People make careers out of nothing but just tuning.  You need to identify, investigate, tweak, monitor, repeat.  It's a never ending process.
0
 
LVL 78

Expert Comment

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

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.
Suggested Courses

831 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