Part of the reason to attend in person training is to pick up the secrets that you cannot read in a book. I got plenty of that at my last Oracle hands on training class. In addition to that, I got some pieces of information that were beyond the scope of the class. However they were nevertheless very interesting and potentially useful. Today I want to discuss a sample of one topic that I have learned. That is what Oracle does when it is working towards executing a SQL statement.The SQL execution portion is separate from the PL/SQL processor in the database. This SQL execution portion is governed by a common subsystem which handles all SQL requests, whether they come from the result of PL/.SQL or a user typing SQL at the SQL*Plus command prompt.
Here is the order of things that happen when Oracle prepares to run a SQL statement. First there are syntax checks. Then there are semantic checks. Third there are checks whether all the permissions are in place for the objects being queried. Then the cost based optimizer (CBO) is consulted on how to best approach the query for performance purposes. The CBO step has a number of sub-steps. Finally the row selector identifies the results.
There are many parts to the CBO step. Indexes are analyzed to determine an access path to the data. Then the join order is evaluated if more than one table is involved. Then the plan costs are counted and evaluated. Obviously Oracle is going to want to choose a plan that has the least costs.
My instructor for PL/SQL programming told me there was a whole separate class for performance tuning of Oracle. In that class he would delve deeper into the SQL execution process. A coworker of mine is encouraging me to attend this class and get tested on it. However I think my heart is with PL/SQL development. Therefore my next class will be an advanced PL/SQL development class. However it is nice to know that there is a whole world of information to learn about within the Oracle universe.
