Pipelined Stored Procedure optimization

May be you get more answers here

brilliant wrote:All great suggestions and pointers. Here's my problem1. I have an external system being used for Analytics/Data Visualizations2. The data is super sensitive therefore I have a live connect with between the external system and Oracle DB3. Given it's sensitivity, having a stored procedure and forcing an input parameter of the user id ensures secured access versus Views or Materialized Views4. The procedure can be wrapped into a package, function, utilizing Table Type via Row Type5. There's 11M records in the SELECT statement outcome6. After applying Row Level Security, user fetches can vary between a few thousands to a few million records but never the entire recordset of 11M records7. the external system computes the ratios and other aggregations.8. The data is as compressed an aggregated with just the right amount of fields to support useful analysisGiven the above parameters of my scenario I am trying to implement a stored procedure via pipelined approach to return results to the external system. If I wait for 2M records to accumulate in the DB and then send it across via BULKCOLLECT, this could be a drain on server resources and cause possible network delays etc.What's teh best strategy to accomplish this with reasonable performance in DB? Views and Materialized views slowed to a crawl.Procedures being compiled code, we have leverage to control the execution plan and optimize along with indexes etc.Hope this gives a better picture. I could paste the syntax but this is more of a performance optimization best practice and standards versus syntactic problem.Thoughts?

It sounds to me like you're doing something like this:

Call asks for everything for USER A

Procedure selects massive result set (11 million rows)

Procedure removes everything from the massive result set that doesn't belong to USER A (discards up to 10.9 million rows)

Procedure gives the remaining rows to the caller

Caller then does some grouping on the result set and probably returns barely anything to the end user

1) Move your filter to act against the data as soon as you can

select my_cols from big_view where user=my_user;

2) Let the client fetch from that query, use a ref cursor

open returning_ref_cursor for select my_cols from big_view where user=my_user;

3) Change the SQL so it does the whole thing (ratios and aggregations are so simple to be done in the database and will be much quicker that sending a ton of data across the network) - It's usually possible to create fast refresh MViews that store this sort of data and can be updated very quickly when changes are made to the source data.

The dbms_rls way would be to:

Add a policy to the views that the caller can use that filters on user_id (or whatever your column is called)

Allow the caller to directly query the view.

Again, aggregation can be done inside the view to really reduce the overhead everywhere.

brilliant wrote:Hi,thanks for for your suggestions. The dbms_rls methodology while great is not practical as the external system connect with the database using a system account with expanded privileges. This is a limitation of the external system In order to provide a smooth user experience versus prompting the user for their account and password for every visual that leverages it. The user in in my case is a column in the database in an entitlements tabLe that has traditional,user, reducingnfieldnameuser1, value1user1, value2user2, value1 and so on. As for aggs cannot be done in database as there can be countless ways to interact with the visuals for analysis. Especially with weighted calculations. That mounts to building a cube/universe which is not efficient the moment a field is added and it can to recommits everythingVery limiting conditions whereby magic is expected. Has there been such scenarios in the past and how were they dealt with. Assuming this is a common problem with new age analytical tools. 

It sounds like this system has some very severe flaws. It should not be connecting as system. It prompting the user for an account and password on every step is just weird - that doesn't happen in any application I've ever used...

Anyway, it looks like it should be very easy to push the filter to the main query so you don't have to read the rows that aren't needed. Have you modified your process so that this is done? Is this now fast enough? At some point you're going to need to look at where the time is really going if you want to do anything about it.

"new age analytical tools" usually support some ETL process so you can take the data from your database and store it again but with the application in memory. You would write this so that only necessary data is sent to the application (i.e. what's changed).

Really good analytical tools will do the heavy lifting in the database.