ORACLE

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 O
PTIONS 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 STATEM
ENT_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