SQL and PL/SQL optimization

Why take this course

Are your SQL queries running too slowly? Want to understand how the Oracle optimizer works and how to write code that makes the best use of available resources? This training will teach you how to analyze query performance, interpret execution plans, and use indexes, statistics, and hints effectively to create optimized SQL code.

What you’ll learn

• Understand how the Oracle query optimizer works
• Use available statistics and histograms when analyzing queries
• Choose and apply the right types of indexes
• Write optimized SQL queries, functions, and procedures
• Generate and interpret query execution plans
• Apply hints to influence query execution paths
• Analyze tuning history and make optimization decisions
• Automate processes and handle long-running queries
• Improve the performance of database applications

Certification & Exam

Upon completing the course, you will receive a personalized certificate confirming your skills in SQL query optimization in the Oracle environment – including working with execution plans, indexes, statistics, and hints. There is no final exam – active participation in the sessions is enough.

Who is this course for

• Developers writing SQL queries and procedures in Oracle
• Project team members working with large datasets
• Professionals responsible for application and database performance
• Specialists who want to understand the optimizer and SQL tuning
• Those looking to strengthen their skills in testing and improving SQL code performance

Topics covered

• Basics of statistics
• Statistics and histograms – available information
• Use of statistics by the optimizer – rules for switching off, optimizer operation control
• Options available at the time of creating the tables – rules of use, benefits
• The choice of index by the optimizer – forced actions, no reaction
• Index types: usefulness, when to create, types of indexes, advantages and disadvantages
• Generating an execution plan (various ways) and reading a plan table. Viewing various query plans / tuning history
• Selected hints (HINT)
• Code optimization