Checking for unreasonable SQL statements in an Oracle database

oracle| Data | database | Statement code:
Select Sql_text, Sharable_mem from V$sql where Sharable_mem > ' 100000 ' Order by Sharable_mem;
　　
　　
The above SQL statement is a query for SQL statements that consume more than 100K of memory in a shared pool.
　　
This SQL can be very effective at checking out those heavily memory-intensive SQL in Oracle shared Pool, and, in my experience, most problematic SQL statements leave traces here, by locating the problematic SQL statements here and modifying them, and then running the SQL script again and again, Until the problematic SQL is processed, this is the best optimization of the Oracle database on SQL, ensuring that Oracle database performance issues are not caused by the programmer's SQL statement problems.
　　
A shared pool consists primarily of a library buffer (shared SQL and Pl/sql) and a data dictionary buffer. In detail, you can find a book that describes the Oracle memory architecture. I will not print the contents of the book to you again, to see.
　　
SELECT * from V$sgastat; --state information for an explicit SGA.
　　
Some people write SQL statements very complex, nested several layers, the SQL statement itself is poorly written, it is possible to occupy a large SQL region.
　　
In fact, many of the actual situation is not need so complicated SQL, because the programmer level is not enough, write that very bad very complex SQL, resulting in database performance problems.
　　
There is also a common sense problem that can easily be overlooked. Like what:
　　
Code:
SELECT * FROM table_name where id = 1;
SELECT * FROM table_name where id = 2;
For this sql,id with parameter =? This place is called the position character (placeholder).
　　
Take PHP For example, a lot of people like to write code
　　
Code:
$sql = "SELECT * FROM table_name where id =";
$id = 1;
$stmt = Ociparse ($conn, $sql. $id);
Ociexecute ($stmt);
......
$id = 2;
$stmt = Ociparse ($conn, $sql. $id);
Ociexecute ($stmt);
　　
Take Java for example, it's like this:
Code:
String sql = "SELECT * FROM table_name where id =";
Statement stmt = Conn.createstatement ();
　　
RSet = Stmt.executequery (sql+ "1");
......
RSet = Stmt.executequery (sql+ "2");
　　
　　
This notation is exactly two different SQL statements for an Oracle database,
Code:
SELECT * FROM table_name where id = 1;
SELECT * FROM table_name where id = 2;
Each query performs parsing of the SQL statements, and each SQL allocates an area to hold the SQL-parsed binary executable code. Imagine, what if the ID is different 100,000 SQL? Oracle allocates 100,000 SQL regions to hold 100,000 separate SQL statements with different IDs. For a database-driven Web site in this case, the SGA is open again, will soon be depleted share pool, and finally reported a ORA-4031 error. The database is not connected, so it has to be reset.
　　
The correct wording should be:
　　
Code:
$stmt = Ociparse ($conn, "SELECT * FROM table_name WHERE ID =: id");
Ocibindbyname ($stmt, ": id",& $id, 12);
　　
$id = 1;
Ociexecute ($stmt);
...
$id = 2;
Ociexecute ($stmt);
　　
　　
Code:
PreparedStatement pstmt = conn.preparestatement ("SELECT * FROM table_name WHERE id =?");
　　
Pstmt.setint (1,1);
RSet = Pstmt.executequery ();
...
Pstmt.setint (1,2);
RSet = Pstmt.executequery ();
　　
So the Oracle database will know that you are actually using the same SQL statement, in this form:
SELECT * FROM table_name WHERE id =: 1
After parsing the execution is stored in the SQL area, when there is the same SQL, replace the parameters, immediately execute, do not need to parse SQL. Both speeds up SQL execution speed and does not occupy the share pool with too many SGA.
　　
Unfortunately, many programmers are aware of this problem, but not aware of the seriousness of the problem, because the above kind of writing, programming time is very flexible, the SQL statement can be dynamically constructed, easy to implement, the following kind of writing, SQL statements are written dead, parameters can not be changed, programming is often very troublesome.
　　
This is the result of many database performance problems.
　　
Interested in a production system, with the above SQL check, to see whether the choice of l have a lot of the same SQL statements, but the parameters are different, if so, it explains that the programmer's code to write a problem.