Oracle Expert and SQL Analyze

This section introduces two powerful tools provided with the Tuning Pack: Oracle Expert and SQL Analyze. Stepping through an entire tuning session and its output would be a lengthy process beyond the scope of this chapter, so this section focuses only on a summary of the tools and their benefits.

Oracle Expert (Figure 21.37) is a powerful tool provided with the Tuning Pack that offers tuning recommendations for new or existing databases based upon data gathered during tuning sessions. In the case of a new database, the recommendations are provided based upon information you provide about the database server, its average workload, and the expected database transactions.

Figure 21.37.  Oracle Expert.

You can decide between a general tuning session and a session focused on a particular problem area. This tuning session can capture data from a single point in time or over a specified period of time. Based upon the scope that you have set, Oracle Expert will provide recommendations for tuning the SGA and other instance-specific parameters, the reusability and access methods of your SQL, and the sizing and placement of database objects.

These recommendations are made on reports that you can generate at any time from the tuning session data. This data is stored in the Repository. As you run additional tuning sessions for a database, the reports will contain even better recommendations, because Oracle Expert will compare the instance data gathered over time during different levels of processing. The reports include instructional information explaining why certain recommendations were made. They also include warnings if there is a risk involved in making a tuning modification.

Although Oracle Expert will provide help with the reusability of your SQL and recommendations for adding or removing indexes, it does not provide any other guidance for making your SQL more efficient. SQL Analyze, a new product in the Tuning Pack, provides the tools to help you analyze and optimize your SQL.

SQL Analyze provides a tool called TopSQL, which identifies the SQL statements with the largest impact on your system. SQL Analyze will also step you through decisions you are making in writing a new SQL statement. You can save these tuning sessions in the Repository and continue working on them at a later time.

After you’ve identified a problem statement or entered a new SQL statement, you use SQL Analyze to perform an explain plan (see Figure 21.38). SQL Analyze will help you walk through the execution plan for your statement and examine any statistics available or the objects used in the plan. It will also help you analyze your join operations.

Figure 21.38.  Generate an explain plan with SQL Analyze.

After you’ve examined the SQL statement, you can modify the structure of the statement or provide optimizer hints. You then compare the new statement by executing it and looking at statistics, or by generating another explain plan and comparing the two. A hint Wizard will even help make sure you use the correct syntax for your hints.

SQL Analyze will also let you know whether your statement is violating any SQL rules of thumb, such as operations that prevent the use of indexes.

Sample Net8 Configuration Files

Listings 21.2 through 21.6 show a set of configuration files from a UNIX database server. The tnsnames.ora and listener.ora files were used by the Intelligent Agent to generate the snmp_ro.ora and snmp_rw.ora files.

If you have just created your listener.ora and tnsnames.ora files, and you find an error in them after the Agent has generated the snmp files, you can stop the Agent, delete the snmp files, correct the error, and restart the Agent. The Agent will generate the snmp files any time it finds them missing.

Notice in Listing 21.6 I added a password so the dbsnmp database user ID does not sign on with a password of dbsnmp. If you’ve made any modifications to snmp_rw.ora, make note of these before you remove the file.

The last two Listings, 21.7 and 21.8, show the Net8 files from the console workstation.

Listing 21.2. tnsnames file from database server.

################

# Filename......: tnsnames.ora

# Name..........: LOCAL_REGION.world

# Date..........: 11-MAR-98 09:46:48

################

prod.world =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = TCP.world)

          (PROTOCOL = TCP)

          (Host = server1)

          (Port = 1521)

        )

    )

    (CONNECT_DATA =

      (SID = prod)

         (GLOBAL_NAME = prod.world)

    ) 

  )

test.world =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = TCP.world)

          (PROTOCOL = TCP)

          (Host = server1)

          (Port = 1521)

        )

    )

    (CONNECT_DATA =

      (SID = test)

         (GLOBAL_NAME = test.world)

    )

  )