Forum Stats

  • 3,855,648 Users
  • 2,264,539 Discussions
  • 7,906,128 Comments

Discussions

Oracle Job running slow vs sqlplus fast

perecd
perecd Member Posts: 7 Blue Ribbon
edited Oct 29, 2017 3:40AM in Scheduler

Hi guys, I've got a problem with one of the job, so any help would be great.  I created a procedure that performs inserts as select operation.  When I run this procedure in sql Plus(exec stat.copy_stt;), it takes about 30 seconds.  If the oracle jobs scheduler runs it takes about 25 minutes. When i digged deeper I saw write complete waits with this job.  Why is this job running hunging ?
Oracle DB ver.12.1.0.2

Tagged:

Answers

  • GregV
    GregV Member Posts: 3,099 Gold Crown
    edited Sep 15, 2017 11:15AM

    Hi,

    Have you checked the all_scheduler_job_run_details view to know the RUN_DURATION of your job? There's no particular reason for the job to insert slower. What does the job wait for when it's running? Check through SQL Developer or with v$session_wait while it runs.

  • perecd
    perecd Member Posts: 7 Blue Ribbon
    edited Sep 16, 2017 11:48AM

    I've checked the all_scheduler_job_run_details view. As I mentioned earlier the main wait is write complete waits. But the same procedure is  completed in 20 seconds via sqlplus. Inside the procedure is the following code: insert /*+ append */ into s_gtt select * from s_f2_gtt, where s_f2_gtt is a view from 2 tables.

  • GregV
    GregV Member Posts: 3,099 Gold Crown
    edited Sep 18, 2017 3:05AM

    Hi,

    Do you run several concurrent instances of this job? The write complete waits event could come from the fact that you insert with the APPEND hint, hence if several jobs run the same insert they'll "block" one another till they commit.

  • perecd
    perecd Member Posts: 7 Blue Ribbon
    edited Sep 19, 2017 1:15PM

    No, I don't. It is only one job. I tried  witout APPEND hint but result was the same. Inside view s_f2_gtt I've got a group by expression.

    This is a part of  select * from v$session_wait

      SID  SEQ#  EVENT                            P1TEXT            P1        P1RAW            

    1972 899    write complete waits  file#                204     00000000000000CC

    P1 is a temp file. Is there smth wrong with the temp files? They are on SSD disks.

  • GregV
    GregV Member Posts: 3,099 Gold Crown
    edited Sep 20, 2017 3:14AM

    Hi,

    Does it work in other DB versions? Maybe it's a bug related to the 12.1.0.2 version. If you can, open an SR to seek Oracle support's help.

  • berx
    berx Member Posts: 219 Silver Badge
    edited Oct 29, 2017 3:40AM

    you can enable SQL_TRACE at the beginning of your procedure (and disable it at the end).
    With these traces and your prefered profiler you will identify where the time was spent.

This discussion has been closed.