Home » RDBMS Server » Performance Tuning » Performance Tuning (Oracle 10g,Unix)
Performance Tuning [message #302421] Mon, 25 February 2008 10:26 Go to next message
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 #302429 is a reply to message #302421] Mon, 25 February 2008 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Re: Performance Tuning [message #302452 is a reply to message #302421] Mon, 25 February 2008 15:09 Go to previous messageGo to next message
alanm
Messages: 282
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 Go to previous messageGo to next message
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 #302685 is a reply to message #302674] Tue, 26 February 2008 10:56 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

what user are you logged in as? SYS, SYSTEM or another user that doesn't have access to the v$ tables


regards

Alan
Re: Performance Tuning [message #302711 is a reply to message #302685] Tue, 26 February 2008 13:55 Go to previous messageGo to next message
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 #302850 is a reply to message #302711] Wed, 27 February 2008 03:08 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

ok,
what operating system are you running on

Windows or unix?

if windows

click on start, then run then type in cmd and hit enter.
at the command prompt type in sqlplus /nolog

or

unix

are your using ssh or telnet

I normally use a utility called putty to connect to unix servers

from the unix prompt (logged in as the oracle user) type in sqlplus /nolog

either way, can you let me know which Oracle database version you are running

regards

Alan
Re: Performance Tuning [message #303271 is a reply to message #302850] Thu, 28 February 2008 10:06 Go to previous messageGo to next message
skooman
Messages: 912
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?
Re: Performance Tuning [message #303317 is a reply to message #303271] Thu, 28 February 2008 21:08 Go to previous message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
thanks
done it works
Previous Topic: trace-strange case
Next Topic: slow pc startup after 10g
Goto Forum:
  


Current Time: Fri Dec 09 09:53:01 CST 2016

Total time taken to generate the page: 0.11353 seconds