| Performance Tuning [message #302421] |
Mon, 25 February 2008 10:26  |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Our record size is 1 million.
when i am running one procedure it take 2 hours to complete.
My question is:
I want a query that will help me to find the below problem.
I want to see the resource consume by the procedure during run.
and want to take the plan view of the procedure.
Thanks In advance.
|
|
|
|
|
|
| Re: Performance Tuning [message #302452 is a reply to message #302421] |
Mon, 25 February 2008 15:09  |
alanm
Messages: 284
Registered: March 2005
|
Senior Member |
|
|
hi,
try the following 2 scripts
PROMPT
PROMPT ##############################################################
PROMPT problem sql scripts. 5 worst sql statements.
prompt split into 2 sections
prompt section 1 = buffer gets
prompt section 2 = disk reads
PROMPT ##############################################################
PROMPT
select c.* from
(select disk_reads,
buffer_gets,
rows_processed,
executions,
-- address,
-- hash_value,
first_load_time,
sql_text
from v$sqlarea
where parsing_user_id !=0
order by
buffer_gets/decode(executions,null,1,0,1,executions) desc ) c
where rownum < 5;
select c.* from
(select disk_reads,
buffer_gets,
rows_processed,
executions,
first_load_time,cpu_time,
sql_text
from v$sqlarea
order by
disk_reads/decode(rows_processed,null,1,0,1,rows_processed) desc ) c
where rownum < 5;
cheers
Alan
|
|
|
|
| Re: Performance Tuning [message #302674 is a reply to message #302452] |
Tue, 26 February 2008 09:30 |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Thanks for help . But when i m trying to execute this query i am getting the error as shown below
select c.* from
(select disk_reads,
buffer_gets,
rows_processed,
executions,
first_load_time,cpu_time,
sql_text
from v$sqlarea
order by
disk_reads/decode(rows_processed,null,1,0,1,rows_processed) desc ) c
where rownum < 5
ORA-00942: table or view does not exist
But when i m trying to execute this query i am getting the error as shown above
|
|
|
|
|
|
| Re: Performance Tuning [message #302711 is a reply to message #302685] |
Tue, 26 February 2008 13:55 |
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
|
Senior Member |
|
|
Hi Alanm,
Thanks for quick reply.
As i m new to oracle.. i know basic about the oracle when the question comes of complex queries i will get little nervous..
can u please guide me how can i bec the good oracle guy.
Thanks In advance
|
|
|
|
|
|
| Re: Performance Tuning [message #303271 is a reply to message #302850] |
Thu, 28 February 2008 10:06 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Perhaps it would be good to take a step back: what sort of database is this? Development or so I hope?
And since you're new to Oracle, what are you planning to do once you've found the "guilty" statement?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
