1. Clients TYPICAL ORACLE SERVER ENVIRONMENT Web Server Remote Web Browser Internet O-O workstations ISDN Remote Host Applications Object Oriented Server ORACLE SERVER Internet (e.g.UNIX box) PC-LAN server Web Server PCs Clients

2. DATABASE PERFORMANCE OPTIMISATION APPLICATIONS TUNING SERVER OPTIMISATION Indexing Query Optimiser Clustering Monitoring Execution Plan Parallel Processing SQL statement tuning Transaction logic tuning Client/server performance

3. Explain Plan EXPLAIN PLAN SET STATEMENT_ID ‘Derby students try1’ INTO PLAN_TABLE FOR SELECT LASTNAME,FIRSTNAME FROM STUDENTS WHERE UNIVERSITY = ‘DERBY’; We can then proceed to perform a simple query on the PLAN_TABLE as follows

4. Query on Plan Table SELECT STATEMENT_ID, OPERATION, OPTIONS, COST FROM PLAN_TABLE; STATEMENT_ID OPERATION OPTIONS COST ----------------------------------------------------------------------------------------------------------- Derby students try1 SELECT STATEMENT 30 Derby students try1 TABLE ACCESS FULL ACCESS 0 Oracle has made a full scan of the database because no index is available possibly because the DBA has not ‘analyzed’ the table to produce statistics for the optimiser to use. This is easily corrected as follows.

5. ANALYZE TABLE STUDENTS COMPUTE STATISTICS; EXPLAIN PLAN SET STATEMENT_ID ‘Derby students try2’ INTO PLAN_TABLE FOR SELECT LASTNAME,FIRSTNAME FROM STUDENTS WHERE UNIVERSITY = ‘DERBY’; SELECT STATEMENT_ID, OPERATION, OPTIONS, COST FROM PLAN_TABLE; STATEMENT_ID OPERATION OPTIONS COST ------------------------------------------------------------------------------------------------ Derby students try1 SELECT STATEMENT 30 Derby students try1 TABLE ACCESS FULL ACCESS 0 Derby students try2 SELECT STATEMENT 5 Derby students try2 TABLE ACCESS BY ROWID 0 Derby students try2 INDEX RANGE SCAN 1