Oracle SQL Performance Optimization 2

Oracle SQL Performance Optimization: 2. When a user establishes a connection with the database, an operation request is sent to the database, that is to say, send the previous SQL statement (or several or one PL/SQL package) to the database. After Oracle receives the SQL statement, it first performs a Hash function operation to obtain a Hash value, and then searches for the existence of the SQL statement that matches the Hash value in the shared pool. If the SQL statement is found, Oracle runs the current SQL statement directly using the execution plan of the existing SQL statement and returns the result to the user. If no SQL statement with the same Hash value is found in the Shared Pool, Oracle considers this as a new SQL statement and runs it in the following order: www.2cto.com. syntax analysis mainly checks whether the SQL statement conforms to the Oracle-defined syntax rules. If a syntax error is found, an error message will be thrown to the user. b. after the syntax analysis is passed, Oracle checks the objects and permissions of the SQL statement to check whether the table operated in the SQL statement exists and whether the columns in the table are correct, whether the user has the permission to operate on this object. C. Generate the execution plan. In this process, Oracle will execute the last SQL statement through a series of operations, such as viewing the statistical information of the operation object and dynamic sampling. Execute www.2cto.com d. SQL according to the execution plan generated in the previous step, execute the SQL statement and return the result to the user. So far, an SQL statement has been executed. For the OLTP system, the same SQL statement has a very high repetition frequency. If the optimizer repeatedly parses the SQL statement, system resources will be greatly consumed. In addition, the result set of user requests in the OLTP system is very small, therefore, we will basically consider using indexes. Since everyone's execution plans are consistent, why should we perform repeated Analysis on SQL statements? After the bind peeking obtains a correct execution plan for the first time, all subsequent SQL statements are executed according to this execution plan, which can greatly improve the system performance, this is determined by the features of the OLTP system. For OLAP systems, their SQL Execution plans have a great relationship with the values of the predicates. Different predicates may have different execution plans. If the same execution plan is used, SQL Execution efficiency must be very low. In addition, the number of SQL statements executed by a database in an OLAP system is much smaller than that in OLTP, and the SQL repetition rate is much lower than that in OLTP. In this case, you can ignore the cost of SQL parsing compared with the cost of SQL Execution. Therefore, for OLAP systems, I don't think variable binding is required. This may cause serious consequences of incorrect Execution Plan Selection. In addition, if the variable is bound, bing peeking can only ensure that the first hard-analyzed SQL statement can select the correct execution plan. If the predicate variable changes, the execution plan with incorrect SQL selection may still appear. Therefore, do not bind variables in the OLAP system.