SQL Plus 与 Toad IDE - 在 SQL Plus 中运行插入需要更长的时间

发布于 2024-11-08 14:03:24 字数 480 浏览 0 评论 0原文

我正在运行这样的查询：

INSERT INTO TableA (colA, colB)
Select ColA, ColB 
from TableB

这是一个巨大的插入，因为它查询超过 200 万行，然后将它们插入表中。我的问题是关于性能的。当我在 toad 中运行查询时，查询大约需要 4-5 分钟才能运行。

当我通过 sqlplus 运行查询时，它花费的时间更长。已经运行了40多分钟了，还没结束。我什至通过关闭服务器输出来进行一些小的调整，以防影响性能。

关于通过 sqlplus 运行查询，我应该注意哪些调整？有什么方法可以找出不同客户端执行/处理查询的差异？

注意：这是我可以将数据从表 A 传输到表 B 的唯一方法。我已经研究过 imp/exp 和 impdp/expdp，但在我的情况下这是不可能的。

蟾蜍 - v. 9.6.1.1 SqlPlus - 9.2.0.1.0 Oracle数据库-10g

原文

I'm running a query like this:

INSERT INTO TableA (colA, colB)
Select ColA, ColB 
from TableB

This is huge insert, as it is querying over 2 million rows an then inserting them into the table. My question is in regard to the performance. When I run the query in toad the query takes around 4-5 minutes to run.

When I run the query through sqlplus it is taking way longer. It has already been running 40 minutes+ and it is not finished. I've even done some minor tuning by setting the server output off in case that effected performance.

Is there any tuning I should be aware of in regard to running the query via sqlplus? Is there any way to find out the difference in how the query is being executed/handled by the different clients?

Note: This is the only way I can transfer my data from table A to table B. I've looked into imp/exp and impdp/expdp and it is not possible in my situation.

Toad - v. 9.6.1.1
SqlPlus - 9.2.0.1.0
Oracle DB - 10g

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌梦深 2024-11-15 14:03:24

这听起来似乎还涉及其他事情。我的疯狂猜测是您的 SQL*Plus 会话被阻止。你能检查一下 v$lock 看看是否是这样吗？有很多脚本/工具可以检查您的会话当前将时间花在什么上。弄清楚这一点，然后从那里开始。我个人喜欢 Tanel Poder 的 Snapper 脚本 (http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper)。

回复收藏 0 原文

不弃不离 2024-11-15 14:03:24

这可能是一千件事。（@John Gardner：这就是我不是 dba.stackexchange.com 忠实粉丝的原因之一 - 在您知道答案之前，您不会知道这是编程问题还是 DBA 问题。我认为如果我们所有这些都在一个站点上一起工作。）

以下是一些想法：

不同的会话设置 - 可以启用、强制或禁用并行 dml 和并行查询。查看您的登录脚本，或者使用 select pdml_stats, pq_status, v$session.* from v$session; A lock 查看会话信息
，如 @Craig 建议的那样。尽管我认为查看 select v$session.blocking_session, v$session.* from v$session; 来识别锁更容易。
延迟块清理将使第二个查询变慢。使用 set autotrace on 运行。第二次时，db block gets 和 redo size 可能会更大（第二条语句有一些额外的工作要做，尽管这可能不足以解释时间不同之处）。
缓冲区缓存可能会使第二次查询更快。使用set autotrace on运行，物理读取可能存在较大差异。尽管数据量如此之大，但其中大部分数据被缓存的可能性可能很小。
其他会话可能会占用大量资源。查看 select * from v$sessmetric order byphysical_reads desc,逻辑_reads desc, cpu desc; 或者查看 v$sysmetric_history。
您可能需要考虑并行和附加提示。您或许可以使该查询的运行速度提高 10 倍（尽管该方法有一些缺点，例如
数据最初无法恢复）。
另外，为了进行测试，您可能需要使用较小的尺寸。使用诸如 和 rownum <= 10000 之类的内容运行插入。性能调优非常困难，如果你能运行的话会有很大帮助
经常发表这样的言论。总会有一些侥幸，你想忽略异常值，但你不能只用两个样本来做到这一点。
您可以查看每次运行的一些详细统计信息，但您可能需要使用 INSERT /*+ GATHER_PLAN_STATISTICS */... 运行查询。然后运行此命令查找 sql_id：select * from v$sql where sql_text like '%INSERT%GATHER_PLAN_STATISTICS%';
然后运行此命令查看每个步骤的详细信息：select * from v$sql_plan_statistics_all where sql_id = '';
（在 11g 中，您可以使用 v$sql_monitor，甚至更好的是 dbms_sqltune.report_sql_monitor。）

It could be a thousand things. (@John Gardner: This is one reason why I'm not a huge fan of dba.stackexchange.com - you won't know if it's a programming issue or a DBA issue until you know the answer. I think it's better if we all work together on one site.)

Here are some ideas:

Different session settings - parallel dml and parallel query may be enabled, forced, or disabled. Look at your login scripts, or look at the session info with select pdml_stats, pq_status, v$session.* from v$session;
A lock, as @Craig suggested. Although I think it's easier to look at select v$session.blocking_session, v$session.* from v$session; to identify locks.
Delayed block cleanout will make the second query slower. Run with set autotrace on. The db block gets and redo size are probably larger the second time (the second statement has some extra work to do, although this probably isn't nearly enough to explain the time difference).
Buffer cache may make the second query faster. Run with set autotrace on, there may be a large difference in physical reads. Although with that much data the chances are probably small that a huge chunk of it is cached.
Other sessions may be taking up a lot of resources. Look at select * from v$sessmetric order by physical_reads desc,logical_reads desc, cpu desc; Or maybe look at v$sysmetric_history.
You may want to consider parallel and append hints. You can probably make that query run 10 times faster (although there are some downsides to that approach, such as the
data being unrecoverable initially).
Also, for testing, you may want to use smaller sizes. Run an insert with something like and rownum <= 10000. Performance tuning is very hard, it helps a lot if you can run
the statements frequently. There are always some flukes and you want to ignore the outliers, but you can't do that with only two samples.
You can look at some detailed stats for each run, but you may need to run the query with INSERT /*+ GATHER_PLAN_STATISTICS */.... Then run this to find the sql_id: select * from v$sql where sql_text like '%INSERT%GATHER_PLAN_STATISTICS%';
Then run this to look at the details of each step: select * from v$sql_plan_statistics_all where sql_id = '<sql_id from above>';
(In 11g, you can use v$sql_monitor, or even better, dbms_sqltune.report_sql_monitor.)

回复收藏 0 原文

寻找一个思念的角度 2024-11-15 14:03:24

这是一个非常明显的观点，但众所周知它会让人绊倒... tableA; 上是否有任何索引？如果是的话，其中任何一个都是独一无二的；如果是这样，您在 SQL*Plus 中再次运行 Toad 会话之前是否提交或回滚了该会话？正如 @Craig 所建议的，不这样做是获得阻止的一种简单方法。在这种情况下，它永远不会完成 - 您的 40 多分钟的等待是在它阻塞第一行插入时发生的。

如果有任何索引，您最好在执行插入时删除它们，然后重新创建它们，因为这通常会快得多。

回复收藏 0 原文