www.sagecomputing.com.au [email protected] jdeveloper adf and the oracle database...
TRANSCRIPT
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
au
JDeveloper ADF and the Oracle database – friends
not foes
SAGE Computing ServicesCustomised Oracle Training Workshops and
Consulting
Penny Cookson
Questions