Loggin of SQL queries for Oracle 12c and Oracle 10.2g, help!

Hi guys, this is an extension to another question which is still being answered, and which led to me needing to know how to go about tracing the queries executed on an oracle db along with their times etc... all the info possible.

In MSSql we use SQL profiler to get all this, along with a nice history (that you can filter).
I guess my lack of experience with Oracle products leads me to this question.

What tools could one use for the job, where would I get them, and I have 10.2g I need to log as well as a new 12c I need to log. If a UI tool exists that would be nice, otherwise, if you will suggest text based or sqlplus based tools, please be thorough with links or description as to what to do to call up the needed app/command line.

I am from MSSQL world, please bear with me, due to a buyout I am needing to learn Oracle and do the switch. Thanks for all your patience! :)
landerson999Asked:
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.

sdstuberCommented:
enterprise manager with diagnostic packs will let you capture and review sql statements.

you can also turn tracing on for any session and use tkprof command line utility to get a report of your traced sessions.

Toad is a great gui for Oracle, but pricey, especially with DBA module, which you'd probably want.
0
landerson999Author Commented:
>you can also turn tracing on
How do i do that if the session being called up is inside of a batch file that is run each night...
I run the sql file as an argument with a command line call to sqlplus inside the batch file...

I am just wondering how the   "alter session set sql_trace=true;"
would apply to the   "sqlplus user/pwd@MyDB @kill_meidbo_sessions.sql"
command I have.... would I need it inside the batch file one line above, or within the .sql file being passed inside as a parameter?

Is there a global way of tracing all incoming sessions and not just the one session (as in SQLProfiler, you just say log all events, and it does not seperate from sessions to session, that is waht the filters are for).

>tkprof command line utility
I can call the tkprof utility, but I would see a file for each session? This means if I have 500 different calls to sqlplus for queries in my batch file each being its own session, I will need to go through the tkprof 500 times? Is there not a better way of doing this?

Also, is this applicable for both 10.2g and 12c
0
landerson999Author Commented:
There are a few tools out there that read the trace files better then tkprof does...and they are out there free. I am surprised no one thought of suggesting them, such as TOAD, QueryAdvisor, etc...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

landerson999Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for sdstuber's comment #a39577336
Assisted answer: 0 points for landerson999's comment #a39594602

for the following reason:

Toad was mentioned, but that was it, no other tools, and only one person answered except myself..
0
sdstuberCommented:
Toad's trace viewer is quite nice but had already been mentioned.  I also like the HOTSOS profiler (also available from methodr) it's not free, but it's quite good.

I've also written my own trace analysis tools.  The files are just plain text and pretty easy to parse and work with.


>>> Is there a global way of tracing all incoming sessions

yes, but turning on tracing for the entire db can be very expensive unless you have few sessions and low activity.  just set sql_trace =true for the system, not just the session.
if you go that route, don't leave it on for long.


To turn tracing on for a session I recommend using dbms_monitor package.
You options for bind and wait capture as well as the statements themselves.
0
landerson999Author Commented:
Thank you, I will check HOTSOS, I am uncertain how to award points after the issue has been closed, had this been a week ago... sigh.
0
sdstuberCommented:
the question isn't closed yet it's simply pending.
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
sdstuberCommented:
why the penalty grade?
0
landerson999Author Commented:
I am not sure, I had selected the 500 points split into 2 solutions, but it came out wrong.
I am not sure if someone here has the power to correct the mistake... ??
0
landerson999Author Commented:
Actually, i see it is split into 3 separate solutions... so i guess that is right.
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.