First a review of 12c, as it came out in June of 2013 and 12.1.0.2 came out in July 2014 which introduced DB In-Memory and the READ privilege which is really READ ONLY as it cannot lock tables via SELECT.
Changes in temporary undo, new parameter TEMP_UNDO_ENABLED.
Another new parameter is APPROX_COUNT_DISTINCT which gives you an approximate number of rows.
File move in 12c has 1 step, without outage ALTER DATABASE MOVE DATAFILE X TO Y, but can only move 1 at a time, can move from ASM to non-ASM and vice versa.
New background process LREG for listener registration, and PMON had this responsibility in 11g, so do not kill off the LREG process because you do not know what it is.
There can be 100 DBWR processes, was 36 in 11g.
PGA_AGGREGATE_LIMIT, set by default.
ENABLE_DDL_LOGGING existed in 11g but changed in 12c and needs additional licensing now.
adrci has a new command SHOW LOG.
Stats are automatically collected during bulk loads!
New security features regarding USER$ table in 11gR2, SPARE6 shows last login time for the user.
CASCADE clause for TRUNCATE, works with some conditions but will remove children downstream.
More ONLINE operations: dropping index, dropping constraint, moving tables/partitions, marking indexes and columns.
The VARCHAR2 column is now bigger but it requires the parameter MAX_STRING_SIZE enabled with DB in UPGRADE mode, and you cannot go back plus it will invalidate views and MVs.
New clause in SELECT for row limiting clause FETCH OFFSET.
Invisible columns, catch is that column numbers/order changes when this becomes visible again!
DBMS_QOPATCH is a queryable patch inventory, learn more at MOS note ID 1530108.1.
Also check out MOS note ID 1585822.1 to learn about datapatch which is used for post patch scripts.
TRANSFORM= DISABLE_ARCHIVE_LOGGING, VIEWS_AS_TABLES for Data Pump.
With db12c a new option (TABLE) to not require a SQL*Loader control file.
Describe is now in RMAN and you can run most SQL statements in RMAN!
SYSBACKUP admin role so you can replace SYSDBA in your scripts.
RECOVER TABLE, biggest RMAN feature, option of NOTABLEIMPORT doesn't import but does create export dump file.
RMAN catalog needs EE but covered under the Infrastructure Repository License.
For more details you can look in the new features guide, an OTN interactive reference, or the 12c Oracle Learning Library.
REMEMBER: Extended Support ends for 11g in 2016!!
9:45 AM - Maria Colgan (@db_inmemory or @SQLMaria) - General and Keynote Session
I actually did not attend this as I have heard Maria's excellent talk before at Hotsos, and I had a very important errand to run, but you can also refresh yourself with the launch event post I put up last year too!
11:15 AM - Alex Gorbachev (@alexgorbachev) - Anomaly Detection for Database Monitoring
What is the motivation here? Unusual metric values vs prior observations.
Typical datasets: time-series metrics, sampled session states, sql execution metrics, IO metrics per disk.
Traditional: human driven threshold settings, different thresholds based on timeframes, load (backup) but have to be set manually and regularly reviewed.
Instead you should use standard distribution metrics +\- 3 stdev
With this it is important to avoid false positives.
Bollinger Bands (stock trading) graph, something to investigate more!
USER_CALLS_PER_SEC_METRIC_VALUE - What is this? I need to find out!
Averages hide individual measurements and introduces a skew.
Histogram buckets in V$EVENT_HISTOGRAM and DBA_HIST_EVENT_HISTOGRAM are default and not the greatest, so try to build a DIY solution from ASH that is tooled to your installation.
Machine learning: supervised algorithms, unsupervised algorithms - again, another great thing to research.
Kale stack by Etsy, Skyline, Oculus, Redis, Carbite (Graphite Carbon) to Agent - not for Oracle but not difficult to write…this had me thinking "whhhhhaaaaatt?!?" when he said it because I know I could not do it!!
Oracle Data Mining demo was really good, using features in Oracle SQL Developer to easily make some analysis which would be easily repeatable too.
DBA_SYSMETRIC_HISTORY - Another thing to research when I get home!
12:30 PM - Panel of @KerryOracleGuy, @csierra_usa, @AlexFatkulin, @riyajshamsudeen, and @mfild - Exadata SIG
Even though a lot of time here was spent trying to convince one person how awesome Exadata is and what benefits his company could get out of it, it was still a very good SIG session hearing what other types of Exadata installations people have, or their experiences with Oracle Platinum Support.
Even though a lot of time here was spent trying to convince one person how awesome Exadata is and what benefits his company could get out of it, it was still a very good SIG session hearing what other types of Exadata installations people have, or their experiences with Oracle Platinum Support.
1:30 PM - Dean Halbeisen (@DeanHalbeisen / www.oracle.com/optimizedsolutions) - Be a Hero with Your DBA: Database Performance Tuning for Admins and Architects
This was a session I was REALLY looking forward to, but not long into it I had to look up the abstract because it did not seem like a normal presentation and that is when I saw that this hour was for exhibitors! No wonder why it seemed like a sales presentation!! That is not to say I did not get a thing or two from it, so yay!
As with most problems, you need to narrow down how much data you are looking at for instance in an AWR report.
As with most problems, you need to narrow down how much data you are looking at for instance in an AWR report.
Automatic Diagnostics Database Monitor ADDM - I have a feeling I know what this is, but I also want to research it just to make sure this is not something I have overlooked.
Dean had an overview of AWR and statspack as major tuning tools and introduced flash cache to convince the audience that they need it. I cannot dispute that, because we have Exadata and flash cache which has worked wonders for us!
Init.ora parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE need some investigation back home so I can be up to speed on our settings!
To learn more review the DB Concepts Guide (Chapter 14) and the Performance Tuning Guide (Chapter 10).
2:45 PM - Graham Wood (@OracleGraham) - Architecture Review by AWR Report
There are usually 3 reasons for performance problems: database not being used as it was designed to be used, application architecture/code design is sub-optimal, sub-optimal algorithm (BUG) in the DB.
Change is required and is scary!
Have to look at the big picture to get order of magnitude gains, which is a great reminder about what needs to be investigated and when!
Go to oracle.com/goto/oll/rwp for some Real World Performance videos!
Graham suggests a good measurement is 10 active sessions per core, which means you will need to be active with your connection management to help performance. Great point!
GC cleans up after Java processes, but not the DB sessions/cursors left behind!!
He showed us an AWR report where there were: 10 logons per second, 900 rollbacks and 1350 transactions per second, lots of _ parameters (which makes your DB a unique snowflake hard to support), cursor_sharing = false (which Graham calls evil), and a DB_block_size value of 16K instead of the default 8K block!
Did you know that open_cursors is how many cursors a session can have open at one time? This helps to hide cursor leaking bugs!
Optimizer_index_cost_adj is another interesting parameter that can cause problems too!
Latches and mutexes pop up when you have CPU problems, which are a symptom not the cause!
4 PM - Iordan Iotzov (iiotzov.wordpress.com) - Managing Statistics of Volatile Tables in Oracle
Distribution of data and all about reducing volatility, but that needs you to get proactive by thinking about DB design (does it have to be stored in the DB?).
Distribution of data and all about reducing volatility, but that needs you to get proactive by thinking about DB design (does it have to be stored in the DB?).
Bigger tables but they are less volatile. For instance, creates a view of just active records.
I thought this was an interesting concept, allowing a table to grow by large scales for soft deletes later but as I asked afterwards this is a custom application and not an EBS, CRM or other standard Oracle application so I do not know how much application it has for most people.
12c introduces Adaptive Execution Plans! Are you up on this?
377152.1 - Automatic Statistics Collection
Long term volume is unknown so this is difficult to know how to handle.
Bug #12897196 is something Iordan has talked about with Oracle to try and get DBMS_STATS not to lock, and why he created his own package JUST_STATS which gathers the stats and does NOT lock.
Hi Rusty,
ReplyDeleteI am aware of MOS/Metalink note 377152.1. I even included it as a reference in my white paper. The note dedicates only a couple of paragraphs on volatile tables, while I believe there is much more to be said about this topic.
Even though the graphs on pages 21 and 23 from my presentation are from a custom solution, I have encountered volatile table in packaged software as well. Oracle Lite, for instance, creates variety of auxiliary tables with prefixes such as SEQ$,SRV$,CMP$,CFM$,etc... Some of those tables are volatile.
Best Regards,
Iordan Iotzov
Iordan,
ReplyDeleteThanks for stopping by the blog! I really enjoyed your session at Training Days!!
I referenced that MOS/Metalink note because you shared it during your session, and I included it here so everybody else can see what it is that you are talking about. I'm certainly not dismissing your contributions by any means by saying it is custom, just that I don't know how much mileage I can get out of it and that could be a concern for other people running "vanilla" or locked into specific structures.
-Rusty