OraFAQ Forum: Performance Tuning » tuning the view (merged)

Home » RDBMS Server » Performance Tuning » tuning the view (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tuning the view (merged) [message #314494]

Wed, 16 April 2008 22:37

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

Report message to a moderator

Re: View tuning [message #314503 is a reply to message #314494]

Wed, 16 April 2008 23:51

Frank
Messages: 7901
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

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

Report message to a moderator

Re: View tuning [message #314511 is a reply to message #314503]

Thu, 17 April 2008 00:24

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..

Report message to a moderator

tuning the view [message #314532 is a reply to message #314494]

Thu, 17 April 2008 01:38

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:17:23 CST 2024