What Happens when a we submit a Query in Oracle Database

The following sequence of events takes place:

Parsing: The database parses the query to validate its syntax and to ensure that the objects referenced in the query exist and are accessible to the user. During this process, the database also generates an execution plan that outlines the steps that will be taken to retrieve the data requested in the query.
Optimization: The database optimizer uses statistics and other information to choose the most efficient execution plan for the query. The optimizer considers factors such as the indexes available on the tables, the distribution of data in the tables, and other performance-related information.
Binding Variables: If the query contains any bind variables, the database will bind their values to the query. This step is performed to improve performance and security by allowing the database to reuse execution plans for similar queries.
Execution: The database executes the query by accessing the data in the tables and processing the data according to the execution plan. The database may also use indexes, materialized views, or other optimizations to improve performance.
Fetching Results: The database retrieves the results of the query and returns them to the user. If the query requires multiple passes over the data, such as for sorting or grouping, the database will perform these operations before returning the results to the user.

Throughout this process, the database logs all changes to the data in the redo logs, ensuring that the database can recover from any failures that may occur. Additionally, the database may perform other tasks such as locking rows or objects, enforcing security constraints, and updating statistics, depending on the specifics of the query and the configuration of the database.

Memory Level Flow

User Process: The user's query is submitted to the database through a user process, which is a separate process running on the database server. This process is responsible for parsing the query, sending it to the database for execution, and retrieving the results.

Program Global Area (PGA): When the user process begins executing the query, it allocates memory in the Program Global Area (PGA) to store intermediate results and other information related to the execution of the query. The PGA is specific to each user session and is not shared among multiple sessions.

Shared Pool: The shared pool is a region of the System Global Area (SGA) that is used to store information such as parsed SQL statements, execution plans, and bind variable values. When the query is parsed, the database will check the shared pool to see if an execution plan for a similar query has already been generated and cached. If it has, the database will reuse this execution plan, improving performance.

Data Buffer Cache: The data buffer cache is a region of the SGA that is used to store data blocks that have been read from the database's datafiles. When the query is executed, the database will check the data buffer cache to see if the data requested in the query is already in memory. If it is, the database will retrieve the data from the cache, improving performance.

Temporary Space: If the query requires temporary space to store intermediate results or sort data, the database will allocate temporary space in either the Temporary Tablespace or in memory. The specific location of the temporary space depends on the specifics of the query and the configuration of the database.