Friday, February 19, 2010

Using "nonunique index to enforce uniqueness" is a good way?

Quote from Oracle® Database Performance Tuning Guide:
14.1.9 Using Nonunique Indexes to Enforce Uniqueness

You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique aspect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.

Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise, Oracle Database creates an additional unique index to enforce the constraint.


But i still think using unique indexes to support uniqueness and primary key constraint is a better choice. And uniqueness is affecting oracle optimizer.


SQL> select constraint_name,status,index_name from user_constraints;

CONSTRAINT_NAME STATUS INDEX_NAME
------------------------------ -------- ------------------------------
SYS_C009770 ENABLED
SYS_C009771 ENABLED
SYS_C009772 ENABLED
SYS_C009773 ENABLED
EMPLOYEES_PK ENABLED EMPLOYEE_N1

SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME UNIQUENES
------------------------------ ---------
EMPLOYEE_N1 NONUNIQUE

SQL> set autotrace traceonly explain statistics
SQL> select * from employees where employee_id=107;


Execution Plan
----------------------------------------------------------
Plan hash value: 2553029091

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 133 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPLOYEE_N1 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPLOYEE_ID"=107)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1083 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> alter table employees disable constraint employees_pk;

Table altered.

SQL> drop index employee_n1;

Index dropped.

SQL> create unique index employee_n1 on employees(employee_id);

Index created.

SQL> alter table employees enable constraint employees_pk;

Table altered.


SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME UNIQUENES
------------------------------ ---------
EMPLOYEE_N1 UNIQUE

SQL> select constraint_name,status,index_name from user_constraints;

CONSTRAINT_NAME STATUS INDEX_NAME
------------------------------ -------- ------------------------------
SYS_C009770 ENABLED
SYS_C009771 ENABLED
SYS_C009772 ENABLED
SYS_C009773 ENABLED
EMPLOYEES_PK ENABLED EMPLOYEE_N1


SQL> select * from employees where employee_id=107;


Execution Plan
----------------------------------------------------------
Plan hash value: 1716760386

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 133 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMPLOYEE_N1 | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPLOYEE_ID"=107)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
987 bytes sent via SQL*Net to client
409 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Wednesday, February 17, 2010

Sample iptables configuration to cater Oracle Listener

With RHEL default configuration:

[root@vmxdb02 ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

WithOUT RHEL default configuration:

[root@vmxdb02 ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -i lo -j ACCEPT
-A INPUT -p icmp --icmp-type any -j ACCEPT
-A INPUT -p 50 -j ACCEPT
-A INPUT -p 51 -j ACCEPT
-A INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A INPUT -p udp -m udp --dport 631 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT



[root@vmxdb02 ~]# service iptables restart
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
Applying iptables firewall rules: [ OK ]
Loading additional iptables modules: ip_conntrack_netbios_n[ OK ]
[root@vmxdb02 ~]# iptables -L -n
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255
ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0
ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0
ACCEPT udp -- 0.0.0.0/0 224.0.0.251 udp dpt:5353
ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:631
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:631
ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:1521
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Converting from ext2 to ext3 filesystem

Before Convertion:

[root@vmxdb02 backup]# mount
...
...
/dev/mapper/VolGroup00-testlv on /testfs type ext2 (rw)


[root@vmxdb02 ~]# fsck -V /dev/VolGroup00/testlv
fsck 1.39 (29-May-2006)
[/sbin/fsck.ext2 (1) -- /dev/VolGroup00/testlv] fsck.ext2 /dev/VolGroup00/testlv
e2fsck 1.39 (29-May-2006)
/dev/VolGroup00/testlv: clean, 12/180224 files, 25310/720896 blocks


Performing Convertion:

[root@vmxdb02 ~]# umount /testfs


[root@vmxdb02 ~]# tune2fs -j /dev/VolGroup00/testlv
tune2fs 1.39 (29-May-2006)
Creating journal inode: done
This filesystem will be automatically checked every 22 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.


[root@vmxdb02 ~]# mount /dev/VolGroup00/testlv /testfs

After Convertion:

[root@vmxdb02 ~]# fsck -V /dev/VolGroup00/testlv
fsck 1.39 (29-May-2006)
[/sbin/fsck.ext3 (1) -- /dev/VolGroup00/testlv] fsck.ext3 /dev/VolGroup00/testlv
e2fsck 1.39 (29-May-2006)
/dev/VolGroup00/testlv: clean, 12/180224 files, 41760/720896 blocks


[root@vmxdb02 ~]# mount
...
...
/dev/mapper/VolGroup00-testlv on /testfs type ext3 (rw)

Tuesday, February 16, 2010

"HASH JOIN FULL OUTER" in explain plan (11g onwards)

Typically, when the full outer join condition between two tables is an equi-join, the hash full outer join execution method is possible, and Oracle Database uses it automatically.

To instruct the optimizer to consider using the hash full outer join execution method, apply the NATIVE_FULL_OUTER_JOIN hint. To instruct the optimizer not to consider using the hash full outer join execution method, apply the NO_NATIVE_FULL_OUTER_JOIN hint. The NO_NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to exclude the native execution method when joining each specified table. Instead, the full outer join is executed as a union of left outer join and an anti-join.


SQL> alter session set optimizer_features_enable='11.1.0.6';

Session altered.

SQL> explain plan for
2 SELECT d.department_id, e.employee_id
3 FROM employees e
4 FULL OUTER JOIN departments d
5 ON e.department_id = d.department_id
6 ORDER BY d.department_id;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3770333139

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 3172 | 7 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 122 | 3172 | 7 (29)| 00:00:01 |
| 2 | VIEW | VW_FOJ_0 | 122 | 3172 | 6 (17)| 00:00:01 |
|* 3 | HASH JOIN FULL OUTER | | 122 | 1342 | 6 (17)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| DEPT_ID_PK | 27 | 108 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

17 rows selected.



SQL> alter session set optimizer_features_enable='10.2.0.4';

Session altered.

SQL> explain plan for
2 SELECT d.department_id, e.employee_id
3 FROM employees e
4 FULL OUTER JOIN departments d
5 ON e.department_id = d.department_id
6 ORDER BY d.department_id;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3122951222

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 4836 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 124 | 4836 | 5 (20)| 00:00:01 |
| 2 | VIEW | | 124 | 4836 | 4 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS OUTER| | 107 | 1177 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN| DEPT_ID_PK | 1 | 4 | 0 (0)| 00:00:01 |
| 7 | NESTED LOOPS ANTI | | 17 | 119 | 1 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 108 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

22 rows selected.

New Implementation for Nested Loop Joins (11g onwards)

Oracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time.

As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.

There are cases where a second join row source is not allocated, and the execution plan looks the same as it did in prior releases. The following list describes such cases:

  • All of the columns needed from the inner side of the join are present in the index, and there is no table access required. In this case, Oracle Database allocates only one join row source.

  • The order of the rows returned might be different than it was in previous releases. Hence, when Oracle Database tries to preserve a specific ordering of the rows, for example to eliminate the need for an ORDER BY sort, Oracle Database might use the original implementation for nested loop joins.

  • The OPTIMIZER_FEATURES_ENABLE initialization parameter is set to a release before Oracle Database 11g. In this case, Oracle Database uses the original implementation for nested loop joins.


    SQL> alter session set optimizer_features_enable=xxx;
    ERROR:
    ORA-00096: invalid value XXX for parameter optimizer_features_enable, must be from among 11.2.0.1.1, 11.2.0.1, 11.1.0.7, 11.1.0.6, 10.2.0.5, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.5,
    10.1.0.4, 10.1.0.3, 10.1.0, 9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0


    SQL> alter session set optimizer_features_enable='10.2.0.4';

    Session altered.

    SQL> explain plan for
    2 SELECT e.first_name, e.last_name, e.salary, d.department_name
    3 FROM hr.employees e, hr.departments d
    4 WHERE d.department_name IN ('Marketing', 'Sales')
    5 AND e.department_id = d.department_id;

    Explained.

    SQL> @?/rdbms/admin/utlxpls

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------
    Plan hash value: 2912831499

    -------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
    | 2 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
    |* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
    |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
    4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

    17 rows selected.



    SQL> alter session set optimizer_features_enable='11.1.0.6';

    Session altered.

    SQL> explain plan for
    2 SELECT e.first_name, e.last_name, e.salary, d.department_name
    3 FROM hr.employees e, hr.departments d
    4 WHERE d.department_name IN ('Marketing', 'Sales')
    5 AND e.department_id = d.department_id;

    Explained.

    SQL> @?/rdbms/admin/utlxpls

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 1021246405

    --------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | | | | |
    | 2 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
    |* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
    |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
    | 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
    4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

    18 rows selected.
  • How the Query Optimizer Chooses Execution Plans for Joins

    The query optimizer considers the following when choosing an execution plan:

  • The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.

  • For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. Similarly, when a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.

    With the query optimizer, the optimizer generates a set of execution plans, according to possible join orders, join methods, and available access paths. The optimizer then estimates the cost of each plan and chooses the one with the lowest cost. The optimizer estimates costs in the following ways:

  • The cost of a nested loops operation is based on the cost of reading each selected row of the outer table and each of its matching rows of the inner table into memory. The optimizer estimates these costs using the statistics in the data dictionary.

  • The cost of a sort merge join is based largely on the cost of reading all the sources into memory and sorting them.

  • The cost of a hash join is based largely on the cost of building a hash table on one of the input sides to the join and using the rows from the other of the join to probe it.


    The optimizer also considers other factors when determining the cost of each operation. For example:

  • A smaller sort area size is likely to increase the cost for a sort merge join because sorting takes more CPU time and I/O in a smaller sort area.

  • A larger multiblock read count is likely to decrease the cost for a sort merge join in relation to a nested loop join. If the database can read a large number of sequential blocks from disk in a single I/O, then an index on the inner table for the nested loop join is less likely to improve performance over a full table scan. The multiblock read count is specified by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

    You can use the ORDERED hint to override the optimizer's choice of join orders. If the ORDERED hint specifies a join order that violates the rule for an outer join, then the optimizer ignores the hint and chooses the order. Also, you can override the optimizer's choice of join method with hints.

    Quote: Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)
  • Additional info on View Merging

    Each view referenced in a query is expanded by the parser into a separate query block. The query block essentially represents the view definition, and therefore the result of a view. One option for the optimizer is to analyze the view query block separately and generate a view subplan. The optimizer then processes the rest of the query by using the view subplan in the generation of an overall query plan. This technique usually leads to a suboptimal query plan, because the view is optimized separately from rest of the query.

    The query transformer then removes the potentially suboptimal plan by merging the view query block into the query block that contains the view. Most types of views are merged. When a view is merged, the query block representing the view is merged into the containing query block. Generating a subplan is no longer necessary, because the view query block is eliminated.

    Grant the MERGE ANY VIEW privilege to a user to enable the optimizer to use view merging for any query issued by the user. Grant the MERGE VIEW privilege to a user on specific views to enable the optimizer to use view merging for queries on these views. These privileges are required only under certain conditions, such as when a view is not merged because the security checks fail.

    Support and Historical Notes for OPTIMIZER_SECURE_VIEW_MERGING

    Note that the documentation for this parameter is incorrect in a number of the manuals.
    In 10g / 11g the parameter can be TRUE or FALSE with the following behavior:


    FALSE
    This is similar to behavior in earlier releases such as 9.2.
    Oracle allows views and PLSQL functions in a query to be merged / moved around during query optimization in order to try and get the lowest cost execution plan even if the views / functions have different owners.

    TRUE
    This is the default value.
    Oracle does not use view merging or predicate move-around/pushing in some cases where the query has user-defined functions and views (regular, inline, unnested,etc) if the views / functions are owned by someone other than the person running the query.

    If OPTIMIZER_SECURE_VIEW_MERGING = FALSE then individual users can be given permission to merge other users views etc.. by granting them the MERGE ANY VIEW privilege.

    The default value of this parameter is NOT affected by the setting of OPTIMIZER_FEATURES_ENABLE.

    Thursday, February 11, 2010

    Top Ten Mistakes Found in Oracle Systems

    1. Bad connection management

    The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.

    2. Bad use of cursors and the shared pool

    Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.

    3. Bad SQL

    Bad SQL is SQL that uses more resources than appropriate for the application requirement. This can be a decision support systems (DSS) query that runs for more than 24 hours, or a query from an online application that takes more than a minute. You should investigate SQL that consumes significant system resources for potential improvement. ADDM identifies high load SQL. SQL Tuning Advisor can provide recommendations for improvement. See Chapter 6, "Automatic Performance Diagnostics" and Chapter 17, "Automatic SQL Tuning".

    4. Use of nonstandard initialization parameters

    These might have been implemented based on poor advice or incorrect assumptions. Most databases provide acceptable performance using only the set of basic parameters. In particular, parameters associated with SPIN_COUNT on latches and undocumented optimizer features can cause a great deal of problems that can require considerable investigation.

    Likewise, optimizer parameters set in the initialization parameter file can override proven optimal execution plans. For these reasons, schemas, schema statistics, and optimizer settings should be managed as a group to ensure consistency of performance.


    5. Getting database I/O wrong

    Many sites lay out their databases poorly over the available disks. Other sites specify the number of disks incorrectly, because they configure disks by disk space and not I/O bandwidth. See Chapter 8, "I/O Configuration and Design".

    6. Online redo log setup problems

    Many sites run with too few online redo log files and files that are too small. Small redo log files cause system checkpoints to continuously put a high load on the buffer cache and I/O system. If too few redo log files exist, then the archive cannot keep up, and the database must wait for the archiver to catch up. See Chapter 4, "Configuring a Database for Performance" for information about sizing redo log files for performance.

    7. Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

    This is particularly common on INSERT-heavy applications, in applications that have raised the block size above 8K, or in applications with large numbers of active users and few rollback segments. Use automatic segment-space management (ASSM) and automatic undo management to solve this problem.

    8. Long full table scans

    Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.

    9. High amounts of recursive (SYS) SQL

    Large amounts of recursive SQL executed by SYS could indicate space management activities, such as extent allocations, taking place. This is unscalable and impacts user response time. Use locally managed tablespaces to reduce recursive SQL due to extent allocation. Recursive SQL executed under another user ID is probably SQL and PL/SQL, and this is not a problem.

    10. Deployment and migration errors

    In many cases, an application uses too many resources because the schema owning the tables has not been successfully migrated from the development environment or from an older implementation. Examples of this are missing indexes or incorrect statistics. These errors can lead to sub-optimal execution plans and poor interactive user performance. When migrating applications of known performance, export the schema statistics to maintain plan stability using the DBMS_STATS package.

    Although these errors are not directly detected by ADDM, ADDM highlights the resulting high load SQL.

    Monday, February 8, 2010

    WARNING: inbound connection timed out (ORA-3136)

    Symptoms:

    Mon Feb 8 15:31:31 2010
    WARNING: inbound connection timed out (ORA-3136)
    Mon Feb 8 15:31:32 2010
    WARNING: inbound connection timed out (ORA-3136)
    Mon Feb 8 15:31:33 2010
    WARNING: inbound connection timed out (ORA-3136)
    Mon Feb 8 15:31:34 2010
    WARNING: inbound connection timed out (ORA-3136)
    Mon Feb 8 15:31:36 2010
    WARNING: inbound connection timed out (ORA-3136)
    Mon Feb 8 15:31:41 2010
    Mon Feb 8 15:33:25 2010
    WARNING: inbound connection timed out (ORA-3136)
    Mon Feb 8 15:34:44 2010
    WARNING: inbound connection timed out (ORA-3136)

    Knowledge Base:

    The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

    From 10.2.0.1 onwards the default value of parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

    Root Cause:


    $ORACLE_HOME/network/log/sqlnet.log (xxx.xxx.xxx.xxx is the masked IP)


    ***********************************************************************
    Fatal NI connect error 12170.

    VERSION INFORMATION:
    TNS for Linux: Version 10.2.0.4.0 - Production
    Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
    Time: 08-FEB-2010 15:33:25
    Tracing not turned on.
    Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
    Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=10961))


    Solution:

    Shutdown these Oracle clients/Application Servers. In above case, it's x10 in-memory database cache.

    Sunday, February 7, 2010

    Subquery Performance Tuning (Part 2, NOT IN and NOT EXISTS))


    SQL> select * from employees e
    2 where e.department_id not in (select department_id from employees);

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2584987836

    -------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 146 | 7 (15)| 00:00:01 |
    |* 1 | HASH JOIN RIGHT ANTI NA| | 1 | 146 | 7 (15)| 00:00:01 |
    | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1391 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 14231 | 3 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")

    Note
    -----
    - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    3 consistent gets
    0 physical reads
    0 redo size
    881 bytes sent via SQL*Net to client
    409 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed


    SQL> select * from employees e
    2 where not exists (select 1 from employees f where e.department_id=f.department_id);

    EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
    ----------- -------------------- ------------------------- ------------------------- -------------------- --------

    JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
    ---------- ---------- -------------- ---------- -------------
    178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-9

    SA_REP 7000 .15 149



    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2796935524

    ----------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 146 | 7 (15)| 00:00:01 |
    |* 1 | HASH JOIN RIGHT ANTI| | 1 | 146 | 7 (15)| 00:00:01 |
    | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1391 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 14231 | 3 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("E"."DEPARTMENT_ID"="F"."DEPARTMENT_ID")

    Note
    -----
    - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
    8 recursive calls
    0 db block gets
    19 consistent gets
    0 physical reads
    0 redo size
    1084 bytes sent via SQL*Net to client
    420 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    1 rows processed


    SQL> select * from employees e
    2 where not exists (select 1 from employees f where e.department_id=f.department_id)
    3 and e.department_id is not null;

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2796935524

    ----------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 146 | 7 (15)| 00:00:01 |
    |* 1 | HASH JOIN RIGHT ANTI| | 1 | 146 | 7 (15)| 00:00:01 |
    | 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1391 | 3 (0)| 00:00:01 |
    |* 3 | TABLE ACCESS FULL | EMPLOYEES | 106 | 14098 | 3 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("E"."DEPARTMENT_ID"="F"."DEPARTMENT_ID")
    3 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

    Note
    -----
    - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
    8 recursive calls
    0 db block gets
    18 consistent gets
    0 physical reads
    0 redo size
    881 bytes sent via SQL*Net to client
    409 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    0 rows processed

    Subquery Performance Tuning (Part 1, IN, EXISTS and COUNT >0)


    SQL> select * from employees e
    2 where (select count(*) from employees f where e.employee_id=f.manager_id) > 0;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2241443404

    -------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 107 | 7276 | 4 (0)| 00:00:01 |
    |* 1 | FILTER | | | | | |
    | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
    | 3 | SORT AGGREGATE | | 1 | 4 | | |
    |* 4 | INDEX RANGE SCAN| EMP_MANAGER_IX | 6 | 24 | 1 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter( (SELECT COUNT(*) FROM "EMPLOYEES" "F" WHERE
    "F"."MANAGER_ID"=:B1)>0)
    4 - access("F"."MANAGER_ID"=:B1)



    SQL> select * from employees e
    2 where exists (select 1 from employees f where e.employee_id=f.manager_id);

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 259920726

    -------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 18 | 1296 | 3 (0)| 00:00:01 |
    | 1 | NESTED LOOPS SEMI | | 18 | 1296 | 3 (0)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
    |* 3 | INDEX RANGE SCAN | EMP_MANAGER_IX | 18 | 72 | 0 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("F"."MANAGER_ID"="E"."EMPLOYEE_ID")



    SQL> select * from employees e
    2 where e.employee_id in (select manager_id from employees f);

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 259920726

    -------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 18 | 1296 | 3 (0)| 00:00:01 |
    | 1 | NESTED LOOPS SEMI | | 18 | 1296 | 3 (0)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
    |* 3 | INDEX RANGE SCAN | EMP_MANAGER_IX | 18 | 72 | 0 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access("E"."EMPLOYEE_ID"="MANAGER_ID")

    ALTER SYSTEM ARCHIVE LOG CURRENT and SCN


    SQL> select max(next_change#) from v$archived_log;

    MAX(NEXT_CHANGE#)
    -----------------
    2184453

    SQL> select current_scn from v$database;

    CURRENT_SCN
    -----------
    2205026


    Current changes are written to archived log triggered by "alter system archive log current".

    SQL> alter system archive log current;

    System altered.

    SQL> select max(next_change#) from v$archived_log;

    MAX(NEXT_CHANGE#)
    -----------------
    2205034

    SQL> select current_scn from v$database;

    CURRENT_SCN
    -----------
    2205044

    ALTER SYSTEM ARCHIVE LOG CURRENT and CHECKPOINT

    Question: Will "alter system archive log current" trigger checkpoint?
    Answer: It will only trigger a checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system archive log current" may trigger checkpoint, but not always.

    SQL> alter system archive log current;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:49:28 2184453 43 INACTIVE
    2010-FEB-07 10:41:46 2205034 44 ACTIVE
    2010-FEB-07 10:50:38 2205435 45 CURRENT

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2205034 2010-FEB-07 10:41:46
    2205034 2010-FEB-07 10:41:46
    2205034 2010-FEB-07 10:41:46
    2205034 2010-FEB-07 10:41:46
    2205034 2010-FEB-07 10:41:46


    SQL> alter system archive log current;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 10:51:33 2205466 46 CURRENT
    2010-FEB-07 10:41:46 2205034 44 ACTIVE
    2010-FEB-07 10:50:38 2205435 45 ACTIVE


    SQL> alter system archive log current;

    System altered.

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2205435 2010-FEB-07 10:50:38
    2205435 2010-FEB-07 10:50:38
    2205435 2010-FEB-07 10:50:38
    2205435 2010-FEB-07 10:50:38
    2205435 2010-FEB-07 10:50:38

    ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT

    Question: Will "alter system switch logfile" trigger checkpoint?
    Answer: It will only trigger a complete checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system switch logfile" may trigger complete checkpoint, but not always.



    SQL> alter system switch logfile;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:42:49 2180598 37 ACTIVE
    2010-FEB-07 09:46:12 2180846 38 CURRENT
    2010-FEB-04 20:51:32 2156580 36 INACTIVE

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49


    SQL> alter system switch logfile;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:42:49 2180598 37 ACTIVE
    2010-FEB-07 09:46:12 2180846 38 ACTIVE
    2010-FEB-07 09:46:40 2180856 39 CURRENT

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49
    2180599 2010-FEB-07 09:42:49


    Check point triggered, due to active redo group being overwritten

    SQL> alter system switch logfile;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:46:55 2180863 40 CURRENT
    2010-FEB-07 09:46:12 2180846 38 INACTIVE
    2010-FEB-07 09:46:40 2180856 39 INACTIVE

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55


    SQL> alter system switch logfile;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:46:55 2180863 40 ACTIVE
    2010-FEB-07 09:47:59 2181178 41 CURRENT
    2010-FEB-07 09:46:40 2180856 39 INACTIVE

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55



    SQL> alter system switch logfile;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:46:55 2180863 40 ACTIVE
    2010-FEB-07 09:47:59 2181178 41 ACTIVE
    2010-FEB-07 09:48:29 2181977 42 CURRENT

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55
    2180863 2010-FEB-07 09:46:55

    Check point triggered, due to active redo group being overwritten

    SQL> alter system switch logfile;

    System altered.

    SQL> select first_time,first_change#,sequence#,status from v$log;

    FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
    -------------------- ------------- ---------- ----------------
    2010-FEB-07 09:49:28 2184453 43 CURRENT
    2010-FEB-07 09:47:59 2181178 41 ACTIVE
    2010-FEB-07 09:48:29 2181977 42 ACTIVE

    SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

    CHECKPOINT_CHANGE# CHECKPOINT_TIME
    ------------------ --------------------
    2181178 2010-FEB-07 09:47:59
    2181178 2010-FEB-07 09:47:59
    2181178 2010-FEB-07 09:47:59
    2181178 2010-FEB-07 09:47:59
    2181178 2010-FEB-07 09:47:59



    log switch checkpoint and complete checkpoint

    2010-02-07 21:21:29.530000 +08:00
    Beginning log switch checkpoint up to RBA [0x31.2.10], SCN: 2242997
    Thread 1 advanced to log sequence 49
    Current log# 1 seq# 49 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO01.LOG
    2010-02-07 21:22:05.590000 +08:00
    Beginning log switch checkpoint up to RBA [0x32.2.10], SCN: 2243020
    Thread 1 advanced to log sequence 50
    Current log# 2 seq# 50 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
    2010-02-07 21:22:15.244000 +08:00
    Thread 1 cannot allocate new log, sequence 51
    Checkpoint not complete
    Current log# 2 seq# 50 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
    2010-02-07 21:22:16.766000 +08:00
    Completed checkpoint up to RBA [0x31.2.10], SCN: 2242997
    Beginning log switch checkpoint up to RBA [0x33.2.10], SCN: 2243027
    Thread 1 advanced to log sequence 51
    Current log# 3 seq# 51 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG
    2010-02-07 21:22:27.363000 +08:00
    Thread 1 cannot allocate new log, sequence 52
    Checkpoint not complete
    Current log# 3 seq# 51 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG
    2010-02-07 21:22:28.647000 +08:00
    Completed checkpoint up to RBA [0x32.2.10], SCN: 2243020
    Beginning log switch checkpoint up to RBA [0x34.2.10], SCN: 2243034
    2010-02-07 21:22:29.828000 +08:00
    Thread 1 advanced to log sequence 52
    Current log# 1 seq# 52 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO01.LOG

    Thursday, February 4, 2010

    Documentation Bug for Oracle database installation on Solaris

    Bug 1:

    "project.max-sem-nsems" appears in the kernel parameter requirement in following document:

    "Oracle® Database Installation Guide
    10g Release 2 (10.2) for Solaris Operating System (x86)
    Part Number B15697-01"

    But the correct setting should be "process.max-sem-nsems", and it has been corrected in the documentation on http://tahiti.oracle.com, and 11gR2 installation guide.

    Bug 2:

    "SUNWsprox" appears in the required packages for Solaris 10. But this package does not exist on Solaris 10.


    Add-on:

    project.max-sem-ids, required value 100, default value from Solaris 128
    process.max-sem-nsems, required value 256, default value from Solaris 512. (This value should be "(all your processes initialization parameter) + 20")
    project.max-shm-ids, required value 100, default value from Solaris 128

    Thus only project.max-shm-memory is required to set to install Oracle on Solaris.

    For example, to create a project for oracle, which allow 12GB used for SGA, and 1000 processes.

    projadd -p 200 -K "project.max-shm-memory=(privileged,12G,deny)" -K "process.max-sem-nsems=(privileged,1024,deny)" user.oracle