DML processing.

Hi,

I have a taken a new job in performance tuning, and like to tune all the plsql code.
suppose when a dml is fired, is it possible to know what all have happened internally.
like, wait events caused, index entries added, triggers fired etc.,

Thanks
sakthikumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi  

Please look at Overview of the Automatic Database Diagnostic Monitor and gathering statistics
                   http://docs.oracle.com/database/121/TGDBA/toc.htm

Look at Use EXPLAIN in the below and other tips
                    http://web.synametrics.com/top10performancetips.htm

Here is some info on SQL Tuning
                    http://www.dba-oracle.com/art_sql_tune.htm
                    http://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_sqltune.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
For me, I would start with tracing the session that is running the procedure and look at that.  I find it is much easier to isolate the procedure and see what it is doing.

Start with the documentation here -> http://docs.oracle.com/database/121/RPTUG/dialogs.htm#BACICJAF

I would use a combination of setting trace on in the session running the procedure and tkprof, but there are a bunch of new things outlined in that documentation that are useful as well.
0
slightwv (䄆 Netminder) Commented:
>>Please look at Overview of the Automatic Database Diagnostic Monitor and gathering statistics

I believe ADDM requires some additional licensing.

>>is it possible to know what all have happened internally.

I don't think this by itself gives you everything you asked for but used in combination with other things in the docs, it might.

Look at DBMS_PROFILER.

There are a few things discussed in the docs:
12 PL/SQL Optimization and Tuning
Profiling and Tracing PL/SQL Programs
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#LNPLS01214
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.