/*** Find top 25 SQL statements ***/
set serverout on size 1000000
cursor c1 is
SELECT buffer_gets, substr(sql_text,1,4000)
ORDER BY buffer_gets desc;
for i in 1..25 loop
fetch c1 into top25, text1;
len1 := length(text1);
x := 66;
while len1 > x-1 loop
x := x+66;
shows top 25 queries (by buffer gets)
select upper(substr(sql_text, 1, 150)) sqltext, count(*)
group by upper(substr(sql_text, 1, 150))
having count(*) > 10;
shows queries and the number of executions that are similiar ...
Both of these only show current info ... You may have to come up with
another way of looking at things after executiion (audit) ...
I have set the sql_trace=True in the init.ora file, this is a development database, Now I can see a lot of *.trc file in the Udump, I need to figure out from the *.trc file every day what are the
1. top ten queries that took the longest time
2. top ten queries that returned the largest data
3. top ten queries most frequently called
my friend has done some script to find the above, but he his not with our company, and no tracablility, I hope some of you would have done the same excercise, please if any one knows how to extract the above three quires please from the *.trc file please help me, It will be really helpful for me, because I need to give the same by tomorrow, but no clues, I would appreciate, any kind of advice and suggestion , help provided
I am realy sorry, actualy I am not lazzy, the thing is I am very busy with other work, and if the wheel is invented why to invent again that is the reason we are sharing the knowledge here, I can do the thing what I want but it needs some time and as i said I am busy in other things, I am sorry if you have mistaken me.