Shulou(Shulou.com)06/01 Report--

Oracle has made a number of improvements to diagnosticability in the 11g release, while the syntax for diagnosing events prior to Oracle 11g was more limited. Kernel debugging and diagnostics in the 11g release have allowed us to see trace and dump diagnostic information in greater detail. In the new release of Oracle 12c, Oracle continues to optimize diagnostics and provide more practical features to make fault diagnosis and handling easier.

I. Introduction to oracle diagnosis events:

In MOS document "Introduction to Oracle Diagnostic EVENTS (Document ID 218105.1)" to oracle diagnostic events do some introduction, the following is only a brief introduction, the focus of this article is to introduce oracle 12c in the new diagnostic event function.

Introduction to ORACLE Diagnostic EVENTS

----------------------------------------

1. Diagnostic events are mainly used to generate more diagnostic information when there is not enough information to solve a certain problem.

2. Diagnostic events are also used to resolve problems by changing Oracle's behavior or enabling certain undocumented features.

Setting EVENTS

--------------

There are many ways to set events.

Setting events depends on the nature of the event and the circumstances of the time. ORACLE repeatedly emphasizes that setup events should be based on explicit Oracle support services or related articles. Remember not to install and use them randomly in the production system.

Most events can be set in several ways:

(1)Through initialization parameters:

EVENT = ""

(2)Through the current session:

ALTER SESSION SET EVENTS '';

(3)Using debugging tools

o ORADEBUG

oradebug event

o ORAMBX (VMS only)

EVENT Categories

----------------

The most common events fall into four categories:

o Immediate Dump on request

For example, you can dump the following related information: SYSTEMSTATE, ERRORSTACK, CONTROLF, FILE_HDRS and REDOHDR

o On-Error Dump

For example, when an ora error occurs, dump appears with relevant information: EVENT "942 trace name ERRORSTACK level 3"

o Change the behavior of oracle

Often used to fix certain defects or enable certain hidden features.

o Generate Trace Events during instance runtime

For example: 10046

EVENT = "10046 trace name context forever, level 12"

II. New diagnostic events added to oracle 12c:

The following command oradebug doc event name can be used to view the list of events supported by oracle 11g and 12c respectively:

Some diagnostic event functions in oracle 11g:

Some diagnostic event functions in oracle 12c (new events in 12c in red box):

From the results of the above query, we can see that oracle 12c events in library RDBMS does not add events. In fact, even if it is 11g, we will really use very few in daily database operation and maintenance, so we cannot understand them all. I have selected some newly added events for testing and understanding.

1. Diagnostic event: wait_event[]

Description: event to control wait event post-wakeup actions

From the interpretation of this event, it can be initially estimated that it is related to waiting events in the database, and it seems to be related to certain actions after some waiting events wake up.

Oracle provides the function of diagnosing events, but there is very little information about its detailed description. I read a case before and just used this new function of 12c to diagnose a "log file sync" problem. On oracle version 12.1.0.1, the user session waited for a long time for "log file sync". Although there was no latency exception for "log file parallel write" and no explicit contention in "log buffer" at the time.

In this case, the author uses the new feature to obtain the call stack trace information of log file sync waiting to find out the function call order of Oracle when this problem occurs and whether the call is an abnormal function.

If the same information is needed before 12c, it may be captured by operating system tools such as DTrace on Solaris, but it is very difficult to target only one waiting event, especially on other operating systems.

Here, we can try to simulate using the diagnostic event to track the waiting event.

Use the following command syntax to trace call stack information:

SQL> alter session set events 'wait_event[""] trace("%s\n", shortstack())';

The call stack information generated by log file sync is unique. According to this information, you can know what order the functions are called by those functions, so as to further analyze:

In addition, the above can be combined with the SQL tracing function to track the waiting time of each "log file sync" to cooperate with the analysis.

In this way, we can track and compare the execution of each problem waiting event, including the duration, and then check whether the call stack information is different from normal. This is particularly convenient for tracking single waiting events.

2. Diagnostic event: sql_monitor_test

sql_monitor event to force monitoring SQL statements

sql_monitor_test event to test SQL monitoring

sql_monitor_test looks like an added feature based on sql_monitor. The original sql_monitor function is to force some sql statements to be monitored, while the sql_monitor_test function is to test SQL monitoring. Since there are no other relevant documents, only calculations can be made here.

What I can think of here should be the new SQL MONITORING function after Oracle 11G. Through SQL MONITORING, you can know which type of resources are consumed the most in the entire SQL execution process, and which step is currently executed by an executing SQL statement? It is also easy to get binding variables for statements, monitor the entire index creation process, and the workload remaining after creating the index.

Query sql_monitor usage:

SQL> oradebug doc event name sql_monitor

sql_monitor: event to force monitoring SQL statements

Usage

-------

sql_monitor

recursive

,

force

Recursive: It should be a recursive sql that monitors sql together, and force means mandatory.

Since there is no more detailed documentation, I can only borrow sql_trace usage, you can specify a sql_id to set.

sql_trace Use Case Syntax:

ALTER session SET EVENTS 'sql_trace [sql: sql_id=56bs32ukywdsq] bind=true, wait=true';

sql_monitor

ALTER system SET EVENTS 'sql_monitor [sql:sql_id=56bs32ukywdsq] recursive = true , force = true';

In the database can be successfully executed, indicating that the syntax should be no problem, after setting should be able to force some sql monitoring.

And in 10G database execution of the above command oracle is not recognized should not be supported.

According to the event description, sql_monitor_test is only used to do a SQL monitoring test.

SQL_Monitor_Test:

SQL> oradebug doc event name sql_monitor_test

sql_monitor_test: event to test SQL monitoring

Usage

-------

sql_monitor_test

level

According to the above, the diagnostic event can be opened by the following command.

ALTER system SET EVENTS ' sql_monitor_test [sql:sql_id=f3yfg50ga0r8n]level 12';

The above can also be set normally, but it may be affected by the environment or the correct way, no relevant information is generated, and it is difficult to find more relevant description information, so further research is needed on this new function.

3. Diagnostic event: fault

Event used to inject fault in RDBMS kernel

From the description, it seems to be used to inject faults into the RDBMS kernel. Is it to cause database faults by setting events? I don't think it's likely, and it seems that we can't really understand its real purpose at present. It shouldn't be a tracking function, but perhaps it is to avoid certain problems in some special situations.

The method of using it is also to set the event to fault. I tested it in my own experimental environment. It is recommended not to operate in the generated environment.

SQL> oradebug doc event name fault

fault: Event used to inject fault in RDBMS kernel

Usage

-------

fault

ALTER system SET EVENTS 'fault';

There is no exception after execution, no trace file is generated, and there is no alarm in the alert log, so it is impossible to explore its true purpose.

4. Other diagnostic events

awrdiag[] AWR Diagnostic Event

I am more interested in awrdiag[], which is an AWR-related diagnostic event from the description. I guess it is to do some awr diagnosis on some object or operation, but check its usage syntax:

SQL> oradebug doc event name awrdiag[]

Error: " awrdiag[] " not a known event/library name

Use , or .

Is event_name here referring to the diagnostic event name and library_name is referring to its categorical library, such as Events in library RDBMS we saw, but I tried to use the statement to execute it and it was wrong.

The target for which the ora-49115 description event was reported was not specified, and other descriptions related to awrdiag[] were not found so far, so analysis was not continued.

III. Summary:

Oracle adds no new features in version 12c, but also improves some of the original features. In fact, some oracle diagnostic event functions are generally only used in some extreme cases. oracle does not disclose more information about these functions. We can only try to understand them. In fact, some newly added functions have great significance. From the preliminary understanding of some newly added diagnostic events in oracle 12c this time, we found that wait_event[] events play a greater role for us. Especially in the case of some of the more difficult waiting events.

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.