Performance of Stored Programs [message #670829] |
Sun, 29 July 2018 07:20 |
|
ninan
Messages: 163 Registered: June 2011 Location: Noida
|
Senior Member |
|
|
Hello All,
I have on question on Stored Programs. I am not an expert in the Oracle Stored Programs.
We have a Client-Server Application, where the Application is written in Java.
It is accessing Oracle database and firing Queries against the Oracle 12c Database.
It builds the Queries based on different conditions and fires queries against the specific Oracle Database tables.
At database side it does not have any Stored Programs which the Application is using.
There is lot of performance issues in the Application and one of the Architects suggested to shift all the Database queries in the Application to a Stored Database program, according to him this can improve performance.
I am not very sure how it will help improve performance given the below facts.
1. The Procedure has to be called same number of times as the Queries are fired from the Application
Can anyone please advise if this idea to move the Queries from Application to a Database Stored Program is a good idea indeed and why?.
How it helps to improve the performance ?
What is the difference between reading the data directly via Queries and Stored DB Programs as the only difference now going to happen is that the Queries will reside inside the Database Stored Programs instead of the Application.
Thanks,
Ninan
|
|
|
Re: Performance of Stored Programs [message #670832 is a reply to message #670829] |
Sun, 29 July 2018 08:14 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The chance of the code being re-usable is astronomically higher if it is within a stored procedure.
There is also the question of code quality. One example I have noticed is that Java programmers often use SELECT * to transfer the entire row to the client, when they need only a few columns. This is a Bad Thing for many reasons. Coding everything into stored procedures does not prevent this, but it does seem to make it less common. It is also easier for your DBA to discover what is going on, and identify bad code for review.
What reason does your Architect give?
|
|
|
|
|
|
|
|
|
Re: Performance of Stored Programs [message #670843 is a reply to message #670835] |
Sun, 29 July 2018 12:39 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ninan wrote on Sun, 29 July 2018 14:47I am not convinced with the Reasons given, below are the reasons given :-
1.Stored Program at the first invocation will bring the associated Tables to the memory.
2.So any subsequent calls to the Program improves the performance.
3.The doubt i have on this is if tables are in memory, and any data changes in the Database during this time, what will happen to the Data stored in memory by the Stored Program.
These are not the correct reasons aren't they.
I have doubt on this. Your points (1) and (2) are correct in a way, but apply to whether the SQL is submitted from the client or called by PL/SQL. So I think we can conclude that your Architect is not an Oracle DBA. Your point (3) is not something you need to worry about: Uncle Oracle will look after data consistency.
It is difficult to say this, but I shall try: In general, it looks as though your level of knowledge is not sufficient to diagnose, never mind fix, whatever problem you have. You probably need to hire a consultant or attend some courses if you are to proceed.
|
|
|
|
|
|
Re: Performance of Stored Programs [message #670848 is a reply to message #670844] |
Mon, 30 July 2018 02:36 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ninan wrote on Sun, 29 July 2018 18:52Hi John,
SQLs are submitted by PL/SQL.
Regarding Point 3. My query if the Data changes, the Program again need to read from the Disk rather than from Memory right? Wrong.
Quote:Regarding 1 and 2 , Are you sure Tables will be brought to memory, it is only Program which is in the memory right, the DB Stored Program will need to read the Data from the Disk everytime right ?
Wrong.
|
|
|
Re: Performance of Stored Programs [message #670849 is a reply to message #670848] |
Mon, 30 July 2018 02:52 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
When you query data in oracle it reads the blocks on disk and moves those blocks into the buffer cache in the databases SGA - so they are in the DB's memory structures.
This happens regardless of where the query is run from.
Subsequent queries (and inserts/updates/deletes) for the same data work with the blocks in the buffer cache without having to go to disk at all.
They stay there until oracle needs space for newer data and then they are written back to disk.
There is nothing inherently faster about having queries in stored procedure.
However, if you have the stored procedures written by developers who understand how oracle works you are more likely to get better performance than if you have queries written by java developers that don't understand oracle, or queries automatically constructed by something like hibernate.
|
|
|
|