I am debugging poor query performance for my application and recently came across the SQLT tool. The XTRXEC method combines XTRACT and XECUTE and it seems like this provides more info then XTRACT alone. However, one of the statements that I want to investigate is an Insert. Is it safe to run XTRXEC in a live production environment or will the XECUTE try to actually do the insert? If that's the case is it safe to do the XTRACT in production?
closed as primarily opinion-based by RolandoMySQLDBA, Mark Storey-Smith, StanleyJohns, dezso, Taryn♦ Aug 22 '13 at 19:36
Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.
This is from the sqlt instruction file itself:
"For statements that modify data, i.e. INSERT/UPDATE/DELETE, a savepoint is created prior to statement execution and the transaction is rolled back to the savepoint at the conclusion of the session. For further information regarding SAVEPOINT please consult the Oracle Concepts reference manual".
This means if you are using XECUTE method, the data it inserted during execution will be rolled back at the end of execution. If you are running this in a production system with thousands of transactions per second, you might want to consider the additional load this insert/rollback will put on the system.
Hope this helps :)