| Performance Issue With Triggers on 9.2.0.1 [message #53754] |
Wed, 09 October 2002 11:20  |
Glen
Messages: 7
Registered: October 2000
|
Junior Member |
|
|
Has anyone run into performance issues with triggers on the 9.2.0.1 version of Oracle on NT?
I'm running a prototype that performs the steps below.
-insert rows into table
-trigger fires and calls stored procedure
-stored procedure performs the following options
--opens a cursor with DBMS_SQL
--counts the rows that were inserted into the table
that the trigger is on
--updates another table with rows counted
--deletes the rows
--closes the cursor opened by DBMS_SQL.
The performance degradation is compared to the 9.0.1.3 SID on the same machine. Tests were run on both SIDs. The init.ora files are nearly identical. Both SIDs have the same stored procedure and trigger definition. Data is being loaded via SQL*LOADER. Both SIDs are being loaded with the same control and data files.
Does anyone know tuning parameters in the init.ora file (or anything else) that would affect the performance of triggers on 9.2.0.1?
|
|
|
|
|
|
|
|
| Re: Performance Issue With Triggers on 9.2.0.1 [message #53794 is a reply to message #53784] |
Fri, 11 October 2002 05:28  |
Glen
Messages: 7
Registered: October 2000
|
Junior Member |
|
|
Same control file. Trigger fires approximately 200 times.
I'm beginning to think that the issue is a result of the Delete statement in the stored procedure.
The scenario is that the stored procedure being called from the trigger can't use truncate because of transactional issues. Multiple invocations of the stored procedure can exist simultaneously, each can see records populated by a different connection. Truncate will blow away all rows inserted, regardless of transaction. So, I can't use truncate.
Anyone know initialization parameters to tune for the DELETE statement?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
