Pl sql optimizer prior to oracle database 10 g the pl sql compiler translated your source text to system code without applying many changes to improve performance now pl sql uses an optimizer that can rearrange code for better performance the optimizer is enabled by default. Prior to oracle database 10 g release 1 the pl sql compiler translated your source text to system code without applying many changes to improve performance now pl sql uses an optimizer that can rearrange code for better performance the optimizer is enabled by default. Queries tend to perform worse as they age due to volume increase structural changes in the database and application upgrades etc use automatic workload repository awr and automatic database diagnostic monitor addm to better understand change in execution plan and throughput of top queries over a period of time 31 sql tuning advisor and . Use appropriate indexes for details see oracle database performance tuning guide use query hints to avoid unnecessary full table scans for details see oracle database sql language reference collect current statistics on all tables using the subprograms in the dbms stats package for details see oracle database performance tuning guide analyze the execution plans and performance of . In either case youll need sql optimization tools and knowledge of pl sql how do you improve database performance with oracle query optimization follow this five step process that uses sql optimization tools and pl sql know how 1 find high consuming sql statements