Oracle如何解决大量并发写操作？
我正在维护一个拥有超过数百万用户的彩票网站。一些活跃用户（可能超过30,000）会在1秒内购买超过1000张彩票。 现在当前的逻辑使用 select .... for update 来确保帐户余额，但同时数据库服务器过载且处理速度非常慢？我们必须实时处理它们。
有人见过类似的场景吗？
I am maintaining a lottery website with more than millions of users. Some active user(Perhaps more than 30,000) will buy more than 1000 lotteries within 1 second.
Now the current logics use select .... for update to make sure the account balance, but meantime the database server is over-loaded and very slow to deal with? We have to process them in real-time.
Have anyone met the similar scene before?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
首先，您需要设计一个满足您的业务规则的事务系统。暂时忘掉磁盘和内存，以及什么放在哪里。尝试设计一个尽可能轻量的系统，执行所需的最少锁定量，以满足您的业务规则。
现在，运行系统，会发生什么？如果性能可以接受，恭喜你，你就完成了。
如果性能不可接受，避免猜测问题的诱惑，并开始进行调整。您需要对系统进行概要分析。您需要了解花费最多时间的地方，以便您知道应该将调整工作重点放在哪些方面。最简单的方法是使用 SQL_TRACE 进行跟踪。您没有提及 Oracle 版本、版本或平台。所以，我假设您至少使用的是 10gR2 的某个版本。因此，使用 DBMS_MONITOR 来开始/结束跟踪。现在，范围界定在这里很重要。我的意思是，启动跟踪、运行要分析的代码然后立即关闭跟踪至关重要。这样，您只跟踪您感兴趣的内容，并且配置文件不会包含任何无关信息。获得跟踪文件后，您需要对其进行处理。有几种工具。最常见的是TkProf，它是Oracle提供的，但确实做得不是很好。据我所知，最好的免费分析器是 OraSRP。下载 OraSRP 的副本，并检查结果。报告中的数据应该为您指明正确的方向。
完成所有这些操作后，如果您仍有疑问，请在这里提出一个新问题，我相信我们可以帮助您解释 OraSRP 的输出，以帮助您了解瓶颈在哪里。
希望有帮助。
First, you need to design a transactional system that satisfies your business rules. For the moment, forget about disk and memory, and what goes where. Try to design a system that is as lightweight as possible, that does the minimum required amount of locking, that satisfies your business rules.
Now, run the system, what happens? If performance is acceptable, congratulations, you're done.
If performance is not acceptable, avoid the temptation to guess at the problem, and start making adjustments. You need to profile the system. You need to understand where the most time is being spent, so that you know what areas to focus your tuning efforts on. The easiest way to do this, is to trace it, using SQL_TRACE. You've not made any mention of Oracle edition, version, or platform. So, I'll assume you're at least on some version of 10gR2. So, use DBMS_MONITOR to start/end traces. Now, scoping is important here. What I mean is, it's critically important that you start the trace, run the code that you want to profile and then immediately shut off the trace. This way, you trace only what you're interested in, and the profile won't contain any extraneous information. Once you have the trace file, you need to process it. There are several tools. The most common is TkProf, which is provided by Oracle, but really doesn't do a very good job. The best free profiler that I'm aware of, is OraSRP. Download a copy of OraSRP, and check your results. The data in the report should point you in the right direction.
Once you've done all that, if you still have questions, ask a new question here, and I'm sure we can help you interpret the output of OraSRP, to help you understand where your bottlenecks are.
Hope that helps.
就我个人而言，我会锁定/更新内存中的帐户并将更新数据库作为后台任务。使用这种方法，您可以轻松支持数千个更新和帐户。
Personally, I would lock/update the accounts in memory and update the database as a background task. Using this approach you can easily support thousands of updates and accounts.
A.无需修改代码即可加快速度：
1 - 您可以将表完全保留在内存中（即 SGA - 因为它也在磁盘上）：（
在执行此操作之前与您的 dba 讨论）
2 - 如果表太大，并且您一次又一次地更新相同的行，可能使用缓存属性就足够了：
此命令将表中的块在使用时以最佳优先级放入 LRU 列表中，因此不太可能从 SGA 开始老化。
这是关于差异的讨论： 问汤姆
3 - 另一个需要更多分析和资源的高级解决方案是TimesTen
B. 加快数据库操作速度：< /strong>
识别热门查询并：
您是否已确定热门查询？
A. Speed up things without modifying the code:
1 - You can keep the table entirely in the memory(that is SGA - because it is also on disks):
(discuss with your dba before to do this)
2 - if the table is too big and you update same rows again and again, probably it is sufficient to use the cache attribute:
This command put the blocks of your table when they are used with best priority in the LRU list, so it is less chance to be aged from the SGA.
Here is it a discusion about differences: ask tom
3 - Another solution, advanced, that need more analysis and resources is TimesTen
B.Speed up your database operations:
Identify top querys and:
Have you identified a top query?