Search

OakieTags

Who's online

There are currently 0 users and 41 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

12c In-Memory in PDB

In preparation for our upcoming 12c In-Memory Webcast @CaryMillsap, @TanelPoder, and I solicited questions from members of the universe at large on the interweb. We got a question about how In-Memory works with the 12c multi-tentant option and it got me thinking so I gave it a quick try. As it turns out, it works about as you would expect. The basic idea is to turn it on for the container DB (which is where the memory is actually allocated (ala the other main shared memory regions) and then decide which PDBs to allow to use it (and if so how much of it to use) or not. First, here are the steps necessary to allocate the memory in the container DB.

-bash-3.2$ rlwrap sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 12 16:07:31 2014
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME               CURRENT_TIME                  DAYS    SECONDS
---------------- -------------------------- -------------------------- ------- ----------
democ1           12-SEP-2014 09:49          12-SEP-2014 16:07              .26      22675
 
SYS@democ1> select cdb from v$database;
 
CDB
---
YES
 
SYS@democ1> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
 
    PDB_ID PDB_NAME                       STATUS
---------- ------------------------------ ---------
         2 PDB$SEED                       NORMAL
         3 DEMOPDB                        NORMAL
 
SYS@democ1> @parms
Enter value for parameter: inmem
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default                                                                                                   TRUE     FALSE      FALSE
inmemory_force                                     DEFAULT                                                                TRUE     FALSE      FALSE
inmemory_max_populate_servers                      12                                                                     TRUE     FALSE      FALSE
inmemory_query                                     ENABLE                                                                 TRUE     FALSE      FALSE
inmemory_size                                      0                                                                      FALSE    FALSE      TRUE
inmemory_trickle_repopulate_servers_percent        1                                                                      TRUE     FALSE      FALSE
optimizer_inmemory_aware                           TRUE                                                                   TRUE     FALSE      FALSE
 
7 rows selected.
 
SYS@democ1> alter system set inmemory_size=500M;
alter system set inmemory_size=499M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
 
SYS@democ1> alter system set inmemory_size=500M scope=spfile;
 
System altered.
 
SYS@democ1> startup force
ORACLE instance started.
 
Total System Global Area 8589934592 bytes
Fixed Size                  6877112 bytes
Variable Size            1644167240 bytes
Database Buffers         6257901568 bytes
Redo Buffers              144117760 bytes
In-Memory Area            536870912 bytes
Database mounted.
Database opened.
SYS@democ1> @parms
Enter value for parameter: inmem
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default                                                                                                   TRUE     FALSE      FALSE
inmemory_force                                     DEFAULT                                                                TRUE     FALSE      FALSE
inmemory_max_populate_servers                      12                                                                     TRUE     FALSE      FALSE
inmemory_query                                     ENABLE                                                                 TRUE     FALSE      FALSE
inmemory_size                                      536870912                                                              FALSE    FALSE      TRUE
inmemory_trickle_repopulate_servers_percent        1                                                                      TRUE     FALSE      FALSE
optimizer_inmemory_aware                           TRUE                                                                   TRUE     FALSE      FALSE
 
7 rows selected.

So I logged into a container database that has one PDB (DEMOPDB). I checked to see if there was memory assigned to the column store, and there wasn’t. (inmemory_size was set to the default value of 0). Then I attempted to change the value, which as you can see is not a dynamic component. I had to make the change in the spfile and then restart the entire database including any PDBs.

So we’ve enabled inmemory at the container level. Now let’s check the PDB.

Note: The CDB/PDB is still a little weird for me. I use this little script to set SQL*Plus prompt to keep me straight. connect_pdb.sql

Note 2: Someone else asked about the very simple inmem_segs.sql script so here’s a link to it too: inmem_segs.sql

 
SYS@democ1> @connect_pdb
Enter value for pdb_name: DEMOPDB
 
Session altered.
 
 
 
 
 
SYS@democ1:DEMOPDB> alter system set inmemory_size=0;
alter system set inmemory_size=0
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
 
SYS@democ1> select pdb_id con_id, pdb_name, status, open_mode, restricted from dba_pdbs a, v$pdbs b where a.con_id = b.con_id order by pdb_id;
 
    CON_ID PDB_NAME                       STATUS    OPEN_MODE  RES
---------- ------------------------------ --------- ---------- ---
         2 PDB$SEED                       NORMAL    READ ONLY  NO
         3 DEMOPDB                        NORMAL    MOUNTED
 
SYS@democ1:DEMOPDB> startup
Pluggable Database opened.
SYS@democ1:DEMOPDB> @parms
Enter value for parameter: inmem
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
inmemory_clause_default                                                                                                   TRUE     FALSE      FALSE
inmemory_force                                     DEFAULT                                                                TRUE     FALSE      FALSE
inmemory_max_populate_servers                      12                                                                     TRUE     FALSE      FALSE
inmemory_query                                     ENABLE                                                                 TRUE     FALSE      FALSE
inmemory_size                                      536870912                                                              FALSE    FALSE      TRUE
inmemory_trickle_repopulate_servers_percent        1                                                                      TRUE     FALSE      FALSE
optimizer_inmemory_aware                           TRUE                                                                   TRUE     FALSE      FALSE
 
7 rows selected.
 
SYS@democ1:DEMOPDB> -- inmemory_size value is inherited from CDB as documented. 
SYS@democ1:DEMOPDB> -- let's check which inmem parameters can be set at PDB level
SYS@democ1:DEMOPDB> 
SYS@democ1:DEMOPDB> select name from v$system_parameter where ispdb_modifiable = 'TRUE' and name like nvl('&name',name);
Enter value for name: inmem%
 
NAME
--------------------------------------------------------------------------------
inmemory_size
inmemory_clause_default
inmemory_force
inmemory_query
 
SYS@democ1:DEMOPDB> -- So inmemory_size can be set
SYS@democ1:DEMOPDB> alter system set inmemory_size = 0;
 
System altered.
 
SYS@democ1:DEMOPDB> alter system set inmemory_size = 10M;
 
System altered.
 
SYS@democ1:DEMOPDB> -- no bounce required, that's cool
SYS@democ1:DEMOPDB> -- let's make sure it works
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> create table kso.junk as select * from dba_objects;
 
Table created.
 
SYS@democ1:DEMOPDB> alter table kso.junk inmemory;
 
Table altered.
 
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
 
  COUNT(*)
----------
     91025
 
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO                            JUNK                                     13.0              4.1        3.2                 .0
                                                                             ----------------
sum                                                                                       4.1
 
SYS@democ1:DEMOPDB> -- so it works
SYS@democ1:DEMOPDB> alter system set inmemory_size = 0;
 
System altered.
 
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO                            JUNK                                     13.0              4.1        3.2                 .0
                                                                             ----------------
sum                                                                                       4.1
 
SYS@democ1:DEMOPDB> -- hmmm - doesn't seem to release memory already allocated, not too surprising
SYS@democ1:DEMOPDB> -- let's make sure nothing can be loaded after setting to 0 though
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> alter table kso.junk inmemory;
 
Table altered.
 
SYS@democ1:DEMOPDB> -- another quirk worth mentioning - "alter table X inmemory" flushes object from column store if it happens to already be there
SYS@democ1:DEMOPDB>
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
                                                                             ----------------
sum
 
no rows selected
 
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
 
  COUNT(*)
----------
     91025
 
SYS@democ1:DEMOPDB> -- should repopulate is memory is available
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
                                                                             ----------------
sum
 
no rows selected
 
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
                                                                             ----------------
sum
 
no rows selected
 
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
                                                                             ----------------
sum
 
no rows selected
 
SYS@democ1:DEMOPDB> alter system set inmemory_size = 10M;
 
System altered.
 
SYS@democ1:DEMOPDB> select count(*) from kso.junk;
 
  COUNT(*)
----------
     91025
 
SYS@democ1:DEMOPDB> @inmem_segs
Enter value for owner: 
Enter value for segment_name: 
 
OWNER                          SEGMENT_NAME                   ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO                            JUNK                                     13.0              4.1        3.2                 .0
                                                                             ----------------
sum                                                                                       4.1
 
SYS@democ1:DEMOPDB> -- let's try to make it bigger than the 500M we allocated at the container level
SYS@democ1:DEMOPDB> alter system set inmemory_size = 1G;
alter system set inmemory_size = 1G
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
 
SYS@democ1:DEMOPDB> -- No can do

So as documented, the PDB specific value of inmemory_size is inherited from the CDB if it is not explicitly set at the PDB level. But we can also modify it (without restarting anything) as long as the value doesn’t exceed the overall value allocated at the container level. Note that this can be used to control how much of the column store memory any one PDB can use and that you can under or over allocate the space as you see fit. However, a single PDB can not specify a value that exceeds the actual value set (and allocated) for the entire container DB as was shown by the last attempted change to set it to 1G when only 500M was allocated by the container DB.

Th-th-th-that’s all folks!

Good Blogs to follow

Good blogs keep popping up and other blogs fade into the back ground, so it’s hard to keep track of the good stuff out there. The following is a list of blogs I have either in the past gotten a lot out of or currently do. It would be great to get comments on what the best current blogs are and then iterate on this list and keep it updated.

There use to be a cool blog aggregator site called “collected.info” where I had aggregated a bunch of blogs but that site no longer seems to work and I’ve lost the list I built up there. There must be some new blog aggregator sites out there. Welcome suggestions.

The  Oaktable aggregates the blogs of its members, many list below, on it’s site oaktable.net.

 

Oracle Blogs

#222222;">SQL Server world

    #222222;">
  • Steve Jones - #1155cc;" href="http://www.sqlservercentral.com/blogs/" target="_blank">http://www.sqlservercentral.com/blogs/
  • Chris Shaw - #1155cc;" href="https://chrisshaw.wordpress.com/" target="_blank">https://chrisshaw.wordpress.com/
  • Brent Ozar - #1155cc;" href="http://www.brentozar.com/blog/" target="_blank">http://www.brentozar.com/blog/
  • Gail Shaw - #1155cc;" href="http://sqlinthewild.co.za/" target="_blank">http://sqlinthewild.co.za/
  • Jen McCown - #1155cc;" href="http://www.midnightdba.com/Jen/" target="_blank">http://www.midnightdba.com/Jen/
  • Sean McCown - #1155cc;" href="http://www.midnightdba.com/DBARant/" target="_blank">http://www.midnightdba.com/DBARant/

DevOps

Visualization

  • Good
    • #2970a6;" href="http://www.economist.com/blogs/graphicdetail" rel="nofollow">http://www.economist.com/blogs/graphicdetail
    • #555555;"> #2970a6;" href="http://vizual-statistix.tumblr.com/" rel="nofollow">http://vizual-statistix.tumblr.com/
    • #2970a6;" href="http://vizcup2.splashthat.com/" rel="nofollow">http://vizcup2.splashthat.com/
  • Bad :)
    • #2970a6;" href="http://junkcharts.typepad.com/" rel="nofollow">http://junkcharts.typepad.com
    • #2970a6;" href="http://wtfviz.net/" rel="nofollow">http://wtfviz.net

Unusual Deadlock

Prompted by a question on OTN I came up with a strategy for producing an ORA-00060 deadlock that DIDN’T produce a deadlock graph (because there isn’t one) and didn’t get reported in the alert log (at least, not when tested on 11.2.0.4). It’s a situation that shouldn’t arise in a production system because it’s doing the sorts of things that you shouldn’t do in a production system: but possibly if you’re trying to do some maintenance or upgrades while keeping the system live it could happen. Here’s the starting code:


drop procedure p2;
drop procedure p1;

drop table t1 purge;
create table t1 (n1 number);

insert into t1 values(1);
insert into t1 values(3);

create or replace procedure p1
as
begin
        update t1 set n1 = 2 where n1 = 1;
        dbms_lock.sleep(10);
        update t1 set n1 = 4 where n1 = 3;
end;
/

create or replace procedure p2
as

        procedure q
        is
                pragma autonomous_transaction;
        begin
                execute immediate 'drop procedure p1';
        end;

begin

        update t1 set n1 = 4 where n1 = 3;
        q;

end;
/

Of course you’re asking for trouble if you start doing DDL as part of your production code; and you’re asking for trouble if you start playing around with autonomous transaction; and if you use one to do the other you’re almost guaranteed to hit a problem eventually. All it takes in this case is a simple sequence of actions followed by a short wait (ca. 10 seconds):

Session 1: execute p1
Session 2: wait a couple of seconds, then execute p2

I got the following result from session 2:


BEGIN p2; END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TEST_USER.P2", line 8
ORA-06512: at "TEST_USER.P2", line 14
ORA-06512: at line 1

While this doesn’t show up in the alert log, I do get a trace file dumped for the session; in fact I got a trace file from both processes. The trace from the process that reported the deadlock started like this:


DEADLOCK DETECTED

  Performing diagnostic dump and signaling ORA-00060

  Complete deadlock information is located in the trace file of process (pid: 8, osid: 17861, DIA0)
    trace file: /u01/app/oracle/diag/rdbms/test/test/trace/test_dia0_17861.trc

-------------------------------------------------------------------------------

*** 2014-09-09 12:44:13.427
-------------------------------------------------------------------------------
HUNG PROCESS DIAGNOSTIC DUMP BEGIN:

    dump requested by process (pid: 8, osid: 17861, DIA0)
    trace file: /u01/app/oracle/diag/rdbms/test/test/trace/test_dia0_17861.trc
-------------------------------------------------------------------------------

The other process didn’t mention a deadlock, but started at the “HUNG PROCESS DIAGNOSTIC” line – the trace file had been triggered by the DIAG process. Of course, the DIAG process also dumped a trace file, summarising the situation; and this started with the following important note:


*** 2014-09-09 12:44:13.426
-------------------------------------------------------------------------------

DEADLOCK DETECTED (id=0xf678cfe3)

Chain Signature: 'enq: TX - row lock contention'<='library cache pin' (cycle)
Chain Signature Hash: 0x39f9868d

The rest of the trace file told me what the other two processes had been doing when the ORA-00060 was initiated, but the point I want to pick up here is that we have a very brief summary in the “Chain Signature” that tells us we’ve had a collision between some DML (enq: TX) and some sort of cursor-like problem (library cache pin) and not a simple data cross-over.

If you’ve looked at the original OTN posting you’ll see that the Chain Signature in that case is “Chain Signature: ‘library cache lock'<=’library cache pin’ (cycle)”, which indicates a collision restricted entirely to the library cache (the lock suggests someone is using a package/cursor while the pin suggests that another session is trying to destroy/(re)compile it – and they’re each trying to do it to each other’s package ! (In the past when I’ve a deadlock of this type it’s been reported as ORA-04020 rather than ORA-00060.) I still have to work out exactly how the OP got into their deadlock (especially in view of their latest comment) -but since I don’t have the right version of Oracle to hand, and it might be a version-specific bug, I don’t think I’m going to try.

 

Oracle Database 12.1.0.2.0 – Getting started with JSON Path Expressions

Yesterday my colleague Alex and I had the pleasure to do some extra presentations during AMIS’s Oracle OpenWorld preview evening. While still not getting around...
class="readmore">Read More

Embiggening the Marquee Menu Dots

The Marquee template on the Squarespace platform is wonderful with it's colorful images and parallax scrolling on index pages. It's one of my top two favorite templates. What's not so wonderful sometimes are those tiny dots in the parallax navigation menu at the right side of the browser window. 

The tiny targets in the parallax menu can be tough to hit with a mouse pointer, and a lot of detail or the wrong color in that right-side region can make the fly-out text tough to distinguish from the underlying image. What follows are a series of CSS mods by which you can bring some zest to that menu while making it into a larger and more legible target. 

Caution! As always, be sure to take responsibility when mucking about with custom CSS rules. Invest the time to personally understand what you are doing. Reading my book will help. 

Embiggening the Dots

Figure 1 shows the parallax menu in its default appearance. The dots are small and unobtrusive. They are visible against the particular background in the image, but might be difficult to hit with a mouse pointer on days when you've over-imbibed on the coffee. 

Figure 1. Squarespace Marquee's default parallax navigation menu

Figure 1. Squarespace Marquee's default parallax navigation menu

An interesting aspect of the dots in Figure 1 is that each is an HTML hyperlink element -- an ... tag sequence -- that has been filled with a black background and given a border with corners rounded to form a circle. The active link is indicated by removing the background color and allowing the circle to be transparent. 

You can change the height and width of the hyperlink elements, thereby controlling the size and shape of the dots. Write the following rules and place them in your Custom CSS Editor to change the dots into ovals and size them larger:

#parallax-nav ul li a {width:30px; height:15px;}
#parallax-nav ul li.active a {width:30px; height:15px;}

Read the selectors in these rules from right to left. The first rule targets a hyperlink (a) within a list item (li) within an unnumbered list (ul) within a div named parallax-nav. You'll need separate rules for the active and inactive links, because the active and inactive links are styled separately in the template's original CSS rule set. 

Figure 2 shows the result. Visibility is increased. The larger ovals really begin to stand out and catch your eye. They're also easier to hit and land on with a mouse pointer.

Figure 2. Squarespace Marquee's parallax menu with large, oval

Figure 2. Squarespace Marquee's parallax menu with large, oval "dots"

The menu text is now a problem. It's no good to have it overlapping the oval dots. Also notice how increasing the height of the dots has thrown off the vertical centering of the menu labels. 

Repositioning the Text

You don't want overlapping and out-of-position text like in Figure 2. It's easy to move the text around though, and a little bit of trial and error will set things right again. 

You'll find the text of each menu label in a span of class nav-title. Move the text left and right as needed by adjusting its right margin. Move the text up and down by way of the top margin. For example:

#parallax-nav .nav-title {
  margin-right: 2.5em;
  margin-top: -8px;
}

These rules move the menu text leftward and downward. Figure 3 shows the result, and you can see how much more nicely things are looking. The menu is useable as it sits now. 

Figure 3. Squarespace Marquee's parallax menu in usable form with good spacing and centered alignment

Figure 3. Squarespace Marquee's parallax menu in usable form with good spacing and centered alignment

The top margin setting deserves some explanation. It's easy enough to understand how adding some right margin moves the menu labels leftward. But negative top margin usually pulls an element upward, not downward. How does that work?

The secret to understanding the top margin setting is to realize that the default setting is margin-top: -11px. That makes -11px essentially the zero-point. Changing from -11px to -8px then has the effect of moving the text downward, which is what is needed to align the text to be horizontally centered with its corresponding dot. 

Upsizing the Text

Bigger dots demand larger text. Increasing the text size is a pretty basic CSS operation. So is making the text bold, to improve visibility if you are working against busy image backgrounds. Write the following rule to bolden the text and make it one-third again its normal size:

#parallax-nav .nav-title {
  font-weight: bold;
  font-size: 133%; 
  margin-top: -7px;
}

Figure 4 shows the result. Isn't it beautiful? The large, bold, white text really pops against the image in the background.

Figure 4. Large, bold, white text that stands out easily against a wicked-cool background image

Figure 4. Large, bold, white text that stands out easily against a wicked-cool background image

You may have noticed in this example how I tweaked the top margin of the text just a bit, reducing the negative margin by one more pixel over what you see earlier in Figure 3. You'll be doing the same thing when applying techniques from this post. Set your dot size. Set your text size. Then apply some trial-and-error to the top- and right-margin settings to find a pleasing alignment and spacing.

Filling the Background

You can eliminate any worry of your text standing out against your images by shading the background behind the text. Fill in with a shade of orange, for example. Here's how to do that:

#parallax-nav .nav-title {
  background-color: #ff6f1a; 
  padding-right: 3px !important;
  padding-left: 6px !important;
  padding-bottom: 0px !important;
  padding-top: 0px !important;
}

Figure 5 shows the result. The bold and white text stands out nicely, and there's no missing that bright orange background. Viewers' eyes will be right on the menu items as they scroll their mouse pointer over the navigation dots. 

Figure 5. Squarespace Marquee's parallax menu items with an orange background

Figure 5. Squarespace Marquee's parallax menu items with an orange background

The rule this time looks long and complicated, but what's happening is really very simple. First a background color is applied. Then some padding is added left and right for the sake of appearance. Feel free to adjust the padding values as desired until you get a look that you like. Mine here are from trial and error. 

Making It Shapely

Rectangles are a crude shape. Sure, the bright orange in Figure 5 gets your attention, but the squared-off corners are just not pleasing. You can improve their appearance by rounding them off. Do that by specifying a border-radius as in the following example:

#parallax-nav .nav-title {
  border-radius: 25%;
  text-align: center;
  padding-top: 3px !important;
  margin-top: -11px;
}

You can see the result in Figure 6, and it's much more pleasing to the eye than the harsh rectangle shape in Figure 5. I've again adjusted the top margin by trial and error to align the menu item with the corresponding dot. I've added some padding on top of the text because the font in use happens to place the letters high in the block. Different fonts will position letters differently, and some trial-and-error with padding and margins may be necessary to get just the right look.

Figure 6. The final result: a blazing orange background that cannot go unseen

Figure 6. The final result: a blazing orange background that cannot go unseen

Squarespace's Marquee template is one of my favorites. I've said that before, and I'll repeat myself. The mods in this post provide a way to dress up the Marquee design by adding a bit of flair and your own personal style to what is already a fantastic template. 


coverThumbLarge.jpg

Learn CSS for Squarespace

9.99

Learn CSS for Squarespace is a seven-chapter introduction to working with CSS on the Squarespace platform. The book is delivered as a mobi file that is readable from the Kindle Fire HDX or similar devices. A complimentary sample chapter is available. Try before you buy.

View the complete description for more details.

Add To Cart

Embiggening the Marquee Menu Dots

Building your Squarespace website in the Marquee template? Having trouble
seeing or clicking on those little dots that make up the parallax
navigation on the right side of your browser window?...



Read the full post at www.gennick.com/database.

inmemory: Why did that table was not populated in the column store?

I enabled an huge 70G table for inmemory population, I expected the inmemory population to take a while, but the population didn’t complete even after letting it run for a day. Why?

ASH data

Initial review of the server shows no issues, no resource starvation. This must be a problem with Oracle processes itself. I started digging further, and ASH data shows that in numerous samples the process was seen reading block using single block I/I calls. Also object_id matches with the table I was trying to populate.

   select * from (
    select start_time, end_time, sql_id,event, current_obj#,  cnt_on_cpu + cnt_waiting tot_cnt,
  	rank () over ( order by (cnt_on_cpu + cnt_waiting) desc ) rnk
    from  (
    select
  	  min(start_time) start_time,
  	  max(end_time)   end_time,
  	  sql_id,event,current_obj#,
  	  sum(decode(session_state,'ON CPU',1,0))  cnt_on_cpu,
 	  sum(decode(session_state,'WAITING',1,0)) cnt_waiting
    from
     ( select
 	first_value(sample_time) over( order by sample_time ) start_time,
 	last_value(sample_time) over( order by sample_time
 				rows between unbounded preceding and unbounded following ) end_time,
 	sql_id,event, session_state, current_obj#
	from
 	     (select * from v$active_session_history ash where session_id= &&sid and session_serial#=&&serial_number)
     )
   group by sql_id, event, current_obj#
    )
  )
  where rnk 
/
START_TIME		  END_TIME		    SQL_ID	  EVENT 			 CURRENT_OBJ#	 TOT_CNT	RNK
------------------------- ------------------------- ------------- ------------------------------ ------------ ---------- ----------
18-AUG-14 08.42.03.702 AM 18-AUG-14 09.02.06.463 AM		  db file sequential read	       168967	     990	  1
												       168967	     156	  2
								  direct path read		       168967	      50	  3
						    bdwtqttka2w2y					   -1	       3	  4
						    bdwtqttka2w2y direct path read		       168967	       1	  5
						    24uqc4aqrhdrs				       168967	       1	  5
													   -1	       1	  5

That doesn’t make sense! Whole table must be loaded in to the column store, why would the session initiate huge amount of single block reads? I expected multi-block reads similar to a full table scan. So, I used Tanel’s snapper tool to understand the statistics and time model statistics.

- Session Snapper v2.01 by Tanel Poder ( http://www.tanelpoder.com )

----------------------------------------------------------------------------------------------------------------------
    SID, USERNAME  , TYPE, STATISTIC                               ,         DELTA, HDELTA/SEC,    %TIME, GRAPH
----------------------------------------------------------------------------------------------------------------------
…
    119, (W000)    , STAT, table scan rows gotten                  ,        214554,      6.92k,
    119, (W000)    , STAT, table scan blocks gotten                ,          1825,      58.87,
    119, (W000)    , STAT, table fetch by rowid                    ,             1,        .03,
    119, (W000)    , STAT, table fetch continued row               ,          4107,     132.48,
    119, (W000)    , STAT, index scans kdiixs1                     ,             2,        .06,
    119, (W000)    , STAT, IM prepopulate CUs                      ,             1,        .03,
    119, (W000)    , STAT, IM prepopulate bytes from storage       ,      25165824,     811.8k,
    119, (W000)    , STAT, IM prepopulate accumulated time (ms)    ,         77305,      2.49k,
    119, (W000)    , STAT, IM prepopulate bytes in-memory data     ,       9962722,    321.38k,
    119, (W000)    , STAT, IM prepopulate bytes uncompressed data  ,      44530632,      1.44M,
    119, (W000)    , STAT, IM prepopulate rows                     ,        378657,     12.21k,
    119, (W000)    , STAT, IM prepopulate CUs memcompress for query,             1,        .03,
    119, (W000)    , STAT, session cursor cache hits               ,             2,        .06,
    119, (W000)    , STAT, buffer is not pinned count              ,         26142,     843.29,
    119, (W000)    , STAT, parse count (total)                     ,             2,        .06,
    119, (W000)    , STAT, execute count                           ,             2,        .06,
    119, (W000)    , TIME, background IM prepopulation elapsed time,      32113087,      1.04s,   103.6%, |@@@@@@@@@@|
    119, (W000)    , TIME, background cpu time                     ,       3033539,    97.86ms,     9.8%, |@         |
    119, (W000)    , TIME, background IM prepopulation cpu time    ,       3014541,    97.24ms,     9.7%, |@         |
    119, (W000)    , TIME, background elapsed time                 ,      32131726,      1.04s,   103.7%, |@@@@@@@@@@|
    119, (W000)    , WAIT, db file sequential read                 ,      28170073,   908.71ms,    90.9%, |@@@@@@@@@@|
    119, (W000)    , WAIT, direct path read                        ,        601828,    19.41ms,     1.9%, |@         |

Analysis

I see that 90% of the time spent on single block reads, which matches with ASH metrics. W000 process is processing about 6920 rows per second, it could do much better. But, look closely, and check ‘table fetch continued row’ statistics (4th row in the output). About 132 chained rows per second was processed! Chained rows could cause huge number of single block reads. But I expected that the whole table to be read similar to a Full table scan skipping the chained rows. Full tables scan will read the whole table using Full scan, when it encounters the tail pieces of that row later it will be matched with head row, but that’s not what happened with inmemory population.

Looks like, inmemory worker processes must follow the chained row: Because the table is populated in parallel by many worker processes and a worker process need to read the whole row to populate column store, it can not wait until the chained row’s tail block to be read. So, the worker process will simply follow the chain. That’s the reason why all worker processes were triggering huge amount of single block read calls and populating the table very, very slowly, almost painful to watch. So, if you are converting to use inmemory option, make sure to check for chained rows. Of course, you need to use ‘analyze table list chained rows’, as chain_cnt column is not maintained by dbms_stats package.

We know the reason for chained rows in that table. We reorg’ed it to remove row chaining. After the reorg, table was populated quickly.

Update: Minor edit to fix a few grammatical mistake.

inmemory: sa00 process

After the restart of a 12c inmemory database with 300GB+ SGA, I noticed that an Oracle background process sa00 was consuming a bit of CPU. Documentation suggests that it is SGA Allocator process, however, ipcs -ma command shows that the shared memory segment is already allocated. I was curious, of course, what would that background process will be allocating?.

pstack

Process stack of the process shows that it is touching SGA pages to pre-page SGA memory pages.

$ pstack 21131
#0  0x0000000000d9996e in ksmprepage_memory ()
#1  0x0000000000d99369 in ksm_prepage_sga_seg ()
#2  0x0000000003a5c78b in skgmapply ()
#3  0x0000000000da686a in ksmapply_v2 ()
#4  0x0000000000d9a82c in ksmprepage ()
#5  0x0000000000d99f89 in ksm_sslv_exec_cbk ()
#6  0x0000000000f79810 in ksvrdp ()
#7  0x00000000031013b7 in opirip ()
#8  0x0000000001bb0a08 in opidrv ()
#9  0x00000000026c0f71 in sou2o ()
#10 0x0000000000bbd85e in opimai_real ()
#11 0x00000000026cb6bc in ssthrdmain ()
#12 0x0000000000bbd72c in main ()

$ ps -ef|grep  21131
oracle   21131     1 96 15:00 ?        00:01:50 ora_sa00_XXXXXX

Two notable changes in this area:
1. Incidentally, pre_page_sga initialization parameter was defaulted to a value of FALSE until 11.2. In version 12.1, the parameter value defaults to TRUE.
2. As huge SGA is expected for inmemory databases, a new background process SA00 is also created to touch all SGA memory pages at startup.

As inmemory worker processes will be populating the inmemory column store soon after the startup, touching memory pages at instance startup makes sense, and the feature should improve the performance of inmemory population. At least, Worker processes doesn’t need to suffer from huge amount page faults. (note that this SGA is not using hugepages).

Change to the parameter pre_page_sga also should improve the performance of inmemory scan, as the memory map entries will be setup at the process startup. However, I am not quite clear, how this change will affect the performance of a connection storm, i.e. if there are numerous database connections in a short period of time and disconnects. Isn’t that the reason why the pre_page_sga was defaulted to FALSE? But, I need to test this thoroughly to understand the implications further.

Inmemory: Not all inmemory_size is usable to store tables.

I have been testing the inmemory column store product extensively and the product is performing well for our workload. However, I learnt a bit more about inmemory column store and I will be blogging a few them here. BTW, I will be talking about internals of inmemory in Oaktable world presentation, if you are in the open world 2014, you can come and see my talk: http://www.oraclerealworld.com/oaktable-world/agenda/

inmemory_size

Inmemory_size initialization parameter determines the amount of memory allocated to the in-memory column store. But only 80% of that memory value is allocated to store the objects. For examples, if you set inmemory_size=272G, then only 217G (=272*0.8) is used to store the objects, and the remaining 55GB is allocated for inmemory journal and internal objects. This is the reason why the inmemory heap is also split and tagged: IMCA_RO and IMCA_RW. IMCA_RW seems to be storing inmemory journal and IMCA_RO is to store objects in the memory. (Previous statement is not completely validated yet).

SELECT mem inmem_size,
       tot disk_size,
       bytes_not_pop,
       (tot/mem) compression_ratio,
       100 *((tot-bytes_not_pop)/tot) populate_percent
FROM
  (SELECT SUM(INMEMORY_SIZE)/1024/1024/1024 mem,
    SUM(bytes)              /1024/1024/1024 tot ,
    SUM(bytes_not_populated)/1024/1024/1024 bytes_not_pop
   FROM v$im_segments
   ) 
/
INMEM_SIZE  DISK_SIZE BYTES_NOT_POP COMPRESSION_RATIO POPULATE_PERCENT
---------- ---------- ------------- ----------------- ----------------
    217.25     231.17           .00        1.06407869              100

So, you should plan accordingly when you enable inmemory option. Notice that the compression ratio is about 1, but, that is because these objects are already compressed using HCC compression and so, memory compression is not going to give us a better compression. However, I see that size of a few objects have increased in-memory compared to the disk size, but, I am hoping that will be considered as a bug and will be fixed in later release.

Unfortunately, inmemory area chunks are not visible in v$sgastat at all, but v$sga shows the inmemory area correctly (show SGA output is correct as it queries v$sga too).

Can you have high redundancy files in a normal redundancy diskgroup?

One of the perks of teaching classes is that I get to research questions asked. In the last Exadata Administration Class I taught someone asked: can you have your disk groups in Exadata on normal redundancy yet have certain databases use high redundancy? This would be a good interview question …

The answer is yes, which I remembered from researching material on the 11g RAC book but I wanted to prove that it is the case.

Update: I planned a second blog post where I wanted to test the effect but Alex Fatkulin was quicker, and I promise I didn’t see his post when I wrote mine. Otherwise there probably wouldn’t have been one :) In summary, you aren’t really any better protected. The disk group remains at normal redundancy, even with the data files in high. Looking at Alex’s results (and I encourage you to do so) I concur with his summary that although you have a 3rd copy of the extent protecting you from corruption, you don’t have higher resilience.

This is not Exadata specific by the way. When I face a question around ASM and internals my first idea is to use an Internet search engine and look up the ASM work by Luca Canali. This page is almost always of relevance when looking at ASM file structures: https://twiki.cern.ch/twiki/bin/view/PDBService/ASM_Internals.

ASM Templates

I am not aware of many users of ASM who know about ASM templates, although we all make use of them. The templates define the striping and mirroring of files, and are explained in the ASM Admin Guide chapter 5. You are not limited to using the Oracle provided templates, you can create your own as well, which is key to this post. I headed over to the ASM instance on my cluster and created the template:

SQL> alter diskgroup data add template adminclassHigh attributes(high);

Diskgroup altered.

With the template in place I can create a high redundancy tablespace for example in my normal redundancy diskgroup:

SQL> select name, group_number, type from v$asm_diskgroup;

NAME                           GROUP_NUMBER TYPE
------------------------------ ------------ ------
RECO                                      3 NORMAL
DBFS_DG                                   2 NORMAL
DATA                                      1 NORMAL

Unlike what the documentation suggests you do not need to change db_create_file_dest for this to work.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA

Let’s put that template to use.

SQL> create tablespace testhigh datafile '+data(adminclassHigh)' size 10m;

Tablespace created.

SQL> select file_name from dba_data_Files;

FILE_NAME
-----------------------------------------------------------------------------------------------
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/system.363.857664247
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/sysaux.364.857664247
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/users.365.857664253
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/testhigh.357.857880753

Note that I specify the newly created template in () in the datafile clause. The tablespace has been created, next I need the file number and the incarnation to look up how the extents are distributed.

SQL> select DISK_KFFXP,AU_KFFXP,PXN_KFFXP,XNUM_KFFXP,LXN_KFFXP 
  2   from x$kffxp  where number_kffxp = 357 and incarn_kffxp = 857880753;

DISK_KFFXP   AU_KFFXP  PXN_KFFXP XNUM_KFFXP  LXN_KFFXP
---------- ---------- ---------- ---------- ----------
         4      73706          0          0          0
        23      73767          1          0          1
        24      28895          2          0          2
         1      73679          3          1          0
        27      30015          4          1          1
        22      73717          5          1          2
        21      73725          6          2          0
         7      73698          7          2          1
        33      73707          8          2          2

9 rows selected.

What looks a little cryptic can be translated using Luca’s notes as:

  • DISK is the ASM disk where the extent is located (as in V$ASM_DISK)
  • AU indicates the relative position of the allocation unit from the beginning of the disk
  • PXN is the progressive extent number ( = actual extent)
  • XNUM is the ASM file extent number (mirrored extent pairs have the same extent value)
  • LXN indicates the type of extent: 0 = primary extent, 1 = first mirror copy, 2 = second mirror copy

So you can see that each primary extent has 2 mirror copies, also known as high redundancy.

If you omit the template in the datafile clause you get what you’d expect: normal redundancy.

SQL> drop tablespace testhigh including contents and datafiles;

Tablespace dropped.

SQL> create tablespace testhigh datafile '+data' size 10m;

Tablespace created.

SQL>  select file_name from dba_data_Files;

FILE_NAME
-----------------------------------------------------------------------------------------------
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/system.363.857664247
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/sysaux.364.857664247
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/users.365.857664253
+DATA/PRI/02810A3FB7F65856E053D908A8C003E1/DATAFILE/testhigh.357.857880927

And on ASM:

SQL> select DISK_KFFXP,AU_KFFXP,PXN_KFFXP,XNUM_KFFXP,LXN_KFFXP
  2  from x$kffxp  where number_kffxp = 357 and incarn_kffxp = 857880927;

DISK_KFFXP   AU_KFFXP  PXN_KFFXP XNUM_KFFXP  LXN_KFFXP
---------- ---------- ---------- ---------- ----------
        30      73724          0          0          0
        16      73749          1          0          1
         0      73690          2          1          0
        28      73682          3          1          1
        17      73722          4          2          0
        10      73697          5          2          1

6 rows selected.

As you can see each extent has 1 mirror copy, not 2.