We also discuss the features and applications of variable binding in Oracle databases.

Bindingvariable, sharedbufferpool, and SGA

Binding variable, shared buffer pool, and SGA

Binding variable, shared buffer pool, and SGA (system global area );

Before developing a database system, who knows a lot about the Oracle system, especially its features? It seems very rare. For Beginners, this is even more impossible; with a simple understanding of the SQL writing method, we started database development. The result is only the development of an inefficient and scalable system;
Therefore, I wrote this topic to help you better understand the features of Oracle databases, so that you can consider the scalability and scalability of the system when building a new system, system efficiency and stability are also taken into account;

Using the bound variable is one of the features of the Oracle database. So we have to ask why, how to use it, and what restrictions are imposed on its use? I will answer your questions based on this idea, and I will also answer these questions in an example;

1. Why bind a variable?
This is a key link to solve the scalability of Oracle Applications. The Oracle sharing pool determines that developers must use the bound variable. If you want to slow down Oracle operations or even completely terminate Oracle applications, you do not need to bind variables;
The preceding problems are illustrated here;
To query an employee code 123, You can query it as follows:
Select * from emp where empno = '201312 ';
You can also query:
Select * from emp where empno =: empno;

As we always do, you may not need to query the employee '1234568' once. Then you may query the employee '1234568', and then query '1234568; if you use the first query statement, each query is a new query (we call it a hard-coded query method). Therefore, Oracle must analyze, parse, and perform security checks each time, optimization and so on;
The second query statement provides the Bind Variable empno, which is provided during query execution. After a query is compiled, the query solution is stored in the Shared Pool and can be used for retrieval and reuse; in terms of performance and scalability, the difference between the two is enormous, or even astonishing. In layman's terms, it is not a level;
The higher the usage frequency of the first query, the larger the system hardware resources consumed, thus reducing the user's usage. It also kicks out other optimized query statements from the shared pool; as a mouse breaks down, the overall performance of the system is reduced. Instead, the user executes the binding variable and submits identical queries for the same object (in this sentence, it sounds hard to understand, and I will give you a detailed explanation later). You can use it all at once, and the efficiency is not whispering, the first query is like disposable chopsticks, while the second query is like chopsticks. As long as the chopsticks are cleaned, Michael Jacob can use the chopsticks properly and effectively;

,