user defined views on tables

views complicate the issue of performance when used by the application itself because you not only need to optimize the view by itself, but you need to worry about any additional where clause that is applied by the application at runtime.

you should run the app, then query the data dictionary for which statemnts are being run the most, then optimize for those statements either by rewiting the views or doing the full sql statemtn in the application inthe first place.

the views should be for end users not for developers.

hth
randy

Be carefull nesting Views.  We have run into the situation that where some views relied on several other views.  This caused performance to drop.  In some cases we where able to rewrite the view so that it didn't depend on other views.  In other cases we had to switch to PL/SQL.

Some of the advice offered here is mistaken.

For instance, removing all FULL TABLE SCANS.

A FTS may very likely be faster than another execution plan. Do not force a query to quite using FTS unless you have a (proven) faster plan.

If you are having performance problems, you need to find out where your database is spending most of its time, pure and simple.

Here's something to get you started.

Restart your database so that all statistics are reset.

Be sure that TIMED_STATISTICS=TRUE in your init.ora.

You can also turn this on with the following SQL:

  alter system set timed_statistics=true;

Run the following sqlplus script:

--------------------------
col event format a35 head 'EVENT NAME'
col total_waits format 999,999,999 head "TOTAL|WAITS"
col total_timeouts format 999,999,999 head "TOTAL|TIMEOUTS"
col time_waited format 999,999,999 head "TIME|WAITED|SECONDS"
col average_wait format 99999 head "AVG|WAIT|100ths"

set line 150
set trimspool on

select
     event,
     total_waits,
     total_timeouts,
     time_waited/100 time_waited,
     average_wait
from v$system_event
order by time_waited
/

---------------------------

This will show you where Oracle is spending most of its time.

You can also do this for individual sessions.  There will be much more output, but you can zero in on individual sessions this way.

Just enter a % when prompted for input.

---------------------------

clear break
clear col

set echo off pause off pages 60 line 100
set trimspool on

col cevent new_value uevent noprint
prompt Event Name ( Partial OK ):
set feed off term off
select '&1' cevent from dual;
set feed on term on

col username format a10
col event format a35
col total_waits format 9999999 head "TOTAL|WAITS"
col total_timeouts format 9999999 head "TOTAL|TIMEOUTS"
col time_waited format 9999999 head "TIME|WAITED|SECONDS"
col average_wait format 99999 head "AVG|WAIT|100ths"

break on username on sid skip 1 on report

select
     sess.username,
     sess.sid,
     se.event,
     se.total_waits,
     se.total_timeouts,
     se.time_waited/100 time_waited,
     se.average_wait
from v$session_event se, v$session sess
where event like '&uevent%'
and sess.sid = se.sid
and sess.username is not null
order by username, sid
/

------------------------------------

Please post the output from the first query.