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?
Learn more about Oracle basics on SearchOracle.com.
6. What is Explain Plan in Oracle, and how do we use it?
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.
Learn about Oracle's 51 security fixes.