JDeveloper ADF and the Oracle database – friends not foes

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