what is instance_caging ?
Managing CPU usage can be an issue when trying to consolidate multiple databases on to a single server, because if one database starts to hog all the CPU on the server, all the other databases will be affected.
Instance caging is a new feature in Oracle Database 11g Release 2 (11.2) that simplifies the management of CPU usage in consolidation environments. By enabling Resource Manager and setting the CPU_COUNT parameter in each instance, you can limit the maximum amount of CPUs/Cores the instance can use.
With the Resource Manager active, you must set the CPU_COUNT for each instance running on the server. The CPU_COUNT parameter is dynamic, but it is not recommended to change it too often, or by too large an amount. The following command shows how to set the parameter.
ALTER SYSTEM SET cpu_count = 2;
SELECT plan FROM dba_rsrc_plans;
10 rows selected.
SQL>If you have no specific resource management needs within the instance, the simplest solution is to use the default plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;
What is Cardinality in Database Systems ?
In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column. Thus, a column with the lowest possible cardinality would have the same value for every row. SQL databases use cardinality to help determine the optimal query plan for a given query.
Values of CardinalityWhen dealing with columnar value sets, there are 3 types of cardinality: high-cardinality, normal-cardinality, and low-cardinality.
High-cardinality refers to columns with values that are very uncommon or unique. High-cardinality column values are typically identification numbers, email addresses, or user names. An example of a data table column with high-cardinality would be a USERS table with a column named USER_ID. This column would contain unique values of 1-n. Each time a new user is created in the USERS table, a new number would be created in the USER_ID column to identify them uniquely. Since the values held in the USER_ID column are unique, this column’s cardinality type would be referred to as high-cardinality.
Normal-cardinality refers to columns with values that are somewhat uncommon. Normal-cardinality column values are typically names, street addresses, or vehicle types. An example of a data table column with normal-cardinality would be a CUSTOMER table with a column named LAST_NAME, containing the last names of customers. While some people have common last names, such as Smith, others have uncommon last names. Therefore, an examination of all of the values held in the LAST_NAME column would show “clumps” of names in some places (e.g.: a lot of Smith’s ) surrounded on both sides by a long series of unique values. Since there is a variety of possible values held in this column, its cardinality type would be referred to as normal-cardinality.
Low-cardinality refers to columns with few unique values. Low-cardinality column values are typically status flags, Boolean values, or major classifications such as gender. An example of a data table column with low-cardinality would be a CUSTOMER table with a column named NEW_CUSTOMER. This column would contain only 2 distinct values: Y or N, denoting whether the customer was new or not. Since there are only 2 possible values held in this column, its cardinality type would be referred to as low-cardinality.
What is a cursor? What are its types?
Oracle engine uses private working area to process queries. This work area is called as cursor. The data that is stored in the cursor is called as Active Data Set.
The main types of Cursors are:
Implicit cursors (predefined cursor): – The oracle engine implicitly opens a cursor on the server to process each SQL statement. Implicit cursors are managed by the Oracle engine itself.
Explicit Cursors (user defined cursor): – Explicit Cursors are the cursors created by the user inside a PL/SQL block. The declare section of a PL/SQL block and used within its executable Section.
ASM striping and mirroring
ASM provides striping by dividing files into equal-sized extents. Fine-grained striping extents are 128KB in size. For Oracle 10g, coarse-grained striping extents are 1MB in size. For Oracle 11g, coarse-grained striping extents can be 1, 2, 4, 8, 16, 32, or 64MB in size. Striping spreads each file extent evenly across all disks in the assigned disk group.
ASM also provides automatic mirroring of ASM files and allows the mirroring level to be specified by group. This mirroring occurs at the extent level. If a disk group is mirrored, each extent has one or more mirrored copies, and mirrored copies are always kept on different disks in the disk group.
There are three ASM mirroring options:
•Two-way mirroring – Each extent has one mirrored copy in this option.
•Three-way mirroring – Each extent has two mirrored copies in this option.
•Unprotected mirroring – ASM provides no mirroring in this option, which is used when mirroring is provided by the disk subsystem.