Browse Prior Art Database

Wasted resources driven database job scheduler

IP.com Disclosure Number: IPCOM000246529D
Publication Date: 2016-Jun-15
Document File: 3 page(s) / 40K

Publishing Venue

The IP.com Prior Art Database

Abstract

The most common approach used in database engines is privileges driven task scheduler. The main idea behind this scheduler is to optimize time execution of the tasks taking into account privileges and costs. Invariably the subject of the optimization is single SQL command. Such approach works perfectly in case when single SQL are atomic operations. The disadvantage of the solution can be observed when user operates on the jobs consisted with few sequential SQL commands treated as one atomic piece (logically by application). In some circumstances it possibly leads to abnormal usage of the critical resources (as memory and disk).

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 53% of the total text.

Page 01 of 3

Wasted resources driven database job scheduler

Example:

Job A among others SQL statements has at least one locking some objects: […]
create temp table TempA1 as select … ;
create temp table TempA2 as select … ;
[...]
update T2 set T2.a = 'some value' ...
[…]
commit;

And job B got among others SQL statements has following:

[…]
create temp table TempB1 as select … ; create temp table TempB2 as select … ; […]
delete from T2 ...
[…]

In case we start parallel both jobs A and B we can simply stuck in lock state when in the same time we are updating T2 and trying to delete. Assuming that B is waiting for lock because of update from A we needlessly consume memory (here temporary disk space for TempB1, TempB2...) needed for storing all objects created by B before delete. Similarly if A is waiting for lock due to B processing (disk space for TempA1, TempA2 …) . If the objects are enough huge it can lead system to out of memory issue. The aim of our idea is to cut down risk of such situation.

The main idea here is to create task scheduler dedicated for jobs (consisted of several SQL statements) and streamlining performance by minimizing wasted resources.

The whole idea is based on four cooperating concepts:

1) job identification

2) wasted resource histogram for job

3) jobs histograms table

4) optimization rule

Details:

   1) job identification:
- each external job (e.g. BI tool job) is identified by tag fields:

- unique job name

- compilation time-stamp
- each internal job (plsql procedure) is identified by meta data obtained from catalog

- procedure name (DB/schema/name)

- compilation time...