Computer system, computer program, and computer-implemented method (workload-driven database reorganization)

Abstract

To provide a system, computer program product and method for driving database reorganizations subject to determined workloads and, more specifically, provide an online partial reorganization of a database to improve workload performance.SOLUTION: The method includes the steps of: collecting performance information for one or more performance sensitive workloads associated with one or more portions of a database, and analyzing the collected performance information. For each of the one or more performance sensitive workloads, indications of performance declines thereof are determined. Subject to the determined performance declines, at least one of the one or more database portions affected by the determined performance declines is determined. Subject to the determination of the database portions, the determined performance declines are classified. Subject to the classification of the performance declines, one or more partial reorganizations of the one or more database portions are executed.SELECTED DRAWING: Figure 4

Description

[Technical Field]

[0001] The present disclosure relates to driving database reorganization in response to a determined workload, and more particularly to providing online partial reorganization of a database to improve workload performance. [Background technology]

[0002] Many known database storage systems require periodic reorganization of the data stored therein based on collected performance data of the database storage and associated indexes. The collected performance data is typically at the tablespace or partition level, and the collected performance data is analyzed by one or more tools to determine the need for reorganization of the stored data. Summary of the Invention [Problem to be solved by the invention]

[0003] A system, computer program product, and method are provided for determining the need to perform a partial data reorganization driven by workload performance. [Means for solving the problem]

[0004] In one aspect, a computer system for determining the need to perform partial data reorganizations driven by workload performance is provided. The system includes one or more processing devices and at least one memory device operably coupled to the one or more processing devices. The one or more processing devices are configured to collect performance information for one or more performance-sensitive workloads associated with one or more portions of a database and analyze the collected performance information. The analysis determines an indicator of performance degradation for each of the one or more performance-sensitive workloads, and, in response to the determined performance degradation, determines at least one of one or more database portions affected by the determined performance degradation. The analysis also classifies the determined performance degradation in response to the determination of the database portions, and performs one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation.

[0005] In another aspect, a computer program product for determining the need to perform partial data reorganizations driven by workload performance is provided. The computer program product includes one or more computer-readable storage media and program instructions collectively stored on the one or more computer storage media. The product also includes program instructions for collecting performance information for one or more performance-sensitive workloads associated with one or more portions of a database. The product further includes program instructions for analyzing the collected performance information. The product also includes program instructions for determining, for each of the one or more performance-sensitive workloads, an indicator of its performance degradation, and, in response to the determined performance degradation, determining at least one of one or more database portions affected by the determined performance degradation. The product also includes program instructions for, in response to the determination of the database portions, classifying the determined performance degradation, and, in response to the classification of the performance degradation, performing one or more partial reorganizations of the one or more database portions.

[0006] In yet another aspect, a computer-implemented method for determining the need to perform a partial data reorganization driven by workload performance is provided. The method includes receiving a dataset, the dataset including a plurality of data records contained within the dataset. The data records include one or more data records that are incomplete data records missing one or more predictors and one or more data records that are complete data records. The method includes training a model using at least a portion of the one or more complete data records and generating one or more candidate predictors for the one or more missing predictors. The method also includes determining a predictor importance value for each candidate predictor among the one or more candidate predictors and recommending some of the candidate predictors that have predictor importance values ​​exceeding a first threshold. The method also includes creating one or more temporary data records by inserting one or more respective recommended candidate predictors into one or more respective incomplete data records. The method further includes inputting the one or more temporary data records into a model, determining a fitness value for each of the one or more temporary data records, and selecting temporary data records having a fitness value that exceeds a second threshold.

[0007] This summary is not intended to describe every aspect, every implementation, or every embodiment of the present disclosure, or combinations thereof. These and other features and advantages will become apparent from the following detailed description of the present embodiments taken in conjunction with the accompanying drawings. [Brief explanation of the drawings]

[0008] The drawings contained herein are incorporated into and form a part of the specification. The drawings, together with the detailed description, illustrate embodiments of the present disclosure and serve to explain the principles of the disclosure. The drawings illustrate particular embodiments and are not intended to limit the disclosure.

[0009] [Figure 1] FIG. 1 is a schematic diagram illustrating a cloud computing environment, according to some embodiments of the present disclosure.

[0010] [Figure 2] FIG. 2 is a block diagram illustrating a set of functional abstraction model layers provided by a cloud computing environment, in accordance with some embodiments of the present disclosure.

[0011] [Figure 3] FIG. 1 is a block diagram illustrating a computer system / server that may be used as a cloud-based support system for implementing the processes described herein, according to some embodiments of the present disclosure.

[0012] [Figure 4] 4 is a schematic diagram illustrating a portion of the computer system / server shown in FIG. 3 for implementing the processes described herein, according to some embodiments of the present disclosure.

[0013] [Figure 5] 1 is a high-level flowchart illustrating a process for driving workload-based online partial database reorganization to improve workload performance, according to some embodiments of the present disclosure.

[0014] [Figure 6A] 6 is a detailed flowchart illustrating a process 600 for driving the workload-based online partial database reorganization shown in FIG. 5 according to some embodiments of the present disclosure.

[0015] [Figure 6B] 6B is a continuation from the flowchart of FIG. 6A, according to some embodiments of the present disclosure.

[0016] [Figure 6C] 6B, in accordance with some embodiments of the present disclosure.

[0017] [Figure 7] FIG. 1 is a schematic diagram illustrating a process for restoring a desired clustering sequence of affected records to mitigate low cluster ratio determinations, according to some embodiments of the present disclosure.

[0018] [Figure 8] FIG. 1 is a schematic diagram illustrating a process for mitigating performance issues associated with hot spots, according to some embodiments of the present disclosure.

[0019] [Figure 9] FIG. 1 is a schematic diagram illustrating a process for mitigating performance issues related to high overflow rates, according to some embodiments of the present disclosure.

[0020] [Figure 10] FIG. 1 is a schematic diagram illustrating a process for mitigating performance issues related to space utilization issues, in accordance with some embodiments of the present disclosure.

[0021] [Figure 11] FIG. 1 is a schematic diagram illustrating a process for defragmenting and reorganizing a tablespace to alleviate space utilization issues, according to some embodiments of the present disclosure.

[0022] [Figure 12] FIG. 2 is a schematic diagram illustrating a process for converting a non-partitioned tablespace to a partitioned tablespace according to some embodiments of the present disclosure.

[0023] While the present disclosure is susceptible to various modifications and alternative forms, specific aspects of the disclosure have been shown by way of example in the drawings and are hereinafter described in detail. It is to be understood, however, that there is no intention to limit the disclosure to the specific embodiments described. On the contrary, the intention is to cover all modifications, equivalents, and alternatives falling within the spirit and scope of the present disclosure. DETAILED DESCRIPTION OF THE INVENTION

[0024] It will be readily understood that the components of the present embodiments, as generally described herein and illustrated in the Figures, could be arranged and designed in a wide variety of different configurations. Thus, the following detailed description of the present apparatus, system, method and computer program product embodiments as illustrated in the Figures is not intended to limit the scope of the claimed embodiments, but merely represents selected embodiments.

[0025] Throughout this specification, references to "a select embodiment," "at least one embodiment," "one embodiment," "another embodiment," "other embodiments," or "an embodiment," and similar phrases, refer to particular features, structures, or characteristics described with respect to an embodiment included in at least one embodiment. Thus, appearances of the phrases "a select embodiment," "at least one embodiment," "in one embodiment," "another embodiment," "other embodiments," or "an embodiment" in various places throughout this specification are not necessarily referring to the same embodiment.

[0026] The illustrated embodiments are best understood by referring to the drawings, in which like reference numerals refer to like parts throughout the accompanying drawings. The following description is intended to be illustrative only and merely illustrates selected specific embodiments of devices, systems and processes consistent with the embodiments claimed herein.

[0027] Although this disclosure includes detailed descriptions related to cloud computing, it should be understood that implementation of the teachings described herein is not limited to cloud computing environments. Rather, embodiments of the present disclosure can be implemented in conjunction with any other type of computing environment currently unknown or later developed.

[0028] Cloud computing is a model of service delivery that enables convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, network bandwidth, servers, processing, memory, storage, applications, virtual machines, and services) that can be rapidly provisioned and released with minimal management effort or interaction with a service provider. The cloud model may include at least five characteristics, at least three service models, and at least four deployment models.

[0029] The characteristics are as follows:

[0030] On-Demand Self-Service: Cloud consumers can automatically and unilaterally provision computing capabilities such as server time and network storage as needed, without requiring human interaction with the service provider.

[0031] Widespread network access: Features are available over the network and accessed through standard mechanisms that facilitate use by heterogeneous thin or thick client platforms (e.g., mobile phones, laptops, and PDAs).

[0032] Resource Pooling: A provider's computing resources are pooled to serve multiple consumers using a multi-tenant model with different physical and virtual resources dynamically allocated and reallocated on demand. While consumers generally have no control over or awareness of the exact location of the resources provided, there is a sense of location independence in the sense that location can be specified at a higher level of abstraction (e.g., country, state, or data center).

[0033] Rapid Elasticity: Capabilities can be provisioned quickly and elastically, in some cases automatically, to quickly scale out, quickly release, and quickly scale in. To the consumer, the capabilities available for provisioning often appear infinite, and can be purchased in any quantity at any time.

[0034] Measured Services: Cloud systems automatically control and optimize resource utilization by utilizing metering capabilities at a specific level of abstraction appropriate to the type of service (e.g., storage, processing, bandwidth, and active user accounts). Resource utilization can be monitored, controlled, and reported, providing transparency to both providers and consumers of the services used.

[0035] The service model is as follows:

[0036] Software as a Service (SaaS): The consumer is offered the ability to use a provider's applications running on a cloud infrastructure. The applications are accessible from a variety of client devices through a thin-client interface such as a web browser (e.g., web-based email). With the possible exception of limited user-specific application configuration settings, the consumer does not manage or control the underlying cloud infrastructure, including the network, servers, operating systems, storage, or even individual application functions.

[0037] Platform as a Service (PaaS): The ability offered to consumers is to deploy applications they create or acquire using programming languages ​​and tools supported by the provider onto a cloud infrastructure. The consumer does not manage or control the underlying cloud infrastructure, including networks, servers, operating systems, or storage, but does have control over the deployed applications and potentially the application hosting environment configuration.

[0038] Infrastructure as a Service (IaaS): The ability provided to consumers is to provision processing, storage, network, and other basic computing resources, on which the consumer can deploy and run any software, which may include operating systems and applications. The consumer does not manage or control the underlying cloud infrastructure, but does have control over the operating system, storage, deployed applications, and possibly limited control over the selection of network components (e.g., host firewalls).

[0039] The deployment model is as follows:

[0040] Private Cloud: Cloud infrastructure is operated solely for one organization. A private cloud may be managed by that organization or a third party and may exist on-premise or off-premise.

[0041] Community Cloud: Cloud infrastructure is shared by multiple organizations to support a specific community with shared interests (e.g., mission, security requirements, policy and compliance considerations). A community cloud may be managed by the organizations or a third party and may exist on-premises or off-premises.

[0042] Public Cloud: Cloud infrastructure is made available to the public at large or to large industry groups and is owned by a single organization that sells cloud services.

[0043] Hybrid cloud: a cloud infrastructure composed of two or more clouds (private, community, or public) that remain distinct entities but are joined by standard or proprietary technologies that allow data portability and application portability (e.g., cloud bursting for load balancing between clouds).

[0044] Cloud computing environments are service-oriented, focusing on statelessness, low coupling, modularity, and semantic interoperability. At the core of cloud computing is an infrastructure that includes a network of interconnected nodes.

[0045] Referring to FIG. 1 , an exemplary cloud computing environment 50 is shown. As shown, the cloud computing environment 50 includes one or more cloud computing nodes 10, with which local computing devices used by cloud consumers, such as, for example, a personal digital assistant (PDA) or cellular phone 54A, a desktop computer 54B, a laptop computer 54C, and / or a vehicle computer system 54N, may communicate. The nodes 10 may also communicate with each other. The nodes may be physically or virtually grouped (not shown) in one or more networks, such as a private cloud, a community cloud, a public cloud, or a hybrid cloud, or combinations thereof, as described above. This enables the cloud computing environment 50 to provide infrastructure as a service, platform as a service, or software as a service, or combinations thereof, without the need for cloud consumers to maintain resources on their local computing devices. It should be understood that the types of computing devices 54A-N illustrated in FIG. 1 are intended to be exemplary only, and that computing node 10 and cloud computing environment 50 can communicate with any type of computerized device through any type of network or network-addressable connection (e.g., using a web browser), or both.

[0046] Referring now to Figure 2, a set of functional abstraction layers provided by cloud computing environment 50 (Figure 1) is illustrated. It should be understood in advance that the components, layers, and functions illustrated in Figure 2 are intended to be exemplary only, and that embodiments of the present disclosure are not limited thereto. As shown, the following layers and corresponding functions are provided:

[0047] Hardware and software layer 60 includes hardware and software components. Examples of hardware components include mainframe 61, RISC (reduced instruction set computer) architecture-based server 62, server 63, blade server 64, storage device 65, and network and network components 66. In some embodiments, software components include network application server software 67 and database software 68.

[0048] The virtualization layer 70 provides an abstraction layer in which the following examples of virtual entities may be provided: virtual servers 71, virtual storage 72, virtual networks including virtual private networks 73, virtual applications and operating systems 74, and virtual clients 75.

[0049] In one example, the management layer 80 may provide the following functions: Resource provisioning 81 provides dynamic procurement of computing and other resources utilized to execute tasks within the cloud computing environment. Metering and pricing 82 provides cost tracking as resources are used within the cloud computing environment and charging or billing for the consumption of these resources. In one example, these resources may include application software licenses. Security provides identity verification of cloud consumers and protection for tasks, data, and other resources. User portal 83 provides access to the cloud computing environment for consumers and system administrators. Service level management 84 provides cloud computing resource allocation and management so that required service levels are met. Service level agreement (SLA) planning and fulfillment 85 provides advance arrangements and procurement for cloud computing resources that anticipate future requirements according to SLAs.

[0050] The workload layer 90 provides examples of functionality for which a cloud computing environment may be used. Examples of workloads and functionality that may be provided from this layer include mapping and navigation 91, software development and lifecycle management 92, virtual classroom instruction delivery 93, data analytics processing 94, transaction processing 95, and determining the need to perform partial data reorganization 96 driven by workload performance.

[0051] 3, a block diagram of an example data processing system, referred to herein as computer system 100, is shown. System 100 may be embodied in a computer system / server at a single location, or, in at least one embodiment, may be configured in a cloud-based system that shares computing resources. For example, but not by way of limitation, computer system 100 may be used as cloud computing node 10.

[0052] Aspects of computer system 100 may be embodied in a computer system / server at a single location to implement the systems, tools, and processes described herein, or, in at least one embodiment, may be configured as a cloud-based support system within a cloud-based system sharing multiple computing resources. Computer system 100 is operable in numerous other general-purpose or special-purpose computer system environments or configurations. Non-limiting examples of well-known computer systems, environments, or configurations, or combinations thereof, that may be suitable for use with computer system 100 include personal computer systems, server computer systems, thin clients, thick clients, handheld or laptop devices, microprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputer systems, mainframe computer systems, and file systems (e.g., distributed storage environments and distributed cloud computing environments) that include any of the above systems, devices, and their equivalents.

[0053] Computer system 100 may be described in the general context of computer system-executable instructions, such as program modules, executed by computer system 100. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. Computer system 100 may be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computer system storage media, including memory storage devices.

[0054] As illustrated in FIG. 3, computer system 100 is shown in the form of a general-purpose computing device. Components of computer system 100 may include, but are not limited to, one or more processors or processing devices 104 (sometimes referred to as processors and processing units), such as a hardware processor, system memory 106, and a communication bus 102 that couples various system components including system memory 106 to processing device 104. Communication bus 102 represents one or more of any of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of a variety of bus architectures. By way of example and not limitation, such architectures include an Industry Standard Architecture (ISA) bus, a MicroChannel Architecture (MCA) bus, an Enhanced ISA (EISA) bus, a Video Electronics Standard Association (VESA) local bus, and a Peripheral Component Interconnect (PCI) bus. Computer system 100 typically includes a variety of computer system-readable media. Such media may be any available media that is accessible by computer system 100, including both volatile and nonvolatile media, removable and non-removable media. Computer system 100 may also include one or more persistent storage devices 108, communications units 110, input / output (I / O) units 112, and displays 114.

[0055] Processing device 104 serves to execute instructions of software that may be loaded into system memory 106. Processing device 104 may be multiple processors, a multi-core processor, or another specific type of processor, depending on the particular implementation. As used herein, the number of an item means one or more of the item. Furthermore, processing device 104 may be implemented using a heterogeneous processor system in which a main processor resides on a single chip along with secondary processors. As another example, processing device 104 may be a symmetric multiprocessor system including multiple processors of the same type.

[0056] System memory 106 and persistent storage 108 are examples of storage devices 116. A storage device may be any piece of hardware capable of storing information, such as, but not limited to, data, program code in functional form, and / or other suitable information on either a temporary and / or permanent basis. In these examples, system memory 106 may be, for example, a random access memory or any other suitable volatile or non-volatile storage device. System memory 106 may include a computer system-readable medium in the form of volatile memory, such as random access memory (RAM) or cache memory, or both.

[0057] Persistent storage 108 may take various forms, depending on the particular implementation. For example, persistent storage 108 may include one or more components or devices. For example, without limitation, persistent storage 108 may be provided for reading from and writing to a non-removable, non-volatile medium (not shown and typically referred to as a “hard drive”). Although not shown, a magnetic disk drive may be provided for reading from and writing to a removable, non-volatile magnetic disk drive (e.g., a “floppy disk”), and an optical disk drive may be provided for reading from or writing to a removable, non-volatile optical disk, such as a CD-ROM, DVD-ROM, or other optical media. In such examples, each may be connected to communication bus 102 by one or more data media interfaces.

[0058] In these examples, the communication unit 110 may provide for communication with other computer systems or devices. In these examples, the communication unit 110 is a network interface. The communication unit 110 may provide for communication using one or both of physical and wireless communication links.

[0059] The input / output unit 112 may enable the input and output of data to and from other devices that may be connected to the computer system 100. For example, the input / output unit 112 may provide a connection for user input through a keyboard, a mouse, or some other suitable input device, or a combination thereof. Additionally, the input / output unit 112 may send output to a printer. The display 114 may provide a mechanism for displaying information to a user. Examples of the input / output unit 112 that facilitate establishing communications between various devices in the computer system 100 include, but are not limited to, a network card, a modem, and an input / output interface card. The computer system 100 may also communicate with one or more networks, such as a local area network (LAN), a general wide area network (WAN), or a public network (e.g., the Internet), or a combination thereof, via a network adapter (not shown in FIG. 3 ). It should be understood that other hardware and / or software components, not shown, may be used in conjunction with the computer system 100. Examples of such components include, but are not limited to, microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data archive storage systems.

[0060] Instructions for the operating system, applications, or programs, or combinations thereof, may be located in storage device 116, which communicates with processing device 104 over communication bus 102. In these examples, the instructions are in functional form on persistent storage 108. These instructions may be loaded into system memory 106 for execution by processing device 104. The processes of the different embodiments may be performed by processing device 104 using computer-implemented instructions, which may be located in a memory, such as system memory 106. These instructions may be referred to as program code, computer-usable program code, or computer-readable program code, which may be read and executed by a processor in processing device 104. The program code in the different embodiments may be embodied on different physical or tangible computer-readable media, such as system memory 106 or persistent storage 108.

[0061] Program code 118 may be located in a functional form on computer-readable medium 120, which is selectively removable, and may be loaded onto or transferred to computer system 100 for execution by processing device 104. In these examples, program code 118 and computer-readable medium 120 may form computer program product 122. In one example, computer-readable medium 120 may be computer-readable storage medium 124 or computer-readable signal medium 126. Computer-readable storage medium 124 may include, for example, an optical or magnetic disk inserted into or placed into a drive or other device that is part of persistent storage 108 for transfer to a storage device, such as a hard drive that is part of persistent storage 108. Computer-readable storage medium 124 may also take the form of persistent storage, such as a hard drive, thumb drive, or flash memory that is connected to computer system 100. In some examples, computer-readable storage medium 124 may not be removable from computer system 100.

[0062] Alternatively, program code 118 may be transferred to computer system 100 using computer-readable signal medium 126. Computer-readable signal medium 126 may be, for example, a propagated data signal containing program code 118. For example, computer-readable signal medium 126 may be an electromagnetic signal, an optical signal, or any other suitable type of signal or combination thereof. These signals may be transmitted over communications links, such as wireless communications links, fiber optic cable, coaxial cable, a wire, or any other suitable type of communications link or combination thereof. In other words, in examples, communications links and / or connections may be physical or wireless.

[0063] In some exemplary embodiments, program code 118 may be downloaded to persistent storage 108 through computer-readable signal medium 126 from another device or computer system over a network and used within computer system 100. For example, program code stored in a computer-readable storage medium in a server computer system may be downloaded from the server over a network to computer system 100. The computer system providing program code 118 may be a server computer, a client computer, or some other device capable of storing and transmitting program code 118.

[0064] Program code 118 may include, by way of example and not limitation, one or more program modules (not shown in FIG. 3 ) that may be stored in system memory 106, as well as an operating system, one or more application programs, other program modules, and program data. Each of the operating system, one or more application programs, other program modules, and program data, or some combination thereof, may include an implementation of a network environment. The program modules of program code 118 generally perform the functions and / or methodologies of the embodiments described herein.

[0065] The different components illustrated for computer system 100 are not meant to provide architectural limitations with respect to the manner in which different embodiments may be implemented. Different illustrative embodiments may be implemented in computer systems including components in addition to or instead of those illustrated for computer system 100.

[0066] The present disclosure may be a system, method, or computer program product, or combination thereof, at any conceivable level of technical detail of integration. The computer program product may include a computer-readable storage medium or media having computer-readable program instructions for causing a processor to perform aspects of the present disclosure.

[0067] A computer-readable storage medium may be a tangible device capable of holding and storing instructions for use by an instruction execution device. A computer-readable storage medium may be, for example, but not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing. A non-exhaustive list of more specific examples of computer-readable storage media includes the following: portable computer diskettes, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disk read-only memory (CD-ROM), digital versatile disk (DVD), memory sticks, floppy disks, mechanically encoded devices such as punch cards or ridge structures in a groove having instructions recorded thereon, and any suitable combination of the foregoing. Computer-readable storage media as used herein should not be construed as transitory signals per se, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagated through a waveguide or other transmission medium (e.g., light pulses passing through a fiber optic cable), or electrical signals transmitted over wires.

[0068] The computer-readable program instructions described herein may be downloaded from a computer-readable storage medium into each computing / processing device, or may be downloaded to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, or a wireless network, or a combination thereof. The network may include copper transmission cables, optical transmission fiber, wireless transmission, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. A network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and transfers the computer-readable program instructions for storage in a computer-readable storage medium in the respective computing / processing device.

[0069] The computer-readable program instructions described herein may be downloaded from a computer-readable storage medium into each computing / processing device, or may be downloaded to an external computer or external storage device via a network, such as the Internet, a local area network, a wide area network, or a wireless network, or a combination thereof. The network may include copper transmission cables, optical transmission fiber, wireless transmission, routers, firewalls, switches, gateway computers, or edge servers, or a combination thereof. A network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and transfers the computer-readable program instructions for storage in a computer-readable storage medium in the respective computing / processing device.

[0070] The computer-readable program instructions for carrying out the operations of the present disclosure may be either assembler instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, configuration data for an integrated circuit, or source or object code written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Smalltalk®, C++, and procedural programming languages ​​such as the "C" programming language or similar programming languages. The computer-readable program instructions may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet Service Provider). In some embodiments, an electronic circuit, including, for example, a programmable logic circuit, a field programmable gate array (FPGA), or a programmable logic array (PLA), may execute computer-readable program instructions using state information of the computer-readable program instructions to personalize the electronic circuit to carry out aspects of the present disclosure.

[0071] Aspects of the present disclosure are described herein with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the present disclosure. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer-readable program instructions.

[0072] These computer-readable program instructions may be provided to a computer processor or other programmable data processing apparatus to produce machine, which, when executed by the computer processor or other programmable data processing apparatus, forms means for implementing the functions / acts specified in the block(s) of the flowcharts and / or block diagrams. These computer-readable program instructions may also be stored in a computer-readable storage medium, which may instruct a computer, programmable data processing apparatus, and / or other device to function in a particular manner, such that the computer-readable storage medium having instructions stored thereon comprises a product including instructions that implement aspects of the functions / acts specified in the block(s) of the flowcharts and / or block diagrams.

[0073] The computer-readable program instructions may also be loaded into a computer, other programmable data processing apparatus, or other device and executed on the computer, other programmable apparatus, or other device to produce a series of operational steps to generate a computer-implemented process, such that the instructions executing on the computer, other programmable apparatus, or other device implement the functions / operations specified in the block(s) of the flowcharts and / or block diagrams.

[0074] The flowcharts and block diagrams in these figures illustrate the architecture, functionality, and processing of possible implementations of systems, methods, and computer program products according to various embodiments of the present disclosure. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of instructions comprising one or more executable instructions for implementing a specified logical function(s). In some alternative implementations, the functions noted in the blocks may occur in a different order than that shown in the figures. For example, two blocks shown in succession may actually be performed as a single step, in parallel, substantially in parallel, partially or fully overlapping in time, or in some cases the blocks may be performed in reverse order, depending on the functionality involved. It should also be noted that each block of the block diagrams and / or flowchart diagrams, and combinations of blocks in the block diagrams and / or flowchart diagrams, may be implemented by a special-purpose hardware-based system that performs the specified functions or operations or executes a combination of special-purpose hardware and computer instructions.

[0075] Many known electronic database storage systems typically store data information in multiple sections located adjacent to one another in physical storage. These sections are called "pages," where a page is a predefined physical storage capacity value, such as, but not limited to, 8 kilobytes (KB). Data in a storage system is typically organized into tables defined through multiple rows and columns. Each page may contain at least a portion of a table row, and the logical aspects of a table row may overlap multiple pages, resulting in a table spanning multiple pages. Multiple tables define a tablespace, where multiple pages are again organized into rows of tablespaces or pages, where rows of pages should not be confused with rows of tables. During database use, read, write, and delete transactions are directed to specific pages within the rows of the specific pages where the affected data table row resides. For example, deleting specific data can cause fragmentation issues, and data modifications on a page(s) can result in a suboptimal physical storage image. Furthermore, for example, if a row of a page is updated to have a longer text string than originally present, an "overflow record" may be created, in which case a pointer to a new storage location that holds sufficient storage space is stored in the data field. In such a case, a subsequent read operation on the original data field is redirected to the larger data field, resulting in two separate I / O operations rather than the original single I / O operation. Thus, exhaustive use of the database can result in a highly fragmented storage image. The more fragmented the storage image, the greater the number of redirections. As a result, the average access time for read and / or write operations can increase relative to the "ideal" state.

[0076] Stored data may also be organized so that certain pages are frequently accessed through an extended sequence of parallel or serialized transactions. Because pages represent a volume of physical storage, transaction traffic near each page can induce bottlenecks, thereby increasing transaction latency. Parallel and serialized transactions from one or more application processes may also be required to maintain concurrency of the data there using locks on the affected data, with the active process "owning" or "holding" the lock. However, locks may result in one or more types of contention, including, but not limited to, aborts, timeouts, and deadlocks. Contention is a measurable or determinable characteristic. Once the degree of parallel or serialized transactions reaches a threshold, pages and rows may be designated as "hot spots."

[0077] Furthermore, data residing in a database storage system is indexed based on the physical location of the data. Database indexing is a data structure technique for efficiently retrieving records from a database file based on specific indexed attributes, allowing queries to efficiently retrieve data from the database. An index is associated with a specific table and a relationship, consisting of one or more index attributes, such as a key field. The key field is typically the primary key of the relationship, and the key is a search value based on a table column. As the size of the database increases, so does the size of the index. Comparing a key to an index makes it possible to find one or more database records with identical values. Indexes greatly speed up data retrieval, so it is important to define the correct index for each table. Indexes also contain multiple pointers, which hold the physical address of each record in the database. Using pointers provides superior data retrieval performance, but at the expense of operations such as database loading and reorganization.

[0078] Thus, many known database storage systems require periodic reorganization of the data stored therein. During reorganization, the logical and / or physical organization and indexing of the data within the database are changed. Positive outcomes of reorganization include reducing hot spots and restoring the proximity of related data to the originally intended state. In traditional operations, many types of reorganization require denying access to the database (i.e., taking the database offline) during the reorganization. Taking the database offline can be unacceptable for high availability (e.g., 24 hours a day) databases, such as those serving e-commerce or the military, or large databases. Thus, a combination of resource and temporal constraints can make the necessary reorganization activity unattractive.

[0079] Evidence for the need to reorganize stored data may be based on collected performance data of database storage and associated indexes. The collected performance data is typically at the tablespace or partition level, and the collected performance data, e.g., statistics of data tables or partitions and their respective indexes, is analyzed by one or more tools to determine the need to reorganize the stored data. For example, after many changes to table data, logically sequential data may reside on non-sequential physical data pages, especially if many update operations create overflow records. When data is organized in this manner, database administrators must perform additional read operations to access the required data. Additional read operations are also required when many rows are deleted. Furthermore, many changes to table data can lead to degradation of index performance, which manifests as a noticeable change in the performance of queries that use the index. Therefore, all of these issues can increase input / output operations and degrade performance.

[0080] As mentioned above, performance data collected is typically at the tablespace or partition level. However, workload performance issues may be more granular, i.e., limited to a specific small portion of a table or partition, such that the affected workload performance is not reflected in table- or partition-level statistics. Also, in known database storage systems, reorganization activities may be performed only on a table- or partition-level basis, which entails the need to constrain database operations during the reorganization.

[0081] Systems, computer program products, and methods disclosed and described herein relate to driving database reorganization in response to a determined workload, and more particularly, to providing online partial reorganization of a database to improve workload performance. Performance-sensitive workloads are identified, with a "performance-sensitive workload" being defined herein as a workload that has either been previously identified as such by a user or determined to be performance-sensitive based on empirical data collection. In some embodiments, for example, but not limited to, performance-sensitive workloads include workloads that have at least satisfactory processing performance but have user-specified runtime requirements that typically result in relatively high costs or extended execution times, and thus are deemed performance-sensitive by a user because failure to run each workload at a minimum level of execution performance may result in additional costs and delays. In some embodiments, a relationship between runtime performance requirements and actual runtime execution is discovered and brought to the user's attention through collected performance information. Additionally, in some embodiments, performance-sensitive workloads are identified through one or more of a defined record access path, a record number or other record identifier, a record range within a table, a page range within a physical storage device, the number of pages read, list prefetching, sequential I / O operation requirements, and any determined data record overflow conditions, including overflow rates, hotspot conditions, and tablespace lookup times. Thus, performance-sensitive workloads are either explicitly identified prior to their execution or discovered during the collection of runtime measurements.

[0082] In at least one embodiment, to advance performance information collection, the access plan of each search workload is used to analyze the methods used to access tables and indexes. Methods include, but are not limited to, index scan, table scan, list prefetch, and non-matching index access. Execution log information, such as page I / O operations, CPU cost, overflow rate, and execution time for each workload, is also collected. Workload information, such as data update and insert workloads, page I / O operations, and tablespace search time, is also recorded. Database statistics, such as cluster ratio, record deletion number, record insertion number, and record overflow count, are also recorded. Thus, performance information collection includes data collection from execution logs, workload operations, and database statistics.

[0083] In one or more embodiments, once the above performance information is collected, the workload performance information is analyzed to determine the nature of any indicators of performance problems, what portions of the stored data are affected, and what characteristics or characteristics of the tablespaces, partitions, or indexes need to be improved. Generally, there are four main problems that are analyzed to determine potential workflow problems, each with a general solution:

[0084] The first issue analyzed involves cluster ratio. Table records may be organized so that the records themselves are partially or totally ordered with respect to the record's key field, which defines the associated index for that particular table or tables. A table that has a meaningful ordering of its records with respect to a defined key is said to be clustered with respect to that key. Clustered tables are more efficient than non-clustered tables, and key-based clustered operations are performed on the table. Clustering is measured through a cluster ratio, which represents the degree of clustering for each index; in some embodiments, the cluster ratio is expressed as the percentage of rows that are stored sequentially on contiguous pages with respect to the index's sort order. The higher the cluster ratio value, the better the affected rows are ordered, or clustered, with respect to the desired index key sequence. If a table row is close to the desired index key sequence, the table row can be read from the affected data page while the page is still in the buffer, thereby reducing or eliminating extraneous I / O operations. Indicators of performance degradation of a performance-sensitive workload due to clustering issues include, but are not limited to, determining that the clustering of rows approaches or passes below a predetermined low threshold, as indicated by a decreasing cluster ratio, determining an increase in the number of page I / O operations, and scanning tables rather than using indexes to perform certain query operations. When one or more of the above indicators are determined, a typical high-level solution may include local and partial reorganization of the table on the affected record ranges, including performing a local partial reorganization to restore the desired clustering sequence of the affected records.

[0085] A second problem analyzed involves hot spots, which are defined earlier in this specification. Indicators of performance degradation of performance-sensitive workloads due to hot spot issues include, but are not limited to, extensive or excessive contention and extended table space search times. When one or more of the above indicators are determined, a typical high-level solution may include a localized partial reorganization of the table on the affected page range, performing a localized partial reorganization to spread the hot data across more of the table space and increase the amount of free physical storage space available on the affected pages.

[0086] A third problem analyzed involves data record overflow issues, with indicators including, but not limited to, the ratio of overflow records to total records, i.e., the overflow rate approaching or exceeding an established threshold. When one or more of the above indicators are determined, a typical high-level solution may include a local partial reorganization of the table on the affected page range, performing a local partial reorganization to remove overflow records and increase the amount of free physical storage space available on the affected pages.

[0087] A fourth problem analyzed involves data record space utilization issues, with indicators including, but not limited to, the number of page I / O transactions approaching or exceeding an established threshold for the number of records in the affected tablespace. After a period of time, free space within the pages of the tablespace may become unevenly distributed across pages through the addition and deletion of data and data records. When the above indicators are determined, a typical high-level solution may include a localized partial reorganization of the table on the affected page range, performing a localized partial reorganization to reorganize free space within the affected pages of the tablespace through defragmentation so that the free space is more evenly distributed and meets the desired distribution pattern of free space within the pages of the database table.

[0088] The number of four problems analyzed is not a limiting value. Also, the serialization order of the analysis as presented herein is not limiting; in at least some embodiments, the analysis is performed in parallel. Thus, indicators of cluster ratio problems, hot spots, overflow problems, and space utilization problems resulting from performance information collection and analysis for performance-sensitive workloads may be mitigated through localized partial reorganization of tables for affected records and page ranges.

[0089] Referring to Figure 4, a schematic diagram is shown illustrating a portion of the computer system / server illustrated in Figure 3 (computer system / server 300), hereinafter referred to as a database partial reorganization system 400, for implementing the processes described herein. Still referring to Figure 3, the database partial reorganization system 400 includes at least one processing device 404 similar to processing device 104, which may also be referred to as a central processing unit (CPU). The database partial reorganization system 400 also includes at least one memory device 406 similar to at least a portion of system memory 106. The database partial reorganization system 400 further includes a database storage system 408 that is at least a portion of persistent storage device 108. The processing device 404, the memory device 406, and the database storage system 408 are communicatively and operably coupled through a communication bus 402 similar to communication bus 102. Communication bus 402 is illustrated as two separate communication buses 402A and 402B to distinguish communications thereon, as described in further detail herein. However, in some embodiments, with appropriate controls, all communication traffic occurs on a single communication bus 402. Also coupled to communication bus 402A are other devices 430, which may include, but are not limited to, communication unit 110, I / O unit 112, and display 114, all of which are illustrated in FIG. 3 but not FIG. 4. In one or more embodiments, one or more tools reside within memory device 406. Specifically, the tools include a reorganization module 440 that includes an embedded workload analysis module 442. Workload analysis module 442 is communicatively coupled to communication bus 402A, and reorganization module 440 is communicatively and operably coupled to communication bus 402B. Reorganization module 440 and workload analysis module 442 are further described herein below.Therefore, a partial database reorganization system 400 with tools including a reorganization module 440 and a workload analysis module 442 is used to implement the online partial database reorganization described herein to improve workload performance, including performance-sensitive workloads.

[0090] FIG. 5 is a high-level flowchart illustrating a process 500 for driving workload-based online partial database reorganization to improve workload performance. FIGS. 6A-6C show more detailed flowcharts illustrating the process 600 for driving workload-based online partial database reorganization illustrated in FIG. 5. Referring to FIG. 6A, process 600 includes identifying 602 a performance-sensitive workload. In some embodiments, a “performance-sensitive workload” is defined as a workload that has either previously been identified as such by a user or that has been determined to be performance-sensitive based on empirical data collection. The empirical data collection may be fully automated. In some embodiments, for example, but not by way of limitation, a performance-sensitive workload includes a workload that has at least satisfactory processing performance but has user-specified runtime requirements that typically result in relatively high costs or extended execution times, and that is therefore deemed performance-sensitive by the user because failure to run the respective workload at a minimum level of execution performance may result in additional costs and delays. In some embodiments, a relationship between runtime performance requirements and actual runtime execution is discovered and brought to the user's attention through collected performance information. Also, in some embodiments, performance-sensitive workloads are identified through one or more of a defined record access path, a record number or other record identifier, a record range within a table, a page range within a physical storage device, the number of pages read, list prefetching, sequential I / O operation requirements, and any determined data record overflow conditions, including overflow rates, hotspot conditions, and tablespace lookup times. Thus, performance-sensitive workloads may be explicitly identified prior to their execution or discovered during the collection of runtime measurements.

[0091] Still referring to FIG. 4, in some embodiments, process 600 includes collecting 604 performance information for performance-sensitive workloads associated with one or more portions of each database. Sources of information are illustrated in FIG. 5. First, in at least one embodiment, to advance performance information collection, the access plan for each search workload is used to analyze the methods used to access tables and indexes. Methods include, but are not limited to, index scan, table scan, list prefetch, and non-matching index access. An execution log 502 is also accessed by workload analysis module 442 over communication bus 402A. At least some of the information in execution log 502 includes, but is not limited to, page I / O operations, CPU cost, overflow rate, and execution time for each workload. Workload information 504 is also accessed by workload analysis module 442 over communication bus 402A. At least some of the workload information 504 includes, but is not limited to, data update and insert workloads, page I / O operations, and tablespace lookup time. Additionally, database statistics 506 are accessed by workload analysis module 442 through communication bus 402A. At least some of database statistics 506 include, but are not limited to, cluster ratios, number of record deletions, number of record insertions, and number of record overflows. Thus, performance information collection includes data collection from execution logs 502, workload information 504, and database statistics 506.

[0092] In one or more embodiments, once the above performance information is collected 604, the collected workload performance information is analyzed 508 / 606 by the workload analysis module 442 to determine the nature of any indicators of performance degradation. In at least some embodiments, the analysis is performed in a fully automated manner, and in some embodiments, is continuous. The determined performance degradation is also used to determine which of one or more database portions is affected by the determined performance degradation, i.e., the portion of stored data that is affected. The determination of the database portions is also used to classify the determined performance degradation, i.e., to determine which characteristic(s) of a tablespace, partition, or index needs to be improved. Generally, there are four main problems that are analyzed to determine potential workflow issues, each of which has a general solution, and each performance problem is further described herein. In at least one embodiment, the process 600 includes determining 608 recommended online partial reorganization actions 510, as further described herein below, to resolve the four performance problems.

[0093] 6B , with continuing reference to FIG. 6A , operation 606 of analyzing performance information and operation 608 of determining recommended online partial reorganization actions to resolve the four performance issues are further described in analysis and action set 610. Analysis and action set 610 is divided into three sections: indicators analyzed for each of the four performance issues 612, a determination of the presence or absence of a performance issue 614, and recommended reorganization actions 616. The determinations associated with operations 602, 604, 606, and 608 may be fully automated. The recommended online partial reorganization actions may be fully automated. The number of four issues analyzed is not a limiting value. Also, the serialized order of the analysis as presented herein is not limiting; in at least some embodiments, the analysis is performed in parallel. Thus, indicators of cluster ratio issues, hot spots, overflow issues, and space utilization issues resulting from performance information collection and analysis for performance-sensitive workloads may be mitigated through local partial reorganization of the table for the affected records and page ranges.

[0094] In some embodiments, the first problem analyzed includes the cluster ratio. Indicators 620 of performance degradation of a performance-sensitive workload due to clustering issues include, but are not limited to, analyzing the clustering ratio of each row. Another indicator 620 analyzed includes the number of I / O operations performed for a particular workload. Further indicators 620 analyzed include observing portions of the workload to distinguish between table scans for a query and the use of indexes for a query. A determining operation 622 is performed to determine whether the cluster ratio is low enough to justify a reorganization. Specifically, the indicator 620 indicates one or more of a declining cluster ratio determined to approach or pass below a predetermined low threshold, an increase in the number of page I / O operations, and an increase in scans of tables relative to the use of indexes to perform a particular query operation. If the result of the determination operation 622 is "yes," a typical high-level resolution includes a local partial reorganization of the table over the range of affected records, which may be performed to restore 624 the desired clustering sequence of the affected records. Such partial reorganization to restore 624 the desired clustering sequence may be implemented through a user interface or may be automated.

[0095] Referring to Figure 7, a schematic diagram illustrating a process 700 for restoring a desired clustering sequence of affected records to mitigate a low cluster ratio determination is shown. Also referring to Figure 4, a portion of a database 702 is illustrated with a tablespace 704, a plurality of data records 706, an index 708, and a plurality of pointers 710 (only two are labeled) that extend from the index 708 to individual records 706. The index 708 and tablespace 704 reside in the database storage system 408.

[0096] In some embodiments, process 700 includes unloading 720 (i.e., copying or scanning) each record 706 within the determined record range from tablespace 704 through reorganization module 440 to temporarily reside in memory device 406, as illustrated by arrow 722, thereby defining a set of unloaded records 724. Records 706 are selected for unloading through respective cluster index access paths (not shown) or using a list prefetch function to unload 720 the records 706. As described further herein, the records 706 remain in use until the switch activity is completed. Thus, each record 706 to be reorganized is copied from their respective portion of tablespace 704 to memory device 406.

[0097] In at least some embodiments, process 700 also includes loading 730 the unloaded records 724 into new pages 732 in a buffer pool 734, as illustrated by arrow 736. In some embodiments, the buffer pool 734 resides in memory device 406, and in some embodiments, the buffer pool 734 resides in tablespace 704. Prior to loading 730, the storage space in tablespace 704 required to receive the unloaded records 724 is calculated, including an appropriate value for the percentage of free space remaining on each page after loading 730 and the number of free pages to be added. The reserved pages 732 are allocated to a contiguous portion 738 of tablespace 704, as illustrated by arrow 740; in some embodiments, the contiguous portion 738 is an existing partition. In some embodiments, a new partition (described further herein) is created. Online transaction synchronization 742 is applied to record 724 (as indicated by arrow 744), i.e., page 732 is set as "unavailable" to prevent inadvertent use of reserved and allocated page space and page 732 in contiguous portion 738 because database storage system 408 is not taken offline while routine data read, write, and delete activity continues. Although not shown in FIG. 7 , existing record 706 in tablespace 704 is updated in parallel with unloaded record 724 to facilitate maintaining synchronization of the data therein until switch activity (described further herein) is completed. Unloaded record 724 is loaded 730 into new page 732 in buffer pool 734. In some embodiments, page 746 with the loaded record is written 748, i.e., sent, to reserved contiguous portion 738 of tablespace 704. In some embodiments, a new partition 750 is created outside of tablespace 704 and pages 746 are allocated to a contiguous portion 752 of new partition 750 , as illustrated by arrow 754 .The pages 746 with the loaded records are written 756, i.e., sent, to a reserved contiguous portion 752 of the new partition 750. Thus, as each record 724 is loaded into each page 732 and the loaded pages 746 are sent to the tablespace 704 or new partition 750, online transaction synchronization 742 is continually applied to the affected records to mitigate the possibility of stale data.

[0098] In one or more embodiments, keys 762 associated with indexes 708 are constructed 760 to reflect the new location of pages 746. A map is constructed 764 between old ROWID values ​​766 and new ROWID values ​​768. Index keys 762 are constructed 760 for all indexes 708 in the affected range, and the index keys 762 are now associated with the new ROWID values ​​768. Thus, migrating the location of pages 746 involves generating new index keys 762 for each affected row in the data table.

[0099] In at least some embodiments, process 700 further includes, at the data page level, switching 770 the records loaded into page 746 into the appropriate clustering order. The old records 706 (only one shown) are locked (thereby terminating their online transaction synchronization 742) and deleted 772 from tablespace 704, and index 708 is updated to shift each pointer 710 to each record's new location, i.e., generating modified pointers 774. The new page 746 is made available for further updates by online transaction synchronization 742, which now reads and writes 776 to the records in new page 732. Thus, a local partial reorganization of the clustered data records over the affected record range may be performed to restore the desired clustering sequence of the affected records.

[0100] Referring again to FIG. 6B , if the result of determine operation 622 is “no,” in some embodiments, process 600 proceeds to a second problem to be analyzed, which includes a hotspot, as defined earlier in this specification. Indicators 630 of performance-sensitive workload performance degradation due to a hotspot problem include, but are not limited to, extensive or excessive contention and extended tablespace lookup times. Decision operation 632 is performed to determine whether the presence of a hotspot in a tablespace is negatively impacting performance sufficiently to warrant a reorganization. Specifically, indicator 630 may point to one or more of excessive contention and extended tablespace lookup times, which may be determined to cause performance to approach or pass below a predetermined low threshold. If the result of determine operation 632 is “yes,” a typical high-level solution may include a localized partial reorganization of the table on the affected page range, which may be performed to spread 634 the hot data across more of the tablespace and increase the amount of free physical storage space available on the affected pages. Such partial reorganization to spread 634 hot data may be implemented through a user interface or may be automated.

[0101] Referring to Figure 8, a schematic diagram illustrating a process 800 for mitigating performance issues associated with hotspots is illustrated. Also referring to Figure 4, a portion of a database 802 is illustrated with a tablespace 804, a number of pages 806, an index 808, and a number of pointers 810 extending from the index 808 to each of the individual pages 806. The index 808 and the tablespace 804 reside in the database storage system 408.

[0102] The plurality of pages 806 includes multiple page rows (unlabeled), each containing 16 pages, with the number of data pages per row being non-limiting. The page 806 includes a first page group 812 containing two rows, each containing 30 low-load pages 814, one medium-load page 816, and one hot page 818. The page 806 also includes a second page group 820 containing two rows, each containing six medium-load pages 816 and 26 hot pages 818. The page 806 also includes a third page group 822 containing three rows, each containing five medium-load pages 816 and 43 low-load pages 814. The page 806 also includes a fourth page group 824 containing 32 substantially free pages 826. As used herein, the term "low load" refers to some data stored therein with occasional read / write / delete access. Similarly, the term "medium load" refers to a medium amount of data stored therein with moderate read / write / delete access. The term "hot page" is explained elsewhere herein. As used herein, the term "substantially free" refers to a page that has no data or metadata stored thereon and that experiences substantially infrequent access. The plurality of pointers 810 includes five pointers 810, each associated with a hot page 818 in the second group of pages 820. While the illustrated embodiment includes pages 806 distributed in a particular organization, any number of pages 806 in any configuration that enables operation of the partial database reorganization system 400 described herein may be used.

[0103] In some embodiments, process 800 includes unloading 830 (i.e., copying or scanning) each page 806 within the identified paged range from tablespace 804 through reorganization module 440 to temporarily reside in memory device 406, as illustrated by arrow 832, thereby defining a set of unloaded records 834. Records 806 are selected for unloading through a respective hot page determination mechanism or using a list prefetch function, and records 806 are unloaded 830. As described further herein, records within the affected pages 806 remain in use until the switch activity is complete. Thus, each record 806 to be reorganized is copied from its respective portion of tablespace 804 to memory device 406.

[0104] In one or more embodiments, the records within the affected pages (not shown), i.e., the hot pages 818 of the second page group 820, are sorted 836 as needed to properly cluster the records within the affected pages. Thus, as described in more detail below, any clustering of the records is completed before loading the records.

[0105] In at least some embodiments, process 800 also includes loading 840 the unloaded records 834 into allocated pages. In some embodiments, only existing pages are used, such as the 32 substantially free pages 826. In some embodiments, only new substantially free pages specifically allocated to this process 800 are used, and no existing pages receive the unloaded records 834. In some embodiments, a combination of existing and new pages are allocated. While embodiments involving only new substantially free pages are described, those embodiments using only existing pages are treated similarly. Also, embodiments involving a combination of existing and new pages are described.

[0106] Thus, for embodiments in which only new substantially free pages are used, new substantially free pages 842 are created in buffer pool 844. In some embodiments, buffer pool 844 resides in memory device 406, and in some embodiments, buffer pool 844 resides in tablespace 804. Before load 840, the storage space in tablespace 804 required to receive unloaded records 834 is calculated, including the appropriate percentage of free space remaining on each page after load 840 and the number of free pages to be added.

[0107] In embodiments including a first post-switch configuration 870 of tablespace 804, only the reserved new substantially free pages 842 reside in buffer pool 844, and the reserved pages 842 are allocated within a contiguous portion 848 of tablespace 804, as indicated by arrow 850; in some embodiments, contiguous portion 848 may be an existing partition (not shown). In some embodiments, a new partition (as described with respect to FIG. 7) is created. Online transaction synchronization 852 is applied to unloaded records 834 (as indicated by arrow 854), i.e., because database storage system 408 is not taken offline while routine data read, write, and delete activity continues, pages 842 are set as “unavailable” to prevent inadvertent use of reserved and allocated page space and reserved pages 842 within contiguous portion 848. 8, until the switch activity (described further herein) is completed, the records residing in the affected pages 806 in the tablespace 804 are updated in parallel with the unloaded records 834 to facilitate maintaining data synchronization therein. In at least one embodiment, the unloaded records 834 are loaded 840 into new pages 842 in a buffer pool 844.

[0108] In at least one embodiment, the allocation of unloaded records 834 results in the elimination of hot spots through the proper spreading of unloaded records 834. In some embodiments, pages 856 with unloaded records 834 are written 858, i.e., sent, to reserved contiguous portions 848 of tablespace 804. Thus, as each unloaded record 834 is loaded into each page 842 and the loaded pages 856 are sent to tablespace 804 or a new partition (not shown), online transaction synchronization 852 is continually applied to the affected records to mitigate the possibility of stale data.

[0109] In one or more embodiments, keys 862 associated with indexes 808 are constructed 860 to reflect the new location of loaded pages 856. A map between old ROWID values ​​866 and new ROWID values ​​868 is constructed 864. Index keys 862 are constructed 860 for all indexes 808 in the affected range, now associated with the new ROWID values ​​868. Thus, migrating the location of loaded pages 856 involves generating new index keys 862 for each affected row in the data table.

[0110] In at least some embodiments, process 800 further includes switching 872 the loaded records into the loaded pages 856 into the appropriate clustering order at the data page level. Some of the old records, i.e., the records in the 26 hot pages 818 in the second page group 820, are locked (thereby terminating their online transaction synchronization 852) and deleted 874 from the tablespace 804, and the index 808 is updated to shift a portion of each pointer 810 to each record's new location, i.e., generating modified pointers 876. The new pages 856 are made available for further updates through online transaction synchronization 852, now reading and writing 878 to the records. Thus, a local partial reorganization of the tablespace 804 may be performed on the affected page range to spread the hot data across more of the tablespace 804 and increase the amount of available free physical storage space on the affected pages.

[0111] For embodiments in which a combination of existing and new pages are allocated, new substantially free pages 842 are created in buffer pool 844. Also, existing pages 843 (which contain 32 substantially free pages 826) are added to buffer pool 844, as illustrated by arrow 846. Before load 840, the storage space in tablespace 804 required to receive unloaded records 834 is calculated, including the appropriate percentage of free space remaining on each page after load 840 and the number of free pages to be added.

[0112] In an embodiment including a second post-switch configuration 880 of tablespace 804, new reserved substantially free page 842 and existing reserved substantially free page 843 reside in buffer pool 844, and reserved page 842 is allocated within contiguous portion 849 of tablespace 804, as indicated by arrow 851; in some embodiments, contiguous portion 849 may be an existing partition (not shown). In some embodiments, a new partition (as described with respect to FIG. 7 ) is created. Online transaction synchronization 852 is applied to unloaded records 834 (as indicated by arrow 854), i.e., pages 842 and 843 are set as “unavailable” to prevent inadvertent use of reserved and allocated page space within contiguous portion 849 and reserved pages 842 and 843, since database storage system 408 is not taken offline while routine data read, write, and delete activity continues. 8, until the switch activity (described further herein) is complete, the records residing in the affected pages 806 in tablespace 804 are updated in parallel with the unloaded records 834 to facilitate maintaining synchronization of the data therein. In at least one embodiment, the unloaded records 834 are loaded 840 into new pages 842 and existing pages 843 in a buffer pool 844.

[0113] In at least one embodiment, the allocation of unloaded records 834 results in the elimination of hot spots through the proper spreading of unloaded records 834. In some embodiments, pages 857 with unloaded records 834 are written 859, i.e., sent, to reserved contiguous portions 849 of tablespace 804. Thus, as each unloaded record 834 is loaded into each page 842 and 843 and the loaded pages 857 are sent to tablespace 804 or a new partition (not shown), online transaction synchronization 852 is continually applied to the affected records to mitigate the possibility of stale data.

[0114] In one or more embodiments, keys 862 associated with indexes 808 are constructed 860 to reflect the new location of loaded pages 857. A map between old ROWID values ​​866 and new ROWID values ​​868 is constructed 864. Index keys 862 are constructed 860 for all indexes 808 in the affected range, now associated with the new ROWID values ​​868. Thus, migrating the location of loaded pages 857 involves generating new index keys 862 for each affected row in the data table.

[0115] In at least some embodiments, process 800 further includes, at the data page level, switching 872 the loaded records into loaded page 857 in the proper clustering order. The old records, i.e., the records in the 26 hot pages 818 in the second page group 820, are locked (thereby terminating their online transaction synchronization 852) and deleted 884 from tablespace 804, and index 808 is updated to shift each pointer 810 to each record's new location, i.e., generating modified pointer 886. Page 857 is made available for further updates through online transaction synchronization 852, and now reads and writes 878 to the records occur. Accordingly, a local partial reorganization of tablespace 804 may be performed on the affected page range to spread the hot data across more of tablespace 804 and increase the amount of available free physical storage space on the affected pages.

[0116] Embodiments including a first post-switch configuration 870 of table space 804 (only new substantially free pages 842) and a second post-switch configuration 880 of table space 804 (both new substantially free pages 842 and existing substantially free pages 843 are used) are distinguished. For example, in first post-switch configuration 870, not all of the affected records in second page group 820 are transferred to loaded page 856, as indicated by modified pointer 876. In contrast, in second post-switch configuration 880, all of the affected records in second page group 820 are transferred to loaded page 857, as indicated by modified pointer 886. An advantage of first post-switch configuration 870 is that fourth page group 824 maintains 32 substantially free pages 826 while eliminating hot spots in second page group 820. An advantage of the second post-switch configuration 880 is that any potential hot spots within the loaded page 856 are mitigated through further spreading of the affected records.

[0117] Referring again to FIG. 6B , if the result of determine operation 632 is “no,” in some embodiments, process 600 proceeds to a third problem to be analyzed, which includes a data record overflow problem. Referring to FIG. 6C , an indicator 640 of performance-sensitive workload performance degradation is due to the ratio of the number of overflow records to the number of total records, i.e., the overflow rate, approaching or exceeding an established threshold. A determine operation 642 is performed to determine whether the presence of a high overflow rate is negatively impacting performance sufficiently to warrant a reorganization. If the result of determine operation 642 is “yes,” a typical high-level solution includes a local partial reorganization of the table on the affected page range, where one or more of the following may be performed: to remove overflow records; and to increase the amount of free physical storage space available on the affected pages. Such partial reorganizations to remove overflow records and increase the amount of free physical storage space available on the affected pages may be performed. Such partial reorganizations to remove overflow records and increase the amount of free physical storage space may be implemented through a user interface or may be automated.

[0118] 9, a schematic diagram illustrating a process 900 for mitigating performance issues associated with high overflow rates is shown. Also referring to FIG. 4, a portion of a database 902 is illustrated with a tablespace 904, a number of pages 906, an index 908, and a number of pointers 910 extending from the index 908 to each individual page 906. The index 908 and the tablespace 904 reside in the database storage system 408.

[0119] A range 912 of pointer records is selected and scanned to determine each pointer 910 record. As shown, five pointer records 910 are within the range 912 associated with the overflow records. A first pointer 920 points to a first page 922 associated with a first overflow page 924 containing each overflow record, as indicated by an overflow arrow 926. The first page 922 is a hot page. A second pointer 930 points to a second page 932 associated with a second overflow page 934 containing each overflow record, as indicated by an overflow arrow 936. The second page 932 is an underloaded page. A third pointer 940 points to a third page 942 associated with a third overflow page 944 containing each overflow record, as indicated by an overflow arrow 946. The third page 942 is an underloaded page. A fourth pointer 950 points to a fourth page 952 associated with a fourth overflow page 954 containing each overflow record, as indicated by overflow arrow 956. The fourth page 952 is a high-load page, but not a hot spot. A fifth pointer 960 points to a fifth page 962 associated with a fifth overflow page 964 containing each overflow record, as indicated by overflow arrow 966. The fifth page 962 is a medium-load page. Thus, if database 902 contains a sufficient number of overflow records 924-964 in tablespace 904 to justify a localized partial reorganization of tablespace 904 on the affected page range, the localized partial reorganization may be performed to remove the overflow records and increase the amount of free physical storage space available on the affected pages.

[0120] In at least one embodiment, if any of the first through fifth pages 922 through 962 has enough space to store each overflow record without exceeding any established parameters, each overflow record is transferred to the first through fifth pages 922 through 962, respectively, and each overflow page no longer contains an overflow record. If there is not enough space, the index 908 is updated to point each pointer 920 through 960 directly to the respective overflow page 924 through 964, and the previous pointer record is deleted, thereby redirecting the overflow page to a page. Thus, in one or more embodiments, a post-high overflow rate database 970 is created.

[0121] In at least one embodiment, after a high overflow rate, database 970 includes one or more modified pointers 980. Specifically, because first page 922 is a hot page, first page 922 cannot receive overflow records from first overflow page 924, so a new pointer 928 pointing to first overflow page 924 is created, and pointer 920 is deleted. Also, because second page 932 is a low-load page, overflow records in second overflow page 934 are transferred to second page 932. Furthermore, because third page 942 is a low-load page, overflow records in third overflow page 944 are transferred to third page 942. Also, because fourth page 952 is a high-load page, fourth page 952 cannot receive overflow records from fourth overflow page 954, so a new pointer 958 pointing to fourth overflow page 954 is created, and pointer 950 is deleted. Also, because fifth page 962 is a medium load page, it is determined that fifth page 962 cannot receive overflow records from fifth overflow page 964, and a new pointer 968 pointing to fifth overflow page 964 is created and pointer 960 is deleted. Thus, database 902 contains a sufficient number of overflow records 924-964 in tablespace 904 to justify a local partial reorganization of tablespace 904 on the affected page range, and a local partial reorganization may be performed to remove the overflow records and increase the amount of free physical storage space available on the affected pages.

[0122] Referring again to FIG. 6C , if the result of determine operation 642 is “no,” in some embodiments, process 600 proceeds to a fourth problem to be analyzed, which includes a data record space utilization issue. Indicators 650 of performance-sensitive workload performance degradation due to data record space utilization issues include the number of page I / O transactions approaching or exceeding an established threshold for the number of records in the affected tablespace. Determine operation 652 is performed to determine whether the presence of the space utilization issue is negatively impacting performance sufficiently to warrant reorganization. If the result of determine operation 652 is “yes,” a typical high-level solution includes a localized partial reorganization of the table on the affected page range, which may be performed to reorganize free space within the affected pages of the tablespace through defragmentation 654 so that the free space is more evenly distributed and meets a desired distribution pattern for free space within the pages of the database table. Such partial reorganization to defragment 654 the tablespace may be implemented through a user interface or may be automated.

[0123] 10, a schematic diagram illustrating a process 1000 for mitigating performance issues related to space utilization issues is shown. Also referring to FIG. 4, a portion of a database 1002 is illustrated with a tablespace 1004, a range of pages 1006, and an index 1008. The index 1008 and tablespace 1004 reside in the database storage system 408.

[0124] The range of pages 1006 includes multiple page rows (unlabeled) (9 shown), each row including 16 pages, with an unlimited number of page rows and data pages per row. The range of pages 1006 includes a first group of pages, i.e., a first sub-range of pages 1010 rows, each including three rows. Similarly, the range of pages 1006 includes a second group of pages, i.e., a second sub-range of pages 1012, and a third group of pages, i.e., a third sub-range of pages 1014, each including three rows. For clarity of this disclosure, the range of pages 1006 is divided into multiple rows, with each sub-range including three rows. However, the number of page rows within each sub-range is unlimited, and a sub-range may include any number of page records, including, but not limited to, portions of a row. Thus, while the illustrated embodiment includes a range of pages 1006 and sub-ranges of pages 1010, 1012, 1014 distributed in a particular organization, any number of pages 1006 and sub-ranges of pages in any configuration that enables operation of the partial database reorganization system 400 as described herein may be used.

[0125] In some embodiments, process 1000 includes unloading 1020 (i.e., copying or scanning) a first sub-range of pages 1010 from tablespace 1004 through reorganization module 440 to temporarily reside in memory device 406, as illustrated by arrow 1022, thereby defining a set of unloaded records 1024. As described further herein, the records in the first sub-range of pages 1010 remain in use until the switch activity is completed. Thus, the records in each sub-range of pages 1010 to be reorganized are copied from their respective portions of tablespace 1004 to memory device 406.

[0126] In at least some embodiments, process 1000 also includes reloading 1030 the unloaded records 1024 into new pages 1032 allocated within a buffer pool 1034, as illustrated by arrow 1036. In some embodiments, buffer pool 1034 resides within memory device 406, and in some embodiments, the buffer pool resides within tablespace 1004. Prior to reloading 1030, new pages 1032 are mapped to pages within a first sub-range of pages 1010. Also prior to reloading 1030, the storage space within tablespace 1004 required to receive the unloaded records 1024 is calculated, including an appropriate value for the percentage of free space remaining on each page after the reload and the number of free pages to be added. Also, in one or more embodiments, prior to reloading 1030, the records within the affected pages (not shown), i.e., first sub-range of pages 1010, are sorted as needed to appropriately cluster the records within the affected pages within first sub-range 1010. Therefore, any clustering of each record is completed before reloading 1030 each record.

[0127] Online transaction synchronization 1042 is applied to the first subrange of pages 101 (as indicated by arrow 1044), i.e., pages 1032 in buffer pool 1034 are set as "unavailable" to prevent inadvertent use of reserved and allocated pages 1032 because the database storage system 408 is not taken offline while routine data read, write, and delete activity continues. Although not shown in FIG. 10 , records residing in affected pages 1006 in tablespace 1004 are updated in parallel with unloaded records 1024 to facilitate maintaining synchronization of the data therein until the switch activity (described further herein) is completed. The unloaded records 1024 are reloaded 1030 into allocated pages 1032 in buffer pool 1034. Additionally, as described further herein, within the allocated pages 1032, the affected subrange 1010 of the data pages 1006 is defragmented, resulting in the generation of a plurality of reorganized pages 1038 and a plurality of substantially free pages 1040. While the number of reorganized pages 1038 to the plurality of substantially free pages 1040 is illustrated as being approximately equal, the ratio of reorganized pages 1038 to substantially free pages may be any value that enables operation of the partial database reorganization system 400 described herein. Also, the ratio of data records to free space within the reorganized pages 1038 may be any value that enables operation of the partial database reorganization system 400 described herein.

[0128] In one or more embodiments, keys 1062 associated with the indexes 1008 are built 1060 to reflect the new locations of the new records within the reorganized page 1038. A map is built 1064 between the old ROWID values ​​1066 and the new ROWID values ​​1068. Index keys 1062 are built 1060 for all indexes 1008 on the reorganized page 1038, now associated with the new ROWID values ​​1068. Thus, defragmentation and reorganization of the unloaded records 1024 that are reloaded 1030 into the allocated pages 1032 to generate the reorganized page 1038 includes generating new index keys 1062 for each of the affected pages 1006 within the affected subrange 1010.

[0129] In at least some embodiments, process 1000 further includes, at the data page level, switching 1070 data pages 1006 in the first subrange of pages 1010 with reorganized pages 1038 and substantially free pages 1040. Old records (not shown) in the affected first subrange of data pages 1010 are locked in tablespace 1004 (thereby terminating their online transaction synchronization 1042), and index 1008 is updated to shift each pointer (not shown) to each record's new location. The reorganized pages 1038 and substantially free pages 1040 in buffer pool 1034 are written 1048, i.e., sent, to the portion of tablespace 1004 to store the first subrange of pages 1010, overwriting the existing pages therein. Thus, while defragmented and reorganized pages 1038 and substantially free pages 1040 are sent to the tablespace 1004, online transaction synchronization 1042 is continually applied to the affected records to mitigate potential inconsistent data.

[0130] Defragmentation and reorganization of the remaining second sub-range 1012 and third sub-range 1014, etc., occurs until all of page 1006 has been processed. Thus, referring again to Figures 6B-6C, one or more of the recommended partial reorganization methods 624, 634, 644, and 654 are performed 660.

[0131] Referring to Figure 11, a schematic diagram illustrating a process 1100 for defragmenting and reorganizing a tablespace to alleviate space utilization issues is shown. A tablespace 1104, a range of pages 1106, and an index 1108 are shown, three artifacts that are substantially similar to the similarly identified objects in Figure 10. The range of pages 1106 includes a first sub-range of pages 1110, a second sub-range of pages 1112, and a third sub-range of pages 1114, all three of which include a particular combination of low-load, medium-load, and high-load pages, with substantially free pages excluded for clarity in describing the process 1100. While the illustrated embodiment includes a range of pages 1106 in a particular organization with records of particular content and dispersed sub-ranges of pages 1110, 1112, 1114, any number of pages 1106 and sub-ranges of pages in any configuration with any amount of embedded records may be used that enables operation of the partial database reorganization system 400 as described herein.

[0132] The affected subrange 1110 of data pages 1106 is defragmented 1180 such that a plurality of reorganized pages 1138A and a plurality of substantially free pages 1140A are generated according to spatial parameters as described herein. The ratio of reorganized pages 1138A to a plurality of substantially free pages 1140A as shown is exemplary only, and the ratio of reorganized pages 1138A to substantially free pages 1140A may be any value that enables operation of partial database reorganization system 400 as described herein. Additionally, the ratio of data records to free space within reorganized pages 1138A may be any value that enables operation of partial database reorganization system 400 as described herein.

[0133] Similarly, the affected subrange 1112 of data pages 1106 is defragmented 1182 to generate a plurality of reorganized pages 1138B and a plurality of substantially free pages 1140B in accordance with the spatial parameters described herein. As shown, the number of reorganized pages 1138B and the number of substantially free pages 1140B have been increased from the reorganized pages 1138A and the substantially free pages 1140A. Also, the affected subrange 1114 of data pages 1106 is defragmented 1184 to generate a plurality of reorganized pages 1138C and a plurality of substantially free pages 1140C in accordance with the spatial parameters described herein. As shown, the number of reorganized pages 1138C and the number of substantially free pages 1140C have been increased from the reorganized pages 1138B and the substantially free pages 1140B. Thus, operation through the affected tablespace 1104 is substantially maintained while defragmentation and reorganization of data pages 1106 is repeated for each of the specified subranges until all data pages 1106 in the tablespace 1104 have been reorganized or substantially freed.

[0134] Referring to FIG. 12, a schematic diagram illustrating a process 1200 for converting a non-partitioned tablespace to a partitioned tablespace is shown. In at least some embodiments, it may be more practical to partition data records upon completion of the reorganization described with reference to FIGS. 7, 8, 9, and 10-11. Database 1202 includes tablespace 1204 and index 1208. In some embodiments, tablespace 1204 is populated with range 1206 of data pages, with 14 rows of data pages illustrated within range 1206, although 14 is not a limiting number. Each row may be identified as belonging to a specified subrange, similar to the subranges described with reference to FIGS. 10-11. However, here, the rows of the data pages are not necessarily organized into sequential or contiguous rows.

[0135] In one embodiment, a first subrange 1210 is identified and its associated data is transferred to a first partition 1214, leaving a blank portion 1212 of tablespace 1204. Similarly, a second subrange 1220 is identified and its associated data is transferred to a second partition 1224, leaving a blank portion 1222 of tablespace 1204. A third subrange 1230 is identified and its associated data is transferred to a third partition 1234, leaving a blank portion 1232 of tablespace 1204. A fourth subrange 1240 is identified and its associated data is transferred to a fourth partition 1244, leaving only a blank portion of tablespace 1204. Thus, the conversion may be performed to partition non-partitioned tables without taking the associated database systems offline, thereby maintaining operation with the affected tablespaces.

[0136] The systems, computer program products, and methods disclosed herein facilitate overcoming the disadvantages and limitations of offline database reorganization. For large databases, periodic data reorganization of performance-sensitive workloads can be performed only on the portions of the database requiring such reorganization, while the remainder of the database remains operational, significantly reducing the time required to take the database out of service. Furthermore, because the target portions are not taken offline, the duration during which smaller portions of the database not undergoing reorganization are unavailable is significantly reduced, minimizing operational interruptions. Furthermore, by significantly reducing operational interruptions, partial reorganizations may be performed more frequently, further enhancing performance of performance-sensitive workloads. Furthermore, the methods and systems described herein may improve overall data management in finite physical storage space by maintaining more substantially free data pages and free data space on pages with data residing therein. The methods and systems described herein also enhance the self-management of each database system, including indexing capabilities.

[0137] Thus, the embodiments disclosed herein provide an improvement to computer technology by providing a mechanism for reorganizing large databases through small, partial reorganizations of individual portions of the database while keeping the database system up and running. Such a mechanism reduces potential interruptions in access to databases that may require around-the-clock operation.

[0138] The description of various embodiments of the present disclosure has been provided for illustrative purposes and is not intended to be exhaustive or to be limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terms used herein have been selected to best explain the principles of the embodiments, practical applications of or technical improvements to the technology found in the market, or to enable others skilled in the art to understand the embodiments disclosed herein.

Claims

1. 1. A computer system comprising one or more processing devices and at least one memory device operatively coupled to the one or more processing devices, wherein the one or more processing devices: collecting performance information for one or more performance-sensitive workloads associated with one or more database portions; and and analyzing the collected performance information, the analysis comprising: determining, for each of the one or more performance-sensitive workloads, an indicator of performance degradation of the workload; receiving the determined performance degradation, determining at least one of the one or more database portions that is affected by the determined performance degradation; receiving the database portion determination, classifying the determined performance degradation; and performing one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation; The one or more processing devices further include:

1. A computer system configured to convert a non-partitioned tablespace to a partitioned tablespace, wherein the non-partitioned tablespace remains operational during the conversion.

2. A computer system comprising one or more processing devices and at least one memory device operably coupled to said one or more processing devices, wherein said one or more processing devices: collecting performance information for one or more performance-sensitive workloads associated with one or more database portions, at least some of the performance information being associated with a classification of performance degradation; and performing one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation; copying the first plurality of records to define a second plurality of records; and updating the first plurality of records and the second plurality of records in parallel to maintain synchronization of data therein; maintaining all online transactions without interruption during all parts of said one or more partial reorganizations; A computer system configured to:

3. 3. The computer system of claim 1, wherein the one or more processing devices are further configured to determine one or more characteristics of the one or more database portions that are subject to the partial reorganization.

4. The one or more processing devices may further comprise: determining that one or more clustering ratio measurements within the collected performance information approach or pass a clustering ratio threshold; determining that one or more hotspot measurements within the collected performance information approach or pass a hotspot threshold; determining that one or more overflow rate measurements in the collected performance information approach or exceed an overflow rate threshold; and 4. The computer system of claim 3, configured to determine that one or more space utilization measurements in the collected performance information approach or exceed a space utilization threshold.

5. The one or more processing devices further include: determining one or more of the affected record ranges; and The computer system of claim 4 configured to determine one or more of the affected page ranges.

6. The one or more processing devices further include:

6. The computer system of claim 1, configured to perform one or more online transactions on at least some of the affected data while at least a portion of the one or more partial reorganizations is being performed.

7. The one or more processing devices further include:

7. A computer system according to claim 1, configured to defragment and reorganize a tablespace while the tablespace remains in operation.

8. On the computer, collecting performance information for one or more performance-sensitive workloads associated with one or more database portions; a step of analyzing the collected performance information, the step of analyzing comprising: determining, for each of the one or more performance-sensitive workloads, an indicator of performance degradation of the workload; receiving the determined performance degradation, determining at least one of the one or more database portions that is affected by the determined performance degradation; categorizing the determined performance degradation in response to the database portion determination; and performing one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation; The computer further comprises:

1. A computer program for causing a computer to perform a procedure for converting a non-partitioned tablespace into a partitioned tablespace, the computer program comprising: a ...

9. A computer comprising: collecting performance information for one or more performance-sensitive workloads associated with one or more database portions, at least some of the performance information being associated with a classification of performance degradation; and performing one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation, copying the first plurality of records to define a second plurality of records; and updating the first plurality of records and the second plurality of records in parallel to maintain synchronization of the data therein; maintaining all online transactions without interruption during all parts of said one or more partial reorganizations. A computer program for executing

10. 10. A computer program product according to claim 8 or 9, further causing the computer to perform the step of determining one or more characteristics of the one or more database portions that are subject to the partial reorganization.

11. The computer, determining that one or more clustering ratio measurements in the collected performance information approach or pass a clustering ratio threshold; determining that one or more hotspot measurements in the collected performance information approach or pass a hotspot threshold; determining that one or more overflow rate measurements in the collected performance information approach or exceed an overflow rate threshold; 11. The computer program product of claim 10, further comprising: determining that one or more space utilization measurements in the collected performance information approach or exceed a space utilization threshold.

12. The computer further comprises:

12. A computer program product as claimed in any one of claims 8 to 11, for causing the computer to execute a procedure for performing one or more online transactions on at least some of the affected data while at least some of the one or more partial reorganizations are taking place.

13. The computer further comprises:

13. A computer program product as claimed in any one of claims 8 to 12, for causing a procedure to be carried out for defragmenting and reorganising a tablespace whilst the tablespace remains in operation.

14. 1. A computer-implemented method comprising: collecting performance information for one or more performance-sensitive workloads associated with one or more database portions; and analyzing the collected performance information; The analyzing step includes: determining, for each of the one or more performance-sensitive workloads, an indicator of performance degradation for the workload; In response to the determined performance degradation, determining at least one of the one or more database portions that is affected by the determined performance degradation; receiving the database portion determination, classifying the determined performance degradation; performing one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation; performing the one or more partial reorganizations of the one or more database portions, 1. A computer-implemented method comprising: converting a non-partitioned tablespace to a partitioned tablespace, wherein the non-partitioned tablespace is in operation during the conversion.

15. A method for managing a database, comprising: collecting performance information for one or more performance-sensitive workloads associated with one or more database portions, at least some of the performance information being associated with a performance degradation classification; and performing one or more partial reorganizations of the one or more database portions in response to the classification of the performance degradation; copying the first plurality of records to define a second plurality of records; and updating the first plurality of records and the second plurality of records in parallel to maintain synchronization of the data therein; maintaining all online transactions without interruption during all parts of said one or more partial reorganizations.

1. A computer-implemented method comprising:

16. The step of classifying the determined performance degradation comprises:

16. A computer-implemented method according to claim 14 or 15, comprising determining one or more characteristics of the one or more database portions to be subjected to the partial reorganization.

17. determining the one or more characteristics determining that one or more clustering ratio measurements in the collected performance information approach or pass a clustering ratio threshold; determining that one or more hotspot measurements within the collected performance information approach or pass a hotspot threshold; determining that one or more overflow rate measurements in the collected performance information approach or exceed an overflow rate threshold; and 17. The computer-implemented method of claim 16, further comprising one or more of determining that one or more space utilization measurements in the collected performance information approach or exceed a space utilization threshold.

18. determining the one or more characteristics determining one or more of the affected record ranges; and 20. The computer-implemented method of claim 17, comprising one or more of the steps of determining one or more of the affected page ranges.

19. performing said one or more partial reorganizations of one or more database portions, 19. The computer-implemented method of claim 14, further comprising performing one or more online transactions on at least some of the affected data while at least a portion of the one or more partial reorganizations is occurring.

20. performing the one or more partial reorganizations of the one or more database portions, 20. The computer-implemented method of any one of claims 14 to 19, comprising defragmenting and reorganizing a tablespace while the tablespace is in operation.

Images