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

Preface

Editor's note, there are many knowledge points in this article. Please add enough positive energy to read it. After reading it, you will question your DBA career. The author's level represents the highest level of Oracle TroubleShooting in China.

I wanted to sum up the problem of ORA-1000 into a topic for a long time. Coincidentally, I happened to encounter several such problems recently. I would like to share the analysis process for your reference.

First of all, what does ORA-1000 mean by reporting an error? Let's take a look at the official explanation:

Very simply, the number of cursors opened by a single process exceeds the maximum, that is, the value of the open_cursos parameter set by the database. In such a situation, different DBA will have different solutions: adjust the open_cursors parameter? Or leave the problem directly to the developer?

Today, Old Cat is going to share with you a classic case of dealing with ORA-1000 to see how your way of dealing with it is different from our way of handling it. What different processing techniques did the old cat use in the process? What kind of experience can be gained after processing?

two

Come to live.

The customer calls for help, and the system periodically reports the error of ORA-1000. The customer has adjusted the open_cursors parameters many times before, and now it has been adjusted to 2000. Is it still going up all the time? Open_cursors is a limit on the number of cursor opened by a single process, for general applications, if it is enough to close cursor,2000 in time, then whether the customer does not close the cursor correctly, or whether it needs to open a large number of cursors at the same time or for other reasons, this has greatly aroused my interest! To find out the root causes and provide solutions is our consistent attitude in the process of serving customers.

three

Capture information

In the face of an application-level error such as ORA-1000, we can generally analyze it by collecting the process dump of the error reporting process when setting the errorstack. If the error is not so easy to occur, we will choose to do a systemstate dump when the error business is executed frequently. The main purpose is to see which cursor are open in the server process during the execution of this kind of business, and then conduct further analysis according to the phenomenon to determine the cause of the problem.

Here the customer system periodically reports ORA-1000 errors, so it is recommended that customers open errorstack and collect trace files for analysis.

four

Start the analysis

The first is to set 1000 errorstack

When an ORA-1000 error occurs, a trace is generated. By observing the trace, it is found that 2000 cursor are indeed opened. Searching cursor# in trace shows that the cursor is all opened with the same SQL:SELECT activityno,ruleno FROM T_RM_COUPONINF.

So the question is, why do you open so many cursor for the same SQL? If we take a closer look, we will find something special about this SQL, Excutioncount=0&LoadCount=299&InvalidationCount=300. Remember the story of issue 12 shared by the old cat? this is a typical parsing error!

But here we don't have to set the 10035 event to confirm, we try to parse the SQL:SELECT activityno,ruleno FROM matching command; soon we find the problem, the T_RM_COUPONINF object doesn't exist at all!

So far, it seems that we have found two problems:

1. The cursor of an incorrect SQL was opened in the process.

two。 After the compilation error is encountered, the process does not close cursor in time, and it seems that there is no catchexecption or finally process for the processing of this code.

So who initiated the SQL and didn't close the cursor without compiling it successfully? It is not difficult to simulate such code. Countless grass-mud horses flashed through my mind. It's hard to say who to scold, just calm down, because this problem can be repeated in the test environment.

five

Locate the source

Generally speaking, the SQL running in the database can be divided into two types, the ordinary SQL and the recursive SQL;. Because there is an error in the sql itself, we cannot directly locate whether the SQL is from external (ordinary SQL) or database (recursive SQL); at this point, we can use the 10046 event to help.

You can see that there is a record of a parsing error in 10046 of trace. Err=942 throws the wrong ORA-942, which means that the table or view does not exist. At the same time, there is another key point: dep=0, that is, the recursive depth of SQL is 0, that is, non-recursive SQL! Therefore, we can only find the developer to confirm, the developer gives a clear answer is frustrating: this SQL is not sent by the app!

It is not written by the application, nor is it generated by the database itself, so where does the SQL come from? I carefully sorted out the train of thought and found that one link that we often overlooked in the daily process of dealing with problems was the JDBC package. It is generally believed that the main purpose of JDBC is to maintain the connection between the application and the database, but in fact, it is possible for JDBC to execute some SQL in this process, and even re-encapsulate the SQL statements sent by the application to the database server through some configurations. In this process, some problems are also possible.

At this point, we temporarily locate the problem to JDBC; check out the JDBC version, which is included in the 12.1.0.2 database. OK, narrowed down to JDBC. Next step, debug JDBC.

six

Different trace

After the initial suspicion on JDBC, the next thing you need to do is to open the trace of JDBC in the application code. The added code is as follows:

Observation results JDBC trace file:

Yes, we saw the SQL, and it seems that the wrong SQL really came from JDBC.

seven

Cause confirmation

It turns out that in the application persistence framework, in order to obtain the binding variable information of SQL, the PreparedStatement.getParameterMetaData () function of Oracle JDBC is called. In this method, JDBC obtains parameter metadata by generating a SQL:SELECT activityno, ruleno FROM T_RM_COUPONINFO, compiling the SQL, obtaining the metadata information of activityno and ruleno columns, and then returning it to the customer application.

Unfortunately, an error occurred while generating this SQL-a letter O was dropped, resulting in an error in ORA-942. If you are wrong, you should at least turn off cursor. The development of JDBC was negligent.

Correct sql:

SELECT activityno, ruleno FROM T_RM_COUPONINFO

The sql parsed to:

SELECT activityno, ruleno FROM T_RM_COUPONINF

This is a problem caused by JDBC recursive SQL, which is actually a BUG of JDBC12.1.0.2.

eight

Solve the problem

It's nice to submit the collected errorstack and JDBC trace to oracle support;GCS, and it doesn't take long to provide a patch.

nine

Tips for an old cat

You may wonder why the old cat's SR feedback is so fast, but my SR hasn't made any progress.

In fact, Lao Cat, as an old driver from the original factory, as far as this CASE is concerned, you should know the following two points:

1. JDBC is a cross-border product within Oracle, and its support is actually a middleware engineer, not an Oracle Database engineer. When you write an ORA-1000 question to an Database engineer, you really can't expect him to java;. When you open it to a middleware engineer, you can't expect him to know much about Database. When the old cat encountered this kind of problem in the original factory, he usually communicated with each other for a while while smoking on the balcony on the fifth floor. Everyone is under a lot of pressure at work, and no one really cares about the problems in their own hands.

two。 If you can reproduce the problem, try to make a test case to Oracle support and provide the information you can provide. In this case, we provide the phenomenon, the function of the problem, and the trace evidence to support, reducing interaction and bickering. Therefore, the problem was solved quickly.

All in all, for each of our own SR, we try to provide all the information we know, and the progress of SR will be more smooth.

ten

Problem summary

We see that as a good DBA, we must not just change the parameters or push the problems that seem to have nothing to do with the database to the developers; what we can do is to locate the problem to a minimum. As for the parts that are really related to the inner layer of the database that are not disclosed, as long as we provide enough information, we believe SR can also help us locate the problems at the bottom of the database.

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.