sdruss
asked on
Oracle Purge Storage Procedure Not Resonsive
Trying to troubleshoot problem with inherited stored procedure from a vendor. Running RAC 11g 2-node database. Vendor's stored procedure is what you would expect. Stored proc is responsible from freeing up space and deleting older obsolete data. The stored procedure is passed a parameter to indicate the percentage of data to be retained. Any good troubleshooting ideas, recommendations, suggestions? We ran stored procedure, which is typcially scheduled via Oracle scheduler jobs, manually and it ran for 14-hours before developers aborted procedure. I will not be able to supply the code for the actual PL/SQL stored procedure for a variety of reasons. Could updating statistics be a good first step?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I was re-reading your question, and previously missed that you ran the PL/SQL manually. Let's drill down for now, to the raw command string that was executed -- mask the actual path and object name, of course. For example, can the developers open a SQL*Plus client session, on the database host, and execute the package.
Don't use an interface like SQL*Developer or TOAD, don't run it as a shell script, and avoid running it across a network if you can. As Suri suggested, deconstruct the complex into its simplest components.
Is there any communication to confirm that the passed parameter is recognized, correctly parsed, and accepted? What if the problem is as simple as the parameter was not recognized by the package, and it simply waited for a correct replacement? What I'd prefer from the developers is something akin to a message "You entered a parameter of 'x' percent. That value is valid. Please enter "YES" to confirm. "
Side comment: I hope you people have a test and working procedure in place to recover if this package were to abnormally terminate. As you are surely aware, DELETED records cannot be rolled back once committed.
Last "obvious" overlooked detail for tonight: are we absolutely sure that the instance and session resources met or exceed the vendor's requirements? I've seen things set up properly for the user account that executes the Scheduler -- but the developer may have a small SORT_AREA_SIZE. 'Night.
Don't use an interface like SQL*Developer or TOAD, don't run it as a shell script, and avoid running it across a network if you can. As Suri suggested, deconstruct the complex into its simplest components.
Is there any communication to confirm that the passed parameter is recognized, correctly parsed, and accepted? What if the problem is as simple as the parameter was not recognized by the package, and it simply waited for a correct replacement? What I'd prefer from the developers is something akin to a message "You entered a parameter of 'x' percent. That value is valid. Please enter "YES" to confirm. "
Side comment: I hope you people have a test and working procedure in place to recover if this package were to abnormally terminate. As you are surely aware, DELETED records cannot be rolled back once committed.
Last "obvious" overlooked detail for tonight: are we absolutely sure that the instance and session resources met or exceed the vendor's requirements? I've seen things set up properly for the user account that executes the Scheduler -- but the developer may have a small SORT_AREA_SIZE. 'Night.
ASKER
Long unproductive day. Trying to debug remotely via a J2kEE developer and a tester. Did notice we had run out of temp tablespace, and then next run, could not extend undo tablespace.
That's on the right track, though.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent help. Thanks!
ASKER