Skip to Main Content
  • Questions

  • Monitoring parallel excution of FULL table scan

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mette.

Asked: March 20, 2019 - 1:51 pm UTC

Last updated: March 20, 2019 - 4:36 pm UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hi

I'm on 12.2 EE on Win 2016

I have the following SQL which selects from a 550 GB table (yes, it is GB due to massive GDPR logging)

create table GFAUDIT.fga_log$_kopi_201809 as
select /*+ PARALLEL (8)*/ (select instance_name from v$instance@ptia1000) db_instance_name
, (select version from v$instance@ptia1000) db_version
,   SESSIONID
  , TIMESTAMP#
  , DBUID
  , OSUID
  , OSHST
  , CLIENTID
  , EXTID
  , OBJ$SCHEMA
  , OBJ$NAME
  , POLICYNAME
  , SCN
  , SQLTEXT
  , LSQLTEXT
  , SQLBIND
  , COMMENT$TEXT
  , STMT_TYPE
  , NTIMESTAMP#
  , PROXY$SID
  , USER$GUID
  , INSTANCE#
  , PROCESS#
  , XID
  , AUDITID
  , STATEMENT
  , ENTRYID
  , DBID
  , LSQLBIND
  , OBJ$EDITION
  , RLS$INFO
  , CURRENT_USER
 from sys.fga_log$_v@ptia1000 a 
 where a.NTIMESTAMP# >= date '2018-09-01' and a.NTIMESTAMP# < date'2018-10-01' 
;


It creates the table on a remote DB over a DBLINK (same site)

But my question is - how do I monitor it - I can see the slaves using long obs - but I have no idea when it will finish when using parallel. I cant seem to find any info on how many granules are left - og progress of the query.

I have looked in various v$px views on both target and source DB with no luck.

Can you guide me where to look for relevant info

best regards Mette

and Chris said...

If you're licensed for Diagnostics & Tuning, the best place to look is the SQL Monitor. This automatically captures parallel executions. And updates in real time.

Otherwise look at see https://asktom.oracle.com/pls/apex/asktom.search?tag=ctas-dblink-parallel-hints

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

No Real Time Monitoring

Mette Stephansen, March 20, 2019 - 3:02 pm UTC

I only have SQL - and LONG OPS (whic is fine when not using PARALELL hint).

I'm looging for a view to give me overview of how far we are by now - ie. 105 of ouf 258 granules)

I have looked in many V$ view by now - but in vain.

I have the job running now (and the next 16-20 hours)

Chris Saxon
March 20, 2019 - 4:36 pm UTC

And did you read what's in the link?

To Mette

J. Laurindo Chiappa, March 20, 2019 - 4:57 pm UTC

Afaik you can monitor Parallel SQL via the (G)V$PXnnn views (see https://gerardnico.com/db/oracle/parallel_monitoring#the_g_v_parallel_execution_views for an example), and you can use the SESSION and SYSTEM statistics (ie, (G)V$SESSTAT and (G)V$SYSSTAT), too : look at the docs for refs for session and system statistics...Beside that, try the Parallel-Query specifi views, ir, (G)V$PQ_nnn views, see https://blogs.sap.com/2014/02/10/oracle-troubleshooting-parallel-executions-px-what-the-heck-why-is-the-sql-not-executed-in-parallel/ for a PQ debug case with them....

Regards,

Chiappa

OBS :

1. sometimes, a dblink can be a Major factor in decreasing performance, due to network issues : PLEASE try the same logic directly in the database where the large data resides and see if you get a better performance

2. when you use a dblink, the RDBMS can choose to execute the SQL here at the local database (getting the data via network) OR it can choose to send the SQL to the remote database, run the SQL there and later receive the processed data : IF you are consulting the Parallel-related views here at the local database and you are not seeeing anything, maybe the SQL is being executed there ? Please consult the Parallel (and session, and system) related views IN THE REMOTE DATABASE, too...


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.