Interview

Oracle database management client concerns

SearchSystemsChannel.com editors
1. Can you describe the difference in performance tuning between 8i, 9i and 10g?
Paul Baumgartel

    Requires Free Membership to View

    Login

Each release of Oracle has introduced features and behaviors that are intended to process SQL more efficiently and to allow better control over performance. The basic principles, however, remain the same: design to maximize scalability by reducing latching and locking to a minimum; consider all options for object implementation; and compare test results for different approaches before making a decision on production implementation.

Get more information about enhancing performance from SearchOracle.com's Ask The Oracle Expert. 2. What tuning indicators can I use for an Oracle database?
The best approach to tuning is to examine and tune individual operations rather than attempting to improve database performance as a whole. Find out from users if there are operations whose slow response times cause negative effects on the business. Then, for each operation (in order of the severity of impact), set up a test and enable Oracle extended tracing (in version 9i and above, use the supplied package dbms_support; you will first need to install it by running the script $ORACLE_HOME/rdbms/admin/dbmssupp.sql as SYS). Run tkprof on the result tracefile, being sure to specify waits=yes on the command line. The output file will show you not only how your process consumed CPU, but how much of the operation's elapsed time consisted of waits for events such as disk I/O, latch gets and enqueues.

Get more information on tuning techniques here: Oracle instance tuning techniques . 3. What are the most common causes and indications of Oracle db performance problems? What should I look for?
It's impossible to answer this question in a few sentences. Briefly, though, the most common causes of Oracle performance problems are applications that cause the database engine to do much more work than is necessary to achieve the desired result; poorly designed database schemas; and poorly designed databases. These can manifest themselves in many ways: excessive CPU consumption due to too many logical I/Os (visits to database blocks in memory), excessive disk reads due to missing indexes, or excessive contention for shared resources.

Get more information database management systems. 4. What tools should I use to tune SQL queries and PL/SQL, and among these tools, which are the best?
Tuning SQL and PL/SQL begins with an understanding of how Oracle processes SQL and PL/SQL. Most of the decisions about SQL processing can and should be left to Oracle's Cost-Based Optimizer. Make sure that you have accurate statistics on your tables and indexes so that the optimizer has the information it needs to do its job. When you are ready to tackle the tuning of individual SQL statements, you will want to use the EXPLAIN PLAN command to see the optimizer plan for a given query, the DBMS_SUPPORT package to trace Oracle sessions, and the TKPROF utility to format trace information.

Learn more about Oracle at Oracle news and advice for value added resellers and systems integrators.
5. I find Oracle tools quite vast. Can you provide some understanding on how basic DB components work, and how to manipulate DLL files using PL/SQL?

Karen Morton
Your question is pretty broad, so what I'll do is direct you back to OTN to the following two documents: 1) Oracle Concepts document - gives a good overview of how the basic DB components work. 2) Oracle Application Developer's Guide Fundamentals - gives an overview in chapter 10 of how to work with external routines in PL/SQL.

Learn more about Oracle basics on SearchOracle.com.
6. What is Explain Plan in Oracle, and how do we use it?

Brian Peasland
An Explain Plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster. Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed. This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. The command is of the form: EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement; You need to use a statement_id and then give your SQL statement.

Ask our database expert Hilary Cotter a question.
7. What books do you recommend to get information on DBA tasks and performance tuning? Is there a book or URL with a list of v$s and their descriptions ?
For performance tuning, I'd start with the Oracle documentation. In particular, have a look at the Oracle9i Database Performance Tuning Guide and Reference After that, there are a few good books on the market. Oracle Performance Tuning 101 by Vaidyanatha on Oracle Press is a good place to start. Expert One-on-One Oracle by Thomas Kyte is another good one, as well as The Art and Science of Oracle Performance Tuning by Christopher Lawson. For information on the V$ views and descriptions of their columns, look at the Oracle documentation, particularly the Oracle Reference Guide.

Learn more about Oracle performance tuning in learning guide excerpt taken from Oracle performance tuning 101.
8. What is SQL tuning?
SQL tuning is the process of ensuring that the SQL statements that an application will issue will run in the fastest possible time. Just like there may be ten different ways for you to drive from work to your house, there may be ten different ways to execute a query. Normally, you are in a hurry to get home so you take the fastest way. Once in a while, you may find a new way that is even faster. You didn't consider this route before because it was not very intuitive. SQL tuning is similar. Tuning SQL statements is finding the fastest route to answer your question, even if that route is not very intuitive.

Get help planning your remote DBA services .
9. I have created a new database using the command (create database). After I reboted my server, it started giving me errors, and now I'm not able to connect to it at all. The error that I get now is ORA-12514. I can connect to all other databases on my server except this one. My servers run Windows 2000 Advanced Server and Oracle 9i for W2K. Could you please help me in this?
You are getting the ORA-12514 error because your database listener is not configured to listen for this database. You'll have start the Net8 Assistant to configure and start a listener. Due to the ORA-12154 error, I think that the listener is up and running, but not configured for your database. You'll have to add a service for your database, then bounce the listener.

Learn more about Oracle applications in this learning guide.
10. Can you outline simple guidelines to ensure that the security requirements are made a part of any Oracle upgrade plan? Please provide a brief checklist to be given to customers, so that the project managers/management doesn't overlook this aspect.

Dan Norris
The most important part about securing a new database or new application is to develop a security model very early in the development process (make sure it is part of the requirements gathering process) and adhere to it throughout development and deployment. There's relatively little that can be done to secure anything just before it is deployed, when compared to what can be done if security is considered early in the project. I recommend reading the books Oracle Security: Step by Step by Pete Finnigan and Oracle Security Handbook by Marlene L. Theriault and Aaron Newman.

Learn about Oracle's 51 security fixes.


Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top