1 / 5

ORACLE SERVER

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. DATABASE PERFORMANCE OPTIMISATION.

meryl
Download Presentation

ORACLE SERVER

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related