2

I would like to ask for some help with a query in Oracle Database.

I have a massive select with multiple tables joined together (over 10) and multiple where clauses applied (10-20). Some tables have 10 columns, some has 300+. Most tables have 10+ million rows, some of them even 60+ million.

The execution time is usually between 25 and 45 minutes, sometimes it drops to 30 seconds. Monitoring the server load shows, that the load was almost the same.

We would like to optimize the select to reduce the usual execution time to 10-15 minutes or less.

My question is: Is there any tool or technique which can provide me information about which part of the query ran so long (like something, that can show me that in the last execution of the query, the 1st join took 36 secs, the 2nd join 40 secs, 1st where clause 10 secs etc.)?

(Note, that i'm not asking for optimization advice but for any tool or technique which can provide me information about which part/operation of executed query took so long)

Thanks in advance, I hope I was clear! :)

Patrik Szpisják
  • 82
  • 7
  • One place to start is by stashing the execution plans to see if the same plan is being run when it take 25-45 minutes as when it takes 30 seconds. Are the parameters the same in those two cases? – Gordon Linoff Mar 07 '17 at 15:23
  • The parameters are the same both time, the execution plan is also similar in both cases. The server load looks the same, tables were not locked by any other queries or processes. Moreover, the processed row count does not differ and the result are correct both of the time. – Patrik Szpisják Mar 07 '17 at 15:29
  • There is no such tool but you can always check AWR report and check whihc query is taking more time. – XING Mar 07 '17 at 15:41
  • Trace (event 10046) + tkprof are your friends. There may be quite a few reasons for the query to sometimes work 'faster' - blocks are already in the buffer cache, SQL statement is already in the shared pool and execution plan stored in the library cache is good for that execution... But trace file will tell you exactly what is going on for every execution. – Goran Stefanović Mar 07 '17 at 15:41
  • @XING There are at least two ways to get that information, I'm not sure why they're not popular. See the answer below. – Jon Heller Mar 07 '17 at 16:42
  • @Jon Heller. OP is looking for tools not oracle features. – XING Mar 07 '17 at 17:03
  • If it's really talking very long, you can monitor progress for each execution plan step in `v$session_longops`. But that only reports steps that exceed a certain threshold, so if you have many, many very short steps in the execution plan they will never show up. –  Mar 07 '17 at 19:22

1 Answers1

3

One option is to do the following:

  1. add /*+ gather_plan_statistics */ to your query
  2. execute the query
  3. after the query, select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

This gives you a plan with columns like actual rows, actual time, memory usage, and more.

If you don't want to re-run the query you can generate actual rows and times of the last execution using a SQL Monitor Report like this:

select dbms_sqltune.report_sql_monitor(sql_id => ' add the sql_id here') from dual;

Using these tools allow you to focus on the relevant operation. A plain old explain plan isn't good enough for complex queries. AWR doesn't focus on individual queries. And tracing is a huge waste of time when there are faster alternatives.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
unleashed
  • 771
  • 3
  • 9
  • For an Arsenal fan, thats a well constructed answer ;) – BobC Mar 07 '17 at 16:42
  • In fact, an ACTIVE SQL Monitor report would be the best tool to start diagnosing the issue. – BobC Mar 07 '17 at 16:44
  • @The Grand Duke. The report_sql_monitor did not work, the output was a CLOB and only contained this sentence: 'SQL Monitoring Report'. However the gather_plan_statistics hint was perfect for retrieving the actual usage of each step. – Patrik Szpisják Mar 08 '17 at 13:03
  • 1
    This was actually and edit I did not add. But, For report_sql_monitor, you'll want to spool the CLOB to a file. There are some additional options for the output file, I think the default is html. Go here for some more detail and an example.... https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1 – unleashed Mar 08 '17 at 14:31
  • For SQL Monitor, you want to pick the ACTIVE format, not HTML (even though the output is still an html file) – BobC Mar 08 '17 at 16:33