触发@执行计划..笛卡尔积

发布于 2024-12-02 22:02:39 字数 146 浏览 1 评论 0原文

这个想法是当用户运行查询并且有一个糟糕的笛卡尔坐标,其成本高于某个阈值时。然后 Oracle 将其通过电子邮件发送给我和用户。我尝试了一些东西,但它们在运行时不起作用。如果toad和sql开发者可以看到执行计划。然后我相信那里有我刚刚找到的信息。或者我可能不得不采用另一种逻辑。

The idea is when a user runs a query and has a bad Cartesian who cost is above a certain threshold . Then oracle emails it to me and user. i have tried few things but they don't work on run time. If toad and sql developer can see the execution plan. then I believe there is information there I just find it. Or i may have to adopt another logic.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

<逆流佳人身旁 2024-12-09 22:02:39

一般来说,这可能是不可能的。

理论上,如果您真的下定决心,您可以为系统中的每个表生成细粒度审核 (FGA) 触发器,这些触发器会触发每个 SELECTINSERTUPDATEDELETE,从V$SESSION获取SQL_ID,加入到V$SQL_PLAN >,并实现任何你想要的逻辑。这在技术上是可行的,但它会涉及相当多的代码,并且您可能会为系统中的每个查询添加大量的潜在开销。这可能不切实际。

您可以编写一个计划通过 DBMS_JOBDBMS_SCHEDULER 将查询 V$SESSION 的包 对于所有活动会话,加入 V$SQL_PLAN,并实现您想要的任何逻辑。这消除了每次用户执行任何语句时尝试运行触发器的开销。但它仍然涉及相当数量的代码。

根据您要解决的业务问题,创建资源可能更容易,而不是编写任何代码对用户配置文件的限制,让 Oracle 对任何单个 SQL 语句可以消耗的资源量实施限制。例如,您可以设置用户的 CPU_PER_CALLLOGICAL_READS_PER_CALLCOMPOSITE_LIMIT 来限制 CPU 数量、逻辑 I/O 数量、或者单个语句在 Oracle 终止之前可以执行的 CPU 和逻辑 I/O 的复合限制。

如果您想要更多控制,可以使用 Oracle 资源管理器。这可以让您采取任何措施,阻止 Oracle 运行某些用户的查询(如果估计运行时间过长),或者限制一组用户可以消耗的资源(如果存在资源争用)。 Oracle 可以自动将长时间运行的查询从特定用户移动到优先级较低的组,它可以自动终止长时间运行的查询,它可以首先阻止它们运行,或者这些事情的任意组合。

In general, this is probably not possible.

In theory, if you were really determined, you could generate fine-grained auditing (FGA) triggers for every table in your system that fire for every SELECT, INSERT, UPDATE, and DELETE, get the SQL_ID from V$SESSION, join to V$SQL_PLAN, and implements whatever logic you want. That's technically possible but it would involve quite a bit of code and you'd be adding a potentially decent amount of overhead to every query in your system. This is probably not practical.

Rather than trying to use a trigger, you could write a procedure that was scheduled to run every few minutes via the DBMS_JOB or DBMS_SCHEDULER packages that would query V$SESSION for all active sessions, joined to V$SQL_PLAN, and implemented whatever logic you wanted. That eliminates the overhead of trying to run a trigger every time any user executes any statement. But it still involves a decent amount of code.

Rather than writing any code, depending on the business problem you're trying to solve, it may be easier to create resource limits on the user's profile to let Oracle enforce limits on the amount of resources any single SQL statement can consume. For example, you could set the user's CPU_PER_CALL, LOGICAL_READS_PER_CALL, or COMPOSITE_LIMIT to limit the amount of CPU, the amount of logical I/O, or the composite limit of CPU and logical I/O that a single statement can do before Oracle kills it.

If you want even more control, you could use Oracle Resource Manager. That could allow you to do anything from prevent Oracle from running queries from certain users if they were estimated to run too long or to throttle the resources that a group of users can consume if there is contention over those resources. Oracle can automatically move long-running queries from specific users to lower-priority groups, it can kill long-running queries automatically, it can prevent them from running in the first place, or any combination of those things.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文