[architecture] shared understanding of SQL

1. Shared SQL function

After SQL is shared, hard parsing can be reduced, which consumes a lot of resources.

two。 Shared SQL concept

Sharing SQL is to make SQL exactly the same. In this way, there will be less hard parsing and more hard parsing during the execution plan of SQL parsing.

The word "complete" here means that the SQL statement must be exactly the same, almost not at all! Multiple spaces, carriage returns, case, etc., all require the same.

3. In order to share SQL, we need

(1) unify the writing style

(2) use bound variables (placeholders)

4. Find SQL statements that are not shared

Query sql statements with less execution times in v$sql. Observe whether these sql statements are executed frequently.

SQL > select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text

5. Analytical hit rate

The hit rate reflects the efficiency of soft analytic hit. The hit rate is usually more than 99%. If the hit rate is 98%, it means the hit rate is relatively poor.

SQL > select sum (pinhits) / sum (pins) * 100 from v$librarycache

SQL > select sum (gets), sum (getmisses), 100*sum (gets-getmisses) / sum (gets) from v$rowcache where gets > 0

-end-