英文:
What is reason of slowness and boost of output of a DB view which was taking 6 sec suddenly taking 1.5 sec?
问题
我有一个包含以下条件的Oracle视图：
CREATE OR REPLACE FORCE EDITIONABLE VIEW ....
.
.
select * from
(select t1.*, (select t.enddate from (select enddate, empid,
ROW_NUMBER() OVER (PARTITION BY empid ORDER BY enddate desc) as seqnum
from employee t2
where t2.empid = t1.empid) t
where seqnum = 1) As enddate_1 from (select * from
(select WED.*, ROW_NUMBER() OVER (PARTITION BY empid ORDER BY startdate desc) as seqnum from
(select t1.*, COUNT(*) OVER (PARTITION BY empid) WDECOUNT from employee t1) WED
where WDECOUNT = 1 or WED.startdate <= sysdate)) t1 WHERE seqnum = 1);
这个视图被 Wildfly 服务器中的 20 到 30 个 Java 集成使用，用于获取员工数据。
自创建后的最后7天以来，检索50条记录需要6秒，现在突然需要1.5秒来获取相同的记录（视图SQL未进行任何更改）。
检查了Oracle历史，没有发现任何清除缓存命令或任何类型的锁定。
根据您的看法，突然性能增加的可能性有哪些？
（在各种笔记本电脑上进行了测试，发现所有情况都相同（速度变慢和突然变快）。对于Wildfly连接池有疑问；但其他表运行正常。
我对Oracle一无所知，不知道是否进行了一些索引操作（历史中没有找到），或者是否提供了资源等。如果有人能够从Oracle的角度提供帮助，那就太好了）
我用以下Oracle查询来检查历史记录：
SELECT v.FIRST_LOAD_TIME, CPU_TIME, ELAPSED_TIME, PARSING_SCHEMA_NAME, MODULE, V.* FROM V$SQL V
where PARSING_SCHEMA_NAME = 'MAHTERJEEDATA' order by v.FIRST_LOAD_TIME desc;
Oracle Database 12c Release 12.1.0.1.0 - 64位 Production
英文:
I have a Oracle view with following condition:
CREATE OR REPLACE FORCE EDITIONABLE VIEW .....
.
.
select * from
(select t1.*,(select t.enddate from (select enddate,empid,
ROW_NUMBER() OVER (PARTITION BY empid ORDER BY enddate desc) as seqnum
from employee t2
where t2.empid=t1.empid) t
where seqnum=1) As enddate_1 from (select * from
(select WED.*,ROW_NUMBER() OVER (PARTITION BY empid ORDER BY startdate desc) as seqnum from
(select t1.*,COUNT(*) OVER (PARTITION BY empid) WDECOUNT from employee t1) WED
where WDECOUNT=1 or WED.startdate <= sysdate)) t1 WHERE seqnum=1);
This view is used by 20 or 30 Java integrations in Wildfly Server to fetch data of employee.
Since last 7 days of creation it was taking 6 sec to returns 50 records and now suddenly it is taking 1.5 for fetching same records (No changes are made in view sql).
Checked oracle history and haven't found any Clear Cache Command or any kind of locks.
What are the probabilities according to you for sudden increase in performance?
(This was under testing on various laptops and we found same behavior in all (slow and sudden speed). A doubt comes for wildfly connection pooling; but other tables are working fine.
I am not aware of oracle at all if some indexing is done (not found in history) or resources are provided or so. If someone can help from oracle angle too)
Oracle query i used to check history:
SELECT v.FIRST_LOAD_TIME, CPU_TIME, ELAPSED_TIME, PARSING_SCHEMA_NAME, MODULE, V.* FROM V$SQL V
where PARSING_SCHEMA_NAME = 'MAHTERJEEDATA' order by v.FIRST_LOAD_TIME desc;
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
答案1
得分: 1
那就像是在干草堆中寻找针一样。这里有很多选择，但如果你问我，我会尝试查看数据库中的自动收集统计信息进程上次执行的时间。
很可能，视图涉及的表具有新的统计信息，优化器现在可能有更好的执行计划，也许更好的基数估算导致了新的计划。
使用 AWR 在过去的 7 天内检查视图的执行计划：
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('your_sql_id'));
也许现在在 AWR 中为相同的 SQL 存储了两个不同的计划。
值得一试。但正如我所说，有很多选择。我告诉你的这个是性能提升最常见的方法之一。
英文:
That is like looking for needles in haystacks. There are a lot of options here, but if you ask me I would try to see when the automatic gather statistics process in your database was executed last time.
Probably, the tables involved in the view have new statistics and the CBO now has a better execution plan, perhaps a better cardinality estimation led to a new plan.
Check the execution plan of the view for the past 7 days using AWR
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('your_sql_id'));
Perhaps you get now two different plans stored on AWR for the same SQL.
It is worth a try. But like I said, there are a lot of options. The one I am telling you is one of the most commons for a boost in performance.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论