OracleBufferCache optimization ideas

Sharedpool is mainly composed of data_dictionary that saves data dictionaries and librarycache that saves SQL and PLSQL code and execution plans. It also includes other features for different supply systems

The shared pool is mainly composed of data_dictionary that saves the data dictionary and the library cache that saves the SQL and PL/SQL code and execution plan. It also includes other features for different supply systems

The shared pool is mainly composed of data_dictionary that saves the data dictionary and the library cache that saves the SQL and PL/SQL code and execution plan. It also includes several other buffers for different system features and technologies, such as the UGA provided for the shared server mode.
Optimization of shared pool:
1) Based on the Setup experience, for example, you can set shared_pool_size = sga_target * (10% ~ 15% ).
2) focus on saving the library cache metrics of SQL and PL/SQL code and execution plans. View the Load Profile section of the AWR report and analyze indicators such as Hard Parses/s. Analyze Library Hit %, Execute to Parse %, Soft Parse %, and so on in Instance Efficiency Percentages (Target 100%)
Wait events that require attention:
Latch: library cache
Latch: shared pool
3) view the metrics related to the shared pool in the Time Model Statistics (parse time elapsed and hard parse elapsed time ).
If the proportion of hard parse elapsed time is high, it indicates that the statement sharing of the application has a serious problem.

Optimization Method:
1) Evaluate statement sharing
Execute to Parse % = (execute times-Parse times)/Execute times X 100%

If Execute to Parse % is too low, it indicates that the resolution frequency is very high and the overall system sharing is poor. Generally, this metric is more than 70%, which indicates that the statement sharing is good.

Library Hit %, Soft Parse %, and Hard Parses/s in the AWR report. Parse contains Hard Parse and Soft Parse times, but we should pay attention to Hard Parses.

Query non-shared SQL statements (1 execution times ):
Select SQL _text from v $ sqlarea where executions = 1 order by upper (SQL _text );