Oracle Learning SQL sharing and 4031 solutions

"Shared sql:

1. Unified writing Style

2. Using bound variables

"To find the SQL statement that is not shared:

Look for SQL statements with smaller executions in v$sql to see if these SQL statements are executed frequently.

Select Sql_fulltext from V$sql where Executions=1 and sql_text like '%from t% ';

Select Sql_fulltext from V$sql where Executions=1 order by Sql_text; --List the statements with only one execution times, and sort

"Analytic hit ratio:

Select SUM (pinhits)/sum (Pins) *100 from V$librarycache; --Soft parsing

Select sum (gets), sum (getmisses), 100*sum (gets-getmisses)/sum (gets) from V$rowcache where gets>0;

"Resolves the 4031 error method

1, alter system flush Shared_pool; --Temporary means

2. Shared SQL

Alter system set cursor_sharing= ' force ';

3. Select * from V$db_object_cache where Sharable_mem > 10000

and (type = ' package ' or type= ' package BODY ' or type= ' FUNCTION ' or type= ' PROCEDURE ')

and kept = ' NO ';

Executes the Dbms_shared_pool.keep (' object name from the previous statement ');

Dbms_shared_pool

@?/rdbms/admin/dbmspool.sql

4. Reserved area

Select Request_misses from v$shared_pool_reserved;

Show parameter gkfx;

5. Add Shared Pool space

Select Component,current_size from V$sga_dynamic_components;

Show Parameter Sga_target

Show Parameter Sga_max_size

Alter system set SHARED_POOL_SIZE=150M scope=both;--oracle will be 159M by default and can only be modified to be larger than 159M

Oracle Learning SQL sharing and 4031 solutions