It seems the loading of Run Log records doesn't page like the Data tab does for Tables. I suspect it takes a very long time to load because it's fetching all the rows.
It shouldn't be. You don't have a filter set on the grid, do you? Grid filters are client-side, so that could cause it to fetch all rows.
No filters are set as far as I can tell. Where can I view the data grid filters?
I only see the Clear all data grid filters button which doesn't tell me if there is an active filter.
I don't have any column sorting checked.
Hi Raymond,
I just meant like a column header filter. Seems like you don't have any. ANother thing you can do is turn on spool sql, grab the sql text that we are running here and see how the runtime compares in the editor.
Re: sorting - that "sorted" checkbox only sorts the items in that popup. So you could find column names more quickly. It does not apply to "order by" clauses.
Looks like there is a sort on log_id even though I don't see it in the column header.
select l.log_id "Log ID",
l.log_date "Log Date",
L.operation "Operation",
L.STATUS "Status", L.user_name "User Name", L.CLIENT_ID "Client ID", L.global_uid "Global UID",
R.REQ_START_DATE "Required Start Date",
R.ACTUAL_START_DATE "Actual Start Date",
R.RUN_DURATION "Run Duration",
R.INSTANCE_ID "Instance ID", R.SESSION_ID "Session ID", R.SLAVE_PID "Slave PID",
to_char(R.CPU_USED) "CPU Used", R.ADDITIONAL_INFO "Additional Info (Run)"
, R.Errors "Errors", R.Output "Output"
from DBA_SCHEDULER_JOB_LOG L, DBA_SCHEDULER_JOB_RUN_DETAILS R
where l.Owner = :o
and l.job_name = :nm
and l.log_id = r.log_id (+)
order by 1 desc;
Yes, that order by is hard-coded. You want the items in order, right?
Hm....we might have a workaround here.
From the code, I see that we remember a column to order by, if you click a column header. If you want to remove the order by, you could
- Close Toad
- Edit Toad.ini with notepad
- find "RunLogOrderBy" in the [TfrmSchedulerJobObject] section.
- Change it so it says:
[TfrmSchedulerJobObject]
RunLogOrderBy=order by null
That seems to be the same as not having an order by clause (at least according to explain plan)
Note 1: if you try to clear it with RunLogOrderBy=, then we'll just add "order by 1 desc" since it's the default value. So don't do that.
Note 2: Add this section and value if it doesn't exist.
I agree it's not very useful without the sort to see the most recent first. The explain plan for that query shows a cost of 787K!
Maybe it's time to update data dictionary stats and/or purge the run log?
Yeah, I was just thinking we need to purge. I'll bring it up with the DBA team.
The DBA_SCHEDULER_JOB_LOG view has 54.6 million records.
It doesn't look like a Toad issue. Thanks.
1 Like
54,6 million rows. WOW! I wonder for how long a period that was.
Cheers,
Russ
Took about 5 minutes to run the query. It's a test environment so it doesn't get enough attention when it comes to housekeeping.
Hi, I am having a similar issue in Toad 16.3.231.2085.
When going into Scheduler Job -> Run Log, toad just freezes up until it loads the data for a particular job, sometimes can take minutes. Myself and a colleague tried to troubleshoot what causes the wait time and we pinpointed that the query used is using a bad explain plan. My colleague uses Toad 16.2 and the only difference in the query is that in 16.3 we bring back "Subname" for the run log. If I take the query that my Toad runs and remove the Subname column I get a response back in couple seconds, compared to couple min if Subname is included.
Query ran on 16.2
SELECT
l.log_id "Log ID",
l.log_date "Log Date",
L.operation "Operation",
L.STATUS "Status",
L.user_name "User Name",
L.CLIENT_ID "Client ID",
L.global_uid "Global UID",
R.REQ_START_DATE "Required Start Date",
R.ACTUAL_START_DATE "Actual Start Date",
R.RUN_DURATION "Run Duration",
R.INSTANCE_ID "Instance ID",
R.SESSION_ID "Session ID",
R.SLAVE_PID "Slave PID",
TO_CHAR (R.CPU_USED) "CPU Used",
R.ADDITIONAL_INFO "Additional Info (Run)",
R.Errors "Errors",
R.Output "Output"
FROM DBA_SCHEDULER_JOB_LOG L, DBA_SCHEDULER_JOB_RUN_DETAILS R
WHERE l.Owner = :o AND l.job_name = :nm AND l.log_id = r.log_id(+)
ORDER BY 1 DESC
Query ran on 16.3
SELECT
l.log_id "Log ID",
l.log_date "Log Date",
L.operation "Operation",
L.job_subname "Subname",
L.STATUS "Status",
L.user_name "User Name",
L.CLIENT_ID "Client ID",
L.global_uid "Global UID",
R.REQ_START_DATE "Required Start Date",
R.ACTUAL_START_DATE "Actual Start Date",
R.RUN_DURATION "Run Duration",
R.INSTANCE_ID "Instance ID",
R.SESSION_ID "Session ID",
R.SLAVE_PID "Slave PID",
TO_CHAR (R.CPU_USED) "CPU Used",
R.ADDITIONAL_INFO "Additional Info (Run)",
R.Errors "Errors",
R.Output "Output"
FROM DBA_SCHEDULER_JOB_LOG L, DBA_SCHEDULER_JOB_RUN_DETAILS R
WHERE l.Owner = :o AND l.job_name = :nm AND l.log_id = r.log_id(+)
ORDER BY 1 DESC
Thanks,
Adrian
Huh.
I've never seen the addition of a column in a select statement have that much impact before. For me, the SQL returns nearly instantly.
For what it's worth, Toad has nothing to do with the execution of the SQL - we send the statement to Oracle, and Oracle is responsible for selecting an execution plan and carrying it out.
If your Oracle license includes the Tuning and Diagnostic pack, try this:
- Go to main menu -> database -> optimize -> Oracle tuning advisor
- Click "New Tuning Task" and Paste in the offending SQL (below). Be sure that the spacing and line feeds are correct and didn't get mangled by this forum. Then click OK.
select l.log_id "Log ID",
l.log_date "Log Date",
L.operation "Operation",
L.job_subname "Subname",
L.STATUS "Status", L.user_name "User Name", L.CLIENT_ID "Client ID", L.global_uid "Global UID",
R.REQ_START_DATE "Required Start Date",
R.ACTUAL_START_DATE "Actual Start Date",
R.RUN_DURATION "Run Duration",
R.INSTANCE_ID "Instance ID", R.SESSION_ID "Session ID", R.SLAVE_PID "Slave PID",
to_char(R.CPU_USED) "CPU Used", R.ADDITIONAL_INFO "Additional Info (Run)",
R.Errors "Errors", R.Output "Output"
from DBA_SCHEDULER_JOB_LOG L, DBA_SCHEDULER_JOB_RUN_DETAILS R
where l.Owner = :o
and l.job_name = :nm
and l.log_id = r.log_id (+)
order by 1 desc;
- For me, Oracle took a few minutes to come up with a recommendation. WHen it did, it looked like the below.
- Click on the "Script" tab and then Run to accept the SQL Profile that Oracle suggested.
- Go back to Schema Browser -> Scheduler Jobs and see how it behaves now.
- If you decide that it's worse and you want to delete the SQL Profile, you can do that here:
If your Oracle license does NOT include the Tuning and Diagnostic pack, I would suggest reproducing the problem in SQL*Plus and taking the case to Oracle. They should be able to help. There really isn't anything I can offer except taking the column back out, but then the customer who requested it would be annoyed at me.