Home » SQL & PL/SQL » SQL & PL/SQL » Performance of Stored Programs (Oracle 12c , Linux)
Performance of Stored Programs [message #670829] Sun, 29 July 2018 07:20 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8048
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 #670835 is a reply to message #670832] Sun, 29 July 2018 08:47 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
I 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.
Re: Performance of Stored Programs [message #670837 is a reply to message #670835] Sun, 29 July 2018 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
If you do NOT know where time is now being spent, you are blindly making changes & hoping to get lucky.
Oracle session creation is a resource intensive operation.
If a new session is being created for every SQL being issued, great benefit would be gained by implementing a Connection Pool.
IMO, your first task is to identify where time is being spent.
Re: Performance of Stored Programs [message #670839 is a reply to message #670837] Sun, 29 July 2018 09:28 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
Connection is being made only once at the beginning.

But the SQLs are being called many times with different parameter values from Application.
Re: Performance of Stored Programs [message #670840 is a reply to message #670839] Sun, 29 July 2018 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
Which system resource, CPU, RAM, I/O, is system bottleneck?
What clues exist in AWR/statspack report?
Where is time being spent?
Re: Performance of Stored Programs [message #670841 is a reply to message #670840] Sun, 29 July 2018 10:16 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
CPU is high above 98%
Re: Performance of Stored Programs [message #670842 is a reply to message #670841] Sun, 29 July 2018 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
Where is time being spent?
Where is time being spent?
Where is time being spent?
Where is time being spent?

if you do not know Where is time being spent, then you don't know exactly what needs to be improved

ALTER SESSION SET SQL_TRACE=TRUE;

above will capture all SQL issued by this session & then using "tkprof" it will report elapsed time for every SQL
Re: Performance of Stored Programs [message #670843 is a reply to message #670835] Sun, 29 July 2018 12:39 Go to previous messageGo to next message
John Watson
Messages: 8048
Registered: January 2010
Location: Global Village
Senior Member
ninan wrote on Sun, 29 July 2018 14:47
I 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 #670844 is a reply to message #670843] Sun, 29 July 2018 12:52 Go to previous messageGo to next message
ninan
Messages: 163
Registered: June 2011
Location: Noida
Senior Member
Hi 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?

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 ?


Re: Performance of Stored Programs [message #670845 is a reply to message #670844] Sun, 29 July 2018 13:06 Go to previous messageGo to next message
John Watson
Messages: 8048
Registered: January 2010
Location: Global Village
Senior Member
You definitely need some form of outside assistance. More than I can provide in a forum. Perhaps someone else can help.
Re: Performance of Stored Programs [message #670846 is a reply to message #670844] Sun, 29 July 2018 20:10 Go to previous messageGo to next message
BlackSwan
Messages: 26620
Registered: January 2009
Location: SoCal
Senior Member
ninan wrote on Sun, 29 July 2018 10:52
Hi 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?

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 ?


whole tables are brought into memory if & only if a Full Table Scan is performed.
Otherwise only the block that contain the select ROWS are brought into memory
If the same data block is needed more than once Oracle will issue a "soft" read (Logical I/O) instead of another "hard read (physical I/O).

You don't know what you don't know.
A session will NOT see uncommitted changed (DML) data from other session.
Re: Performance of Stored Programs [message #670848 is a reply to message #670844] Mon, 30 July 2018 02:36 Go to previous messageGo to next message
John Watson
Messages: 8048
Registered: January 2010
Location: Global Village
Senior Member
ninan wrote on Sun, 29 July 2018 18:52
Hi 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 Go to previous messageGo to next message
cookiemonster
Messages: 13720
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.


Re: Performance of Stored Programs [message #670851 is a reply to message #670844] Mon, 30 July 2018 03:02 Go to previous message
Michel Cadot
Messages: 66685
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Concepts
Database Performance Tuning Guide
Database SQL Tuning Guide
Database Development Guide
Part II SQL for Application Developers
Chapter 6 SQL Processing for Application Developers

Previous Topic: return top 10 records based on dynamic count of prev records results
Next Topic: OR clause degrades performance of a query?
Goto Forum:
  


Current Time: Fri Nov 15 01:44:45 CST 2019