Home » RDBMS Server » Performance Tuning » tuning the view (merged)
tuning the view (merged) [message #314494] Wed, 16 April 2008 22:37 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have created view.But the query in the view performed very low. I just want to incraese the performance.Now I just want to Explain plan the view. How to Explain plan the view. Please give me idea regarding this.

Thank you
Re: View tuning [message #314503 is a reply to message #314494] Wed, 16 April 2008 23:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The view itself cannot really have a good or bad performance, only queries using that view have. The reason for me saying this is that the view by itself does not have a fixed execution plan; the execution plan of queries using this view may vary wildly.
Having said that, it might of course be that you have used some unfortunate constructions in your view-definition that will cause bad performance for all queries using it.

[Updated on: Wed, 16 April 2008 23:53]

Report message to a moderator

Re: View tuning [message #314507 is a reply to message #314503] Thu, 17 April 2008 00:02 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thank you Frank. Now I have done
EXPLAIN PLAN for
Select <stmt> ( Which is defined in the view)

I have statistics for this query. Then pls give me idea what to do next...

Thank you
Re: View tuning [message #314511 is a reply to message #314503] Thu, 17 April 2008 00:24 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Frank I got the statistics as follows
Statistics
----------------------------------------------------------
       1813  recursive calls
          0  db block gets
    7553854  consistent gets
          0  physical reads
          0  redo size
    4488289  bytes sent via SQL*Net to client
     111910  bytes received via SQL*Net from client
      15630  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     234426  rows processed

How to resolve this issue using these statistics..
tuning the view [message #314532 is a reply to message #314494] Thu, 17 April 2008 01:38 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi I have already posted this thread in other "Sql/plsql newbies".
I have created a view. It's performance is very low.I got statistics for the query which is defined in the view.Now i want to increase th performance of the view.
Statistics
----------------------------------------------------------
       1813  recursive calls
          0  db block gets
    7553854  consistent gets
          0  physical reads
          0  redo size
    4488289  bytes sent via SQL*Net to client
     111910  bytes received via SQL*Net from client
      15630  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     234426  rows processed

How to trace this SQL query in the view.pls guide me.
How to resolve this issue using these statistics..

Thank you

[Updated on: Thu, 17 April 2008 01:39]

Report message to a moderator

Re: tuning the view (merged) [message #314602 is a reply to message #314494] Thu, 17 April 2008 04:52 Go to previous message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

do these steps you will get a detailed execution plan

set timimgs on
set autotrace traceonly explain

then rum the query, then go through the explain and post tour views regarding that

gopu
Previous Topic: Loading objects in parallel?
Next Topic: Query taking time to execute in front end(Oracle 10g R2)
Goto Forum:
  


Current Time: Sun Dec 11 07:57:21 CST 2016

Total time taken to generate the page: 0.08241 seconds