Transcript JDeveloper - SAGE Computing Services

SAGE Computing Services
Customised Oracle Training Workshops and Consulting
JDeveloper ADF and the
Oracle database – friends
not foes
(Building applications using JDeveloper ADF
without killing database performance)
www.sagecomputing.com.au
[email protected]
Penny Cookson
SAGE Computing Services
Customised 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
Planning Stage
“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.”
Development Stage
The database is just a persistent
data store, we don’t need to worry
about it
Production Stage
This new web application is really slow
Production Stage
What have you done to MY
database
S
So how do you protect your
database from the Java people?
Its all about education
Database Centric – Model Layer
BookingService (Application Module )
COMMIT
ROLLBACK
Organisations
VO
Organisations
EO
SELECT
INSERT
UPDATE
DELETE
LOCK
Organisations
Events VO
Bookings VO
Events EO
SELECT
Events
INSERT
UPDATE
DELETE
LOCK
Bookings EO
SELECT
Bookings
INSERT
UPDATE
DELETE
LOCK
We are letting JDeveloper generate
our SELECT statements
BookingService (Application Module )
COMMIT
ROLLBACK
Organisations
VO
Organisations
EO
SELECT
INSERT
UPDATE
DELETE
LOCK
Organisations
Events VO
Bookings VO
Events EO
SELECT
Events
INSERT
UPDATE
DELETE
LOCK
Bookings EO
SELECT
Bookings
INSERT
UPDATE
DELETE
LOCK
SELECT …….WHERE col = ‘bbbbb’
Full table scan
aaaaa
bbbbb
ccccc
ddddd
eeeee
aaaaa
ggggg
ccccc
ddddd
eeeee
aaaaa
kkkkk
ccccc
ddddd
eeeee
aaaaa
bbbbb
ccccc
ddddd
eeeee
DB
Index scan
a
bbbb rowid
bbbb rowid
z
aaaaa
bbbbb
ccccc
ddddd
eeeee
aaaaa
ggggg
ccccc
ddddd
eeeee
aaaaa
kkkkk
ccccc
ddddd
eeeee
aaaaa
bbbbb
ccccc
ddddd
eeeee
DB
Optimizer estimates cost of each possible
access path and picks the cheapest
Jdev
Determining the View Object query
Bind variables
View Criteria
Custom sql (Expert mode)
An example - View Object with a Bind Variable
Jdev
An example - View Object with a Bind Variable
Jdev
An example - View Object with a Bind Variable
That’s seems OK
But what did it do in
the database
Jdev
View Objects – Finding the SQL
There’s a row in here for each
optimisation of a sql statement
(until it gets aged out)
DB
It has been
executed once
With 135 blocks
read per execution
View Objects – Finding the SQL
DB
View Objects – Finding the SQL Plan
Paste the sql_id in here
DB
Jdev
Why bother with all that database
stuff when Jdev gives me an
Explain Plan
Jdev
Jdev
Is that what it will really do?
Jdev
Jdev
Jdev
Explain Plan
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
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
Jdev
Make the Bind variable Required = No
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 – Optional
View Criteria – Ignore Null Values = Y
Jdev
Using View criteria
Execute with no parameter value
All the rows returned
Jdev
Bind Parameter – Required = Y
View Criteria – Optional
View Criteria – Ignore Null Values = Y
No parameter value
This is the
statement that
returns the rows
DB
Bind Parameter – Required = Y
View Criteria – Optional
View Criteria – Ignore Null Values = Y
No parameter value
DB
Read the entire table
Bind Parameter – Required = Y
View Criteria – Optional
View Criteria – Ignore Null Values = Y
I am sure it will be
fine if I give it a
parameter value
Parameter value was 1200
DB
Bind Parameter – Required = Y
View Criteria – Optional
View Criteria – Ignore Null Values = Y
With parameter value
DB
Bind Parameter – Required = N
View Criteria – Optional
View Criteria – Ignore Null Values = Y
Required = N
Jdev
Bind Parameter – Required = N
View Criteria – Optional
View Criteria – Ignore Null Values = Y
View Criteria – Optional
View Criteria – Ignore Null Values = Y
Jdev
Bind Parameter – Required = N
View Criteria – Optional
View Criteria – Ignore Null Values = Y
No parameter value supplied
All the rows returned
Jdev
Bind Parameter – Required = N
View Criteria – Optional
View Criteria – Ignore Null Values = Y
Predicate for pBookingNo not
included if bind variable is null
DB
Bind Parameter – Required = N
View Criteria – Optional
View Criteria – Ignore Null Values = Y
Bind variable has a
value of 1206
DB
Bind Parameters – Required = N
Multiple View Criteria – Optional
Multiple View Criteria – Ignore Null Values = Y
Jdev
Bind Parameters – Required = N
Multiple View Criteria – Optional
Multiple View Criteria – Ignore Null Values = Y
Jdev
Bind Parameters – Required = N
Multiple View Criteria – Optional
Multiple View Criteria – Ignore Null Values = Y
Jdev
Bind Parameters – Required = N
DB
Multiple View Criteria – Optional
Multiple View Criteria – Ignore Null Values = Y
Includes the where clause if we have supplied a parameter value
Bind Parameters – Required = N
DB
Multiple View Criteria – Optional
Multiple View Criteria – Ignore Null Values = Y
Which allows it to choose the best index
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 – Optional
View Criteria – Ignore Null Values = Y
Jdev
Using Functions in the SELECT
Jdev
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
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
This is much better
than this
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
So – if you include a derived attribute from another EO
also include the source attributes
DB
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|value2|value3|value4
value1
value2
value3
Define transient attributes in the VO
value4
Jdev
View Object – Tuning Options
Jdev
It defines how many rows are
returned to the middle tier
NOT how many are read
from the database
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 – Optional
View 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
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’
Modified columns don’t use an index on
the column
DB
View Object – Case Sensitive View Criteria
Jdev
View Object – Case Sensitive View Criteria
Create a function based index
DB
Validation – IN View Accessor
Jdev
Validation – IN View Accessor
Jdev
Validation – IN View Accessor
Jdev
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
Validating this reads
99 blocks
in a Unique Index scan
of EVENTS_LARGE
(and is really quick)
Jdev
Displaying Additional Data – View Accessor
Jdev
Displaying Additional Data – View Accessor
Jdev
Displaying Additional Data – View Accessor
Jdev
Displaying Additional Data – View Accessor
Jdev
Displaying Additional Data – View Accessor
Displaying this
needs a full scan
of ORGANISATIONS
Jdev
Jdev
Displaying Additional Data Multiple Entity Usages
Jdev
Displaying Additional Data Multiple Entity Usages
Jdev
Displaying Additional Data Multiple Entity Usages
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 – Optional
View 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
DB
Skewed Data
Majority value
Minority value
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
Jdev
Skewed Data in ADF
Query for the minority case
Skewed Data in ADF
DB
Jdev
Skewed Data in ADF
Query for the majority case
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
Jdev
Add this hint to all Vos on
skewed data (with histograms)
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 – Optional
View 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
Jdev
Connections - Logging
Jdev
Connections - Logging
Jdev
Connections - Logging
Jdev
Connections - Logging
Jdev
Connections
Jdev
Using BTFs in Regions
AppModule Connection defined as:
java:comp/env/jdbc/conf2011DS
Jdev
Using BTFs in Regions
Jdev
Using BTFs in Regions
Database sessions in use
DB
DB
Jdev
Using BTFs in Regions
Database sessions in use
DB
Jdev
Using BTFs in Regions
AppModule Connection defined as:
java:comp/env/jdbc/EventsServiceConnDS
Jdev
Using BTFs in Regions
Database sessions in use
DB
Using BTFs in Regions
Jdev
Using BTFs in Regions
Jdev
Database sessions in use
DB
Use the
same connection
Jdev
Passivation Settings
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 – Optional
View 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
SAGE Computing Services
Customised Oracle Training Workshops and Consulting
JDeveloper ADF and the
Oracle database – friends
not foes
Questions
www.sagecomputing.com.au
[email protected]
Penny Cookson