解析Oracle 12c参数：实现更高性能（oracle12c参数）

It’s known to all that optimizing the system performance of Oracle 12c is critical to a certain extent, while there might come a question:

What kind of parameters should be taken into consideration and how to adjust them properly?

To answer the question, an Oracle 12c parameter needs to be understood and mastered first.

A Oracle 12c parameter is an attribute of an Oracle object, such as a database instance, a session, a SCHEMA, etc. It determines the behavior of an object. It will determine the number of records returned after a SELECT statement, the transaction lifespan, the SQL optimization level, and the performance of an Oracle system. It’s an essential factor of the performance tuning.

Meanwhile, there are different approaches of setting the Oracle 12c parameter. Generally speaking, the parameters could be divided into two types: static parameters and dynamic parameter.

The static parameters are the parameters set once and remain stable. For example, the values of the parameter “db_files” can be determined and set right after the database is Instantiated.

However, the dynamic parameters are adjusted frequently based on the current runtime environment. For instance, the value of the parameter “sga_max_size” could be changed according to the current status when the Oracle application is running.

As an administrator, it’s important to be aware of the Oracle 12c system’s current runtime environment, such as performance and usage patterns. Only with this understanding, can the parameters can be adjusted in a proper way to guarantee the system performance. For instance, by monitoring the performance of Oracle 12c, like buffer cache hit rate, the static parameter, buffer_pool_size, can be adjusted in a timely way.

Oracle 12c provides a tool, sysoper, which can help to display the current value of the parameters and make adjustment. Besides, for the dynamic parameters, a shell script can be used to monitor and adjust the parameter’s value.

To provide high performance for Oracle 12c, the first step is to understand the different Oracle 12c parameters and obtain the relevant knowledge to adjust the parameters properly. Besides, the shell script can be used frequently to help automated the process.

“`sql

— To display the current parameter’s value

SQL> SELECT name, value, isdefault, isdynamic

FROM sys.v_$parameter

WHERE name = ‘buffer_pool_size’;

— To change the parameter

SQL> ALTER SYSTEM SET buffer_pool_size=xxm;