Oracle International Corporation

Gianfranco Putzolu

Juan R Loaiza

William Havinden Bridge, Jr

Alex Tsukerman

Jonathan D Klein

J William Lee

Graham Wood

Andre Kruglikov

Benoit Dageville

Tirthankar Lahiri

Gary C Ngai

Ashok M Joshi

Hasan Rizvi

Alexander C Ho

Sushil Kumar

Elizabeth Davis

William H Waddington

Yunrui Li

Mohamed Zait

Karl Dias

Mohamed Ziauddin

Wei Huang

Mark Charles Ramacher

Vasudha Krishnaswamy

Sujatha Muthulingam

Leng Leng Tan

Wanli Yang

Richard Sarwal

Vishwanath Karra

Amit Jasuja

Francisco Sanchez

Roger Taranto

Hsiao-te Su

Qiang Cao

Mark Coyle

An intelligent database infrastructure wherein the management of all database components is performed by and within the database itself by integrating management of various components with a central management control. Each individual database component, as well as the central management control, is self-managing. A central management control module integrates and interacts with the various database components. The database is configured to automatically tune to varying workloads and configurations, correct or alert about bad conditions, and advise on ways to improve overall system performance.

Self-managing database architecture

A database or some portion thereof is partitioned into ownership groups. Each ownership group is assigned one or more database servers as owners of the ownership group. The database servers that are assigned as owners of an ownership group are treated as the owners of all data items that belong to the ownership group. That is, they are allowed to directly access the data items within the ownership group, while other database servers are not allowed to directly access those data items. Accordingly, at least a portion of the database is partitioned into a plurality of ownership groups. Each ownership group is assigned an owner set. Only processes that are executing on database servers that are members of the owner set of an ownership group are allowed to directly access data within the ownership group.

Partitioning ownership of a database among different database servers to control access to the database

A method for simulating different MTTR settings includes determining a simulated MTTR setting and providing a simulated checkpoint queue. The simulated checkpoint queue is associated with the simulated MTTR setting and is an ordered list of one or more elements. Each element represents a buffer, and the ordered list has a head and a tail. The method also includes providing a simulated write counter associated with the simulated MTTR setting. The method further includes, in response to detecting a change to a first buffer, checking if the first buffer is represented in the simulated checkpoint queue. If the first buffer is not represented in the simulated checkpoint queue, an element that represents the first buffer is linked to the tail of the simulated checkpoint queue. An MTTR advisory system includes a memory, one or more processors coupled to the memory, a simulated MTTR setting, a simulated checkpoint queue, and a simulated write counter. The simulated MTTR setting is maintained in the memory. The simulated checkpoint queue is maintained in the memory and associated with the simulated MTTR setting. The simulated write counter is also maintained in the memory, and is associated with the simulated MTTR setting. The simulated write counter provides a count of the number of times an element is removed from the simulated checkpoint queue, wherein the element is removed from the simulated checkpoint queue in response to a write out of a buffer from volatile memory and storing in nonvolatile memory.

Mean time to recover (MTTR) advisory

A pluggable tablespace is enabled by logically partitioning a database into a set of tablespaces and storing all of the tablespaces disk pointers in tablespace-relative format. A pluggable set of tablespaces is unplugged by generating the associated metadata, and making a binary copy of the tablespaces. The pluggable set of tablespaces is plugged into a target database by making the pluggable set accessible to the target database and incorporating the associated metadata, without patching any of the disk pointers.

Pluggable tablespaces for database systems

A database or some portion thereof is partitioned into ownership groups. Each ownership group is assigned one or more database servers as owners of the ownership group. The database servers that are assigned as owners of an ownership group are treated as the owners of all data items that belong to the ownership group. That is, they are allowed to directly access the data items within the ownership group, while other database servers are not allowed to directly access those data items. Accordingly, a database system is provided which includes one or more persistent storage devices having a database stored thereon, and a plurality of database servers executing on a plurality of nodes. Each node has direct access to the persistent storage devices. At least a portion of the database is partitioned into a plurality of ownership groups. Each ownership group is assigned an owner set. Only processes that are executing on database servers that are members of the owner set of an ownership group are allowed to directly access data within the ownership group. Each ownership group is designated as either a shared nothing ownership group or a shared disk ownership group. Each shared nothing ownership group is assigned an owner from among the database servers. Only the owner of each shared nothing ownership group is allowed to directly access data within the shared nothing ownership group. Each of the database servers is allowed to directly access data within ownership groups that are designated as shared disk ownership groups.

Techniques are disclosed for expanding the concept of partitioning in variety of ways. In particular techniques are provided for performing multiple-dimension partitioning. In multiple-dimension partitioning, a database object is divided into partitions based on one criteria, and each of those resulting partitions is divided into sub-partitions based on a second criteria. The process of partitioning partitions based on different criteria may be repeated across any number of dimensions. Entirely different partitioning techniques may be used for each level of partitioning. The database server takes advantage of partitions when processing queries by selectively accessing a subset of partitions on disk or reducing the number of internal join operations.

Partition pruning with composite partitioning

Provided are mechanisms that may be used to support efficient exportation of user data stored in a database system. According to an aspect of the present invention, a database system is configured to store data for users in separate repositories referred to as tablespaces. To store data in this manner, a database system may be configured in variety of ways.

Using transportable tablespaces for hosting data of multiple users

Described herein are approaches for partitioning a buffer cache for dynamically selecting buffers in the buffer cache to store data items, such as data blocks in a DBMS. The selection is based on data access and/or usage patterns. A buffer cache includes multiple buffer pools. A buffer pool is selected from among the multiple buffer pools to store a data item. The selection of a buffer pool is based on various factors, including the likelihood that storing the data item will produce future cache hits, and properties of buffer pools that vary between the buffer pools. Properties of a buffer pool include not only how the buffer pools are organized, both logically and physically, but also how the buffer pool is managed. Examples of a buffer pool property include buffer pool size, size of a buffer in the buffer pool, and the replacement strategy used for a buffer pool (e.g. LRU).

Managing partitioned cache

The present invention provides a method and apparatus for partitioning a buffer cache for dynamically mapping data blocks with a particular replacement strategy based on the associated table's access and/or usage patterns. According to the method, a buffer cache in a computer system is managed by dividing the buffer cache into multiple buffer pools. In managing the buffer cache, when a data item is requested, it is first determined whether the requested data item is stored in a buffer within the buffer cache. If the requested data item is not stored in a buffer in the buffer cache, then a particular buffer pool in the buffer cache is dynamically selected for storing the requested data item. Once the particular buffer pool is selected, the requested data item is stored into a buffer in the particular buffer pool.

A method and apparatus for multi-phase locking for partition maintenance operations is provided. In the first phase, a shared data dictionary lock is acquired on a body of metadata in a data dictionary. Next, the data dictionary is read and a list of affected partitions is generated. In the second phase, an intent exclusive data lock is acquired on the affected table. Next, an exclusive data lock is acquired on the affected partitions of the affected table. The shared data dictionary lock on the data dictionary is released and a physical attribute of the data of the affected partitions is changed. In the third phase, an exclusive data dictionary lock is acquired on the data dictionary. The metadata associated with the affected partitions in the data dictionary is updated and the exclusive data locks on the affected partitions and the intent exclusive data lock on the affected table are released. Finally, the exclusive data dictionary lock on the data dictionary is released. As a result of performing multi-phase locking for partition maintenance operations, the scope of resources locked is reduced and the efficiency of the partition maintenance operations, from a concurrency point of view, is increased.

Multi-phase locking for partition maintenance operations

A database or some portion thereof is partitioned into ownership groups. Each ownership group is assigned one or more database servers as owners of the ownership group. The database servers that are assigned as owners of an ownership group are treated as the owners of all data items that belong to the ownership group. That is, they are allowed to directly access the data items within the ownership group, while other database servers are not allowed to directly access those data items. A mechanism is provided for transitioning ownership of a data item. Ownership is transferred by disabling access to the data item, waiting for all transactions that have made changes to the data item to either commit or abort, changing data that indicates ownership of the data item from a first owner to a second owner, and enabling access to the data item.

Transitioning ownership of data items between ownership groups

Hybrid shared nothing/shared disk database system

An approach for managing resources in a distributed computing system includes establishing dedicated process pools on nodes where work is to be performed. Each dedicated process pool is associated with a particular remote node and includes one or more processes. The one or more processes in each dedicated process pool perform work on behalf of processes on the associated remote node. Each dedicated process pool is managed by processes on the associated remote node using local synchronization data maintained on the associated remote node. The assignment of particular processes to perform work on behalf of processes on the associated remote node is performed by the processes on the associated remote node. In addition, the spawning of additional processes or the termination of existing processes from a dedicated process pool is managed by the processes on the associated remote node.

Distributed resource management

A database containing datafiles is partitioned into a set of tablespaces. Every disk pointer pointing to a data item in a datafile refers to a tablespace-relative file number for the datafile. Data pointed to by a tablespace-relative disk pointer is retrieved by first checking the cache, and upon a cache miss, the tablespace-relative file number is translated into an absolute file number according to a latch-free look up technique.

Tablespace-relative database pointers

A method and apparatus for multi-phase locking for partition maintenance operations. In the first phase, a shared data dictionary lock is acquired on a body of metadata in a data dictionary. Next, the data dictionary is read and a list of affected partitions is generated. In the second phase, an intent exclusive data lock is acquired on the affected table. Next, an exclusive data lock is acquired on the affected partitions of the affected table. The shared data dictionary lock on the data dictionary is released and a physical attribute of the data of the affected partitions is changed. In the third phase, an exclusive data dictionary lock is acquired on the data dictionary. The metadata associated with the affected partitions in the data dictionary is updated and the exclusive data locks on the affected partitions and the intent exclusive data lock on the affected table are released. Finally, the exclusive data dictionary lock on the data dictionary is released. As a result of performing multi-phase locking for partition maintenance operations, the scope of resources locked is reduced and the efficiency of the partition maintenance operations, from a concurrency point of view, is increased.

Growing community of inventors

San Francisco, CA, United States of America

Gianfranco Putzolu