SQL Performance (MOSC)

MOSC Banner

Equivalent SQLplus arraysize in a procedure selecting over a db link

edited Nov 20, 2023 5:47PM in SQL Performance (MOSC) 3 commentsAnswered

Oracle ver 19.19

Have a query pulling lot of data over a database link long distance

If I run the select in SQLplus altering the arraysize from default 15 to max 5000 brings runtime down from to a fraction of the time with the saving on the round trips.

How can I bake the larger arraysize into a procedural call though if I put the select in a procedure, is there any PLSql setting I can run before the select runs (I know we cant use arraysize)?

The procedure may be called by applications, not sqlplus.


The same question would apply if the select from the link was in an mview? that could work for us as well.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center