PERFORMANCE TOOLKIT in Font

Maker Code 39 in Font PERFORMANCE TOOLKIT

When you use SQL*Plus 9i and before, this login script will be run only once, at startup. So, if you log in at startup as SCOTT and then change to a different account, this won t register on your prompt (this isn t an issue if you re using a 10g database): ora92 33> sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 8 20:38:31 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production Note in the following code that the prompt still shows SCOTT even though you are connected as the user BENCHMARK: scott@ORA92I> conn benchmark/benchmark Connected. scott@ORA92I> The following connect.sql script will solve this: set termout off connect &1 @login set termout on Then you simply run this script (which connects and then runs the login script) every time you want to change accounts: scott@ ORA92I> @connect benchmark/benchmark benchmark@ORA92I> To get SQL*Plus to run the login script automatically on startup, you need to save it in a directory (put connect.sql in the same directory) and then set the SQLPATH environment variable to point at that directory. On UNIX, the command to set the environment variable is different, depending on the shell you use. For example, in tcsh shell, you would use setenv SQLPATH /home/rmenon/mysqlscripts In Windows, there are many ways to run the login script automatically on startup, but the easiest is to set it in the Environment Variables section of the System Properties. In Windows XP, follow this procedure: 1. Right-click the My Computers icon and select Properties. 2. Select the Advanced tab and then click the Environment Variables button. Create a new variable as required (or edit it if it already exists).

In Windows you can also use the set command and put the script in your autoexec.bat file: set SQLPATH=C:\MYSQLSCRIPTS

Throughout the book, it will be useful for us to monitor the performance of the queries we execute. SQL*Plus provides an autotrace facility that allows us to see the execution plans of the queries we ve executed and the resources they used. The report is generated after the successful execution of a SQL Data Manipulation Language (DML) statement. There is more than one way to configure the autotrace facility; the following is a recommended method. 1. Execute cd $ORACLE_HOME/rdbms/admin. 2. Log into SQL*Plus as any user with create table and create public synonym privileges. 3. Run @utlxplan to create a plan_table for use by autotrace. 4. Run create public synonym plan_table for plan_table, so that everyone can access this table without specifying a schema. 5. Run grant all on plan_table to public, so that everyone can use this table. 6. Exit SQL*Plus and change directories as follows: cd $ORACLE_HOME/sqlplus/admin. 7. Log into SQL*Plus as SYSDBA. 8. Run @plustrce. 9. Run grant plustrace to public. You can test your setup by enabling autotrace and executing a simple query: SQL> set AUTOTRACE traceonly SQL> select * from emp, dept 2 where emp.deptno=dept.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'DEPT' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'EMP' Statistics ---------------------------------------------------------0 recursive calls 8 db block gets

2 0 0 2144 425 2 2 0 14

consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed

SQL> set AUTOTRACE off For full details on the use of autotrace and interpretation of the data it provides, see the chapter titled Using Application Tracing Tools of Oracle Database Performance Tuning Guide and Reference (10g Release 1) in the Oracle documentation set or the chapter titled Tuning SQL*Plus of SQL*Plus User s Guide and Reference (10g Release 1).