Shulou(Shulou.com)05/31 Report--

This article shows you the fault diagnosis analysis of oracle 10046 event, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What is an event?

EVENTS are primarily used to produce additional diagnostic information

When insufficient information is available to resolve a given problem.

An EVENT is a special item used by the Oracle server to do one of the following:

Event is a special object used by the oracle server for three purposes:

Change behaviour changes state (behavior)

Enable collection of trace or debug information enables collection of trace or debug information

Enable additional error checking or similar turns on additional error message checking or similar purpose

What is the 10046 incident?

One of the oracle debug events that helps us parse the running state of one or more SQL and PL/SQL statements, including wait events encountered in the three phases of Parse/Fetch/Execute, consumed physical and logical reads, CPU time, execution plan, and so on.

[oracle@hhu mesg] $oerr ora 10046

10046, 00000, "enable SQL statement timing"

/ / * Cause:

/ / * Action:

Several typical uses of the 10046 event

Single sql debugging

Multiple sql debugging

Case

Report or directly query the sql id corresponding to the sql statement through awr, and then open the 10046 event globally to track the execution of a single sql. At the same time, ask the developer to monitor with zabbix. If you find that the execution of the statement takes more than 10 seconds, you will give feedback immediately. After the developer feedback captures the execution at the 10s level, turn off the 10046 event. Go to the trace directory and use the tkprof tool to process two .trc files, one of which is normally time-consuming and the other one that times out. See the other two documents for details. By comparison, it is found that the execution plan of the two sql execution is the same, but the timeout will process millions of rows of data and access more than 100,000 blocks, while the normally time-consuming ones will only process dozens of rows of data. The developer then prints out the binding variables in the statement and finds that the very long time is related to the value of the specific binding variables. The problem is now left to the developer to solve.

The above content is oracle 10046 event troubleshooting analysis, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.