Home » SQL & PL/SQL » SQL & PL/SQL » PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process
icon7.gif  PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199830] Thu, 26 October 2006 07:10 Go to next message
conie
Messages: 3
Registered: October 2006
Location: Philippines
Junior Member
Hi there I am new to oracle programming.Im working on one project that is scorecard generation.The procedure was created already an all I have to do is the revision.However, When Im testing the code the procedure runs for 3 hours considering it is only processing a hundreds records.I dont know where is the possible bottleneck in the code.Please check the attached code and hope you can advise me where is the possible poor implementation of the code . Thanks in advance and more power to all!
Re: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199845 is a reply to message #199830] Thu, 26 October 2006 07:43 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Propably alot of code i guess, hopefullt the guys find something Smile

But for the meantime
What you could do is to see "what takes time"
use dbms_profiler so we can get an idea of exactly what is taking time.

Installing dbms_profiler
http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php

then
what you do is in sqlplus

exec DBMS_PROFILER.START_PROFILER('TEST')
exec your procedure
exec DBMS_PROFILER.STOP_PROFILER
@profsum.sql <--attached (script from asktom)

profsum will give you a nice summary display of how much time
"things" takes.

if you want to run once more you have to do the following
delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

to clear the statistics

for more info on profsum.sql
http://asktom.oracle.com/pls/ask/f?p=4950:8:15609121349358207385::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:490036823886


GL
  • Attachment: profsum.sql
    (Size: 7.18KB, Downloaded 246 times)

[Updated on: Thu, 26 October 2006 07:43]

Report message to a moderator

Re: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199861 is a reply to message #199845] Thu, 26 October 2006 08:20 Go to previous messageGo to next message
conie
Messages: 3
Registered: October 2006
Location: Philippines
Junior Member
Hi tahpush,

I was able able to create the sysnonym following the instruction at your given link.However when I was about to GRANT SELECT on the sysnonyms I created it gives
"ORA-00980: synonym translation is no longer valid
".According to google this means that the oracle
objects im refering to in my sysnonym no longer exists.Ummm, Im not using a dba account.Can you suggest an alternative solutions on that ?

Thanks a lot and God Bless!

Conie
Re: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199864 is a reply to message #199830] Thu, 26 October 2006 08:30 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hmm

1.Perhaps your DBA can help you out with the privs ?
Can you do an explain plan even ?

2.The other option would then be sql_trace with tkprof
http://www.adp-gmbh.ch/ora/tuning/tkprof/index.html

But you might run into priv. problems again.

3.Try to run the functions indvidually a see if any of them
is particullary slow.

4.Havent looked at your code but I hope someone does Wink
Re: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199872 is a reply to message #199861] Thu, 26 October 2006 09:09 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> show user
USER is "SCOTT"
SQL> create table test_syn as select * from all_objects;

Table created.

SQL> create synonym testsyn for test_syn;

Synonym created.

SQL> drop table test_syn purge;

Table dropped.

SQL> select count(*) from testsyn;
select count(*) from testsyn
                     *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid


SQL> create table test_syn as select * from all_objects;

Table created.

SQL> select count(*) from testsyn;

  COUNT(*)
----------
     49378

SQL>




Re: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199891 is a reply to message #199864] Thu, 26 October 2006 10:51 Go to previous messageGo to next message
conie
Messages: 3
Registered: October 2006
Location: Philippines
Junior Member
tahpush wrote on Thu, 26 October 2006 08:30
Hmm

1.Perhaps your DBA can help you out with the privs ?
Can you do an explain plan even ?

2.The other option would then be sql_trace with tkprof
http://www.adp-gmbh.ch/ora/tuning/tkprof/index.html

But you might run into priv. problems again.

3.Try to run the functions indvidually a see if any of them
is particullary slow.

4.Havent looked at your code but I hope someone does Wink


Hello,
May I ask, if is it possible for me to copy the trace file into my specified directory? Oh sorry if this question may look stupid but honestly im just starting to understand oracle.

Thanks again and God Bless!

Conie
Re: PROCEDURE RUNS for 3 HOURS with just a hundreds of records to process [message #199892 is a reply to message #199891] Thu, 26 October 2006 10:53 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Yes You can.

Previous Topic: Help on create trigger
Next Topic: procedure call from SQL*Plus in Korn Shell script
Goto Forum:
  


Current Time: Fri Dec 09 07:59:52 CST 2016

Total time taken to generate the page: 0.08725 seconds