1 / 135

JDeveloper ADF and the Oracle database – friends not foes

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 penny.cookson@sagecomputing.com.au.

more
Download Presentation

JDeveloper ADF and the Oracle database – friends not foes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 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 penny.cookson@sagecomputing.com.au Penny Cookson

  2. 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

  3. 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

  4. 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.”

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

  6. Production Stage This new web application is really slow

  7. Production Stage What have you done to MY database

  8. S So how do you protect your database from the Java people?

  9. Its all about education

  10. Database Centric – Model Layer BookingService (Application Module ) COMMIT ROLLBACK OrganisationsVO Events VO Bookings VO OrganisationsEO Events EO Bookings EO INSERT UPDATE DELETE LOCK INSERT UPDATE DELETE LOCK INSERT UPDATE DELETE LOCK SELECT SELECT SELECT Bookings Events Organisations

  11. We are letting JDeveloper generate our SELECT statements BookingService (Application Module ) COMMIT ROLLBACK OrganisationsVO Events VO Bookings VO OrganisationsEO Events EO Bookings EO INSERT UPDATE DELETE LOCK INSERT UPDATE DELETE LOCK INSERT UPDATE DELETE LOCK SELECT SELECT SELECT Bookings Events Organisations

  12. DB SELECT …….WHERE col = ‘bbbbb’ Full table scan Index scan aaaaa bbbbb ccccc ddddd eeeee aaaaa bbbbb ccccc ddddd eeeee a aaaaa ggggg ccccc ddddd eeeee aaaaa ggggg ccccc ddddd eeeee bbbbrowid bbbbrowid aaaaa kkkkk ccccc ddddd eeeee aaaaa kkkkk ccccc ddddd eeeee z aaaaa bbbbb ccccc ddddd eeeee aaaaa bbbbb ccccc ddddd eeeee

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

  14. Jdev Determining the View Object query Bind variables View Criteria Custom sql (Expert mode)

  15. Jdev An example - View Object with a Bind Variable

  16. Jdev An example - View Object with a Bind Variable

  17. Jdev An example - View Object with a Bind Variable That’s seems OK But what did it do in the database

  18. DB View Objects – Finding the SQL It has been executed once There’s a row in here for each optimisation of a sql statement (until it gets aged out) With 135 blocks read per execution

  19. DB View Objects – Finding the SQL

  20. DB View Objects – Finding the SQL Plan Paste the sql_id in here

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

  22. Jdev

  23. Jdev Is that what it will really do?

  24. Jdev

  25. Jdev

  26. 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

  27. Jdev VO Tuning Options affect the statements executed

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

  29. Jdev Managing Optional Parameters Required parameter works fine, but returns no rows if the parameter is null

  30. Jdev Make the Bind variable Required = No

  31. 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

  32. 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

  33. Jdev Using View Criteria View Criteria – Optional View Criteria – Ignore Null Values = Y

  34. Jdev Using View criteria Execute with no parameter value All the rows returned

  35. Bind Parameter – Required = Y View Criteria – Optional View Criteria – Ignore Null Values = Y No parameter value DB This is the statement that returns the rows

  36. Bind Parameter – Required = Y View Criteria – Optional View Criteria – Ignore Null Values = Y No parameter value DB Read the entire table

  37. Bind Parameter – Required = Y View Criteria – Optional View Criteria – Ignore Null Values = Y DB I am sure it will be fine if I give it a parameter value Parameter value was 1200

  38. Bind Parameter – Required = Y View Criteria – Optional View Criteria – Ignore Null Values = Y With parameter value DB

  39. Bind Parameter – Required = N View Criteria – Optional View Criteria – Ignore Null Values = Y Jdev Required = N

  40. Bind Parameter – Required = N View Criteria – Optional View Criteria – Ignore Null Values = Y Jdev View Criteria – Optional View Criteria – Ignore Null Values = Y

  41. Bind Parameter – Required = N View Criteria – Optional View Criteria – Ignore Null Values = Y Jdev No parameter value supplied All the rows returned

  42. Bind Parameter – Required = N View Criteria – Optional View Criteria – Ignore Null Values = Y DB Predicate for pBookingNo not included if bind variable is null

  43. Bind Parameter – Required = N View Criteria – Optional View Criteria – Ignore Null Values = Y DB Bind variable has a value of 1206

  44. Jdev Bind Parameters – Required = N Multiple View Criteria – Optional Multiple View Criteria – Ignore Null Values = Y

  45. Jdev Bind Parameters – Required = N Multiple View Criteria – Optional Multiple View Criteria – Ignore Null Values = Y

  46. Jdev Bind Parameters – Required = N Multiple View Criteria – Optional Multiple View Criteria – Ignore Null Values = Y

  47. Bind Parameters – Required = N Multiple View Criteria – Optional Multiple View Criteria – Ignore Null Values = Y DB Includes the where clause if we have supplied a parameter value

  48. Bind Parameters – Required = N Multiple View Criteria – Optional Multiple View Criteria – Ignore Null Values = Y DB Which allows it to choose the best index

  49. 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

  50. Jdev Using Functions in the SELECT

More Related