Oakies Blog Aggregator

Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries. The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage.

What perhaps a more interesting area of investigation is the claim that In-memory will also be of benefit in OLTP systems, where (to paraphrase many presenters at OpenWorld 2014), we wont be needing all those indexes on transactional tables.

So I thought I’d have an initial play around in that space, and see what happens.

We’ll start with a simple test bed – a transactional table "T", being a child of parent table "PAR", with a few indexes representing those "additional" OLTP indexes.

We’re using the extended commit syntax, because when we run this routine in a PL/SQL loop, we dont want to ‘cheat’ by using the PL/SQL optimization that avoids waiting for commits to complete. Now we’ll add the other half of our application, the support for adhoc queries on those non-primary key columns.

So there we have it. Our "application" is ready to go :-) Now we need to make it "popular", that is, have lots of people use it. To do this, I’ll create a routine which simulates a moderately busy application server thread. We’ll be firing off lots of these later to crank up the load.

So the "app_server_thread" procedure, will iterate a number of times, calling TRANS_LOGGER with a little bit of "think time", and will roughly run for 10 minutes (the 600 seconds in the iterations expression above).

We’ll also have a another procedure doing a similar thing, but for adhoc query. It will have a little longer think time, representing that our app is mainly transactional focussed with intermittent query.

We’re pretty much ready to go now. I’m running this on a Linux VM which has 12 "virtual" cores, and 128G of RAM hooked up to a Netapp filer for storage. Using some shell script, we can fire off concurrent session of our "app server" routines. I’m taking an AWR before and after so we can poke around in the results.

So about 130 seconds (over the 10mins) for the query threads, and 21 seconds (over the 10mins) for the transactional threads. Now I’ve re-run the exact same setup script above, and added a few more steps:

Well thats certainly showing some promise. Our transaction times dont seem to have been adversely affected, and our query performance is better. We can also look at the AWR reports and see how in-memory has influenced things:

Without in-memory

With in-memory

One of the positive outcomes is that redo generation shrinks; less indexes to update means less redo. If you’ve got crappy storage and you’re suffering with redo write issues, then perhaps in-memory is an avenue worth exploring (although you’d want to be carefully considering license costs versus storage upgrade costs!)

Taking a look at the wait events is also interesting

Without in-memory

With in-memory

With in-memory enabled, buffer busy waits have dropped…but they have been "replaced" with a new event "IM buffer busy". Always remember – Memory is memory – if multiple people want to manipulate it, then some sort of serialisation is going to occur. Similarly, note the appearance of the "latch free" event once in-memory comes into play. But (in this simple demo), the enabling of in-memory has not hindered the transactional characteristics of the app, and looks to have yielded some redo and query benefits.

This is by no means a definitive "yes, you are fine to drop those indexes" and similarly, not a definitive "you must keep those indexes" . Your own application will have its own particular idiosyncracies. But the results are promising – if you’ve got the license dollars for in-memory, you may as well look at exploiting that expenditure in every way possible.

A query that requires “star-expansion” fails with ORA-01792, but if you explicitly expand the ‘p.*’ to list all the columns it represents the optimizer is happy.

The person who highlighted the problem supplied code to generate the tables so you can repeat the tests very easily; one of the quick checks I did was to modify the code to produce tables with a much smaller number of columns and then expanded the SQL to see what Oracle would have done with the ANSI. So, with only 3 columns each in table RED and GREEN, this is what I did:

The dbms_utility.expand_sql_text() function is new to 12c, and you’ll need the execute privilege on the dbms_utility package to use it; but if you want to take advantage of it in 11g you can also find it (undocumented) in a package called dbms_sql2.

Here’s the result of the expansion (you can see why I reduced the column count to 3):

As you can now see, the A1 alias lists all the columns in GREEN, plus all the columns in RED, plus all the columns in PURPLE – totalling 3 + 3 + 2 = 8. (There is a little pattern of aliasing and re-aliasing that turns the join column RED.g_column_001 into G_COLUMN_001_3, making it look at first glance as if it has come from the GREEN table).

You can run a few more checks, increasing the number of columns in the RED and GREEN tables, but essentially when the total number of columns in those two tables goes over 998 then adding the two extra columns from PURPLE makes that intermediate inline view break the 1,000 column rule.

Here’s the equivalent expanded SQL if you identify the columns explicitly in the select list (even with several hundred columns in the RED and GREEN tables):

So it looks as if the routine to transform the syntax puts in a lot of redundant text, then the optimizer takes it all out again.

The problem doesn’t exist with traditional Oracle syntax, by the way, it’s an artefact of Oracle’s expansion of the ANSI syntax, and 11.2.0.4 is quite happy to handle the text generated by the ANSI transformation when there are well over 1,000 columns in the inline view.

There was a little conversation on Oracle-L about ASH (active session history) recently which I thought worth highlighting – partly because it raised a detail that I had got wrong until Tim Gorman corrected me a few years ago.

Once every second the dynamic performance view v$active_session_history copies information about active sessions from v$session. (There are a couple of exceptions to the this rule – for example if a session has called dbms_lock.sleep() it will appear in v$session as state = ‘ACTIVE’, but it will not be recorded in v$active_session_history.) Each of these snapshots is referred to as a “sample” and may hold zero, one, or many rows.

The rows collected in every tenth sample are flagged for copying into the AWR where, once they’ve been copied into the underlying table, they can be seen in the view dba_hist_active_sess_history. This is where a common misunderstanding occurs: it is not every 10th row in v$active_session_history it’s every 10th second; and if a sample happens to be empty that’s still the sample that is selected (which means there will be a gap in the output from dba_hist_active_sess_history). In effect dba_hist_active_sess_history holds copies of the information you’d get from v$session if you sampled it once every 10 seconds instead of once per second.

It’s possible to corroborate this through a fairly simple query as the rows from v$active_session_history that are going to be dumped to the AWR are as they are created:

As you can see at the beginning of the output the samples have a sample_time that increases one second at a time (with a little slippage), and the flagged samples appear every 10 seconds at 5.53.01, 5.53.11 and 5.53.21; but then the instance becomes fairly idle and there are several sample taken over the next 20 seconds or so where we don’t capture any active sessions; in particular there are no rows in the samples for 5.53.31, and 5.53.41; but eventually the instance gets a little busy again and we see that we’ve had active sessions in consecutive samples for the last few seconds, and we can see that we’ve flagged the sample at 5.53.51 for dumping into the AWR.

You’ll notice that I seem to be losing about 1/100th second every few seconds; this is probably a side effect of virtualisation and having a little CPU-intensive work going on in the background. If you see periods where the one second gap in v$active_session_history or 10 second gap in dba_hist_active_sess_history has been stretched by several percent you can assume that the CPU was under pressure over that period. The worst case I’ve seen to date reported gaps of 12 to 13 seconds in dba_hist_active_sess_history. The “one second” algorithm is “one second since the last snapshot was captured” so if the process that’s doing the capture doesn’t get to the top of the runqueue in a timely fashion the snapshots slip a little.

When the AWR snapshot is taken, the flagged rows from v$active_session_history are copied to the relevant AWR table. You can adjust the frequency of sampling for both v$active_session_history, and dba_hist_active_sess_history, of course – there are hidden parameters to control both: _ash_sampling_interval(1,000 milliseconds) and _ash_disk_filter_ratio (10). There’s also a parameter controlling how much memory should be reserved in the shared pool to hold v$active_session_history.: _ash_size (1048618 bytes per session in my case). The basic target is to keep one hour’s worth of data in memory, but if there’s no pressure for memory you can find that the v$active_session_history holds more than the hour; conversely, if there’s heavy demand for memory and lots of continuously active sessions you may find that Oracle does “emergency flushes” of v$active_session_history between the normal AWR snapshots. I have heard of people temporarily increasing the memory and reducing the interval and ratio – but I haven’t yet felt the need to do it myself.

This post has nothing to do with Oracle or Enterprise Manager at all, so if that’s all you’re interested in you can stop reading now.

Yesterday I ran into the situation where PowerPoint would only open minimized on the task bar and nothing I could do would get it to budge from there. The only way I could get PowerPoint to start and be displayed on any monitor was to start it in safe mode. [As an aside, to start PowerPoint (or indeed any Office product) in Safe Mode you just need to hold down the Control key while double-clicking either the PowerPoint program or any PowerPoint document.] After starting PowerPoint successfully in Safe Mode, I could see PowerPoint on my screen, but when I closed it down and tried to restart in normal mode, it would always start minimized.

So one answer is simply to start always in Safe Mode, which of course means a variety of things are disabled or don’t work fully. Not a very good answer. I searched a bit further afield, and found some Microsoft notes that basically explained the screen X-Y coordinates for PowerPoint may be corrupt (actually the notes were talking about Microsoft Word, not PowerPoint, but the explanation applies in either case). In the support note, it suggests 3 different methods to fix the issue:

Maximize the program

Use the move feature to reposition the program into view

Edit the registry

Obviously you should work through these from top to bottom, but I had already found that the first two did nothing in my situation, so I was left with editing the registry.

IMPORTANT: As always, as the Microsoft note says, “Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base: 256986 Description of the Microsoft Windows Registry”. In other words, do the rest of this at your own risk!!!

The note that I found from Microsoft talked about removing a registry key that for me didn’t exist. Of course, it was somewhat dated and there may be a more up to date version that I hadn’t found. In any case, here’s how I fixed the problem:

Run regedit.exe to start the Registry Editor

Navigate through the tree to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\PowerPoint

Right-click on “Options” and select “Export” to save a backup in case I needed it (which in fact I did – see below for the reason)

Delete the “Options” key completely

Voila! PowerPoint now started and displayed on the screen as I expected.

NOTE: I might also have been able to simply delete the entries Bottom, Left, Right and Top in the Options key, but I didn’t try that directly. Maybe if someone else that has this situation arise can test this and put a comment back on this post, we can see if that solution works as well. There were other options listed such as PersonalTemplates, ToolbarConfigSaved and so on that it could have been useful to keep. I added those back by editing the export I had taken beforehand to remove the Bottom, Left, Right and Top entries, then opening the Registry Editor and importing them back by choosing File -> Import, which merged the entries from my backup into the Options key.

When you go to a conference like UKOUG Tech15 there are hundreds of talks given over several days and a dozen or so streams. Who decides what is presented and how do they decide?

You do. Well, I’d say you have about 60-70% of the input, if you are a member of the UKOUG (and I know many reading this are not – but you are probably members {via your employer, if not personally} of other user groups. And, if you are not, you can probably benefit from joining one.) The point is, the members of the UK Oracle User Group have a fair say in what gets talked about at the UKOUG conferences. And, though not all are run in the same way, I know several of the large oracle conferences run on similar principles. You also provide the raw material, the proposed talks. That is open to each and every one of you, member or not. Anyone can offer a talk.

What about the other 30-40% of the input? Well, that would be me :-). {Note, British ironic humour}. As I mentioned in my first post about organising Tech15 I am the Lead for the database area this year, and some people did blame me last year for the content – but being the Lead does not put me in charge. There is a technical committee that decides what they feel should be the overall structure of the conference and have the final 30-40% say in what talks are given.

I’ll go into more details about aspect of the paper selection process in future posts, but the general structure is thus:

The steering committee meet for a kick-off meeting and decide on:

Who is in which committee (though this is pretty much sorted out before the meeting).

the general structure of the event – The major areas (Database, Middleware, Development, Business Analytics and Hardware/OS/Engineered), the number of streams each major area gets each day, the length of sessions and if anything is happening outside the main 3 days of the conference.

How we handle the labeling of topics in our streams (endless discussions there!).

Topics and considerations that we feel are important to our streams that should be mentioned in the call for papers.

How we will run the sub-committees and overall committee – again, this is generally known but we look at what we learnt the prior year and change accordingly.

The call for papers goes out (it will be the 13th April to 10th May this year). This is advertised by the UKOUG, being sent to previous paper submitters, the User Group members and is announced in the UKOUG mailings, tweeted and several other avenues. The committee will have suggested areas to submit for, but what is submitted is up to the presenting community – and this can alter our thoughts on content.

Judging – From 20th April to close to the Agenda Planning Day, volunteers and members of UKOUG are asked to judge the paper abstracts. These scores are important for the next step…

Agenda Planning Day – the steering committee members get together and spend pretty much a whole day reviewing the abstracts, the judging scores, the slots available, what we know of the speakers and presentations, the spread of topics, percentage of established and new speakers and half a dozen other things to come up with the rough agenda. It’s a bit of a bun fight, but we get there in the end. Every abstract is looked at along with it’s judging score.

Speakers are informed if their papers are accepted, rejected or we would like them as reserves – and the speakers confirm or decline acceptance or reserves (and occasionally question rejections). Sometimes a speaker will be asked if they would modify a submission.

The technical committees may well try and source some papers where we feel a topic is under-represented or to fit with some other aim (like a stream at a given level).

Reserves are slotted in to replace any speakers who decline and any clashes, alterations and agenda tweaks are dealt with as they arise.

The agenda is launched (ie we say what is on it) mid July.

From the agenda launch to the start of the conference, any paper changes are handled as they come up – usually a speaker pulling out or needing to change dates but occasionally other issues.

Why is it called “Paper Selection” when people are talking? Why do we talk about abstracts? Well, conferences pretty much started off as scientific conferences and you would submit you scientific paper – and then read it out to the conference. The abstract is a brief “why you should read my 35 page argument with long, impressive words for why I think hyaenas are more closely related to cats than dogs” {they are}. We inherit those terms.

So you can see that the steering committee has a fair input, so how do WE get chosen? Fundamentally, it is via a call for volunteers from the UKOUG community. The UKOUG ask people to volunteer in their regular emails to members/volunteers. (Volunteers have to be members of the UKOUG but the membership may well belong to a company. The UKOUG keeps track of the nominated contacts for an organisation, who are responsible for the membership management, but also the individuals who have helped out at any time under that membership. As an example, someone in purchasing or HR may be the nominated contact for the memberships a company has with UKOUG, but it is members of the technical IT staff who come to the events and may start helping out).
The office UKOUG staff/board members may well ask one or two of the experienced volunteers known to them to take a lead and help chose which volunteers to accept. Or, more commonly, to go and pester people they know to step up and volunteer! New volunteers are always part of the mix, we recognise that without new people and perspectives we will stagnate, and they challenge us when we say “we always do it this way”.

I have not mentioned Oracle Corporation involvement. Strictly speaking, people from Oracle are not volunteers and are certainly not members. They are Oracle Liaisons. The UKOUG gets good support from Oracle, we have talks from them, we have some SIG meetings in their offices. Oracle Corporation of course is happy to talk about the latest/greatest aspects of Oracle and if they can get us all fired up for an extra cost option, so much the better for them. But the relationship is generally balanced and varies over the years – and is influenced by individuals. Some people who work for Oracle will push to be allowed to help out the UKOUG, some product managers are more than happy to come and give talks about free, standard or old features as well as the shiny new stuff. Others I am sure see us as an annoyance. The input we get from the Oracle Liaisons is very helpful and appreciated – but don’t think it buys acceptance of whatever Oracle Corp want. I had to help deal with an Oracle product manager last year who was upset that their area had very few talks. It got as far as them almost demanding some slots. However, the number of talks submitted and the poor judging scores for those few that were told us on the committee that the user community were not currently interested in that topic. So no talks. Faye and I talked it over, I gave the logic and reason and she was good enough to then deal with Upset Product Manager.

I have helped with the agenda planning day a couple of time – I think I got pestered to help way back in 2007 or 8! – and I have been a SIG chair and deputy chair as well as a regular presenter, so I am a known soft-touch for helping the UKOUG. A key aspect to my being the Lead is simply that I have more free time than most other volunteers, so I can be got hold of and can spend a bit of time thinking about things and making decisions. This can be important on the run-up to the actual event as you sometimes need to make decisions quickly and a group discussion may not be the best way to do it. I might check with a couple of others (and I usually do) but the key thing is to make a decision in the timeframe allowed.

So that is who the Agenda Planning committee are and where we fit in. We are volunteers, filtered and guided by some old hands but with new blood each year. We aim to guide and give structure but the talks submitted are what anyone wants to submit. Judging scores by the community are key to paper selection and though Oracle Corp supports they don’t get to dictate.

Some time ago I wrote a blog note describing a hack for refreshing a large materialized view with minimum overhead by taking advantage of a single-partition partitioned table. This note describes how Oracle 12c now gives you an official way of doing something similar – the “out of place” refresh.

I’ll start by creating a matieralized view and creating a couple of indexes on the resulting underlying table; then show you three different calls to refresh the view. The materialized view is based on all_objectsso it can’t be made available for query rewrite (ORA-30354: Query rewrite not allowed on SYS relations) , and I haven’t created any materialized view logs so there’s no question of fast refreshes – but all I intend to do here is show you the relative impact of a complete refresh.

This was a default install of 12c, so there were about 85,000 rows in the view. You’ll notice that I’ve created all the objects as “nologging” – this will have an effect on the work done during some of the refreshes.

Here are the three variants I used – all declared explicitly as complete refreshes:

The first one (atomic_refresh=>true) is the one you have to use if you want to refresh several materialized views simultaneously and keep them self consistent, or if you want to ensure that the data doesn’t temporarily disappear if all you’re worried about is a single view. The refresh works by deleting all the rows from the materialized view then executing the definition to generate and insert the replacement rows before committing. This generates a lot of undo and redo – especially if you have indexes on the materialized view as these have to be maintained “row by row” and may leave users accessing and applying a lot of undo for read-consistency purposes. An example at a recent client site refreshed a table of 6.5M rows with two indexes, taking about 10 minutes to refresh, generating 7GB of redo as it ran, and performing 350,000 “physical reads for flashback new”. This strategy does not take advantage of the nologging nature of the objects – and as a side effect of the delete/insert cycle you’re likely to see the indexes grow to roughly twice their optimal size and you may see the statistic “recursive aborts on index block reclamation” climbing as the indexes are maintained.

The second option (atomic_refresh => false) is quick and efficient – but may result in wrong results showing up in any code that references the materialized view (whether explicitly or by rewrite). The session truncates the underlying table, sets any indexes on it unusable, then reloads the table with an insert /*+ append */. The append means you get virtually no undo generated, and if the table is declared nologging you get virtually no redo. In my case, the session then dispatched two jobs to rebuild the two indexes – and since the indexes were declared nologging the rebuilds generated virtually no redo. (I could have declared them with pctfree 0, which would also have made them as small as possible).

The final option is the 12c variant – the setting atomic_refresh => false is mandatory if we want out_of_place => true. With these settings the session will create a new table with a name of the form RV$xxxxxx where xxxxxx is the hexadecimal version of the new object id, insert the new data into that table (though not using the /*+ append */ hint), create the indexes on that table (again with names like RV$xxxxxx – where xxxxxx is the index’s object_id). Once the new data has been indexed Oracle will do some name-switching in the data dictionary (shades of exchange partition) to make the new version of the materialized view visible. A quirky detail of the process is that the initial create of the new table and the final drop of the old table don’t show up in the trace file [Ed: wrong, see comment #1] although the commands to drop and create indexes do appear. (The original table, though it’s dropped after the name switching, is not purged from the recyclebin.) The impact on undo and redo generation is significant – because the table is empty and has no indexes when the insert takes place the insert creates a lot less undo and redo than it would if the table had been emptied by a bulk delete – even though the insert is a normal insert and not an append; then the index creation honours my nologging definition, so produces very little redo. At the client site above, the redo generated dropped from 7GB to 200MB, and the time dropped to 200 seconds which was 99% CPU time.

Limitations, traps, and opportunities

The manuals say that the out of place refresh can only be used for materialized views that are joins or aggregates and, surprisingly, you actually can’t use the method on a view that simply extracts a subset of rows and columns from a single table. There’s a simple workaround, though – join the table to DUAL (or some other single row table if you want to enable query rewrite).

Because the out of place refresh does an ordinary insert into a new table the resulting table will have no statistics – you’ll have to add a call to gather them. (If you’ve previously been using a non-atomic refreshes this won’t be a new problem, of course). The indexes will have up to date statistics, of course, because they will have been created after the table insert.

The big opportunity, of course, is to change a very expensive atomic refresh into a much cheaper out of place refresh – in some special cases. My client had to use the atomic_refresh=>true option in 11g because they couldn’t afford to leave the table truncated (empty) for the few minutes it took to rebuild; but they might be okay using the out_of_place => true with atomic_refresh=>false in 12c because:

the application uses this particular materialized view directly (i.e. not through query rewrite), and the query plans are all quick, light-weight indexed access paths

most queries will probably run correctly even if they run through the moment of exchange

I don’t think we could guarantee that last statement – and Oracle Corp. may not officially confirm it – and it doesn’t matter how many times I show queries succeeding but it’s true. Thanks to “cross-DDL read-consistency” as it was called in 8i when partition-exchange appeared and because the old objects still exist in the data files, provided your query doesn’t hit a block that has been overwritten by a new object, or request a space management block that was zero-ed out on the “drop” a running query can keep on using the old location for an object after it has been replaced by a newer version. If you want to make the mechanism as safe as possible you can help – put each relevant materialized view (along with its indexes) into its own tablespace so that the only thing that is going to overwrite an earlier version of the view is the stuff you create on the next refresh.

#000000;">Download both of these and start them up in Virtualbox and you can start virtualizing your PDBs.

#000000;">The Oracle pre-installed VM has a few eccentricities that have to be addressed before using it. There is no IP address and there is no history of the install and it’s missing a glibc library. After we address these in the instructions below you can link to the PDB and then provision that PDB back as a virtual PDB (vPDB) or provision that PDB to another 12c instance on another machine as a vPDB.

Here is a video of linking a PDB to Delphix and provisioning a vPDB

before doing the above with the Oracle pre-installed VM, follow these steps:

#000000;">get the ip address with “ifconfig -a” and vi /etc/hosts to add hostname “oraclevm” with the ip address. Also increase /dev/shm or otherwise will get “MEMORY_TARGET not supported on this system”

#000000;">Waited a few minutes and was able to run. Oracle VM must run some yum stuff just after starting up. After one try, the running yum process never seemed to exit, so rebooted VM and upon reboot was able to run yum

#000000;">

#000000;">

Back as Oracle unset TWO_TASK as it blocks “/ as sysdba” connections and get rid of the .bash output as it messes up scp and Delphix trying to put the toolkit onto the box

#000000;">Download both of these and start them up in Virtualbox and you can start virtualizing your PDBs.

#000000;">The Oracle pre-installed VM has a few eccentricities that have to be addressed before using it. There is no IP address and there is no history of the install and it’s missing a glibc library. After we address these in the instructions below you can link to the PDB and then provision that PDB back as a virtual PDB (vPDB) or provision that PDB to another 12c instance on another machine as a vPDB.

Here is a video of linking a PDB to Delphix and provisioning a vPDB

before doing the above with the Oracle pre-installed VM, follow these steps:

#000000;">get the ip address with “ifconfig -a” and vi /etc/hosts to add hostname “oraclevm” with the ip address. Also increase /dev/shm or otherwise will get “MEMORY_TARGET not supported on this system”

#000000;">Waited a few minutes and was able to run. Oracle VM must run some yum stuff just after starting up. After one try, the running yum process never seemed to exit, so rebooted VM and upon reboot was able to run yum

#000000;">

#000000;">

Back as Oracle unset TWO_TASK as it blocks “/ as sysdba” connections and get rid of the .bash output as it messes up scp and Delphix trying to put the toolkit onto the box

Here are the slides of a presentation I did at the IOUG Virtual Exadata conference in February. I’m explaining the basics of some new Oracle 12c things related to Exadata, plus current latest cellsrv improvements like Columnar Flash Cache and IO skipping for Min/Max retrieval using Storage Indexes:

Note that Christian Antognini and Roger MacNicol have written separate articles about some new features: