Can you explain "ratio of user calls to recursive calls"? We have a monitoring tool, and it is telling me that this is bad. I understand it should be high (especially since our database has been up for quite a while). How can I get this stablized? I also get messages about the "quality of the data dictionary cache," and I believe these are related.
User calls are those SQL statements that are directly initiated by a user or an application. For instance, the application may say something like "SELECT empname FROM employee WHERE empid=1001". Before the system can answer your request, it has to find out some things. Does this table exist? Does this column exist? Where is the table? Do you have privileges to view this data? And many more questions are asked. Each simple SQL statement requires many "recursive SQL" statements to be generated. Recursive SQL statements are generated by the system, for the system. And a recursive SQL statement may generate other recursive SQL calls. This can lead to multiple levels of recursive SQL statements.
You really can't do anything to eliminate or alleviate recursive SQL statements. They system decides which recursive SQL statements it needs to generate to successfully satisfy your statement. For this reason, I've yet to see a monitoring tool that computes a user call to recursive call ratio. Personally, I'd ignore this ratio. It's possible for one SQL statement to generate more recursive SQL than another SQL statement. So the ratio is meaningless.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.