Oracle 数据库 10g VIEW 性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/559759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Database 10g VIEW performance
提问by N2EE
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.
我在我的 Oracle 数据库之一中有一个执行时间过长的视图。当语句运行时，它似乎并没有停止。
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
谢谢，N2EE
UPDATE
更新
I realised that the issue is with the underlying query in the view. Thanks to Edwin for the autotrace fix.
我意识到问题出在视图中的基础查询上。感谢 Edwin 修复了自动跟踪。
回答by Edwin
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:
如果您有 SQL*Plus，您可以使用以下语句轻松完成此操作：
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.
如果资源之一非常高，则可以重写查询和/或向您正在使用的表添加索引。
回答by Nick
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.
您需要查看构成视图的查询的性能。最好的方法是对视图使用的 sql 语句做一个解释计划。这将表明它是进行全表扫描还是其他一些不太理想的行为。调整查询，您的视图应该运行得更好。
回答by Gary Myers
Are you talking the creation or replacement of an existing view (ie executing the CREATE OR REPLACE VIEW... statement) or selecting from a view.
您是在谈论创建或替换现有视图（即执行 CREATE OR REPLACE 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.
在前一种情况下，可能是某个会话已将其锁定。例如，如果有人通过视图更新或删除，您将无法替换它。根据您的版本，您可能可以通过检查 v$session 的“BLOCKING_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.
在后一种情况下，它不是一个缓慢的视图，而是一个查询。观点几乎无关紧要。检查解释计划（最好使用带有来自 v$sql 的 sql_id 的 DBMS_XPLAN.DISPLAY_CURSOR）并查看它在做什么。v$session_longops 可能会给出一个指针。
回答by hamishmcn
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_STATSpackage to update Oracle's information about the tables and then see if the speed of the query improves.
假设问题是底层查询，性能问题可能是因为使用的表没有被分析。
您可以使用DBMS_STATS包更新 Oracle 的有关表的信息，然后查看查询速度是否有所提高。
回答by P Sharma
Generate AWR report based on snapshot ID's
根据快照 ID 生成 AWR 报告
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.
有两个 sql 脚本来创建 AWR 报告。1. awrrpt.sql 如果我们只有一个 Oracle 数据库，那么运行 awrrpt.sql sql 脚本。
- 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.
- awrrpti.sql 如果我们有多个 Oracle 实例（如 RAC），则运行 awrrpti.sql 脚本，以便我们可以创建特定的实例来创建 awr 报告。
Location of AWR report sql script $ORACLE_HOME/rdbms/admin
AWR 报告 sql 脚本的位置 $ORACLE_HOME/rdbms/admin
