Oracle Database 10g VIEW performance

Question

Hello,

I have a view in one of my Oracle Database that is taking too long to execute. When the statement runs, it does not seem to stop.

Is there anyway that we can verify the performance of this view or how we can check to see if the statement session is 'hanging'?

Thanks, N2EE

UPDATE

I realised that the issue is with the underlying query in the view. Thanks to Edwin for the autotrace fix.

Answer 1

You'll need to take a look of the performance of the query that makes up the view. The best way to do that is to do an explain plan on the sql statement the view uses. That will indicate if its doing full table scans or some other less than optimal behavior. Tune the query and your view should run much better.

Answer 2

Are you talking the creation or replacement of an existing view (ie executing the CREATE OR REPLACE VIEW... statement) or selecting from a view.

In the former case, it is probably that some session has it locked. For example, if someone is updating or deleting through the view you won't be able to replace it. Depending on your version, you might be able to see the blocker by checking the 'BLOCKING_SESSION' column of v$session.

In the latter case, it isn't a view that is slow, but a query. The view is pretty much irrelevant. Check the explain plan (preferably using DBMS_XPLAN.DISPLAY_CURSOR with the sql_id from v$sql) and see what it is up to. v$session_longops may give a pointer.

Answer 3

Most likely the execution of your query is very slow.

You can see how the query in executed in the database by using explain plan.

If you have SQL*Plus you can do this very easy with the following statement:

set autotrace traceonly

Then type in the query, and you will get statistics on your query like this:

SQL> set autotrace traceonly
SQL>  select * from o_drops;

4461 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3820245448

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  4287 |   280K|    11  (10)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| O_DROPS |  4287 |   280K|    11  (10)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        333  consistent gets
         48  physical reads
          0  redo size
     337057  bytes sent via SQL*Net to client
       2316  bytes received via SQL*Net from client
        299  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4461  rows processed

If one of the resources is very high, it could work to rewrite the query and/or add indexes to the table you are using.

Answer 4

Assuming that the problem is the underlying query, the performance problems might be because the tables used haven't been analyzed.
You can use the DBMS_STATS package to update Oracle's information about the tables and then see if the speed of the query improves.

Answer 5

Generate AWR report based on snapshot ID's

There is TWO sql script to create AWR report. 1. awrrpt.sql If we have only One Oracle Database then run awrrpt.sql sql script.

1. awrrpti.sql If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

Location of AWR report sql script $ORACLE_HOME/rdbms/admin