Processing of a Select statement

When a Select statement grows to an infinite number, it uses a common "select * from table order by…" statement ..." Statement. Figure 2-21 shows the processing process of the statement between each component in the database. Each step represents: (1) the select statement is transmitted to the proxy thread through the network; (2) after the SQL statement is rewritten and compiled, the compilation result is stored in the Package cache. (3) The coordinating agent executes the statement according to the execution plan and sends the prefetch request to the prefetch thread; (4) the prefetch thread executes asynchronous I/O in parallel between containers and puts data pages into the buffer pool (if no prefetch occurs, skip step 1). (5) put the data page in the container into the buffer pool; (6) Move the data to be sorted to the sorting heap; (7) If the sorting heap is insufficient, put the sorting data into the temporary tablespace; (8) The sorted row quilt proxy is sent back to the client. Pay attention to the following details during execution. These details are also the key factors affecting performance: (1) the execution plan of SQL statements may have an extreme impact on performance; (2) if prefetch occurs, the prefetch thread extracts consecutive data pages from the disk, and the proxy thread is in the waiting state. (3) If no prefetch occurs, the Coordination proxy extracts data from the disk in parallel. So far, a select statement has been completely executed. We can see that the most basic query statement is coordinated by various components in DB2 and has been completed in eight steps. When a performance problem occurs, any stage may become a performance bottleneck.