What are the main causes for a package to have a much slower execution time for all users, except the schema owner?


Any advice or knowledge is appreciated I'm pretty new to databases.

I have a oracle package that executes at an acceptable rate by the schema owner however when I grant a role to any other user which has access to the package it is extremely slow.

What are the best methods to trouble shoot would the package be the problem or could this be an issue with how oracle verifies security for the package execution.

The package has essentially got looped select statements and is inserting data using another package.

Many Thanks

Does the package run as the owner or as the current user? What is the full version of Oracle?

  1. If the package runs as the current user then the code could be finding different versions of the objects.
  2. If the code runs as the owner but the users have different session parameter settings, several of which effect the CBO plans, then different query plans could be in use.

Run sql trace on an execution by the owner and by a user and compare the results.