Supporting Time-Constrained SQL Queries in Oracle Ying Hu, Seema - - PowerPoint PPT Presentation

▶
supporting time constrained sql queries in oracle
SMART_READER_LITE
LIVE PREVIEW

Supporting Time-Constrained SQL Queries in Oracle Ying Hu, Seema - - PowerPoint PPT Presentation

Nov 01, 2023 384 likes •794 views

<Insert Picture Here> Supporting Time-Constrained SQL Queries in Oracle Ying Hu, Seema Sundara, Jagannathan Srinivasan Oracle New England Development Center One Oracle Drive, Nashua, NH 03062 Talk Outline The Problem and Our Approach

slide-1
SLIDE 1

<Insert Picture Here>

Supporting Time-Constrained SQL Queries in Oracle

Ying Hu, Seema Sundara, Jagannathan Srinivasan

Oracle New England Development Center One Oracle Drive, Nashua, NH 03062

slide-2
SLIDE 2

VLDB 2007

2

Talk Outline

  • The Problem and Our Approach
  • Time-constrained SQL Queries
  • Supporting Time-constrained SQL Queries
  • Performance Study
  • Conclusions
slide-3
SLIDE 3

VLDB 2007

3

<Insert Picture Here>

The Problem and Our Approach

slide-4
SLIDE 4

VLDB 2007

4

The Problem

  • Databases are growing
  • Giga Bytes Tera Bytes Peta Bytes
  • Arbitrarily complex queries
  • Using SQL (JOINs, GROUP BY, ORDER BY, etc.)

Resulting in Long running SQL Queries Unpredictable Query Response Time

slide-5
SLIDE 5

VLDB 2007

5

The Problem

  • Thus, the current scheme of issuing a SQL query and

letting it take whatever time (and resources) to complete is unsatisfactory especially when the user is constrained by time.

TIME IS MONEY

slide-6
SLIDE 6

VLDB 2007

6

Prior Approaches for Time Constraints

  • Return first few (or top-k) rows

[SIGMOD 1997] M. Carey, D. Kossmann: On saying “enough already!” in SQL.

  • Augment the query with a range predicate

[VLDB 1999] S. Chaudhuri, L. Gravano: Evaluating Top-k Selection Queries. [VLDB 1999] D. Donjerkovic, R. Ramakrishnan: Probabilistic Optimization of Top N Queries.

  • For joins, generate results ordered on a rank function

[VLDB J. 2004] I. F. Ilyas, W. G. Aref, A. K. Elmagarmid: Supporting Top-k Join Queries in Relational Databases.

  • In Oracle,
  • ROWNUM clause to express top-k queries
  • The hint /*+ FIRST_ROWS */ to indicate that query be
  • ptimized for first few rows
slide-7
SLIDE 7

VLDB 2007

7

Prior Approaches for Time Constraints

  • Compute Approximate Results
  • return approximate results by use of sampling,

histograms etc.

  • employed for online aggregation, includes estimating

errors in reported results (e.g. confidence intervals)

[SIGMOD 1997] J. M. Hellerstein, P. J. Haas, H. J. Wang: Online Aggregation. [DMKD 2000] J. M. Hellerstein, R. Avnur, V. Raman: Informix under CONTROL: Online Query Processing.

  • In Oracle, SAMPLE clause to indicate only portion of a table be

used

slide-8
SLIDE 8

VLDB 2007

8

The Problem Remains

  • The onus is on user to employ these

approaches intelligently!

Not easy to translate a time constraint to equivalent

  • a first-few (top-k) rows query
  • r
  • an approximate query
slide-9
SLIDE 9

VLDB 2007

9

Our Approach

  • Introduce a time-constraint clause to SQL

SELECT Query that specifies

  • Type of constraint: Soft or Hard
  • Time limit: in seconds
  • Acceptable Nature of results: partial or approximate
  • Let the Database System do the needed

transformation to execute the query in specified time limit

slide-10
SLIDE 10

VLDB 2007

10

Our Approach

  • The transformed query returns either
  • first-few (top-k) rows, or
  • approximate results
  • Both of which are expected (guaranteed) to

complete in the specified time limit for soft (hard) time constraint

slide-11
SLIDE 11

VLDB 2007

11

<Insert Picture Here>

Time-constrained SQL Queries

slide-12
SLIDE 12

VLDB 2007

12

A New Time Constraint Clause

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … [ [ SOFT |HARD] TIME CONSTRAINT (T) [WITH { APPROXIMATE | PARTIAL} RESULT ] ];

slide-13
SLIDE 13

VLDB 2007

13

An Example

  • A time constrained SQL query

SELECT AVG(salary) FROM employees SOFT TIME CONSTRAINT (50) WITH APPROXIMATE RESULT;

  • Query after rewrite may be transformed into

SELECT AVG(salary) FROM employees SAMPLE BLOCK (10);

slide-14
SLIDE 14

VLDB 2007

14

Soft Time Constraint Definition

Definition: A query Q with a soft time constraint of t sec ⇒ , where d is a small time unit and Q’ is the transformed query

slide-15
SLIDE 15

VLDB 2007

15

Hard Time Constraint Definition

Definition: A query Q with a hard time constraint of t sec ⇒ , where Q’ is the transformed query

slide-16
SLIDE 16

VLDB 2007

16

Functions for Estimating Aggregates and Corresponding Confidence Interval Values

  • For queries returning approximate results
  • Provide functions for estimating aggregates over the entire

table

  • estimatedSum, estimatedCount, estimatedAvg
  • Provide ancillary functions to return the confidence interval

associated with each aggregate function

  • sumConfidence, countConfidence, avgConfidence

The confidence interval functions are based on Central Limit Theorem or Hoeffding’s inequality [SSDBM 1997] P. J. Haas: Large-Sample and Deterministic Confidence Intervals for Online Aggregation

slide-17
SLIDE 17

VLDB 2007

17

Functions for Estimating Aggregates and Corresponding Confidence Interval Values

Example SELECT COUNT(*) SAMPLECOUNT, estimatedCount(*) ESTIMATEDCOUNT, countConfidence(*, 95) COUNTCONFIDENCE FROM employees SOFT TIME CONSTRAINT(5) WITH APPROXIMATE RESULTS; Result

SAMPLECOUNT ESTIMATEDCOUNT COUNTCONFIDENCE 207000 1200900 14000

slide-18
SLIDE 18

VLDB 2007

18

<Insert Picture Here>

Supporting Time Constrained SQL Queries

slide-19
SLIDE 19

VLDB 2007

19

Scheme for Supporting Soft-Time Constraint Queries

  • Basic Idea:
  • Transform the input query by augmenting either with
  • ROWNUM clause that reduces the result set size, OR
  • SAMPLE clause that reduces the data blocks scanned OR

the intermediate result size returned from the referenced table(s)

  • The resulting query is executed, which is expected to finish

sooner

  • The challenge:
  • If ROWNUM clause used - estimating result set cardinality
  • If SAMPLE clause used - estimating table sample size, as

well as deciding the list of tables for which sampling should be done (in case of multi-table queries)

  • Ensuring that the estimated time for resulting query satisfies

the time-constraint

slide-20
SLIDE 20

VLDB 2007

20

Query Transformation: Sampling Referenced Tables

IF original query is SELECT … FROM T WHERE … THEN the transformed query becomes SELECT … FROM T SAMPLE BLOCK(n) WHERE …

slide-21
SLIDE 21

VLDB 2007

21

Estimating Sample Size

  • The function fQ, which represents time to execute

query Q depends on sample size s.

  • Thus, fQ(s) = t, where t is the specified time-

constraint.

  • The desired s is a root of equation

fQ(s) – t = 0 and is obtained using a root finding algorithm

  • Note: Oracle’s cost-based optimizer’ EXPLAIN PLAN

facility is used to estimate fQ(s) for a given s.

slide-22
SLIDE 22

VLDB 2007

22

Estimating Sample Size: Details

  • 1. Obtain estimated query time (by consulting
  • ptimizer) say TQ for original query Q
  • 2. If TQ < t then STOP. No transformation needed
  • 3. If TQ > t then obtain estimated query time TQ’ , where

Q’ is augmented query with minimum sample size

  • 4. If TQ’ > t then return ‘ERROR: NEED MORE TIME’.
  • 5. Iterate (using root-finding algorithm)

till the estimated time is BETWEEN t-d AND t

  • 5. Return the current s
slide-23
SLIDE 23

VLDB 2007

23

Sampling Based Query Transformation for Multi-table Joins w/ Foreign Keys

  • For table joined via foreign key add sampling clause
  • nly to the largest fact table (Aqua System from Bell Labs)
  • It is because a uniform random sample over foreign-

key joins of tables can be achieved through a uniform random sampling over the largest fact table and then joining with other dimension tables

[SIGMOD 1999] S. Acharya, et al : Join Synopses for Approximate Query Answering.

slide-24
SLIDE 24

VLDB 2007

24

Sampling Based Query Transformation for Multi-table Joins w/o Foreign Keys

  • The goal is to have as many resulting rows as

possible, or have as many rows as possible in the resultant joins for aggregate queries

  • Thus, maximize f1 * f2, where f1 and f2 are the sample

sizes for the two tables

  • Case 1: Nested Loop Join:

It can be proved that the sample clause should be put into only the outermost relation, i.e. f2 = 1, no sampling over the inner relations

slide-25
SLIDE 25

VLDB 2007

25

Sampling Based Query Transformation for Multi-table Joins w/o Foreign Keys

  • Case 2: Hash Join:
  • Compute sampling size f1 and f2 such that

f1*T1 = f2*T2 , where T1 and T2 are times used to process the two tables being joined because this will maximize f1*f2

  • Case 3: Sort-Merge Join:
  • Since sort has a time complexity of O(nlogn), there is no easy

solution for sort-merge join. We adapt the above technique of making f1*T1 = f2*T2

slide-26
SLIDE 26

VLDB 2007

26

Sub-query Processing

  • SELECT *

FROM employees outer WHERE outer.salary > (SELECT AVG(inner.salary) FROM tax_return inner) SOFT TIME CONSTRAINT (10);

  • Try not to push sample clause into the sub-query,

because it can cause an approximate predicate

  • Otherwise, the time allocated to each stage is

determined through linear interpolation

slide-27
SLIDE 27

VLDB 2007

27

Scheme for Supporting Hard-Time Constraint Queries

  • Basic Idea:
  • Transform the input query by treating the specified time limit as

soft-constraint

  • The estimated time for the transformed query meets the

specified time limit

  • Generate execution plan and use the estimated time information

for various operations to associate timers as follows:

  • A timer for top-level operation with time set to specified time

limit

  • A timer for every blocking sub-operation with time set to

estimated time for corresponding operation in execution plan

slide-28
SLIDE 28

VLDB 2007

28

Scheme for Supporting Hard-Time Constraint Queries: Example

  • TPC-H Q14: Promotion effect query

SELECT 100.00 * sum (CASE WHEN p_type LIKE ‘PROMO%’ THEN l_extendedprice * (1 - l_discount) ELSE 0 END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue FROM lineitem, part WHERE l_partkey = p_partkey AND l_shipdate >= date ‘1995-09-01’ AND l_shipdate < date ‘1995-09-01’ + interval ‘1’ month HARD TIME CONSTRAINT (300);

slide-29
SLIDE 29

VLDB 2007

29

Scheme for Supporting Hard-Time Constraint Queries: Example

10 270 300 300 300

Estimated Time

PART TABLE ACCESS FULL 4 LINEITEM TABLE ACCESS FULL 3 HASH JOIN 2 SORT AGGREGATE 1 SELECT STATEMENT

… Name Operation Id

  • Timers
  • Id 0: Top level time set to 300 seconds
  • Id 1: Blocking sub-operation timer set to 300 seconds
  • Id 3: Blocking sub-operation timer set to 270 seconds
slide-30
SLIDE 30

VLDB 2007

30

Leveraging Oracle’s Cost-based Optimizer

  • Object Statistics:
  • Number of blocks, number of rows for tables
  • Height of a B-tree indexes, etc.
  • System Statistics:
  • Average number of CPU cycles/sec
  • Average time to read a single block (random read)
  • Average time to read multi-blocks (sequential read), etc.
  • EXPLAIN PLAN
  • Utilizes Statistics collected to calculate CPU and I/O costs for

each access method in a SQL query

  • It returns optimal execution plan as well as estimated time for

query execution

slide-31
SLIDE 31

VLDB 2007

31

<Insert Picture Here>

Performance Study

slide-32
SLIDE 32

VLDB 2007

32

Experiments: TPC-H

  • Platform:
  • Intel P4 3.0Ghz with Hyper-Threading,

2GB main memory, and 80GB hard disk

  • Redhat Enterprise Linux 3 and Oracle Database 10g Release

2 Enterprise Edition

  • Key Database Parameters:
  • db_block_size=8192, db_cache_size=160M
  • Data Set
  • TPC-H database size of about 10GB, consisting of 8 tables
  • LINEITEM table is the biggest and has ~60 million rows
  • ORDERS table is the second largest with 15 million rows
slide-33
SLIDE 33

VLDB 2007

33

Single Table Query with Aggregates

  • TPC-H Q6: This query considers all the line items

shipped in a given year with discounts between a ± 0.01 of DISCOUNT=0.06

SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= date ‘1994-01-01’ AND l_shipdate < date ‘1994-01-01’ + interval ‘1’ year AND l_discount between 0.06 - 0.01 and 0.06 + 0.01 AND l_quantity < 24;

slide-34
SLIDE 34

VLDB 2007

34

Single Table Query with Aggregates

  • Time constraints chosen: 10%, 20%, ... of original query estimated time
  • Transformed query uses sampling
  • Elapsed time > Estimated Time (due to less than expected use of multi-

block I/O)

  • Time does decrease as the user specifies smaller time-constraints

TPC-H Q6

244 55 133 178 196 228 269 27 54 81 108 135 50 100 150 200 250 300

q6 q6- 10% q6- 20% q6- 30% q6- 40% q6- 50% Time (in seconds) exec. est.

slide-35
SLIDE 35

VLDB 2007

35

Sum, Estimated Sum, & Confidence Interval

N/A N/A 1230113636 100% 21449857 1229137335 617119157 50% 24081572 1228986671 489547623 40% 27692357 1228624043 370097887 30% 34194884 1230244879 243045023 20% 49916216 1228484983 113894821 10% sumConfidence (confidence interval) estimatedSum SUM % of time 95% Confidence Interval computing using Hoeffding-based bounds

slide-36
SLIDE 36

VLDB 2007

36

Four Table Join Query with GROUP BY and ORDER BY

  • TPC-H Q10: Returned Item Reporting Query

SELECT c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment FROM customer, orders, lineitem, nation WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND

  • _orderdate >= date ‘1993-10-1’ AND
  • _orderdate < date ‘1993-10-1’ + interval ‘3’ month AND

l_returnflag = ‘R’ AND c_nationkey = n_nationkey GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ORDER By revenue DESC;

slide-37
SLIDE 37

VLDB 2007

37

Four Table Join Query with GROUP BY and ORDER BY

  • Only Lineitem table sampled as it is the largest table and has a foreign

key reference to O_ORDERKEY

  • Time constraint clause is effective in reducing the execution time

TPC-H Q10

636 77 86 97 143 300 699 71 140 210 280 347 100 200 300 400 500 600 700 800

q10 q10- 10% q10- 20% q10- 30% q10- 40% q10- 50% Time (in seconds) exec. est.

slide-38
SLIDE 38

VLDB 2007

38

Four Table Join Query with GROUP BY and ORDER BY

  • The estimated sample size to meet the time constraint for various

time-constraint queries

  • In this case the maximum number of iterations required to

estimate the sample size is 10. However, the total overhead for estimating sample size is quite small (< 0.5 sec)

0.06895 0.9396 22.03549 7.47554 2.11497 5 10 15 20 25

q10-10% q10-20% q10-30% q10-40% q10-50% Tim e Constraint Sample Size

slide-39
SLIDE 39

VLDB 2007

39

Conclusions and Future Work

  • Time-constrained SQL queries must be supported in database
  • systems. It can leverage work in the following areas:
  • top-k query optimization, approximate query processing, and error

estimation

  • plus, the capabilities of cost-based optimizer, namely, the optimal plan

generation, and accurate estimation of the query execution time

  • Both support for soft and hard time-constraint were considered
  • The experimental study conducted (on a prototype implementation

using Oracle) with the TPC-H dataset demonstrates the effectiveness of time-constrained SQL queries

  • In future, we plan to explore
  • tighter integration of the proposed techniques
  • the feasibility and effectiveness of supporting hard time constraints
slide-40
SLIDE 40

VLDB 2007

40

Q U E S T I O N S Q U E S T I O N S A N S W E R S A N S W E R S