Here’s a good tip In SQL*Plus, that I have learned only recently; You’re in the middle of typing in some SQL or a PL/SQL Anonymous block, and can’t remember the column name, or other table information? At the start of the next line type “#desc <table_name>” and the table is described but you keep in the same place in your edit. No need to stop.
NEIL @ orcl > declare 2 l_dt date; 3 l_txt varchar2(50); 4 begin 5 #desc test_tab1 Name Null? Type ------------------ -------- -------------------------------------------- ID_COL NOT NULL NUMBER DATE_COL DATE TEXT_COL VARCHAR2(50) PAD_COL CHAR(50) 5 l_date := sysdate; 6 select text_col into l_txt.... 7 . NEIL @ orcl >
You can teach an old dog, old tricks. Neat time saver.
In Oracle 11G, Oracle introduced SQL Plan Management (SPM). It is excellent (I love it to bits). It allows you to create Baselines against SQL which lock-down the SQL execution plan. No more plan flips. More consistency. Perfect.
Whenever some Baselined SQL is ran, Oracle still parses it and compares the parsed output to the accepted (Evolved) baselines. If the newly parsed plan is better, a new baseline is added to DBA_SQL_PLAN_BASELINES but is NOT accepted. This means that you need to spend time manually accepting the baseline; running the command DBMS_SPM.EVOLVE_SQL_BASELINE plan and checking the new plan. IF you want it, and/or Oracle evaluates that is it a better plan for that particular set of bind variables, the plan is accepted and becomes a candidate to be used by future execution of your SQL. Complete control over your execution plans.
So, Oracle, what’s all this about in Oracle 12C, eh?
In Oracle 12C there’s a new SPM Evolve advisor task. “By default,
SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window” - So, it runs every night and by default it runs DBMS_SPM.EVOLVE_SQL_BASELINE for all new baselines created today and automatically accepts the new plans.
BY DEFAULT? NO! the Oracle, NO!
That is precisely what I don’t want from baselines – Oracle making it’s own mind up about plans without any input from me. I’m using baselines to stop Oracle changing its mind. To explicitly limit the number of paths allowed by the Optimizer to ones I know about and with which I am comfortable. Don’t introduce functionality to do the opposite.
So, immediately following the installation of 12C, I would recommend running (you need to be SYS for this):
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) ); PARAMETER_NAME VALUE ------------------------- ---------- ACCEPT_PLANS TRUE TIME_LIMIT 3600
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK', 'ACCEPT_PLANS', 'false'); END; /
OK, back where we were, with any baselines fixed in place and doing what I want them to do! Not change.
I attended the UKOUG Tech13 conference this week, along with over 1,000 other Oracle Technical Professionals (and some less-professional too), to learn what I could about how everyone else is doing stuff with Oracle, and how it works. And to share some experiences.
I wasn’t disappointed.
As with everything these days, there was a lot of activity on Twitter about the 4 day conference, and one of the sponsors, RittmanMead, had knocked up a rather wonderful tweet visualisation (click here to see how – magical):
As you can see, there are some of the worlds best-known Oracle techies on the list of top speaker tweets, but I am as pleased as anything to have been in the top 10 most tweeted speakers at the end of the 1st day (although I suspect that’s because I knew where the pub was, rather than too much about my 1st presentation)
Get involved with the Oracle community, in whatever form. It leads to professional and personal development, real insights about what Oracle really does (rather than what the marketeers say), and some good friendships. And possibly liver disease judging by the amount of alcohol consumed by all.
I’m giving 2 presentation this year at the UK Oracle User Group Tech13 Conference
The first is a discussion about the reason why you do or do not need to do low level detail traces and block dumps, and argument with Martin Widlake (and the audience hopefully). This is at 4pm on “Super” Sunday 1st.
Presentation 1 Abstract
I will also be presenting Why did my plan change? A run through of the top few reasons why Oracle (un)expectedly changed your execution plan, and what you can do about it using SQL Plan Management. This is on Tuesday 3rd December at 17:45.
Presentation 2 Abstract
I hope to see you there, or in the Pub at a MOB* (probably propping up the bar in the Peveril of the Peak given it’s close and good)
*Manchester Oracle Beers, a sister evening to the London Oracle Beers
Running RAC? (Why? No, really, WHY? Never heard of DataGuard? With a broker?)
Not sure if you’ve configured it correctly?
Not sure if you have all of the recommended initialisation parameters set?
All recommended RPM’s installed?
All daemons running?
etc, etc, etc,
Well, as of Oracle 18.104.22.168 where’s a new feature provided by default called RACCheck. You can find it installed in directory $ORACLE_HOME/suptools/raccheck, (or you can download it from MOS article 1268927.1) and it’s called “raccheck”. With a little sudo configuration, or the root passwords, you can check the configuration on every node in a few minutes per node (run at a sensible time). All the basics appear to be covered, and you get a nice list of anomalies out of the system in HTML format.
I don’t necessarily agree with some of the errors/warnings produced (you might want the “problems” it’s finding!), but it gives you cause to re-think about an element of the system that may be configured in a non-standard way, and you get lots of relevant and useful links to MOS articles.
e.g. One problem:
|WARNING||SQL Check||Some user sessions lack proper failover mode (BASIC) and method (SELECT)||All Databases|
Can be happily ignored as I’m using a SCAN listener, which renders this WARNING irrelevant.
but I would recommend that you use the utility and accept/understand any exceptions. It should help stabilise any RAC installations you may have.
Just implementing Goldengate between a platform I don’t understand, a Tandem/HP non-stop, and Oracle 11G R2 RAC. So, I spend the day trying to get it working, have all of the configuration seemingly correct and when the Tandem guy an I try to for a connection to send data over, we keep getting the following warning (and no data):
WARNING OGG-01223 Oracle GoldenGate Collector for Oracle: Could not find definition for <Tandem-table-thing>
So, after extensive tweaks, mods, changes, banging heads off desks and general disbelief, I spend a while Googling for (or, more accurately, duckduckgo-ing – I’m sick of being the commodity), and on MOS, and noticed a problem between Version 11.2 of GG and 11.1. It was in no way related to the problem we had, but it prompted me into a though; exactly which versions of GG are on each platform? There was a minor discrepancy of versions.
OK – download GG 22.214.171.124.0 for Linux to match the Tandem version. Install. Replay the Configuration. Run. Works. Simples.
Lesson: Check your versions, check your compatibility. Where possible, keep them absolutely perfectly aligned and you might not waste 1/2 day troubleshooting. Grrrr.