Shulou(Shulou.com)06/01 Report--

The common parameters of ORACLE 11GR2 (including implied parameters) are set as follows:

Alter system set "_ PX_use_large_pool" = true scope=spfile

Alter system set "_ clusterwide_global_transactions" = false scope=spfile

Alter system set "_ gc_defer_time" = 3 scope=spfile

Alter system set "_ resource_manager_always_off" = true scope=spfile

Alter system set "_ resource_manager_always_on" = false scope=spfile

Alter system set "_ serial_direct_read" = never scope=spfile

Alter system set "_ cleanup_rollback_entries" = 400 scope=spfile

Alter system set "_ optimizer_use_feedback" = false scope=spfile

Alter system set "_ dbms_sql_security_level" = 0 scope=spfile

Alter system set "_ bloom_pruning_enabled" = false scope=spfile

Alter system set "_ gc_policy_time" = 0 scope=spfile

Alter system set "_ bloom_filter_enabled" = false scope=spfile

Alter system set "_ gc_read_mostly_locking" = false scope=spfile

Alter system set "_ gc_undo_affinity" = false scope=spfile

Alter system set "_ smu_debug_mode" = 134217728 scope=spfile

Alter system set "_ undo_autotune" = false scope=spfile

Alter system set deferred_segment_creation = false scope=spfile

Alter system set Audit_trail = none scope=spfile

Alter system set event='28401 trace name context forever,level 1 'scope=spfile

Alter system set "_ optimizer_extended_cursor_sharing_rel" = none

Alter system set "_ optimizer_extended_cursor_sharing" = none

Alter system set "_ optimizer_adaptive_cursor_sharing" = false

Parameter definition:

1. _ PX_use_large_pool

Parallel execution of dependent processes exchanges data and information when they work together, so we need to allocate memory from shared pool or large pool

This depends on the setting of the PARALLEL_AUTOMATIC_TUNING parameter value, and the role of _ PX_use_large_pool is similar to that of the PARALLEL_AUTOMATIC_TUNING parameter.

Allocate memory from large pool when PARALLEL_AUTOMATIC_TUNING=TRUE, otherwise from shared pool.

In 10g, PX information is cached and allocated in large pool, if:

A.) Parallel_automatic_tuning = true (deprecated)

Or

B.) _ PX_use_large_pool = true

Or

C.) Sga_target is set

In 11g, PX information is cached and allocated in large pool, if:

A.) Parallel_automatic_tuning = true (deprecated)

Or

B.) _ PX_use_large_pool = true

Or

C.) SGA memory is auto tuned (sga_target or memory_target)

II. _ clusterwide_global_transactions

Clustered global transactions (Clusterwide global transactions) is a new feature of 11g that allows XA transactions (XA distributed transactions) to be more transparent in RAC. Basically

A clustered global transaction is a distributed transaction in which each node in RAC has a local transaction, when _ clusterwide_global_transactions=true (default)

ORACLE treats these local transactions as one transaction, and when _ clusterwide_global_transactions=false, ORACLE treats these local transactions as separate transactions

Coordinate processing through multi-stage submission. Setting this parameter to false does not have any performance impact.

Setting the parameter value to FALSE can solve the following problems:

Bug 13605839 ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds] ORA-600 [kdBlkCheckError]. Corruption in Rollback with Clusterwide Global Transactions in RAC

ORA-00600: [kjuscl:!free]

* * *

XA interpretation:

XA is a two-phase commit protocol defined by X/Open DTP Organization (X/Open DTP group). XA is used by many databases (such as Oracle and DB2) and middleware tools (such as CICS and Tuxedo). Local support.

The X/Open DTP model (1994) includes four parts: application (AP), transaction manager (TM), resource manager (RM) and communication resource manager (CRM). In this model,

Generally, transaction manager (TM) is transaction middleware, resource manager (RM) is database, and communication resource manager (CRM) is message middleware.

In general, one database cannot know what other databases are doing, so in a DTP environment, transaction middleware is necessary to notify and coordinate the commit or rollback of related databases.

On the other hand, a database only maps its own actions (recoverable) to the global transaction.

XA is the interface specification (interface function) between transaction middleware and database defined by X/Open DTP. Transaction middleware uses it to inform database transaction start, end, commit, rollback and so on.

The XA interface function is provided by the database vendor. Usually, transaction middleware and database use two-phase commit to complete a global transaction through XA interface specification.

The XA specification is based on a two-phase commit protocol:

In the first phase, the transaction middleware requests all related databases to prepare to commit (pre-commit) their respective transaction branches to confirm whether all related databases can commit their respective transaction branches.

When a database receives a pre-commit, if it can commit its own transaction branch, it will record its actions in the transaction branch and give the transaction middleware a reply agreeing to commit.

At this point, the database will no longer be able to add any operations to the transaction branch, but at this time the database has not actually committed the transaction, and the database's operation on the shared resource has not been released (locked).

If for some reason the database cannot commit its own transaction branch, it will roll back all its operations, release the lock on the shared resource, and return to the transaction middleware failure response.

In the second phase, the transaction middleware reviews the pre-commit results returned by all databases. If all databases can be committed, the transaction middleware will require all databases to formally commit so that the global transaction is committed.

If any database pre-commit fails, the transaction middleware will require all other databases to roll back their operations so that the global transaction is rolled back.

* * *

3. _ gc_defer_time

How long to defer pings for hot buffers in milliseconds

Used to determine the length of time that the server waits before writing frequently used blocks to disk (in 1x1000 seconds) to reduce the process's contention for hot blocks, which defaults to 0.

4. _ resource_manager_always_off, _ resource_manager_always_on

Default FALSE, TRUE, which enables resource scheduling by default.

_ resource_manager_always_off = true, _ resource_manager_always_on = false is the resource scheduling enabled by disabling Oracle by default.

Avoid possible resmgr:cpu quantum wait events. Because there are many BUG in resource scheduling in 11g, it is chosen to turn off.

Some official files:

'resmgr:cpu quantum' wait event in 11g when VKRM process is not present (document ID 1603996.1)

Awr Reports hang, MMon slaves are waiting on resmgr:cpu quantum (document ID 1530676.1)

5. _ serial_direct_read

In Oracle 11g, a full table scan may use direct path read (regardless of table size) instead of buffer cache, so a full table scan is a physical read.

_ serial_direct_read = false disable direct path read

_ serial_direct_read = true enable direct path read

_ serial_direct_read = never can significantly reduce direct path read

VI. _ cleanup_rollback_entries

This parameter specifies the number of ENTRIES per rollback, which is set to 400 by default to speed up the rollback.

7. _ optimizer_use_feedback

11.2 at the beginning, Oracle has a new feature Cardinality Feedback,Cardinality Feedback is a process of automatic optimization by optimizer

The optimizer automatically corrects the execution plan of repeatedly executed queries. For some complex queries, such as multi-field conditions, string range comparison, data SKEW, etc.

And the lack of statistical information, the optimizer may not be able to produce a completely accurate cardinality estimate, such as missing or inaccurate statistics, or complex predicate cardinality estimates.

It is for this reason that cardinality feedback comes into being.

The _ optimizer_use_feedback parameter defaults to TRUE, that is, enabling Cardinality Feedback,FALSE means disabling Cardinality feedback.

Because there are many restrictions on the effectiveness of Cardinality feedback in 11GR2 and there are many BUG, it is not necessary to enable it.

8. _ dbms_sql_security_level

This parameter has three values (the default is 1). 0 turns off the security check of the dbms_sql package, and the requirement to execute / bind and parse the user id at cursor level 1 is the same.

Level 2 is more stringent and requires that id and roles are the same all operations, such as binding, description, execution, extraction, and so on. If there is an ORA-29471 error, you have to disconnect the current session

Then reconnect to the database to call the DBMS_SQL package normally. If you want to close security check, you need to set an implicit parameter _ dbms_sql_security_level to 0

Restarting the database takes effect.

9. _ bloom_pruning_enabled, _ bloom_filter_enabled

Bloom filter (Bloom Filter) algorithm is introduced into Oracle database in Oracle Database 10gR2.

Bloom filtering can use very low storage space to store the mapping of large amounts of data, thus providing a fast filtering mechanism.

11R2 encounters BUG 9124206 and BUG 8361126 caused by a BLOOM filter with ORA-00060 ORA-10387 errors

_ bloom_pruning_enabled and _ bloom_filter_enabled are set to FALSE to avoid BUG

The detailed errors are as follows:

ORA-00060: deadlock detected while waiting for resource

ORA-10387: parallel query server interrupt (normal)

10. _ gc_policy_time

The default value of the parameter is 1010. 0 turns off the DRM feature. DRM is unstable in 11G, and there are many BUG.

Eleven, _ gc_read_mostly_locking

The default parameter is TRUE, that is, read mostly locking is enabled.

FALSE disables the feature of read mostly, the read mostly locking mechanism, and reduces message delivery and CPU consumption for read access.

But write access consumes more IO than the traditional cache fusion locking mechanism. The feature of read-mostly is that it reads a lot to those who

It is more appropriate to write very few systems to enable them.

XII. _ gc_undo_affinity

The parameter defaults to TRUE and is set to FALSE to turn off DRM.

XIII. _ smu_debug_mode

Default is 0, and there will be some performance failures and BUG needs to be set "_ smu_debug_mode" = 134217728 to avoid.

In addition, by setting the value of _ smu_debug_ mode, you can specify the transaction in a specific rollback segment in undo automatic management mode.

In some extreme cases, this operation can be used to reduce rollback segment contention.

For example:

(1) when undo automatically manages and allocates undo, in some cases, some undo segments are very busy and some are idle. At this time, we need to change the rollback segment used by the transaction from the busy rollback segment.

Modify to an idle rollback segment.

Select segment_name,owner,tablespace_name from DBA_ROLLBACK_SEGS

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.