DB-04 Tuning OpenEdge

1
DB-04Tuning OpenEdge SQL Boosting Your SQL
Application Performance

• Steve Pittman
• Principle Software Engineer, SQL Team

2
Agenda

• OpenEdge SQL Server architecture
• Configuration and Security
• Applications and SQL
• SQL Execution thru the SQL engine
• Tuning and best practices
• Questions

3
Under Development

• This talk includes information about potential
  future products and/or product enhancements.
• What I am going to say reflects our current
  thinking, but the information contained herein is
  preliminary and subject to change. Any future
  products we ultimately deliver may be materially
  different from what is described here.

4
OpenEdge SQL System Architecture
SQL Servers
Shared Memory
SQL client
SQL client
SQL 4GL Broker
Database
4GL client
4GL client
4GL Servers
5
SQL Architecture
Components

• SQL engine
• Statement planning
• Statement execution - scan, join, sort, etc.
• Communications
• Database storage manager
• Persistent data storage and indexes
• Transactions
• Locking
• Etc.

6
Agenda

• OpenEdge SQL Server architecture
• Configuration and Security
• Applications and SQL
• SQL Execution thru the SQL engine
• Tuning and best practices

7
OpenEdge SQL Server Configuration
Tuning the server Threads vs. Processes

• Threads are better than processes
• Less resource consumption
• More sharing - pages, caches
• Better load balancing
• Faster execution
• Startup parameters
• -Mi - minimum threads (clients) per server
• -Ma - maximum threads (clients) per server
• -Mn - maximum server processes
• Default settings not the best for SQL!
• Better example -Mi 4 -Ma 8 -Mn 4

8
OpenEdge SQL - Startup Parameters
SQL Servers
Shared Memory
SQL client
SQL client
-Mi 5 5 threads min
-Ma 1010 threads max
4GL Servers
9
OpenEdge SQL Server Configuration
Separating 4GL and SQL brokers/servers examples

• Example Start a 4GL Primary broker
• Example Start a Secondary SQL broker

proserve Sports2000 -S 6000 -H localhost -n 48
-Mn 8 -Mpb 4 -ServerType 4GL -minport 6100
-maxport 6300 -Mi 1 -Ma 5
proserve Sports2000 -S 5000 -H localhost -m3
Mpb 3 -ServerType SQL -minport 5100 -maxport
5300 -Mi 4 -Ma 8
10
OpenEdge SQL - Configured
SQL Servers
SQL Broker
Shared Memory
SQL client
SQL client
Database
4GL client
4GL client
4GL Servers
4GL Broker
11
OpenEdge SQL Security Model
Making your data safe and accessible

• Authentication
• Who am I?

• aaa
• jones
• smith

12
OpenEdge SQL Security Model
Enabling safe data access

• Authorization
• What can I do?
• Closed model (SQL) vs. open model (4GL)
• Every action possible must be authorized
• Privileges
• DBA - can do everything
• Table (also column) privileges
• Sequence, stored procedure privileges
• Common error
• Access Denied (Authorization failed) (7512)

13
OpenEdge SQL Security Model
Best practices

• Require user authentication
• Define two, limited-use DBA users
• Do not use predefined system DBA userid, please!!
• Grant table privileges to all or selected users
• GRANT is online

14
Agenda

• OpenEdge SQL Server architecture
• Configuration and Security
• Applications and SQL
• SQL Execution thru the SQL engine
• Tuning and best practices

15
Applications and OpenEdge SQL
A look at the relationship

• Representative applications
• Crystal Reports
• Java, WebSphere, and JDBC
• Delphi, ODBC, Web server

16
Statement-oriented
Select SQL statements

• Prepare, execute, fetch cycle
• Result set
• ODBC fetch array size
• Statement may not be visible (Crystal, etc.)

Select onum, name, cnum from pub.customer c,
pub.orders owhere c.custnum o.custnum
SQL Server
Database
SQL client
8765 General Motors 1235143 Toyota Mfg,
Inc. 4228123 Chrysler Motors 274
17
Statement-oriented
Update sql statements

• Prepare, execute cycle
• Simple execute message exchange
• No table data flow between client and server

Update pub.ordersset delivery_date
06-30-2005where delivery_date 06-15-2005
SQL Server
Database
SQL client
2 rows updated
18
Statement-oriented
Patterns of statements

• Similar statement are executed by common prepared
  statement
• Select from sales where date 06/15/05
• Select from sales where date 01/08/04
• Automatic optimization for statements on a
  connection

Select from pub.sales where date 06/15/05
Select from pub.sales wheredate 01/08/04
SQL Server
Database
SQL client
8765 General Motors 1235143 Toyota Mfg,
Inc. 4228123 Chrysler Motors 274
3205 Laval Motors 689
19
Transactions
Isolation levels

• Read Committed - default
• Repeatable Read
• Change via ODBC/JDBC API
• ODBC DSN Advanced option

SQL Server
SQL client 1
Database
4GL client 2
20
Schemas
Sets of tables with common owner

• PUB schema
• Schema for inter-operability with 4GL
• Default schema
• Changing the default
• Set schema pub

SQL Server
PUB
SMITH
SQL client
JONES
21
Agenda

• OpenEdge SQL Server architecture
• Configuration and Security
• Applications and SQL
• SQL Execution thru the SQL engine
• Tuning and best practices

22
Executing SQL Statements
What the server does

• Build query plan for SQL statement
• Execute query plan to build result set
• Stream result set back to client
• How to choose best query plan?

OpenEdge SQL Server
Query Plan
Select from T1,T2 where T1.fT2.g

• Join
• Table Scan T1
• Index Scan T2

Database
23
Whats in a Query Plan
Building blocks for execution

• Query plan elements
• Table scan
• Index scan
• Join
• Restrict
• Project
• Sort
• Organization and form
• Whats useful to know

24
Building the Query Plan
schema
sql statistics
sql statement
SQL Optimizer
U s e r
SQL Runtime
Query plan
25
Cost-based Optimization
What does this mean?

• Optimization model
• Figure out all feasible ways to do a step
• Figure out the costs of each way
• Choose way with smallest cost
• Optimize from the inside out
• Optimize table access
• Optimize joins
• Optimize result set
• What cost is

26
How the Optimizer Knows Cost
Rule-based mode

• When no statistics exist
• Table and index metadata
• number of key components used
• unique and non-unique indexes
• Default column selectivity per operator
• is .04, between is .1, etc.
• Heuristics
• Assume all tables have n K rows
• Cost
• cardinality selectivity row-cost k

27
How Optimizer Knows Cost
Statistics-based mode - basic statistics

• Table statistics - cardinality
• Column statistics - data distribution
• Individual column selectivity per operator
• of tables data returned by predicate
• Combining multiple columns selectivities
• Best for range operators(between, etc.),
  especially in Version 9
• Cost
• cardinality selectivity row-cost k

28
How Optimizer Knows Cost
Default statistics

• select
• from pub.customer c, pub.order o
• where c.custnum o.custnum
• and o.orderdata between 05/01/05
• and 05/30/05

What percentage of data - column statistics
How many rows - table statistics
29
More on Column Statistics

• Based on sampling tables data
• Histogram derived from sampled data
• OpenEdge 10 vs. Version 9
• New estimated number of distinct values per
  histogram bucket - an explanation
• New extensibility in statistics format in schema

110 230 340 424 545 676 757 868 990 1104
Example - Histogram of 2000 random integers
1..1150
95 101 92 81 108 94 102 100 96 104
Example - number distinct values for Histogram
30
How the Optimizer Knows Cost
Statistics-based mode - index statistics

• Counts number of values for components of index
• Prefixes of an index key - leading sequence of
  key components
• Gives most precise estimate of number of rows
  satisfying and in operators
• Accounts for correlation between components of an
  index key
• Can accurately model very, very low selectivity
  and very high selectivity

31
How Optimizer Knows Cost
Index statistics

• select
• from pub.customer c, pub.order o
• where c.custnum o.custnum
• and o.orderdata between 05/01/05
• and 05/30/05

How many matching rows - index statistics
32
More on Index Statistics

• Count of number of unique values for each prefix
• OpenEdge 10 vs. Version 9
• OE 10 counts for all prefixes
• Prefix - key components 1 to n
• V9 counts for first key component, and last 3
  prefixes
• V9 interpolation for prefixes without counts
• Estimate via straight line between first 2
  counts

33
Example - OpenEdge 10 Index Statistics

• Sample single table query

select from Sales_History where terr_id
abc and subt_id 1 and yr
2004 and zip 05601 and demo_cat
xyz and cust_stat M and regn
NE and countycd 5
34
Example - OpenEdge 10 Index Statistics
Indexes and key components
Xsales_terr
index statistics
300K
90
1M
800K
20K
10K
Xconsumer_id
Assume cardinality (total number of rows)
1,000,000
Xmarket_seg
35
Example - Cost via Index Statistics
Index comps count
Xconsumer_id 1 90
2 10K
3 20K
4 300K
5 800K
6 1M
index statistics
Cost (1M/ 800K )rows IO cost per row
select from Sales_History where terr_id
abc and subt_id 1 and zip
05601 and demo_cat xyz and cust_stat
M
36
Optimizing Join Execution
Or, What gets optimized

• Join order
• Join methods
• index join ( augmented nested loop)
• nested loop
• dynamic index (looks like index join)
• Hash join when low data volume
• Index join when larger data volume

37
What Optimizer Does for Join Order

• Consider many possible join orders
• choose least cost order
• Use join cardinality as cost metric
• Joining small amount of data to larger amount of
  data is usually least cost
• Cost estimation drivers
• Table statistics
• Index statistics
• OpenEdge 10 and V9.1E employ much more powerful
  join order exploration

38
Planning Join Method - Index Join
Or, augmented nested loop
Joined data
Select From Sales s, SalesHist h Where
s.city MyTown And s.acct h.acct
Join
39
Example - OpenEdge10 Index Statistics
Indexes and key components
index statistics
Xsales_terr
select from Sales s, Sales_History
h where s.terr h.F1 and s.acct h.F2 and
s.city h.F3 and s.col01 h.F4 and s.col02
h.F5 and s.regn h.F10 and s.segid h.F11
100K
90
1M
800K
10k
20k
Xconsumer_id
Xmarket_seg
40
Agenda

• OpenEdge SQL Server architecture
• Configuration and Security
• Applications and SQL
• SQL Execution thru the SQL engine
• Tuning and best practices

41
What to Tune

• Tune your SQL server
• SQL Statistics
• Releases
• Tune your SQL statements
• Possible problems
• Finding problems
• Special situations

OpenEdge SQL Server
SQL client
Database
Tune here
42
Tuning Your SQL Server

• Create, or update, sql statistics
• Move to newer release
• OpenEdge 10.0B is better than 10.0A
• OpenEdge 10.0A is better than Version 9.1E
• Version 9.1E is better than 9.1D
• Latest Service Pack
• OpenEdge 10 service packs
• Version 9.1 service packs
• Possibly consider adding indexes

43
Updating SQL Statistics

• Default statistics
• update statistics for lttable namegt
• Best statistics
• update table statistics and index statistics and
  column statistics for lttable namegt
• reads all of each index for all tables, or for
  one table.
• May be resource intensive
• Example - 4.5G customer db, 600 tables 4500
  indexes
• Index stats runtime 25 cpu minutes
• Index statistics drive best join optimizations
• Must be DBA
• When to do
• relationships between tables or indexes change

44
Tuning Your SQL Statements
Possible problems and remedies

• Join relationships not completely expressed in
  predicates
• Remedy - more, better join predicates on sql
  statements
• Every pair of tables with a relationship should
  have a predicate giving that relation
• select from pub.orders O, pub.orderlines L
  where O.onum L.onum

45
Tuning Your SQL Statements
Possible problems and remedies - more

• Leading keys of indexes not specified
• Remedy - give predicates on leading keys
• Predicates best for index use not used
• Remedy - best are , IN
• Almost best - BETWEEN
• Good - gt, gt, lt, lt
• Note - OR can disable optimizations
• Several similar indexes not distinguished as
  expected
• Remedy index statistics

46
Tuning Your SQL Statements
Possible problems and remedies - example
F3
F1
F1
F2
F4
Indexes and key components
Xsales_terr
select from Sales s, Sales_History
h where s.accno h.F2 and s.city
h.F3 and s.col01 h.F4 and s.col02
h.F5 and s.regn h.F10 and s.segid h.F11
F4
F1
F6
F5
F7
F1
F2
Xconsumer_id
s.terr h.F1
and
Xmarket_seg
47
Tuning Your SQL Statements
Finding problems

• Time do simple timing of data access requests
• Inspect SQL statement executed
• Investigate SQL virtual system table for query
  plan
• Access query plan for sql statement executed
• Query plan data will show
• tables
• indexes
• joins
• predicates
• order
• Note only your query plans available (currently)
• Must be DBA or have DBA grant privileges

48
Getting the Query Plan

• Basic form

select substring("_Description",1,80) from
pub."_Sql_Qplanwhere "_Pnumber" (select
max("_Pnumber") from pub."_Sql_Qplan" where
"_Ptype" gt 0 )

• Simplify with views

select from my_Qplan
49
Getting the Query Plan

• Get your SQL statement
• Crystal Reports
• Database menu
• Show SQL query
• Copy into a SQL query tool
• SQL Explorer, WinSQL, DB Visualizer
• Run your statement
• Run the SQL statement to get query plan

50
Query Plan - Operations
What you need to find

• Order of operations
• Top to bottom
• Tables
• Indexes
• Index keys
• Joins
• Join predicates

51
Query Plan - Operations
What you can skip

• Things to ignore
• Project, sort
• Sometimes useful to check out
• Restrict
• Dynamic index

52
Query Plan - What to Look for
Single-table query

• Example from simple single table select
• select from pub.customer where custnum between
  1000 and 1100
• Simple indentation to show tree form

SELECT COMMAND. PROJECT 66 ( PROJECT 64
( PUB.CUSTOMER. 0( INDEX
SCAN OF ( CustNum,
(PUB.CUSTOMER.CustNum) between
(1000,1100))
53
Query Plan - What to Look for
Two-table query

• Example with 1 join key

-- 1 key join select c.custnum, c.name,
o.ordernum, o.orderdate from pub.customer
c, pub.order o where custnum between 1000
and 1021 and c.custnum o.custnum
54
Query Plan - What to Look for

• Example with 1 join key

JOIN 13AUG_NESTED_LOOP-JOIN
PUB.O. 12( INDEX SCAN OF (
CustOrder,
(PUB.O.CustNum) between () (PEXPR3)
(PEXPR5) -- above defines ANL left
side keys ltrelopgt
right side keys. PUB.C. 11(
INDEX SCAN OF (
CustNum,
(PUB.C.CustNum) (null))
55
Query Plan - What to Look for
Two-table query

• Example with 2 join keys

-- 2 key join select o.ordernum, o.orderdate,
l.itemnum from pub.order o, pub.orderline
l where o.custnum between 1 and 3 and
o.ordernum l.ordernum and o.custnum
l.linenum
56
Query Plan - What to Look for

• Example with 2 join keys

JOIN 13AUG_NESTED_LOOP-JOIN PUB.O.
2( INDEX SCAN OF (
CustOrder,
(PUB.O.CustNum) between (1,3))
(PEXPR1, PEXPR3) (PEXPR5, PEXPR6) --
above defines ANL left side keys ltrelopgt
right side keys.
PUB.L. 3(
INDEX SCAN OF (
orderline,
(PUB.L.Ordernum, PUB.L.Linenum)
(null,null))
57
Tuning Your SQL Statements
Special situations

• Experiment with alternate queries without cost of
  query execution!
• Use NOEXECUTE to experiment
• Test, inspect query plan, repeat until done
• OpenEdge 10 and Progress Version 9
• NOEXECUTE is 1 word
• Example

select from Table1 t1, Table2 t2 where t1.key
5 and t1.key t2.key NOEXECUTE
58
Tuning Your SQL Statements
Special situations

• Forcing the join order
• NO REORDER phrase at end of FROM clause
• When all else fails!
• Example
• Index hints
• Use carefully - not deterministic

select from Table1, Table2, Table3 NO
REORDER where
59
Index Hint

• Syntax
• WITH ( INDEX ( ltindex namegt ) )
• Choose index if it is an eligible candidate

• select
• from PUB.ih_hist ih_hist, PUB.pt_mstr pt_mstr
  with (index(pt_part_type)), PUB.idh_hist
  idh_hist
• where idh_hist.idh_part pt_mstr.pt_part
• and ih_hist.ih_inv_nbr idh_hist.idh_inv_nbr
• and pt_mstr.pt_part_type FG
• and ih_hist.ih_inv_date lt 01/24/2005
• and ih_hist.ih_inv_date gt to_date(01/24/2005)
  

60
In Summary

• OpenEdge SQL Server configuration
• What the server does for your application
• Tuning to make the server do what you want

61
Related SQL Exchange Sessions

• DB-15 Developing Performance-Oriented ODBC/JDBC
  OpenEdge Applications
• Wednesday, 8 June, 215pm - 315pm
• DB-07 OpenEdge SQL and Sonic - Using JMS with
  SQL Applications
• Tuesday, 7 June, 915am - 1015am
• DB-09 Database Roadmap
• Tuesday, 7 June, 215pm - 315pm

62
Questions?
63
Thank you for your time!
64
(No Transcript)
65
Appendix - online resources

• White paper on query optimizer
• http//psdn.progress.com/library/whitepapers/sql92
  /docs/sql92_optimizer.pdf
• Collection of white papers
• Getting Started with Crystal
• ODBC, JDBC Configuration
• Locking
• Index statistics
• Server configuration
• Visit
• http//psdn.progress.com/library/white_papers/sql/
  index

66
Appendix - online resources

• Documentation
• http//www.progress.com/products/documentation/ind
  ex.ssp
• Knowledge base articles
• 19968, p7843 - secondary broker
• 20143 - authorization
• 21676, 20007, 20327 - query plan

67
OpenEdge SQL Server Configuration
Separating 4GL and SQL brokers/servers syntax

• 4GL Primary Broker
• -n and -Mn Primary Broker Specific
• SQL Secondary Broker

proserve dbname -S n -H x -n n -Mn n -Mpb n
-ServerType 4GL -minport n -maxport n -Mi n -Ma
n
proserve dbname -S n -H x -m3 Mpb n
-ServerType SQL -minport n -maxport n -Mi n
-Ma n
68
Appendix - setting default schema for ODBC

• In Windows registry, find entry for
• HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
• Find sub-entry for your ODBC Data Source Name
• \SOFTWARE\ODBC\ODBC.INI\ltDSN NAMEgt
• Create new string value for schema name
• Value name \ODBC\ODBC.INI\ltDSN
  NAMEgt\DefaultSchema
• Value data ltyour schema namegt
• Example

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PD6D1KP
M001 "Driver""c\\progress-91d\\bin\\PGPRO915.DL
L" "Description""" "HostName""cuttyhunk" "PortNu
mber""2525" "DatabaseName""test1" "LogonID""pda
dmin" "StaticCursorLongColBuffLen""4096" "Default
IsolationLevel""SQL_TXN_REPEATABLE_READ" "ArraySi
ze""50" "DefaultLongDataBuffLen""2048" "DefaultS
chema""PUB"
69
OpenEdge SQL Security Model
Commands

• GRANT statement
• GRANT is online
• Example
• grant select on pub.orders to jones
• grant all on pub.orders to public
• REVOKE statement
• REVOKE is offline
• Example
• revoke all on pub.orders from public

70
Security Considerations
Privileges Syntax GRANTing them (2 types)

• Database wide (system admin or general creation)
• For specified Tables or Views
• Where privilege is
• SELECT INSERT DELETE INDEX
• UPDATE ( column , column , ... )
  REFERENCES ( column , column , ... )

GRANT DBA, RESOURCE TO user_name , user_name
,
GRANT privilege , privilege , ALL ON
table_name TO user_name , user_name ,
PUBLIC WITH GRANT OPTION
71
Security Considerations
Privileges Syntax REVOKEing them (2 types)

• Database wide (system admin or general creation)
• For specified Tables or Views
• Where privilege is
• SELECT INSERT DELETE INDEX
• UPDATE ( column , column , ... )
  REFERENCES ( column , column , ... )

REVOKE DBA, RESOURCE FROM user_name ,
user_name ,
REVOKE GRANT OPTION FOR privilege ,
privilege , ALL PRIVILEGES ON
table_name FROM user_name , user_name ,
PUBLIC RESTRICT CASCADE
72
Join method - nested loop
Select From Order O, OrderLine L Where
O.ordnum 1004 And O.ordnum L.ordnum
Joined data
Join and eval
73
Join method - dynamic index
Or, index join
Joined data
Select From Order O, OrderLine L Where
O.city MyTown And O.shipper L.shipper
Join
Temp table with extracted, indexed OrderLine
data
74
Updating sql statistics
Specific categories of statistics

• Table statistics
• update table statistics for lttable namegt
• Column statistics
• update all column statistics for lttable
  namegt
• Index statistics
• update index statistics for lttable namegt
• reads all of each index for 1, or all, tables.

75
Appendix - query plan view 1
-- to show all of query plan for most recent
statement. create view qplan_full as
select from pub."_Sql_Qplan"
where "_Pnumber" (select max( "_Pnumber" )
from pub."_Sql_Qplan"
where "_Ptype" gt 0
) grant select on qplan_full to
public create public synonym qplan_full for
qplan_full commit work
76
Appendix - query plan view 2
-- try to show just the highlights of query plan,
omitting data specifics. create view
qplan_no_data as select from
pub."_Sql_Qplan" where "_Pnumber" (select
max( "_Pnumber" ) from
pub."_Sql_Qplan" where
"_Ptype" gt 0 ) and "_Description" not like
' , ' and "_Description" not like ',
PEXPR' and "_Description" not like ',
substr' and "_Description" not like '
)' and "_Description" not like ' )'
and "_Description" not like ' ,' and
"_Description" not like 'callback' and
"_Description" not like 'col id ' and
"_Description" not like '_at_' and
"_Description" not like 'terminate' and
("_Description" not like ' )' or
"_Description" like 'OJ Predicate' ) grant
select on qplan_no_data to public create
public synonym qplan_no_data for qplan_no_data
commit work
77
Appendix - query plan definition

• Table "_Sql_Qplan" exists as if it had been
  created by the sql syntax below
• The definition of this sql virtual system table
  is not visible to client tools such as Crystal
  Reports.

create table "_Sql_Qplan" (
"_Pnumber" integer not null, -- plan
number. "_Ptype" integer not
null, -- plan type. "_Dtype"
integer not null, -- description
type. "_Description" varchar(255)
not null, -- description line.
"_Dseq" integer not null --
description sequence. )