Oracle database engine calculates and sets this parameter automatically and it is not recommended to adjust this parameter until it is set to a wrong value or suggested by Oracle Support. Various Oracle RDBMS kernel functions use this parameter for various calculations especially Oracle CBO uses this parameter if CPU costing is enabled. If you intentionally increase value of this parameter to a value different from thel # of CPUs calculated by Oracle database engine, then you are inviting performance implications on that database.

ALL_ROWS is the default optimizer mode in Oracle database release 10g and 11g.

For example, if you want to set event 10046 at level 12 for the current process then login to the database as SYSDBA and execute the following commands:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/u01/oracle/products/diag/rdbms/prod/PROD/trace/PROD_ora_31497.trc
SQL> exit

Execute the following statement on the SQL*Plus prompt before executing the INSERT statement.

SET DEFINE OFF