Help with 12c vs 10.2g [message #598517] |
Tue, 15 October 2013 10:24 |
|
hesaigo999ca
Messages: 8 Registered: October 2013
|
Junior Member |
|
|
Hi all, let me start by thanking you all for a great website, it seems very well maintained.
I am getting back into Oracle (from a long haul in MS only env.) and am now testing Oracle installs.
I have been given a task of seeing the diff. between 12c and 10.2g
I set up 2 vms (excatly same configs) and used the same dmp file (on both env.) to restore data and settings for our jobs to run.
We have some aggregated data, and cubes with DIM tables each being run on the vm machines. We run nightly jobs to rebuild our cubes.
I am supposed to see/analyze the value of 12c, and understand things might vary from company to company, but am perplexed at my result.
12c is half the speed of 10.2g, both env. are the same out of the box with same dmp file and same hardware.
My questions is if I am using the same dmp file, with the same jobs on each machine, with both vms having 10.2g or 12c installed out of the box as is... what default oracle settings might have changed from 10.2g to 12c that could make the exact same env. run twice as slow on the 12c? I am hazy a bit with Oracle as I am starting back into it, so please be patient with me....
Expectations were that out of the box with both machines running same jobs on same data (from dmp files) would have it that 10.2g would be slower then the 12c, except the 12c takes 2 times as long to run the jobs. I have reviewed every possibility as I know usually the problem is the person sitting in the chair and not the pc...but I confirmed all was identical from the one vm env. to the other, except the version of oracle out of the box.
If anyone out there has any clue as to what to start testing to see what could be done to bring that default setting back to atleast equal time between the 2, that would give me a great starting point. Otherwise, I would have to toss this up to bloatware.
Thanks in advance for help, links and comments,
ps - I read up a bit on the CBO, and know this might have changed in 12c....
is there a way to bring it back to a backwards ealier config, so as to atleast match both env. execution plans?
[Updated on: Tue, 15 October 2013 10:27] Report message to a moderator
|
|
|
|
Re: Help with 12c vs 10.2g [message #598522 is a reply to message #598520] |
Tue, 15 October 2013 11:17 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you want to get to the bottom of this then you're going to have to trace the sessions on the two DBs and compare to see what's going wrong on 12c.
|
|
|
|
|
Re: Help with 12c vs 10.2g [message #598531 is a reply to message #598527] |
Tue, 15 October 2013 12:10 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
But how would it help in real time cases if you degrade the optimizer? Find out the slow running SQL,turn on tracing event, execute it, and post the tkprof output. Impossible to conclude anything or make any decision without any proof.
|
|
|
Re: Help with 12c vs 10.2g [message #598532 is a reply to message #598527] |
Tue, 15 October 2013 12:10 |
|
hesaigo999ca
Messages: 8 Registered: October 2013
|
Junior Member |
|
|
I apologize BlackSwan, the link you provided talks about something different then what we are talking about here (or mentioned in your post) I think maybe the link is wrong? The link sends me to a page about creating a table my_salary3 and doing a doing some sql queries... I am not sure if this correlates to finding some features or configs on 12c that could make the results come out in twice the time.
The list of things you mentioned are things I can take a look into separately, and post my findings when I have found out more about this...
[Updated on: Tue, 15 October 2013 12:10] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Help with 12c vs 10.2g [message #598637 is a reply to message #598613] |
Wed, 16 October 2013 08:50 |
|
hesaigo999ca
Messages: 8 Registered: October 2013
|
Junior Member |
|
|
>I still think the place to start is to trace both sessions and compare.
I am in agreement, yet no one is able to provide me with the name of a similar UI tool like MS SQLProfiler,
Am i to believe Oracle has yet to figure out how to write a proper UI for their backend? Is the SQL_TRACE really the only text based option otherwise?
>What OS name & version is running the VM hosting V12 Oracle?
Both are identical same hardware windows 2008 R2 64bit with all updates patched,
one has 10.2g and the other has 12c (out of the box)
Both have the same dmp file being used, and exactly the same configs and jobs running to rebuild the cubes.
The log files of each are as follows, I have tried to do a side by side to better identify the diffs.
Notice all the times are almost exactly twice as long on 12c then on the 10.2g
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
What I wanted to point out with this grid is that you can see a consistent time difference between the 2 jobs from 10.2g to 12c.
All these have multiple queries (such as inserts, updates, etc...) but are identical in terms of queries and records affected.
I am wondering if there might be a 12c specific parameter or config out of the box (such as I/O readwrite buffer, etc... that would
make this exactly always twice as long...
[Updated on: Wed, 16 October 2013 09:02] Report message to a moderator
|
|
|
Re: Help with 12c vs 10.2g [message #598652 is a reply to message #598637] |
Wed, 16 October 2013 10:07 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
hesaigo999ca wrote on Wed, 16 October 2013 14:50>I still think the place to start is to trace both sessions and compare.
I am in agreement, yet no one is able to provide me with the name of a similar UI tool like MS SQLProfiler,
Am i to believe Oracle has yet to figure out how to write a proper UI for their backend? Is the SQL_TRACE really the only text based option otherwise?
Have you looked at enterprise manager? It does a lot of stuff, though admittedly I never use it so I can't swear to what features it has.
and for what you're looking at the trace file should have all you need in one place and comparing that (with a file compare tool) will probably spot issues faster than looking at lots of different graphs.
hesaigo999ca wrote on Wed, 16 October 2013 14:50
The log files of each are as follows, I have tried to do a side by side to better identify the diffs.
Notice all the times are almost exactly twice as long on 12c then on the 10.2g
Is the time the elapsed column? what's that in, seconds? There's one or two that are double, seems to vary wildly.
hesaigo999ca wrote on Wed, 16 October 2013 14:50
I am wondering if there might be a 12c specific parameter or config out of the box (such as I/O readwrite buffer, etc... that would
make this exactly always twice as long...
Except of course the figures appear to indicate that the difference differs much more than you think.
There are probably several parameters that would make a difference, enough that tinkering with them without knowing why 12c is running slower would be a total wild goose chase.
|
|
|
|
Re: Help with 12c vs 10.2g [message #598723 is a reply to message #598670] |
Thu, 17 October 2013 02:41 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Your logs appear to show that these jobs ran in 2010....
That aside, you're assuming that it's a parameter that is causing problems whereas the real question is why is it slower. You're trying to treat a problem without knowing the specifics of it.
From what I'm reading you have a bunch of "jobs" that do lots of "stuff". You need to understand the working of these jobs - the SQL they pass and where and when these sql's wait/spent their time on.
Once you know that, compare THAT analysis between versions. Those are the differences you need to address and depending on what they are, the corrections could be any number of things.
Things previously suggested like enterprise manager, 10046 traces etc are all ideal for this. However in problems like this, before I even bother with those sorts of things, the first thing I (well, you) want to be doing is comparing the explain plans between the systems. Finally, a cautionary note - explain plans in 12c are a bit....interesting as adaptive plans are active out of the box so you be careful with them and understand what it is telling you.
[Updated on: Thu, 17 October 2013 02:42] Report message to a moderator
|
|
|
Re: Help with 12c vs 10.2g [message #598731 is a reply to message #598517] |
Thu, 17 October 2013 04:02 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I'm going to take a step back. It sounds as though you can't identify the SQLs being executed. So I would begin by identifying them. And at the same time, get an overall picture of how the two databases are configured, and the workload.
Do this by generating statspack reports for the two databases over the period when you run these jobs. Or if you have the licences, generate AWR reports. If you are not familiar with generating these reports, well, it will be very difficult to proceed.
Can you do that, and post the reports here?
|
|
|
Re: Help with 12c vs 10.2g [message #598769 is a reply to message #598731] |
Thu, 17 October 2013 09:57 |
|
hesaigo999ca
Messages: 8 Registered: October 2013
|
Junior Member |
|
|
Tyvm all for being patient and lots of suggestions...
Here so far is extra info as well as the progress I have made so far.
Info...
Roachcoach:I do know what the jobs are doing, the sql is the same in both cases, hence why I consider this to be simple and the exact sql to be irrelevant. If i run a statement in my job that is "select * from table", and that statement is the same on both machines, why would I need to review execution plan (explain plans) when out of the box, technically both should be acting the same due to the same amount of records (same dmp file). If they are not, then this would be a place to start as it would indicate that 12c does things diff. then 10.2g. Also, if you say the execution plans from 1 version to another again wont be the same in order to be able to compare properly side by side, then what would be the point of running them at all comparatively and just do as you say ; worry only about version 12 with its own tuning parameters instead of comparing the parameters used to tune 10.2g... This in itself now means you have to relearn a whole new set of standards for the 12c, regardless of what was done to tweak 10.2g.
John: I can identify the SQL statements individually, the problem is there are so many in each job, and there are many jobs, hence why in MSSQL, you can use a tool like sqlprofiler, tell it to log ALL sqlqueries overnight (to get the time/execution info), and then in the morning have 1 file that shows all the info from last nights statements, no matter how many or what session they came from. I did figure out how to do SQL_Trace and tkprof, but this is too slow and cumbersome to do for individual query look ups...or even per session, this is not an acceptable time loss in my opinion.... there should be a tool that allows you to run a full tablespace(database in MSSQL) logging.
In the end the reports you are looking for would be to compare, what I could do is grab the biggest statements from the jobs and post the different times here....
?: Someone asked me to post the different parameters by using the SHOW PARAMETERS command.
Here is a list of diff if not non existing params from one to the other, I omitted anything that was the same on both sides... of course same thing here, using a compare tool is best
10.2
aq_tm_processes integer 0
audit_file_dest string C:\ORACLE\ORA10G\RDBMS\AUDIT
background_dump_dest string C:\ORADB\TRB\BDUMP
compatible string 10.2.0
core_dump_dest string C:\ORACLE\ORA10G\RDBMS\TRACE
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 16384
ddl_wait_for_locks boolean FALSE
``
dg_broker_config_file1 string C:\ORACLE\ORA10G\DATABASE\DR1TRB.DAT
dg_broker_config_file2 string C:\ORACLE\ORA10G\DATABASE\DR2TRB.DAT
``
dml_locks integer 504
drs_start boolean FALSE
gc_files_to_locks string
log_archive_max_processes integer 2
log_buffer integer 14251008
logmnr_max_persistent_sessions integer 1
max_commit_propagation_delay integer 0
``
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_numeric_characters string
nls_sort string
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
``
``
optimizer_features_enable string 10.2.0.5
``
parallel_execution_message_size integer 2152
``
permit_92_wrap boolean TRUE
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
pre_11g_enable_capture boolean FALSE
``
pre_page_sga boolean FALSE
remote_archive_enable string true
session_cached_cursors integer 20
sessions integer 115
sga_max_size big integer 1712M
sga_target big integer 1712M
shadow_core_dump string partial
shared_pool_reserved_size big integer 18454937
sql_version string NATIVE
transactions integer 126
user_dump_dest string C:\ORADB\TRB\UDUMP
12
aq_tm_processes integer 1
asm_preferred_read_failure_groups string
audit_file_dest string C:\ORACLE\ORA12C\PRODUCT\12.1.0\DBHOME_1\RDBMS\AUDIT
awr_snapshot_time_offset integer 0
background_dump_dest string C:\Oracle\ora12c\diag\rdbms\trb\trb\trace
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
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
clonedb boolean FALSE
commit_logging string
commit_wait string
compatible string 12.0.0
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),((TYPE=EMON)(BROKERS=1))
control_management_pack_access string NONE
core_dump_dest string C:\Oracle\ora12c\diag\rdbms\trb\trb\cdump
cursor_bind_capture_destination string memory+disk
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_flash_cache_file string
db_flash_cache_size big integer 0
db_index_compression_inheritance string NONE
db_lost_write_protect string NONE
db_securefile string PREFERRED
db_ultra_safe string OFF
db_unrecoverable_scn_tracking boolean TRUE
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
diagnostic_dest string C:\ORACLE\ORA12C
``
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
global_txn_processes integer 1
heat_map string OFF
java_jit_enabled boolean TRUE
ldap_directory_sysauth string no
listener_networks string
log_archive_max_processes integer 4
log_buffer integer 6053888
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_numeric_characters string .,
nls_sort string BINARY
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
optimizer_adaptive_features boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
``
optimizer_features_enable string 12.1.0.1
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
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_io_cap_enabled boolean FALSE
parallel_min_time_threshold string AUTO
parallel_servers_target integer 32
pdb_file_name_convert string
``
permit_92_wrap_format boolean TRUE
pga_aggregate_limit big integer 2G
plscope_settings string IDENTIFIERS:NONE
``
pre_page_sga boolean TRUE
processor_group_name string
redo_transport_user string
resource_manager_cpu_allocation integer 2
result_cache_max_result integer 5
result_cache_max_size big integer 0
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0
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
session_cached_cursors integer 50
sessions integer 172
sga_max_size big integer 1712M
sga_target big integer 1712M
shadow_core_dump string none
shared_pool_reserved_size big integer 19293798
spatial_vector_acceleration boolean FALSE
temp_undo_enabled boolean FALSE
threaded_execution boolean FALSE
transactions integer 189
unified_audit_sga_queue_size integer 1048576
use_dedicated_broker boolean FALSE
user_dump_dest string C:\Oracle\ora12c\diag\rdbms\tr
use_large_pages string TRUE
xml_db_events string enable
|
|
|
Re: Help with 12c vs 10.2g [message #598773 is a reply to message #598769] |
Thu, 17 October 2013 10:39 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
hesaigo999ca wrote on Thu, 17 October 2013 15:57
John: I can identify the SQL statements individually, the problem is there are so many in each job, and there are many jobs, hence why in MSSQL, you can use a tool like sqlprofiler, tell it to log ALL sqlqueries overnight (to get the time/execution info), and then in the morning have 1 file that shows all the info from last nights statements, no matter how many or what session they came from.
statspack does that.
|
|
|