Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g (Oracle 12.1.)
| Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g [message #665240] |
Sun, 27 August 2017 12:25  |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
We are noticing that large table inserts with CLOB is taking more time on 12c than 11g. The inserts are during an online transaction processing where we copy data from one business unit to another. There is one table in particular that is taking considerably longer. To debug this, I created a simple Insert script on a generic table
SET LINESIZE 200
SET SERVEROUTPUT ON
set timing on
set time on
set echo on
SELECT SYSTIMESTAMP FROM DUAL;
column PRODUCT format a45
column version format a12
SELECT product,version FROM PRODUCT_COMPONENT_VERSION where upper(Product) like 'ORACLE%' ;
CREATE TABLE TMP_TEST_LARGE_ROWS_CLOB
(ID NUMBER, CLOB_VALUE CLOB, RANDOM_STRING VARCHAR2(100));
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO TMP_TEST_LARGE_ROWS_CLOB
(id, clob_value,random_string)
select
i*rownum id,
DBMS_RANDOM.string ('B', 500),
DBMS_RANDOM.string('A', 20)
from dual
CONNECT BY rownum <= 1000 ;
COMMIT;
END LOOP;
END ;
/
Here are the results in both 11g and 12c.
11g
21:49:41 SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
26-AUG-17 09.49.41.912231 PM -07:00
Elapsed: 00:00:00.02
21:49:41 SQL>
21:49:41 SQL> column PRODUCT format a45
21:49:41 SQL> column version format a12
21:49:41 SQL> SELECT product,version FROM PRODUCT_COMPONENT_VERSION where upper(Product) like 'ORACLE%' ;
PRODUCT VERSION
--------------------------------------------- ------------
Oracle Database 11g Enterprise Edition 11.2.0.3.0
Elapsed: 00:00:00.10
21:49:42 SQL>
21:49:42 SQL>
21:49:42 SQL> CREATE TABLE TMP_TEST_LARGE_ROWS_CLOB
21:49:42 2 (ID NUMBER, CLOB_VALUE CLOB, RANDOM_STRING VARCHAR2(100));
Table created.
Elapsed: 00:00:00.26
21:49:42 SQL>
21:49:42 SQL> BEGIN
21:49:42 2 FOR i IN 1..1000 LOOP
21:49:42 3 INSERT INTO TMP_TEST_LARGE_ROWS_CLOB
21:49:42 4 (id, clob_value,random_string)
21:49:42 5 select
21:49:42 6 i*rownum id,
21:49:42 7 DBMS_RANDOM.string ('B', 500),
21:49:42 8 DBMS_RANDOM.string('A', 20)
21:49:42 9 from dual
21:49:42 10 CONNECT BY rownum <= 1000 ;
21:49:42 11 COMMIT;
21:49:42 12 END LOOP;
21:49:42 13 END ;
21:49:42 14 /
PL/SQL procedure successfully completed.
Elapsed: 00:14:47.99
22:04:30 SQL>
22:04:30 SQL> spool off
12c
21:51:10 SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
26-AUG-17 09.51.10.638013 PM -07:00
Elapsed: 00:00:00.00
21:51:10 SQL>
21:51:10 SQL> column PRODUCT format a45
21:51:10 SQL> column version format a12
21:51:10 SQL> SELECT product,version FROM PRODUCT_COMPONENT_VERSION where upper(Product) like 'ORACLE%' ;
PRODUCT VERSION
--------------------------------------------- ------------
Oracle Database 12c Enterprise Edition 12.1.0.2.0
Elapsed: 00:00:00.01
21:51:10 SQL>
21:51:10 SQL>
21:51:10 SQL> CREATE TABLE TMP_TEST_LARGE_ROWS_CLOB
21:51:10 2 (ID NUMBER, CLOB_VALUE CLOB, RANDOM_STRING VARCHAR2(100));
Table created.
Elapsed: 00:00:00.02
21:51:10 SQL>
21:51:10 SQL> BEGIN
21:51:10 2 FOR i IN 1..1000 LOOP
21:51:10 3 INSERT INTO TMP_TEST_LARGE_ROWS_CLOB
21:51:10 4 (id, clob_value,random_string)
21:51:10 5 select
21:51:10 6 i*rownum id,
21:51:10 7 DBMS_RANDOM.string ('B', 500),
21:51:10 8 DBMS_RANDOM.string('A', 20)
21:51:10 9 from dual
21:51:10 10 CONNECT BY rownum <= 1000 ;
21:51:10 11 COMMIT;
21:51:10 12 END LOOP;
21:51:10 13 END ;
21:51:10 14 /
PL/SQL procedure successfully completed.
Elapsed: 00:46:05.83
22:37:16 SQL> spool off
Again, this is just an example, just to show that creating a million rows in a loop is considerably slower in 12c than 11g. The actual table where we are facing:
- Has FK references and hence cannot use APPEND hint. Also has a couple of triggers
- The table is updated in multiple ways from the UI - either 1 or 2 rows inserts or during a business unit copy , where records are inserted in BULK using Insert... select ... clause.
Additional Info:
We want to migrate to 12c asap and do not want to change a lot of such Inserts, which are inside various PL/SQL package procedures.
Any help or pointers on why it is taking more time would be appreciated.
In our test instances where I got the above results, the 12c instance has more memory and sga size
12c:
sga_max_size 85899345920
sga_target 85899345920
11g:
sga_max_size 10737418240
sga_target 10737418240
- Ramesh
Rkrishna@yahoo.com
|
|
|
|
|
|
| Re: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g [message #665242 is a reply to message #665241] |
Sun, 27 August 2017 13:00  |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
We are in the verge of making the decision to upgrade to 12c, and this extremely slow performance on Inserts is a major blocker. The expectation is that 12c should be at least as fast as 11g, if not better. I am looking for any suggestion on whether we need to change
any specific 12c parameters in order to get the same level of performance.
12c Parameters from v$parameter:
NAME VALUE
DBFIPS_140 FALSE
O7_DICTIONARY_ACCESSIBILITY FALSE
_b_tree_bitmap_plans FALSE
_index_join_enabled FALSE
_like_with_bind_as_equality TRUE
_optim_peek_user_binds FALSE
_optimizer_aggr_groupby_elim FALSE
_optimizer_ansi_join_lateral_enhance FALSE
_optimizer_autostats_job FALSE
_optimizer_connect_by_cost_based TRUE
_optimizer_max_permutations 2000
_optimizer_mjc_enabled FALSE
_optimizer_push_pred_cost_based FALSE
_optimizer_sortmerge_join_enabled FALSE
_session_cached_instantiations 601
_trace_files_public TRUE
active_instance_count
aq_tm_processes 1
archive_lag_target 0
asm_diskgroups
asm_diskstring
asm_power_limit 1
asm_preferred_read_failure_groups
audit_file_dest /opt/oracle/product/12.1/rdbms/audit
audit_sys_operations TRUE
audit_syslog_level
audit_trail DB
awr_snapshot_time_offset 0
background_core_dump partial
background_dump_dest /opt/oracle/product/12.1/rdbms/log
backup_tape_io_slaves FALSE
bitmap_merge_area_size 1048576
blank_trimming TRUE
buffer_pool_keep
buffer_pool_recycle
cell_offload_compaction ADAPTIVE
cell_offload_decryption TRUE
cell_offload_parameters
cell_offload_plan_display AUTO
cell_offload_processing TRUE
cell_offloadgroup_name
circuits
client_result_cache_lag 3000
client_result_cache_size 0
clonedb FALSE
cluster_database FALSE
cluster_database_instances 1
cluster_interconnects
commit_logging
commit_point_strength 1
commit_wait
commit_write
common_user_prefix C##
compatible 12.1.0
connection_brokers ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
control_file_record_keep_time 7
control_files /opt/oradata/loaddb/control01.ctl, /opt/oradata/loaddb/control02.ctl, /opt/oradata/loaddb/control03.ctl
control_management_pack_access DIAGNOSTIC+TUNING
core_dump_dest /opt/oracle/product/12.1/admin/diag/rdbms/loaddb/loaddb/cdump
cpu_count 16
create_bitmap_area_size 8388608
create_stored_outlines
cursor_bind_capture_destination memory+disk
cursor_sharing EXACT
cursor_space_for_time FALSE
db_16k_cache_size 0
db_2k_cache_size 0
db_32k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_big_table_cache_percent_target 0
db_block_buffers 0
db_block_checking FALSE
db_block_checksum TYPICAL
db_block_size 16384
db_cache_advice ON
db_cache_size 0
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_domain
db_file_multiblock_read_count 64
db_file_name_convert
db_files 500
db_flash_cache_file
db_flash_cache_size 0
db_flashback_retention_target 1440
db_index_compression_inheritance NONE
db_keep_cache_size 0
db_lost_write_protect NONE
db_name loaddb
db_performance_profile
db_recovery_file_dest
db_recovery_file_dest_size 0
db_recycle_cache_size 0
db_securefile PREFERRED
db_ultra_safe OFF
db_unique_name loaddb
db_unrecoverable_scn_tracking TRUE
db_writer_processes 20
dbwr_io_slaves 0
ddl_lock_timeout 0
deferred_segment_creation TRUE
dg_broker_config_file1 /opt/oracle/product/12.1/dbs/dr1loaddb.dat
dg_broker_config_file2 /opt/oracle/product/12.1/dbs/dr2loaddb.dat
dg_broker_start FALSE
diagnostic_dest /opt/oracle/product/12.1/admin
disk_asynch_io TRUE
dispatchers
distributed_lock_timeout 60
dml_locks 23232
dnfs_batch_size 4096
dst_upgrade_insert_conv TRUE
enable_ddl_logging FALSE
enable_goldengate_replication FALSE
enable_pluggable_database FALSE
event
exclude_seed_cdb_view TRUE
fal_client
fal_server
fast_start_io_target 0
fast_start_mttr_target 0
fast_start_parallel_rollback LOW
file_mapping FALSE
fileio_network_adapters
filesystemio_options setall
fixed_date
gcs_server_processes 0
global_context_pool_size
global_names FALSE
global_txn_processes 1
hash_area_size 131072
heat_map OFF
hi_shared_memory_address 0
hs_autoregister TRUE
ifile
inmemory_clause_default
inmemory_force DEFAULT
inmemory_max_populate_servers 0
inmemory_query ENABLE
inmemory_size 0
inmemory_trickle_repopulate_servers_percent 1
instance_groups
instance_name loaddb
instance_number 0
instance_type RDBMS
instant_restore FALSE
java_jit_enabled TRUE
java_max_sessionspace_size 0
java_pool_size 0
java_restrict none
java_soft_sessionspace_limit 0
job_queue_processes 100
large_pool_size 0
ldap_directory_access NONE
ldap_directory_sysauth no
license_max_sessions 0
license_max_users 0
license_sessions_warning 0
listener_networks
local_listener
lock_name_space
lock_sga FALSE
log_archive_config
log_archive_dest
log_archive_dest_1
log_archive_dest_10
log_archive_dest_11
log_archive_dest_12
log_archive_dest_13
log_archive_dest_14
log_archive_dest_15
log_archive_dest_16
log_archive_dest_17
log_archive_dest_18
log_archive_dest_19
log_archive_dest_2
log_archive_dest_20
log_archive_dest_21
log_archive_dest_22
log_archive_dest_23
log_archive_dest_24
log_archive_dest_25
log_archive_dest_26
log_archive_dest_27
log_archive_dest_28
log_archive_dest_29
log_archive_dest_3
log_archive_dest_30
log_archive_dest_31
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6
log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
log_archive_dest_state_1 enable
log_archive_dest_state_10 enable
log_archive_dest_state_11 enable
log_archive_dest_state_12 enable
log_archive_dest_state_13 enable
log_archive_dest_state_14 enable
log_archive_dest_state_15 enable
log_archive_dest_state_16 enable
log_archive_dest_state_17 enable
log_archive_dest_state_18 enable
log_archive_dest_state_19 enable
log_archive_dest_state_2 enable
log_archive_dest_state_20 enable
log_archive_dest_state_21 enable
log_archive_dest_state_22 enable
log_archive_dest_state_23 enable
log_archive_dest_state_24 enable
log_archive_dest_state_25 enable
log_archive_dest_state_26 enable
log_archive_dest_state_27 enable
log_archive_dest_state_28 enable
log_archive_dest_state_29 enable
log_archive_dest_state_3 enable
log_archive_dest_state_30 enable
log_archive_dest_state_31 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_dest_state_6 enable
log_archive_dest_state_7 enable
log_archive_dest_state_8 enable
log_archive_dest_state_9 enable
log_archive_duplex_dest
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
log_archive_min_succeed_dest 1
log_archive_start FALSE
log_archive_trace 0
log_buffer 154861568
log_checkpoint_interval 10000
log_checkpoint_timeout 1800
log_checkpoints_to_alert FALSE
log_file_name_convert
max_dispatchers
max_dump_file_size unlimited
max_enabled_roles 150
max_shared_servers
max_string_size STANDARD
memory_max_target 0
memory_target 0
nls_calendar GREGORIAN
nls_comp BINARY
nls_currency $
nls_date_format DD-MON-RR
nls_date_language AMERICAN
nls_dual_currency $
nls_iso_currency AMERICA
nls_language AMERICAN
nls_length_semantics BYTE
nls_nchar_conv_excp FALSE
nls_numeric_characters .,
nls_sort BINARY
nls_territory AMERICA
nls_time_format HH.MI.SSXFF AM
nls_time_tz_format HH.MI.SSXFF AM TZR
nls_timestamp_format DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format DD-MON-RR HH.MI.SSXFF AM TZR
noncdb_compatible FALSE
object_cache_max_size_percent 10
object_cache_optimal_size 102400
olap_page_pool_size 524288000
open_cursors 600
open_links 4
open_links_per_instance 4
optimizer_adaptive_features FALSE
optimizer_adaptive_reporting_only FALSE
optimizer_capture_sql_plan_baselines FALSE
optimizer_dynamic_sampling 2
optimizer_features_enable 12.1.0.2
optimizer_index_caching 0
optimizer_index_cost_adj 100
optimizer_inmemory_aware TRUE
optimizer_mode FIRST_ROWS
optimizer_secure_view_merging FALSE
optimizer_use_invisible_indexes FALSE
optimizer_use_pending_statistics FALSE
optimizer_use_sql_plan_baselines TRUE
os_authent_prefix
os_roles FALSE
parallel_adaptive_multi_user TRUE
parallel_automatic_tuning FALSE
parallel_degree_level 100
parallel_degree_limit CPU
parallel_degree_policy MANUAL
parallel_execution_message_size 16384
parallel_force_local FALSE
parallel_instance_group
parallel_io_cap_enabled FALSE
parallel_max_servers 28
parallel_min_percent 0
parallel_min_servers 2
parallel_min_time_threshold AUTO
parallel_server FALSE
parallel_server_instances 1
parallel_servers_target 28
parallel_threads_per_cpu 2
pdb_file_name_convert
pdb_lockdown
pdb_os_credential
permit_92_wrap_format TRUE
pga_aggregate_limit 0
pga_aggregate_target 2231369728
plscope_settings IDENTIFIERS:NONE
plsql_ccflags
plsql_code_type INTERPRETED
plsql_debug FALSE
plsql_optimize_level 2
plsql_v2_compatibility FALSE
plsql_warnings DISABLE:ALL
pre_page_sga TRUE
processes 3500
processor_group_name
query_rewrite_enabled true
query_rewrite_integrity trusted
rdbms_server_dn
read_only_open_delayed FALSE
recovery_parallelism 0
recyclebin OFF
redo_transport_user
remote_dependencies_mode TIMESTAMP
remote_listener
remote_login_passwordfile EXCLUSIVE
remote_os_authent FALSE
remote_os_roles FALSE
replication_dependency_tracking TRUE
resource_limit TRUE
resource_manager_cpu_allocation 16
resource_manager_plan SCHEDULER[0x4DEEF79]:DEFAULT_MAINTENANCE_PLAN
result_cache_max_result 5
result_cache_max_size 16121856
result_cache_mode MANUAL
result_cache_remote_expiration 0
resumable_timeout 0
rollback_segments
sec_case_sensitive_logon TRUE
sec_max_failed_login_attempts 3
sec_protocol_error_further_action (DROP,3)
sec_protocol_error_trace_action TRACE
sec_return_server_release_banner FALSE
serial_reuse disable
service_names loaddb
session_cached_cursors 500
session_max_open_files 500
sessions 5280
sga_max_size 85899345920
sga_target 85899345920
shadow_core_dump partial
shared_memory_address 0
shared_pool_reserved_size 104857600
shared_pool_size 1610612736
shared_server_sessions
shared_servers 0
skip_unusable_indexes TRUE
smtp_out_server
sort_area_retained_size 0
sort_area_size 65536
spatial_vector_acceleration FALSE
spfile
sql92_security FALSE
sql_trace FALSE
sqltune_category DEFAULT
standby_archive_dest ?/dbs/arch
standby_file_management MANUAL
star_transformation_enabled FALSE
statistics_level TYPICAL
streams_pool_size 0
tape_asynch_io TRUE
temp_undo_enabled TRUE
thread 0
threaded_execution FALSE
timed_os_statistics 0
timed_statistics TRUE
trace_enabled TRUE
tracefile_identifier
transactions 5808
transactions_per_rollback_segment 5
undo_management AUTO
undo_retention 3500
undo_tablespace rbs
unified_audit_sga_queue_size 1048576
use_dedicated_broker FALSE
use_indirect_data_buffers FALSE
use_large_pages TRUE
user_dump_dest /opt/oracle/product/12.1/rdbms/log
utl_file_dir
workarea_size_policy auto
xml_db_events enable
11g Parameters from v$parameter:
NAME VALUE
O7_DICTIONARY_ACCESSIBILITY FALSE
_b_tree_bitmap_plans FALSE
_index_join_enabled FALSE
_like_with_bind_as_equality TRUE
_optim_peek_user_binds FALSE
_optimizer_autostats_job FALSE
_optimizer_connect_by_cost_based TRUE
_optimizer_cost_based_transformation off
_optimizer_max_permutations 1000
_optimizer_mjc_enabled FALSE
_optimizer_push_pred_cost_based FALSE
_optimizer_sortmerge_join_enabled FALSE
_session_cached_instantiations 601
_trace_files_public TRUE
active_instance_count
aq_tm_processes 1
archive_lag_target 0
asm_diskgroups
asm_diskstring
asm_power_limit 1
asm_preferred_read_failure_groups
audit_file_dest /opt/oracle/product/11.2/rdbms/audit
audit_sys_operations FALSE
audit_syslog_level
audit_trail DB
awr_snapshot_time_offset 0
background_core_dump partial
background_dump_dest /opt/oracle/product/11.2/admin/diag/rdbms/sanddb/sanddb/trace
backup_tape_io_slaves FALSE
bitmap_merge_area_size 1048576
blank_trimming TRUE
buffer_pool_keep
buffer_pool_recycle
cell_offload_compaction ADAPTIVE
cell_offload_decryption TRUE
cell_offload_parameters
cell_offload_plan_display AUTO
cell_offload_processing TRUE
circuits
client_result_cache_lag 3000
client_result_cache_size 0
clonedb FALSE
cluster_database FALSE
cluster_database_instances 1
cluster_interconnects
commit_logging
commit_point_strength 1
commit_wait
commit_write
compatible 11.2.0.3
control_file_record_keep_time 7
control_files /opt/oradata/sanddb/control01.ctl, /opt/oradata/sanddb/control02.ctl, /opt/oradata/sanddb/control03.ctl
control_management_pack_access DIAGNOSTIC+TUNING
core_dump_dest /opt/oracle/product/11.2/admin/diag/rdbms/sanddb/sanddb/cdump
cpu_count 24
create_bitmap_area_size 8388608
create_stored_outlines
cursor_bind_capture_destination memory+disk
cursor_sharing EXACT
cursor_space_for_time FALSE
db_16k_cache_size 0
db_2k_cache_size 0
db_32k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_block_buffers 0
db_block_checking FALSE
db_block_checksum TYPICAL
db_block_size 16384
db_cache_advice ON
db_cache_size 0
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_domain
db_file_multiblock_read_count 4
db_file_name_convert
db_files 500
db_flash_cache_file
db_flash_cache_size 0
db_flashback_retention_target 1440
db_keep_cache_size 0
db_lost_write_protect NONE
db_name sanddb
db_recovery_file_dest
db_recovery_file_dest_size 0
db_recycle_cache_size 0
db_securefile PERMITTED
db_ultra_safe OFF
db_unique_name sanddb
db_unrecoverable_scn_tracking TRUE
db_writer_processes 20
dbwr_io_slaves 0
ddl_lock_timeout 0
deferred_segment_creation TRUE
dg_broker_config_file1 /opt/oracle/product/11.2/dbs/dr1sanddb.dat
dg_broker_config_file2 /opt/oracle/product/11.2/dbs/dr2sanddb.dat
dg_broker_start FALSE
diagnostic_dest /opt/oracle/product/11.2/admin
disk_asynch_io TRUE
dispatchers
distributed_lock_timeout 60
dml_locks 36432
dst_upgrade_insert_conv TRUE
enable_ddl_logging FALSE
event
fal_client
fal_server
fast_start_io_target 0
fast_start_mttr_target 0
fast_start_parallel_rollback LOW
file_mapping FALSE
fileio_network_adapters
filesystemio_options setall
fixed_date
gcs_server_processes 0
global_context_pool_size
global_names FALSE
global_txn_processes 1
hash_area_size 131072
hi_shared_memory_address 0
hs_autoregister TRUE
ifile
instance_groups
instance_name sanddb
instance_number 0
instance_type RDBMS
java_jit_enabled TRUE
java_max_sessionspace_size 0
java_pool_size 0
java_soft_sessionspace_limit 0
job_queue_processes 100
large_pool_size 0
ldap_directory_access NONE
ldap_directory_sysauth no
license_max_sessions 0
license_max_users 0
license_sessions_warning 0
listener_networks
local_listener
lock_name_space
lock_sga FALSE
log_archive_config
log_archive_dest
log_archive_dest_1
log_archive_dest_10
log_archive_dest_11
log_archive_dest_12
log_archive_dest_13
log_archive_dest_14
log_archive_dest_15
log_archive_dest_16
log_archive_dest_17
log_archive_dest_18
log_archive_dest_19
log_archive_dest_2
log_archive_dest_20
log_archive_dest_21
log_archive_dest_22
log_archive_dest_23
log_archive_dest_24
log_archive_dest_25
log_archive_dest_26
log_archive_dest_27
log_archive_dest_28
log_archive_dest_29
log_archive_dest_3
log_archive_dest_30
log_archive_dest_31
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6
log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
log_archive_dest_state_1 enable
log_archive_dest_state_10 enable
log_archive_dest_state_11 enable
log_archive_dest_state_12 enable
log_archive_dest_state_13 enable
log_archive_dest_state_14 enable
log_archive_dest_state_15 enable
log_archive_dest_state_16 enable
log_archive_dest_state_17 enable
log_archive_dest_state_18 enable
log_archive_dest_state_19 enable
log_archive_dest_state_2 enable
log_archive_dest_state_20 enable
log_archive_dest_state_21 enable
log_archive_dest_state_22 enable
log_archive_dest_state_23 enable
log_archive_dest_state_24 enable
log_archive_dest_state_25 enable
log_archive_dest_state_26 enable
log_archive_dest_state_27 enable
log_archive_dest_state_28 enable
log_archive_dest_state_29 enable
log_archive_dest_state_3 enable
log_archive_dest_state_30 enable
log_archive_dest_state_31 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_dest_state_6 enable
log_archive_dest_state_7 enable
log_archive_dest_state_8 enable
log_archive_dest_state_9 enable
log_archive_duplex_dest
log_archive_format %t_%s_%r.dbf
log_archive_local_first TRUE
log_archive_max_processes 4
log_archive_min_succeed_dest 1
log_archive_start FALSE
log_archive_trace 0
log_buffer 264855552
log_checkpoint_interval 10000
log_checkpoint_timeout 1800
log_checkpoints_to_alert FALSE
log_file_name_convert
max_dispatchers
max_dump_file_size unlimited
max_enabled_roles 150
max_shared_servers
memory_max_target 0
memory_target 0
nls_calendar GREGORIAN
nls_comp BINARY
nls_currency $
nls_date_format DD-MON-RR
nls_date_language AMERICAN
nls_dual_currency $
nls_iso_currency AMERICA
nls_language AMERICAN
nls_length_semantics BYTE
nls_nchar_conv_excp FALSE
nls_numeric_characters .,
nls_sort BINARY
nls_territory AMERICA
nls_time_format HH.MI.SSXFF AM
nls_time_tz_format HH.MI.SSXFF AM TZR
nls_timestamp_format DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format DD-MON-RR HH.MI.SSXFF AM TZR
object_cache_max_size_percent 10
object_cache_optimal_size 102400
olap_page_pool_size 524288000
open_cursors 600
open_links 4
open_links_per_instance 4
optimizer_capture_sql_plan_baselines FALSE
optimizer_dynamic_sampling 2
optimizer_features_enable 11.2.0.3
optimizer_index_caching 0
optimizer_index_cost_adj 100
optimizer_mode FIRST_ROWS
optimizer_secure_view_merging FALSE
optimizer_use_invisible_indexes FALSE
optimizer_use_pending_statistics FALSE
optimizer_use_sql_plan_baselines TRUE
os_authent_prefix
os_roles FALSE
parallel_adaptive_multi_user TRUE
parallel_automatic_tuning FALSE
parallel_degree_limit CPU
parallel_degree_policy MANUAL
parallel_execution_message_size 16384
parallel_force_local FALSE
parallel_instance_group
parallel_io_cap_enabled FALSE
parallel_max_servers 960
parallel_min_percent 0
parallel_min_servers 0
parallel_min_time_threshold AUTO
parallel_server FALSE
parallel_server_instances 1
parallel_servers_target 384
parallel_threads_per_cpu 2
permit_92_wrap_format TRUE
pga_aggregate_target 2147483648
plscope_settings IDENTIFIERS:NONE
plsql_ccflags
plsql_code_type INTERPRETED
plsql_debug FALSE
plsql_optimize_level 2
plsql_v2_compatibility FALSE
plsql_warnings DISABLE:ALL
pre_page_sga FALSE
processes 5500
processor_group_name
query_rewrite_enabled true
query_rewrite_integrity trusted
rdbms_server_dn
read_only_open_delayed FALSE
recovery_parallelism 0
recyclebin OFF
redo_transport_user
remote_dependencies_mode TIMESTAMP
remote_listener
remote_login_passwordfile EXCLUSIVE
remote_os_authent FALSE
remote_os_roles FALSE
replication_dependency_tracking TRUE
resource_limit FALSE
resource_manager_cpu_allocation 24
resource_manager_plan SCHEDULER[0x4DEEF79]:DEFAULT_MAINTENANCE_PLAN
result_cache_max_result 5
result_cache_max_size 19136512
result_cache_mode MANUAL
result_cache_remote_expiration 0
resumable_timeout 0
rollback_segments
sec_case_sensitive_logon TRUE
sec_max_failed_login_attempts 10
sec_protocol_error_further_action CONTINUE
sec_protocol_error_trace_action TRACE
sec_return_server_release_banner FALSE
serial_reuse disable
service_names sanddb
session_cached_cursors 500
session_max_open_files 150
sessions 8280
sga_max_size 10737418240
sga_target 10737418240
shadow_core_dump partial
shared_memory_address 0
shared_pool_reserved_size 104857600
shared_pool_size 1912602624
shared_server_sessions
shared_servers 0
skip_unusable_indexes TRUE
smtp_out_server
sort_area_retained_size 0
sort_area_size 65536
spfile
sql92_security FALSE
sql_trace FALSE
sqltune_category DEFAULT
standby_archive_dest ?/dbs/arch
standby_file_management MANUAL
star_transformation_enabled FALSE
statistics_level TYPICAL
streams_pool_size 0
tape_asynch_io TRUE
thread 0
timed_os_statistics 0
timed_statistics TRUE
trace_enabled TRUE
tracefile_identifier
transactions 9108
transactions_per_rollback_segment 5
undo_management AUTO
undo_retention 3500
undo_tablespace rbs
use_indirect_data_buffers FALSE
use_large_pages TRUE
user_dump_dest /opt/oracle/product/11.2/admin/diag/rdbms/sanddb/sanddb/trace
utl_file_dir
workarea_size_policy auto
xml_db_events enable
|
|
|
|
|
|
|
|
| Re: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g [message #665245 is a reply to message #665244] |
Sun, 27 August 2017 13:52 |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
My intent of posting is to see if someone else has seen such performance differences for a simple insert script. I posted the whole script so that anyone can try the same in 11g and 12c and see if they notice the same degradation, to provide some help.
1. The 11g and 12c instances are running on 2 different machines
2. They both run on same platform -
12c
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;
ldt-db01
select banner from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
select platform_name from v$database;
Linux x86 64-bit
11g
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;
groo
select banner from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
select platform_name from v$database;
Linux x86 64-bit
|
|
|
|
|
|
| Re: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g [message #665248 is a reply to message #665247] |
Sun, 27 August 2017 19:29 |
rkrishna
Messages: 9
Registered: March 2007
Location: Silicon Valley, Californi...
|
Junior Member |
|
|
Hi, Thanks for your immediate responses. Really appreciate it.
To address the same point that you mention, on hardware differences, we tried the following:
1. First run the script (in my original posting) on 11g
2. Upgraded the same DB to 12C
3. Re-ran the exact script again
We found that the execution time on 12c was much slower.
We also tried the following:
1. On the same DB server, had a 11g instance and a 12c instance
2. Ran the scripts on both, one by one.
Again, 12c was much slower.
We have determined that this is definitely an Oracle 12c issue when it involves CLOB columns and have presented the test cases to Oracle support.
Again, thanks for your help and some of your questions/pointers. It has helped us in making sure we are comparing the numbers on identical DB server configurations.
|
|
|
|
|
|
| Re: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g [message #665389 is a reply to message #665240] |
Sat, 02 September 2017 06:59 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
INIT.ORA parameter DB_SECUREFILE in 12C defaults to PREFERRED (assuming you didn't set compatibility to lower version) meaning all LOBS are created as SECUREFILE unless BASICFILE is explicitly specified. IN 11G it defaults to PERMITTED meaning LOBs are created as BASICFILE unless SECUREFILE is explicitly specified. And SECUREFILE can be slower after upgrade - check MOS Doc ID 2189248.1, Doc ID 20165574.8, etc.
SY.
|
|
|
|
| Re: Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g [message #665392 is a reply to message #665389] |
Sun, 03 September 2017 06:38 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I just ran your test on 11.2.0.4 and 12.2.0.1 on my laptop, little instances with 1GB memory target. I reduced the iterations of the loop from 1000 to 10.
12c:12:20:11 orclx> BEGIN
12:23:54 2 FOR i IN 1..10 LOOP
12:23:54 3 INSERT INTO TMP_TEST_LARGE_ROWS_CLOB
12:23:54 4 (id, clob_value,random_string)
12:23:54 5 select
12:23:54 6 i*rownum id,
12:23:54 7 DBMS_RANDOM.string ('B', 500),
12:23:54 8 DBMS_RANDOM.string('A', 20)
12:23:54 9 from dual
12:23:54 10 CONNECT BY rownum <= 1000 ;
12:23:54 11 COMMIT;
12:23:54 12 END LOOP;
12:23:54 13 END ;
12:23:54 14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.4712:23:23 11.2>
12:23:24 11.2> BEGIN
12:23:30 2 FOR i IN 1..10 LOOP
12:23:30 3 INSERT INTO TMP_TEST_LARGE_ROWS_CLOB
12:23:30 4 (id, clob_value,random_string)
12:23:30 5 select
12:23:30 6 i*rownum id,
12:23:30 7 DBMS_RANDOM.string ('B', 500),
12:23:30 8 DBMS_RANDOM.string('A', 20)
12:23:30 9 from dual
12:23:30 10 CONNECT BY rownum <= 1000 ;
12:23:30 11 COMMIT;
12:23:30 12 END LOOP;
12:23:30 13 END ;
12:23:30 14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.31
First, those _underscore instance parameters. Get rid of them. They may have been helpful a long time ago, but in release 12 they are going to cripple the optimizer. Let it work properly.
Second, check your memory config. You have given the 12c instance 8 times as much memory which will have a huge effect on the algorithms for caching, particularly for serial direct read.
Third, why have you disabled the 12c optimizer adaptive features?
Fourth, is there a reason for testing on 12.1 and not 12.2? The current release has a lot of optimizer fixes and enhancements.
Lastly, please use [code] tags next time you post.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 01:22:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
