Deadly Sins against Database Performance

1
Deadly Sins against Database Performance
Vladimir Andreev Semantec GmbH
2
About the Author

• 7 years of experience with Oracle
• Specialization in Performance Tuning
• Performance Assessments of many production
  databases
• Different industries (automotive, pharmaceutical,
  chip production, public sector, banking, machine
  building, etc.)
• Different workloads (OLTP, DW, Web)
• Different sizes (100MB 500GB)
• Different architectures (2-tier, 3-tier)
• Different application technologies (PL/SQL, C,
  Java, Forms, ABAP, Teamcenter, Vignette, )

3
Top Problems

• Architecture database independence through
  isolation layers
• Sub-optimal performance in any database
• Higher development and maintenance costs
• Design row-by-row processing
• Hard limits on performance and scalability
• Coding Un-shareable SQL
• Disaster waiting to happen

4
Middle Layers

• Application Servers
• SAP, BAAN
• Custom Design
• Database Isolation Layers
• DAO/OLEDB, ODBC, JDBC, etc.
• J2EE Persistency Layers (e.g. TopLink)
• Custom Design Persistency layers

5
Database Isolation Layers

• Are cast in stone
• Export an API with limited or no support for
• Non-standard SQL (with, merge, connect by,
  analytics, hints)
• stored procedure calls
• ref cursor, object types, etc.
• bind variables
• Insist on maintaining the data model, incl.
  indexes
• Generate sub-optimal SQL

6
Live Specimens

• We have no control over the SQL
• The Application Server is not supported with
  CBO
• We need to trick the middle layer

7
Row-by-Row Processing

• Seems easy to code and understand, but
• Has many negative consequences
• Round-trip overhead
• Context switching
• Increased server load
• Increased network load
• Thats why it is sometimes called Slow-by-Slow
  processing

8
Slow-by-Slow Processing

• Over the network
• Increased traffic
• Multiplies the network overhead
• On the same machine (via IPC)
• Voluntary context switches
• In the same database
• Context switches between the PL/SQL and SQL
  machine

9
Live Specimen

• Create or replace view VM01_WORKFLOW_STEPS_ACT as
• SELECT T.ID, T.USER_ID, T.WORKFLOW_ID,
  T.CONTENT_ID
• FROM TM01_WORKFLOW_STEPS T
• WHERE NUMMER FM01_WORKFLOW_STEPS_ACT(T.CONTENT_I
  D)
• Create or replace
• FUNCTION "FM01_WORKFLOW_STEPS_ACT" (vCONTENT_ID
  IN NUMBER)RETURN NUMBER
• IS
• vNr NUMBER
• BEGIN
• SELECT MAX(NUMMER) INTO vNr FROM
  TM01_WORKFLOW_STEPS WHERE CONTENT_IDvCONTENT_ID
• RETURN vNr
• EXCEPTION
• WHEN OTHERS
• THEN RETURN NULL
• END FM01_WORKFLOW_STEPS_ACT

10
Better

• Create or replace view VM01_WORKFLOW_STEPS_ACT as
• SELECT T.ID, T.USER_ID, T.WORKFLOW_ID,
  T.CONTENT_ID
• FROM TM01_WORKFLOW_STEPS T
• WHERE NUMMER (select max(nummer) from
  TM01_WORKFLOW_STEPS where CONTENT_IDT.CONTENT_ID)
  
• Or, even better
• Create or replace view VM01_WORKFLOW_STEPS_ACT as
• select from (
• SELECT T.ID, T.USER_ID, T.WORKFLOW_ID,
  T.CONTENT_ID,
• max(nummer) over (partition by CONTENT_ID) mNum
• FROM TM01_WORKFLOW_STEPS T )
• where nummermNum

11
Effects on a 6-CPU System
12
Un-Shareable SQL

• Worst kind Literals instead of binds
• Bad kind Dynamically built in-lists or or-lists
• Harmless kinds Capitalisation, white space,
  comments, variable names

13
Literals and Bind Variables PL/SQL

• Bad
• execute immediate 'delete from T where
  id'id_to_delete
• Better
• execute immediate 'delete from T where iddelID
  using id_to_delete
• Best
• delete from T where idid_to_delete

14
Literals and Bind Variables - Java

• Bad
• Statement stmt conn.createStatement()
• stmt.executeUpdate("delete from T where id"
  Integer.toString(id_to_telete))
• Better
• PreparedStatement pstmt conn.prepareStatement("d
  elete from T where id?")
• pstmt.setInt(1, id_to_delete)
• pstmt.execute()

15
Live Specimens

• UPDATE ABRUFE
• SET ENDE_ABRUF
• TO_DATE('27.01.2004 105000','DD.MM.YYYY
  HH24MISS')
• WHERE ABRUF_ID 242392
• begin PM01_EXPORT_PROG_BEITRAG(result,
  '1209923') end
• statement
• 'SELECT t1.faktor
• FROM loader_faktoren_pr_segment t1, dim_person t2
  WHERE t2.kundennummer 'val_p_kunden.kundennumm
  er_echt'
• AND t1.art ''Ausbildung'
• AND t2.ausbildung_s t1.schluessel'
• DBMS_SQL.PARSE(source_cursor, statement,
  dbms_sql.NATIVE)
• SELECT FROM apttab
• WHERE apttab.finr 1 AND apttab.artnr
  '122-0147-0 '
• AND apttab.varnr 0 AND apttab.appos 30
• Select from ODIN_STACK where ODS_INDEX
  'PODSB_5714733'

16
Thank you for your attention!

• Visit us here at the conference Booth C10