www.sagecomputing.com.au penny.cookson@sagecomputing.com.au jdeveloper adf and the oracle database...

www.sagecomputing.com.aupenny.cookson@sagecomputing.com.

au

JDeveloper ADF and the Oracle database – friends

not foes

SAGE Computing ServicesCustomised Oracle Training Workshops and

Consulting

Penny Cookson

(Building applications using JDeveloper ADF without killing database performance)

SAGE Computing ServicesCustomised Oracle Training Workshops and

Consulting

Penny Cookson

Managing Director and Principal Consultant

Working with since 1987

Oracle Magazine Educator of the Year 2004

Agenda

View Objects and the WHERE clause

How to find out what’s happening in the database

View criteria

View Objects Tuning Options

Using functions

View Accessors

Skewed data

Adding Hints

Managing database connections

Application Module Pooling

“ADF Business Components stands out for the task of business service development and object relational mapping by virtue of its highly declarative metadata based development style.”

Planning Stage

The database is just a persistent data store, we don’t need to worry about it

Development Stage

This new web application is really slow

Production Stage

What have you done to MY database

Production Stage

S So how do you protect your database from the Java

people?

Its all about education

Database Centric – Model Layer

EventsOrganisations Bookings

OrganisationsVO

Bookings VO

Events VO

SELECTSELECT SELECT

OrganisationsEO

Bookings EO

Events EO

INSERTUPDATEDELETELOCK

INSERTUPDATEDELETELOCK

INSERTUPDATEDELETELOCK

BookingService (Application Module )COMMITROLLBACK

We are letting JDeveloper generate our SELECT

statements

EventsOrganisations Bookings

OrganisationsVO

Bookings VO

Events VO

SELECTSELECT SELECT

OrganisationsEO

Bookings EO

Events EO

INSERTUPDATEDELETELOCK

INSERTUPDATEDELETELOCK

INSERTUPDATEDELETELOCK

BookingService (Application Module )COMMITROLLBACK

SELECT …….WHERE col = ‘bbbbb’

Full table scan Index scan

aaaaabbbbbcccccdddddeeeee

aaaaagggggcccccdddddeeeee

aaaaakkkkkcccccdddddeeeee

aaaaabbbbbcccccdddddeeeee

a

z

bbbb rowidbbbb rowid

aaaaabbbbbcccccdddddeeeee

aaaaagggggcccccdddddeeeee

aaaaakkkkkcccccdddddeeeee

aaaaabbbbbcccccdddddeeeee

DB

Optimizer estimates cost of each possible access path and picks the cheapest

DB

Determining the View Object queryJdev

Bind variables

View Criteria

Custom sql (Expert mode)

An example - View Object with a Bind Variable

Jdev

JdevAn example - View Object with a Bind Variable

That’s seems OKBut what did it do in the database

JdevAn example - View Object with a Bind Variable

View Objects – Finding the SQL

There’s a row in here for each

optimisation of a sql statement

(until it gets aged out)

It has been executed once

With 135 blocks read per

execution

DB

View Objects – Finding the SQL DB

View Objects – Finding the SQL Plan

Paste the sql_id in here

DB

Why bother with all that database stuff when Jdev gives me an Explain Plan

Jdev

Jdev

Jdev

Is that what it will really do?

Jdev

Jdev

Doesn’t peek at bind variables

Optimiser statistics need to be the same as production

System statistics need to be the same as production

Environment needs to be same as production

Jdev

Explain Plan

Find out what its really doing

VO Tuning Options affect the statements executed

Jdev

VO Tuning options

This statement gets executed when the VO tuning options are set to: Rows in batches as needed

DB

Jdev

Managing Optional Parameters

Required parameter works fine, but

returns no rows if the parameter is null

Make the Bind variable Required = NoJdev

Bind Variable Oracle Documentation

Required

Select if you want to make the value of a name bind variable required for any usage the references the named bind variable. For example, when the value is required (default), all view criteria items that reference the named bind variable will fail to execute unless a valid value is supplied at runtime. Alternatively, you can leave the value not required and use the Create View Criteria dialog to specify whether or not individual view criteria items require the value

Recommendations So Far

Forget about Explain plan – find out what its really done

Required Bind variable is fine if you always provide a value for an indexed column

Using View Criteria

View Criteria – OptionalView Criteria – Ignore Null Values =

Y

Jdev

Using View criteria

Execute with no parameter value

All the rows returned

Jdev

Bind Parameter – Required = YView Criteria – OptionalView Criteria – Ignore Null

Values = YNo parameter value

This is the statement that returns the rows

DB

Bind Parameter – Required = YView Criteria – OptionalView Criteria – Ignore Null

Values = YNo parameter value

Read the entire table

DB

I am sure it will be fine if I give it a parameter value

Bind Parameter – Required = YView Criteria – OptionalView Criteria – Ignore Null

Values = Y

Parameter value was 1200

DB

Bind Parameter – Required = YView Criteria – OptionalView Criteria – Ignore Null

Values = YWith parameter value

DB

Bind Parameter – Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

Required = N

Jdev

Bind Parameter – Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

View Criteria – OptionalView Criteria – Ignore Null Values =

Y

Jdev

Bind Parameter – Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

No parameter value suppliedAll the rows returned

Jdev

Bind Parameter – Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

DB

Predicate for pBookingNo not included if bind variable is null

Bind Parameter – Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

DB

Bind variable has a

value of 1206

Bind Parameters – Required = N Multiple View Criteria – OptionalMultiple View Criteria – Ignore Null

Values = Y

Jdev

Bind Parameters – Required = N Multiple View Criteria – OptionalMultiple View Criteria – Ignore Null

Values = Y

Jdev

Bind Parameters – Required = N Multiple View Criteria – OptionalMultiple View Criteria – Ignore Null

Values = Y

Jdev

Bind Parameters – Required = N Multiple View Criteria – OptionalMultiple View Criteria – Ignore Null

Values = Y

Includes the where clause if we have supplied a parameter value

DB

Bind Parameters – Required = N Multiple View Criteria – OptionalMultiple View Criteria – Ignore Null

Values = Y

Which allows it to choose the best index

DB

Recommendations So Far

Forget about Explain plan – find out what it really did

Required Bind variable is fine if you always provide a value for an indexed column

For flexibility and performance use multiple View Criteria with

Parameter Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

Using Functions in the SELECTJdev

Using Functions in the SELECT

Didn’t someone say it was bad to do this?

Jdev

View Object Tuning Options

But it doesn’t matter - I am only getting 20 rows

Jdev

Using Functions in the SELECT DB

Sort the Rows (either User Sort or in the VO)

Jdev

Sort the Rows (either User Sort or in the VO)

You might be getting only getting 20 rows back to the middle tier – but the database has to read all of them

DB

View Object – Multiple Entities + Transient Attribute

Jdev

View Object – Multiple Entity Objects

Jdev

View Object – Multiple Entity Objects - Sort

Jdev

View Object – Multiple Entity Objects

than thisThis is much better

DB

View Object – Multiple Entity Objects

But why does it have to keep selecting from EVENTS

DB

View Object – Multiple Entity Objects

Jdev

View Object – Multiple Entity Objects

Now its all selected from the join

DB

So – if you include a derived attribute from another EO also include the source attributes

View Object – Fetching multiple values from the same tables

SELECT taxFileNo, getTaxCalc1(taxFileNo), getTaxCalc2(taxFileNo); getTaxCalc3(taxFileNo),

getTaxCalc4(taxFileNo)FROM TaxDetailsTable

All these functions get the

same rows from the tables,

they just return different columns

Jdev

View Object – Fetching multiple values from the same tables

SELECT taxFileNo, getTaxCalcs(taxFileNo),

FROM TaxDetailsTable

value1

value1|value2|value3|value4

value2

value3

value4

Define transient attributes in the VO

Jdev

View Object – Tuning Options

It defines how many rows are returned to the middle tier

NOT how many are read from the database

Jdev

Recommendations So Far

Forget about Explain plan – find out what it really did

Required Bind variable is fine if you always provide a value for an indexed column

For flexibility and performance use multiple View Criteria with

Parameter Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

For derived values, don’t use functions unless you have to

Select all the sources of a derived attribute

If you have to use functions fetch everything you need in one operation

Modified columns in the WHERE clause

Modified columns don’t use an index on the column

DB

 Columns that are modified by expressions or functions are unable to use an index unless a function based index has been created Examples: WHERE upper(name) = ‘VALUE’ WHERE sal * 1.1 = 2000 WHERE to_char(start_date,’dd/mm/yy’) = ‘01/03/93’

View Object – Case Sensitive View Criteria

Jdev

View Object – Case Sensitive View Criteria

Create a function based index

DB

Validation – IN View AccessorJdev

Validation – IN View AccessorJdev

Validation – IN View AccessorJdev

Validating this reads 100,438 blocks in a full scan of EVENTS_LARGE(and takes a very long time)

Validation – KEY EXISTS in View Accessor

Jdev

Validation – KEY EXISTS in View Accessor

Jdev

Validating this reads 99 blocks in a Unique Index scan of EVENTS_LARGE(and is really quick)

Displaying Additional Data – View Accessor

Jdev

Jdev

Displaying Additional Data – View Accessor

Jdev

Displaying Additional Data – View Accessor

Jdev

Displaying Additional Data – View Accessor

Jdev

Displaying this needs a full scanof ORGANISATIONS

Displaying Additional Data – View Accessor

Displaying Additional Data -Multiple Entity Usages

Jdev

Displaying Additional Data -Multiple Entity Usages

Jdev

Displaying Additional Data -Multiple Entity Usages

Jdev

Displaying this fetches Organisations.name from

a join in the VO query

Recommendations So Far

Forget about Explain plan – find out what it really did

Required Bind variable is fine if you always provide a value for an indexed column

For flexibility and performance use multiple View Criteria with

Parameter Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

For derived values, don’t use functions unless you have to

Select all the sources of a derived attribute

If you have to use functions fetch everything you need in one operation

Don’t use Ignore Case unless you have a function based index

Be wary of using View Accessors for large data sets

Skewed Data

Majority value

Minority value

DB

Skewed Data in SQL

If I execute the statement with a minority value then a

majority (twice) it reassesses the plan and makes

a different decision

DB

Skewed Data in ADF

Query for the minority case

Jdev

Skewed Data in ADF  

DB

Skewed Data in ADF

Query for the majority case

Jdev

View Object – Skewed Data in ADF

Even after 4 executions its

still using the same plan

DB

View Object – Execute in a different session

Open the same page in a different window

DB

View Object – BIND_AWARE

Add this hint to all Vos on skewed data (with

histograms)

Jdev

Recommendations So Far

Forget about Explain plan

Required Bind variable is fine if you always provide a value for an indexed column

For flexibility and performance use multiple View Criteria with

Parameter Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

For derived values, don’t use functions unless you have to

Select all the sources of the derived attribute

If you have to use functions fetch everything you need in one operation

Be wary of using View Accessors for large data sets

Don’t use Ignore Case unless you have a function based index

Use BIND_AWARE hint for skewed data

So what about all the cute new query components

Jdev

Jdev

xxxxxxxxxx

xxxxxxxxxx

xxxxxxxxxx

xxxxxxxxxx

xxxxxxxxxx

This is fine because the user picked an indexed attribute to query

xxxxxxxxxx

xxxxxxxxxx

xxxxxxxxxx

This has to full scan because the attribute the user queried was not indexed

xxxxxxxxxx

Running PL/SQL from Java

LIKE ‘%something%’ can’t use an index even if there is one

xxxxxxxxxx

xxxxxxxxxx

Write our own searches with a known set of parameters

Don’t allow users to sort large sets of records

Connections - LoggingJdev

Connections - LoggingJdev

Connections - LoggingJdev

Connections - LoggingJdev

ConnectionsJdev

Using BTFs in RegionsJdev

AppModule Connection defined as:

java:comp/env/jdbc/conf2011DS

Using BTFs in RegionsJdev

Using BTFs in RegionsJdev

Database sessions in use  

DB

DB

Using BTFs in RegionsJdev

Database sessions in use  

DB

Using BTFs in Regions

AppModule Connection defined as:java:comp/env/jdbc/

EventsServiceConnDS

Jdev

Using BTFs in RegionsJdev

Database sessions in use  

DB

Using BTFs in RegionsJdev

Using BTFs in RegionsJdev

Database sessions in use  

DB

Use thesame connection

Passivation SettingsJdev

Passivation Settings

Recommended for Development / Testing

Passivation Settings

alter system flush shared_pool

Execute With Params twice more 8 executions

Recommended for Development / Testing

You need to run like this for development / testing

But turn it back on for UAT

Recommendations

Forget about Explain plan Required Bind variable is fine if you always provide a value for an indexed

column For flexibility and performance use multiple View Criteria with

Parameter Required = N View Criteria – OptionalView Criteria – Ignore Null Values = Y

For derived values, don’t use functions unless you have to Select all the sources of the derived attribute If you have to use functions fetch everything you need in one operation Don’t use Ignore Case unless you have a function based index Be wary of using View Accessors for large data sets Use BIND_AWARE hint for skewed data Use the same connection Bundle PL/SQL calls Use Application Module pooling in UAT

www.sagecomputing.com.aupenny.cookson@sagecomputing.com.

au

JDeveloper ADF and the Oracle database – friends

not foes

SAGE Computing ServicesCustomised Oracle Training Workshops and

Consulting

Penny Cookson

Questions