Oracle 12c twice as slow as Oracle 10.2g, help

I was given the task of comparing the new 12c to our older 10.2g setup.

I come from a MS Sql env. and find the lack of tools to do what I could do in MS SQL  inexplicable. I am sure it must be because I am unaware of the tools themselves and not because they don't exist.

The goal is to find out why on 2 separate vms running windows 2008R2, same amount of disk space and ram, with one instance 10.2g and the other with 12c, running the same jobs to build cubes at night, why the 12c is 2 times longer then the 10.2g to run.

I know it sounds vague, but the dbs out of the box configs of both machines should make the 12c faster then 10.2g (or so I am led to believe). as there are no other jobs/apps running on those machines (it was setup just exactly for this scenario).

My concern though is the possibility of wasting time to analyze #2 below when maybe just changing a small default parameter (that may have changed in 12c) could return the times to almost the same (#1). Here are the questions I need answered if possible.

1) Out of the box, what parameters specifically could Oracle have modified from 12c to 10.2g that could lead to such a decrease in performance, and

2) I need a MS SQL Profiler equivalent that will allow me to sit back and log all the queries and times that runs on a machine (like SQLProfiler) and just get an output of times for each query. SQLProfiler could allow me to cut and paste into Enterprise manager the query then get an execution plan for it, it would be nice to know how to do this as well in Oracle.

Please excuse the lack of knowledge when it comes to Oracle, but was dba for MS and had to turn a new leaf due to company policy of switching to Oracle backends when we changed ownership.
landerson999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>and find the lack of tools to do what I could do in MS SQL  inexplicable

Just because you can't find them doesn't mean they don't exist...

In 10g there was dbConsole or Enterprise Manager Grid Control depending on how it was set up.

12c comes with EM Express.  I've not played with the 12c version yet but it should be very similar to dbConsole.

http://docs.oracle.com/cd/E16655_01/server.121/e17643/em_manage.htm#ADMQS12310

As far as quick settings, check memory_target and memory_max_target on both instances.
0
landerson999Author Commented:
Yes i agree with you as I pointed out in my comment after that
> I am sure it must be because I am unaware of the tools themselves and not because they don't exist.

Thank you for letting me know about the tools and the quick settings lookup.

I see you mention Enterprise manager for the 10.2 instance... I have that open now, yet fail to see any monitoring aspect to the tool.... what item in the menu do I need to click on to see this.. I see Tools->Database tools -> etc... but all paths have failed to lead me to something where I can see real time query execution times per session.... am I looking in the wrong place?
0
DavidSenior Oracle Database AdministratorCommented:
Wrong place yes, but regrettably I don't recall the sequence (tactful reminder that 10g is desupported ).  Try searching the inline help?

Is the person who did the 12c install no longer available?  Any number of details could be misconfigured or mis-sized.

Also remember that the CPU time is only a part of the entire task time.  The delay could be completely irrelevant to the task or even the Oracle part.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
I really don't use the GUI much.  Check under the Advisors.  There are several that help with tuning.

If you are licensed for it, there is also ADDM and AWR that can generate reports over a selected time period.
0
landerson999Author Commented:
Tyvm for the reminder, as I explained I was the one to setup all the vms and their configs are all out of the box (as per our clients). I also come from a MS sql world, and am now stuck trying to learn the Oracle way of doing things... please bear with me...

10g is desupported, got it, but I thought out of the box, 12c would run faster then 10.2g....
even with certain default params left as is.... ?

I did find the sql tab for each session (sql) query run, but it seems it is extra real time, meaning I see no logging happeneing, so if a query has finished running it no longer shows its stats on that tab...the session has ended...where as SQLProfiler logs and allows to come back afterwards...

If I use the SQLTrace (which is not a UI tool) could I atleast get a lon list fo all queries and their times run without fear of losing the logs as the sessions close?
0
slightwv (䄆 Netminder) Commented:
>>, but I thought out of the box, 12c would run faster then 10.2g

Unfortunately Oracle isn't a 'run setup.exe and go' type of database.  There are certain defaults Oracle uses for initialization parameters but you should have a good idea what they should be.

The memory tunables are a great place to start.

From both databases from a sql prompt perform the following:
create pfile='c:\mypfile.ora' from spfile;

--of course change the path appropriately.

Then edit the pfile's and compare the parameters.  Make sure they are close enough to be comparing apples to apples.
0
landerson999Author Commented:
Here is a result set display of the "show parameters" command from either db...
If you do a select copy and paste into a compare tool, you will see side by side what the differences are... I am not sure of which exactly would be the ones to really make a difference (memory wise?) ...maybe you might find one that stands out ?

thanks in advance...

10.2g
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
active_instance_count                integer
aq_tm_processes                      integer     0
archive_lag_target                   integer     0
asm_diskgroups                       string
asm_diskstring                       string
asm_power_limit                      integer     1
audit_file_dest                      string      C:\ORACLE\ORA10G\RDBMS\AUDIT
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
background_core_dump                 string      partial
background_dump_dest                 string      C:\ORADB\TRB\BDUMP
backup_tape_io_slaves                boolean     FALSE
bitmap_merge_area_size               integer     1048576
blank_trimming                       boolean     FALSE
buffer_pool_keep                     string
buffer_pool_recycle                  string
circuits                             integer
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
commit_point_strength                integer     1
commit_write                         string
compatible                           string      10.2.0
control_file_record_keep_time        integer     7
control_files                        string      C:\ORADB\TRB\CONTROLFILES\CONT
                                                 ROL01.CTL, C:\ORADB\TRB\CONTRO
                                                 LFILES\CONTROL02.CTL, C:\ORADB
                                                 \TRB\CONTROLFILES\CONTROL03.CT
                                                 L
core_dump_dest                       string      C:\ORACLE\ORA10G\RDBMS\TRACE
cpu_count                            integer     2
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     16384
db_cache_advice                      string      ON
db_cache_size                        big integer 0
db_create_file_dest                  string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
db_domain                            string
db_file_multiblock_read_count        integer     4
db_file_name_convert                 string
db_files                             integer     1024
db_flashback_retention_target        integer     1440
db_keep_cache_size                   big integer 64M
db_name                              string      TRB
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_recycle_cache_size                big integer 0
db_unique_name                       string      TRB
db_writer_processes                  integer     1
dbwr_io_slaves                       integer     0
ddl_wait_for_locks                   boolean     FALSE
dg_broker_config_file1               string      C:\ORACLE\ORA10G\DATABASE\DR1T
                                                 RB.DAT
dg_broker_config_file2               string      C:\ORACLE\ORA10G\DATABASE\DR2T
                                                 RB.DAT
dg_broker_start                      boolean     FALSE
disk_asynch_io                       boolean     TRUE
dispatchers                          string
distributed_lock_timeout             integer     60
dml_locks                            integer     504
drs_start                            boolean     FALSE
event                                string
fal_client                           string
fal_server                           string
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
file_mapping                         boolean     FALSE
fileio_network_adapters              string
filesystemio_options                 string
fixed_date                           string
gc_files_to_locks                    string
gcs_server_processes                 integer     0
global_context_pool_size             string
global_names                         boolean     FALSE
hash_area_size                       integer     131072
hi_shared_memory_address             integer     0
hs_autoregister                      boolean     TRUE
ifile                                file
instance_groups                      string
instance_name                        string      trb
instance_number                      integer     0
instance_type                        string      RDBMS
java_max_sessionspace_size           integer     0
java_pool_size                       big integer 0
java_soft_sessionspace_limit         integer     0
job_queue_processes                  integer     4
large_pool_size                      big integer 0
ldap_directory_access                string      NONE
license_max_sessions                 integer     0
license_max_users                    integer     0
license_sessions_warning             integer     0
local_listener                       string
lock_name_space                      string
lock_sga                             boolean     FALSE
log_archive_config                   string
log_archive_dest                     string      C:\oradb\TRB\archives
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      arch_%t_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
log_buffer                           integer     14251008
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
log_checkpoints_to_alert             boolean     FALSE
log_file_name_convert                string
logmnr_max_persistent_sessions       integer     1
max_commit_propagation_delay         integer     0
max_dispatchers                      integer
max_dump_file_size                   string      10240
max_enabled_roles                    integer     150
max_shared_servers                   integer
nls_calendar                         string
nls_comp                             string
nls_currency                         string
nls_date_format                      string
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string
nls_sort                             string
nls_territory                        string      AMERICA
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
olap_page_pool_size                  big integer 0
open_cursors                         integer     50
open_links                           integer     4
open_links_per_instance              integer     4
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.5
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      all_rows
optimizer_secure_view_merging        boolean     TRUE
os_authent_prefix                    string      OPS$
os_roles                             boolean     FALSE
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     0
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
permit_92_wrap                       boolean     TRUE
pga_aggregate_target                 big integer 700M
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
plsql_debug                          boolean     FALSE
plsql_native_library_dir             string
plsql_native_library_subdir_count    integer     0
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL
pre_11g_enable_capture               boolean     FALSE
pre_page_sga                         boolean     FALSE
processes                            integer     100
query_rewrite_enabled                string      false
query_rewrite_integrity              string      enforced
rdbms_server_dn                      string
read_only_open_delayed               boolean     FALSE
recovery_parallelism                 integer     0
recyclebin                           string      on
remote_archive_enable                string      true
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
replication_dependency_tracking      boolean     TRUE
resource_limit                       boolean     FALSE
resource_manager_plan                string
resumable_timeout                    integer     0
rollback_segments                    string
serial_reuse                         string      disable
service_names                        string      TRB
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     115
sga_max_size                         big integer 1712M
sga_target                           big integer 1712M
shadow_core_dump                     string      partial
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 18454937
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     0
skip_unusable_indexes                boolean     TRUE
smtp_out_server                      string
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
spfile                               string
sql92_security                       boolean     FALSE
sql_trace                            boolean     FALSE
sql_version                          string      NATIVE
sqltune_category                     string      DEFAULT
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS
standby_file_management              string      MANUAL
star_transformation_enabled          string      false
statistics_level                     string      typical
streams_pool_size                    big integer 0
tape_asynch_io                       boolean     TRUE
thread                               integer     0
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
trace_enabled                        boolean     TRUE
tracefile_identifier                 string
transactions                         integer     126
transactions_per_rollback_segment    integer     5
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      undotbs
use_indirect_data_buffers            boolean     FALSE
user_dump_dest                       string      C:\ORADB\TRB\UDUMP
utl_file_dir                         string      C:\oradb\TRB\trace
workarea_size_policy                 string      AUTO

Open in new window


12c
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
active_instance_count                integer
aq_tm_processes                      integer     1
archive_lag_target                   integer     0
asm_diskgroups                       string
asm_diskstring                       string
asm_power_limit                      integer     1
asm_preferred_read_failure_groups    string
audit_file_dest                      string      C:\ORACLE\ORA12C\PRODUCT\12.1.
                                                 0\DBHOME_1\RDBMS\AUDIT
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
awr_snapshot_time_offset             integer     0
background_core_dump                 string      partial
background_dump_dest                 string      C:\Oracle\ora12c\diag\rdbms\tr
                                                 b\trb\trace
backup_tape_io_slaves                boolean     FALSE
bitmap_merge_area_size               integer     1048576
blank_trimming                       boolean     FALSE
buffer_pool_keep                     string
buffer_pool_recycle                  string
cell_offload_compaction              string      ADAPTIVE
cell_offload_decryption              boolean     TRUE
cell_offload_parameters              string
cell_offload_plan_display            string      AUTO
cell_offload_processing              boolean     TRUE
cell_offloadgroup_name               string
circuits                             integer
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
clonedb                              boolean     FALSE
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_interconnects                string
commit_logging                       string
commit_point_strength                integer     1
commit_wait                          string
commit_write                         string
compatible                           string      12.0.0
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),
                                                  ((TYPE=EMON)(BROKERS=1))
control_file_record_keep_time        integer     7
control_files                        string      C:\ORADB\TRB\CONTROLFILES\CONT
                                                 ROL01.CTL, C:\ORADB\TRB\CONTRO
                                                 LFILES\CONTROL02.CTL, C:\ORADB
                                                 \TRB\CONTROLFILES\CONTROL03.CT
                                                 L
control_management_pack_access       string      NONE
core_dump_dest                       string      C:\Oracle\ora12c\diag\rdbms\tr
                                                 b\trb\cdump
cpu_count                            integer     2
create_bitmap_area_size              integer     8388608
create_stored_outlines               string
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_big_table_cache_percent_target    string      0
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     16384
db_cache_advice                      string      ON
db_cache_size                        big integer 0
db_create_file_dest                  string
db_create_online_log_dest_1          string
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string
db_domain                            string
db_file_multiblock_read_count        integer     4
db_file_name_convert                 string
db_files                             integer     1024
db_flash_cache_file                  string
db_flash_cache_size                  big integer 0
db_flashback_retention_target        integer     1440
db_index_compression_inheritance     string      NONE
db_keep_cache_size                   big integer 64M
db_lost_write_protect                string      NONE
db_name                              string      TRB
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_recycle_cache_size                big integer 0
db_securefile                        string      PREFERRED
db_ultra_safe                        string      OFF
db_unique_name                       string      TRB
db_unrecoverable_scn_tracking        boolean     TRUE
db_writer_processes                  integer     1
dbwr_io_slaves                       integer     0
ddl_lock_timeout                     integer     0
deferred_segment_creation            boolean     TRUE
dg_broker_config_file1               string      C:\ORACLE\ORA12C\PRODUCT\12.1.
                                                 0\DBHOME_1\DATABASE\DR1TRB.DAT
dg_broker_config_file2               string      C:\ORACLE\ORA12C\PRODUCT\12.1.
                                                 0\DBHOME_1\DATABASE\DR2TRB.DAT
dg_broker_start                      boolean     FALSE
diagnostic_dest                      string      C:\ORACLE\ORA12C
disk_asynch_io                       boolean     TRUE
dispatchers                          string
distributed_lock_timeout             integer     60
dml_locks                            integer     756
dnfs_batch_size                      integer     4096
dst_upgrade_insert_conv              boolean     TRUE
enable_ddl_logging                   boolean     FALSE
enable_pluggable_database            boolean     FALSE
event                                string
fal_client                           string
fal_server                           string
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
file_mapping                         boolean     FALSE
fileio_network_adapters              string
filesystemio_options                 string
fixed_date                           string
gcs_server_processes                 integer     0
global_context_pool_size             string
global_names                         boolean     FALSE
global_txn_processes                 integer     1
hash_area_size                       integer     131072
heat_map                             string      OFF
hi_shared_memory_address             integer     0
hs_autoregister                      boolean     TRUE
ifile                                file
instance_groups                      string
instance_name                        string      trb
instance_number                      integer     0
instance_type                        string      RDBMS
java_jit_enabled                     boolean     TRUE
java_max_sessionspace_size           integer     0
java_pool_size                       big integer 0
java_soft_sessionspace_limit         integer     0
job_queue_processes                  integer     4
large_pool_size                      big integer 0
ldap_directory_access                string      NONE
ldap_directory_sysauth               string      no
license_max_sessions                 integer     0
license_max_users                    integer     0
license_sessions_warning             integer     0
listener_networks                    string
local_listener                       string
lock_name_space                      string
lock_sga                             boolean     FALSE
log_archive_config                   string
log_archive_dest                     string      C:\oradb\TRB\archives
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      arch_%t_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
log_buffer                           integer     6053888
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
log_checkpoints_to_alert             boolean     FALSE
log_file_name_convert                string
max_dispatchers                      integer
max_dump_file_size                   string      10240
max_enabled_roles                    integer     150
max_shared_servers                   integer
max_string_size                      string      STANDARD
memory_max_target                    big integer 0
memory_target                        big integer 0
nls_calendar                         string      GREGORIAN
nls_comp                             string      BINARY
nls_currency                         string      $
nls_date_format                      string      DD-MON-RR
nls_date_language                    string      AMERICAN
nls_dual_currency                    string      $
nls_iso_currency                     string      AMERICA
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      .,
nls_sort                             string      BINARY
nls_territory                        string      AMERICA
nls_time_format                      string      HH.MI.SSXFF AM
nls_time_tz_format                   string      HH.MI.SSXFF AM TZR
nls_timestamp_format                 string      DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format              string      DD-MON-RR HH.MI.SSXFF AM TZR
noncdb_compatible                    boolean     FALSE
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400
olap_page_pool_size                  big integer 0
open_cursors                         integer     50
open_links                           integer     4
open_links_per_instance              integer     4
optimizer_adaptive_features          boolean     TRUE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      12.1.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      all_rows
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
os_authent_prefix                    string      OPS$
os_roles                             boolean     FALSE
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_fault_tolerance_enabled     boolean     FALSE
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     0
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     32
parallel_threads_per_cpu             integer     2
pdb_file_name_convert                string
permit_92_wrap_format                boolean     TRUE
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 700M
plscope_settings                     string      IDENTIFIERS:NONE
plsql_ccflags                        string
plsql_code_type                      string      INTERPRETED
plsql_debug                          boolean     FALSE
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      DISABLE:ALL
pre_page_sga                         boolean     TRUE
processes                            integer     100
processor_group_name                 string
query_rewrite_enabled                string      false
query_rewrite_integrity              string      enforced
rdbms_server_dn                      string
read_only_open_delayed               boolean     FALSE
recovery_parallelism                 integer     0
recyclebin                           string      on
redo_transport_user                  string
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
replication_dependency_tracking      boolean     TRUE
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     2
resource_manager_plan                string
result_cache_max_result              integer     5
result_cache_max_size                big integer 0
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
resumable_timeout                    integer     0
rollback_segments                    string
sec_case_sensitive_logon             boolean     TRUE
sec_max_failed_login_attempts        integer     10
sec_protocol_error_further_action    string      CONTINUE
sec_protocol_error_trace_action      string      TRACE
sec_return_server_release_banner     boolean     FALSE
serial_reuse                         string      disable
service_names                        string      TRB
session_cached_cursors               integer     50
session_max_open_files               integer     10
sessions                             integer     172
sga_max_size                         big integer 1712M
sga_target                           big integer 1712M
shadow_core_dump                     string      none
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 19293798
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     0
skip_unusable_indexes                boolean     TRUE
smtp_out_server                      string
sort_area_retained_size              integer     0
sort_area_size                       integer     65536
spatial_vector_acceleration          boolean     FALSE
spfile                               string
sql92_security                       boolean     FALSE
sql_trace                            boolean     FALSE
sqltune_category                     string      DEFAULT
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS
standby_file_management              string      MANUAL
star_transformation_enabled          string      false
statistics_level                     string      typical
streams_pool_size                    big integer 0
tape_asynch_io                       boolean     TRUE
temp_undo_enabled                    boolean     FALSE
thread                               integer     0
threaded_execution                   boolean     FALSE
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
trace_enabled                        boolean     TRUE
tracefile_identifier                 string
transactions                         integer     189
transactions_per_rollback_segment    integer     5
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      undotbs
unified_audit_sga_queue_size         integer     1048576
use_dedicated_broker                 boolean     FALSE
use_indirect_data_buffers            boolean     FALSE
user_dump_dest                       string      C:\Oracle\ora12c\diag\rdbms\tr
use_large_pages                      string      TRUE
utl_file_dir                         string      C:\oradb\TRB\trace
workarea_size_policy                 string      AUTO
xml_db_events                        string      enable

Open in new window

0
slightwv (䄆 Netminder) Commented:
On a quick scan they seem very similar (almost as exact as they can be) and nothing obvious jumps out at me.

I probably wouldn't have set up my memory in the 12c database that way but since it is the same as the 10g instance, it likely isn't the issue.

I would probably start out next with Windows Performance Monitor.  Add counters for Disk, Memory and CPU.  Run your process and see what appears to be maxing out.  This will help focus the tuning efforts.
0
landerson999Author Commented:
I actually used Merge compare to side by side remove any identical lines.. leaving me with only the oes that either are diff out of the box, new or depracated...

Here are the ones that stand out to me, which I guess would lead me to want to know if they would be relevant...

db_block_checksum
dml_locks
optimizer_features_enable
permit_92_wrap
pre_page_sga
transactions
sessions
session_cached_cursors

I will look these up, but if there is one that you consider to have impact on overall db performance, please let me know...
0
slightwv (䄆 Netminder) Commented:
>>used Merge compare to side by side

As did I.  As I mentioned, nothing jumped out at me.  I wouldn't change parameters 'just because'.  I don't think the ones you mentioned would have a huge impact on performance.
0
landerson999Author Commented:
Good, ok, but then this leads me to a slight confusion....
out of the box, both seem to have the same configs and parameters set, running the same logical queries on the same data from the same dmp files.... they are both on the same hardware (VMs) so read write to disk etc... that is all the same down the line... I guess I would conclude that 12c is slower then 10.2g, end of story, no?

If I run tools to look at the execution of the queries, all I am going to see are the times (which I already have technically).... I am posting the run times below, and if you notice they seem to almost have exactly twice as much run time on the 12c then the 10.2g in all lines except a few...

Cycle_Duration
----------------------10.2g_-------------------------------12c-----------------------------
The_jobs_ran_for____:_2381_seconds________________________:_4141_seconds
The_jobs_started_at_:_2010-08-23_01:16:51_________________:_2010-08-23_01:24:18
The_jobs_ended_at___:_2010-08-23_01:56:32_________________:_2010-08-23_02:33:19

---------10.2g_----------------------------------------------12c-----------------------------
Job______Started______________Elapsed_Parallel___Worked____Started___________Elapsed_Parallel___Worked
-------------------------_-------------------_--------_--------_--------__-------------------_--------
Job1__2010-08-23_01:39:38______204________4______769__2010-08-23_02:14:31______325________4_____1147
Job2__2010-08-23_01:28:52______643________1______643__2010-08-23_01:53:39_____1247________1_____1247
Job3__2010-08-23_01:48:21______398________1______398__2010-08-23_02:25:18______400________1______400
Job4__2010-08-23_01:24:08______175________2______337__2010-08-23_01:35:50______959________2_____1887
Job5__2010-08-23_01:19:32______192________1______192__2010-08-23_01:27:45______349________1______349
Job6__2010-08-23_01:43:15______150________1______150__2010-08-23_02:20:17______139________1______139
Job7__2010-08-23_01:55:02_______84________1_______84__2010-08-23_02:31:58_______74________1_______74
Job8__2010-08-23_01:22:48_______79________1_______79__2010-08-23_01:33:35______133________1______133
Job9__2010-08-23_01:46:36_______70________1_______70__2010-08-23_02:23:32_______66________1_______66
Job10_2010-08-23_01:27:51_______59________1_______59__2010-08-23_01:52:33_______63________1_______63
Job11_2010-08-23_01:45:46_______47________1_______47__2010-08-23_02:22:37_______51________1_______51
Job12_2010-08-23_01:29:12_______42________1_______42__2010-08-23_01:53:59_______34________1_______34
Job13_2010-08-23_01:27:11_______35________1_______35__2010-08-23_01:51:58_______34________1_______34
Job14_2010-08-23_01:30:18_______31________1_______31__2010-08-23_01:54:54______124________1______124
Job15_2010-08-23_01:18:22_______31________1_______31__2010-08-23_01:26:25_______35________1_______35
Job16_2010-08-23_01:17:57_______25________1_______25__2010-08-23_01:25:39_______41________1_______41
Job17_2010-08-23_01:48:01_______16________1_______16__2010-08-23_02:24:48_______28________1_______28
Job18_2010-08-23_01:17:37_______12________1_______12__2010-08-23_01:25:19_______13________1_______13
Job19_2010-08-23_01:43:05_______10________1_______10__2010-08-23_02:19:57_______17________1_______17

Open in new window


I guess I would want to know how to go about getting the trace info that some are referring to... i think it would be SQLTrace?

I would like to open another question here http://www.experts-exchange.com/Database/Oracle/Q_28268667.html in order to award fairly more points as this might become a bigger question then just 500 points could buy.
0
slightwv (䄆 Netminder) Commented:
>>I guess I would conclude that 12c is slower then 10.2g, end of story, no?

I would not draw that conclusion.  There could still be differences.

For example: are the data file sizes and allocated extents in the datafiles the same between the two databases?  Not likely.

>>I guess I would want to know how to go about getting the trace info that some are referring to

I'll let the 'how' be answered in the other question.  

I would not jump straight to SQL tracing.  I would turn on Windows Performance Monitor with the counters I mentioned above and see which aspect of the server is struggling.

For example: Does the process you are running generate a LOT of new data/redo/etc...  If so, the performance difference could be as simple as the tablespaces allocating new space.
0
slightwv (䄆 Netminder) Commented:
I would also generate new statistics in the 12c database.  Depending on how you performed the migration, the optimizer might be confused.

There is a decent white paper on 12c stats:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
0
landerson999Author Commented:
tyvm slightwv i will come back to your suggestions...

>Does the process you are running generate a LOT of new data/redo/etc...  If so, the performance difference could be as simple as the tablespaces allocating new space

I know that there is data being imported to test the rebuilding of the cubes, and this means enough that a realignment will happen and therefor new cubes will emerge with enough changes to benchmark the system (this was created before I got here).

if it is a tablespace thing (which could very well be), I imagine the default settings internal to the version would make this so.... I could check the tablespace setup of each, but then if they are the same out of the box, I would not see how this can make a difference unless the Oracle engine had changed somehow...
0
landerson999Author Commented:
I just checked the tablespace, they are both near identical except for about a few places with minor space differences, but nothing big... did you want to see the result set from running the last query from this page http://psoug.org/snippet/TABLESPACE--List-tablespaces-files-allocated-and-free-space_852.htm  ?

I would say the tablespace is not the problem, but what is your opinion?
0
landerson999Author Commented:
here for the side by side
10.2g tablespace info
TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEIADM$M                        1992294400 84017152          1900 1820            80       4.22
MEILARGE$M                      5242880000 3816816640           5000 1360          3640       72.8
MEIMEDIUM$M                     1048576000 898629632           1000 143           857       85.7

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEIMVDATA                       1048576000 85458944           1000 918            82       8.15
MEIMVINDX                       629145600  6291456            600 594             6          1
MEINDXADM$M                     31457280   30932992             30 0            30      98.33

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEINDXLARGE$M                   5242880000 3959422976           5000 1224          3776      75.52
MEINDXMEDIUM$M                  1363148800 1207959552           1300 148          1152      88.62
MEINDXSMALL$M                    314572800   94109696            300 210            90      29.92

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEISMALL$M                       314572800  216268800            300 94           206      68.75
MEITEMP                          104857600   98041856            100 6            94       93.5
SYSAUX                           209715200   23986176            200 177            23      11.44

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
SYSTEM                          314572800   90439680            300 214            86      28.75
UNDOTBS                         2097152000 2033909760           2000 60          1940      96.98
USER_DATA                       1048576000 1040187392           1000 8           992       99.2

Open in new window


12c tablespace info
TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEIADM$M                        2097152000 187957248          2000 1821           179       8.96
MEILARGE$M                      5242880000 3816816640           5000 1360          3640       72.8
MEIMEDIUM$M                     1048576000  898629632           1000 143           857       85.7

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEIMVDATA                       1048576000  84672512           1000 919            81       8.08
MEIMVINDX                       671088640   47448064            640 595            45       7.07
MEINDXADM$M                     31457280   30015488             30 1            29      95.42

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEINDXLARGE$M                   5242880000 3959422976           5000 1224          3776      75.52
MEINDXMEDIUM$M                  1363148800 1207173120           1300 149          1151      88.56
MEINDXSMALL$M                    314572800   93323264            300 211            89      29.67

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
MEISMALL$M                       314572800  215482368            300 94           206       68.5
MEITEMP                          104857600   97255424            100 7            93      92.75
SYSAUX                           503316480   37224448            480 444            36        7.4

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
SYSTEM                          419430400    4456448            400 396             4       1.06
UNDOTBS                         4194304000 3000762368           4000 1138          2862      71.54
USER_DATA                       1048576000 1040187392           1000 8           992       99.2

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>I would say the tablespace is not the problem, but what is your opinion?

Still may be internal to the tablepsace...

In my initial 12c testing, I've noticed major performance differences but then I've not started performance testing with it yet.  Still doing initial functional tests.

Here is where I would go from here:
Regenerate schema stats on the 12c instance.
Before starting the test run, start Windows Performance Monitor (add the necessary counters).
Start the test.

See where to focus the efforts.
0
landerson999Author Commented:
Sounds like you/we might be getting somewhere...!

>Regenerate schema stats on the 12c instance
How would I do this?

>start Windows Performance Monitor (add the necessary counters)
which necessary counters would you suggest?
0
slightwv (䄆 Netminder) Commented:
>>How would I do this?

If you want statistics on ALL objects in the schema:
dbms_stats.gather_schema_stats.

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#i1036456

>>which necessary counters would you suggest?

Ones that will show CPU, Disk usage and Memory.  I don't know them off the top of my head.

I would shoes the general ones then start drilling down once you start identifying things.

For example if there is a general CPU overall usage, choose that one.  If it spikes, then see what other CPU related counters there are.
0
landerson999Author Commented:
Ok, here is something I have found different about both dbs.

Using the SQLDeveloper GUI....i was able to get a quick view at the manage database tab. and when I sort the columns to be all matching, I see that the system,sysaux,and undotbs tables are all extremely full (red) at 98% on the 12c.... based on allocated space being used up. Is this a performance issue of are these tables ok even if they are in the red like that? My thought is that the Oracle engine might have to work more to make sure that the space left is being properly managed, where as a table that is half full might be faster on disk?
0
landerson999Author Commented:
Funny enough , the UNDOTBS table has to do with undo history for transaction rollbacks, and if it is set too low where the allocation might have to be increased repeatedly, this causes a big overhead, I am surprised no one caught this.... I am fairly new to Oracle, and this is something I came up with myself....seems to me similar to SQLServer as well...about the sizing growth of a db if set too low will have an overhead...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
What did you do specifically to the UNDO to resolve the issue?

It didn't appear to be a space issue based on what you posted in http:#a39577390  The 12c size for UNDO is twice the 10g.
0
landerson999Author Commented:
Excatly, the fact that the UNDO table ends up being 2 times the size means it had to grow many times over along the way as it did not have enough room to begin with (something about the new 12c architechture takes up more room for the UNDO as what I read in their docs). This causes a big overhead when you have to continually regrow an allocation...which during read/write operations can cost a lot of time...

So if the 10.2G and 12c had the same size UNDO then my point would be moot, however, if it is twice as big for the same amount of data, it measn something else is going on in 12c that is NOT in 10.2g which is costing this much time, and the fact that the size is almost 2 times as big, which is also just exactly how much more time it took the 12c to run those jobs 2 times as long, leads me to believe the configs of 12c out of the box are the issue due to a new way of doing things with the UNDO history logging.
0
slightwv (䄆 Netminder) Commented:
Thanks for the update.
0
landerson999Author Commented:
ok, seeing as i answered my own question, but no one thought of this before i did...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.