1

Recently I noticed a lot of parallelized queries (create table as select...) which cause high library cache lock concurrancy.

In the following question Mihail stated out to beware of library cache locks in long running CTAS in the form

create table <new_table_name> parallel <partitioning_info> as select * from <old_table_name> where <filter>;

Faster way to load huge data warehouse table

So why is this? The ratio of hard parsing is very low. Is this a problem because all the sessions try to look up the execution plan in the library cache? I thought by soft parsing there is only a pin on the library cache object?

Community
  • 1
  • 1
SanHolo
  • 46
  • 6
  • The only time I can remember a CTAS causing a locking issue was some bugs with extremely high parallel degrees. You might want to check your parameters and see how high `PARALLEL` will go. It is probably a combination of RAC nodes * cpu_count (parameter) * parallel_threads_per_cpu (parameter). Or possibly the partitioning clause is creating a huge number of partitions. Could you add more information about the statements? – Jon Heller Feb 16 '16 at 04:03
  • Also, it would help to have real numbers for the waits. Re-run the statement, find the SQL_ID, and generate the SQL Monitoring report for it by running `select dbms_sqltune.report_sql_monitor(sql_id => '$ADD_SQL_ID_HERE`) from dual;`. Add the entire contents of that report to the question. – Jon Heller Feb 16 '16 at 04:04

1 Answers1

0

Check V$SQLAREA to see whether there are SQL statements with a relatively high number of parse calls or a high number of child cursors (column VERSION_COUNT). Check parse statistics in V$SYSSTAT and their corresponding rate for each second.

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7