I like Violin plots in R

I like the Violin plot and here’s one compared to a box plot.  It’s a box plot and a Kernel Density plot together.

VIOLINPLOT_Example Violin PlotBOXPLOT_exampleBox Plot

The R code to produce it is below.

Read more on the wiki Wiki Violin Plots

I like these sites too. R-Bloggers Vioplots with Clipping, Box Plots Reference

> x2 <- racot$ELAPSED_SECONDS[racot$THREADS==2 & racot$TEST_CORES==2]
> x4 <- racot$ELAPSED_SECONDS[racot$THREADS==4 & racot$TEST_CORES==2]
> x8 <- racot$ELAPSED_SECONDS[racot$THREADS==8 & racot$TEST_CORES==2]
> x16 <- racot$ELAPSED_SECONDS[racot$THREADS==16 & racot$TEST_CORES==2]
> vioplot(x2,x4,x8,x16,names=c("2","4","8","16"),col="Blue")
> title('RAC ONE - 2 Cores per Proc (4 Total)',xlab="Long Threads",ylab="Elapsed Seconds")

Bad tasting Pie

The material looks great for “Oracle R”. So, I thought I’d get a few books and try a quick lab for myself.

The books covered data loading, data manipulation, statics stuff I’ve either forgotten or never learned, some plotting and a host of other useful stuff to be discovered.

So to focus my learning and usefulness I quickly eliminated data loading and data manipulation since I have tons of data, and already have a rich pl/sql language to do that anyway. So maybe some data retrieval from the database and a plot.

At several of my recent clients auditing has been a top priority, so how about a pie chart showing the status of all users.

I’m using the default user “RQUSER” and played around with a few of the demos. So far I like the “RStudio” app.

and find:


> ore.ls()
[1] "DF1" "DF2" "IRIS" "IRIS_TABLE"

So, no visibility to dba_users, what I’d normally pick.

Create local maybe?

GRANT SELECT ANY TABLE TO rquser
/
create synonym users for sys.dba_users
/

And no luck, local synonyms don’t show up on the R tools. And I did grant select any table and no luck with ore.ls() either. So more reading.

How about creating a local table so I can move passed this and have some pie – charts.


create table users as select * from dba_users;

Progress:
> ore.ls()
[1] “DF1” “DF2” “IRIS” “IRIS_TABLE” “USERS”

Now for my pie, I’d like to show a group by account status. Well I can’t directly.

I type:
> head(USERS)

and it shows my table correctly, but don’t see any clear functions to get there. I think they are there, but I just haven’t discovered them yet. I do see some good filtering, but I’m used to writing a good query and being done with it. So, moving on. Next plan, create a table with the right columns, need my pie.


create table users as select account_status, count(*) status_count from dba_users group by account_status;

> ore.ls()
[1] "DF1" "DF2" "IRIS" "IRIS_TABLE" "USERS"

Progress...
head(USERS)
ACCOUNT_STATUS STATUS_COUNT
1 LOCKED 3
2 OPEN 7
3 EXPIRED & LOCKED 7

Now for Pie:
pie(USERS$STATUS_COUNT,labels=USERS$ACCOUNT_STATUS)
Error in seq.int(x[i], x[i + 1], length.out = n) :
'length.out' must be a non-negative number

Oops, Houston we have a problem.

So what’s going on here?

Let’s try something different, using one of the more normal examples (without the Oracle part).


&GT; slices <- c(3,7,7)
&GT; lbls <- c("LOCKED","OPEN","EXPIRED & LOCKED")
&GT; pie(slices,labels=lbls)

Success with the data going in directly:
Rplot

So what’s up?

I know that Oracle uses Oracle uses data frames to store some of it’s objects and that the R packages are overloaded so let’s check the classes.


> class(slices)
[1] "numeric"
> class(USERS$STATUS_COUNT)
[1] "ore.numeric"
attr(,"package")
[1] "OREbase"

Trying the plot package:

plot(USERS$STATUS_COUNT,USERS$ACCOUNT_STATUS)

Rplot_PLOT

That worked, adding values instead of labels for each number, I’m sure there’s a way to change that, but the important thing is that it accepted my values with no errors and produced a plot.

OK, well lots of learning, but no sweet pie. Too much for Christmas anyway.

Installing Oracle R

I’m trying to learn R, particularly Oracle R. Setting it up on a W7 laptop first and see what can be learned. Then possibly some bigger machines, later.

Pulled a getting started doc from Oracle’s website http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/index.html
“ore-trng1-gettingstarted-1501628.pdf” The whole series of training documents are helpful to get started.

Adding a user went fine as well running “rquser.sql” (provided by Oracle), but I prefer a less cryptic script. So I replaced some of the code in rquser.sql for the next time I run it:

-- create RQUSER user
create user &&new_user_name identified by &&new_password
default tablespace &&new_default_tablespace
temporary tablespace &&new_temp_tablespace
quota &&new_quota on &&new_default_tablespace;

-- grant privileges
grant create session, rqrole
to &&new_user_name;

-- BUG: the following grant does not work when granted to RQROLE
grant execute on rqsys.rqGroupEvalImpl to &&new_user_name;

alter session set current_schema = &&new_user_name;

Not sure why you’d grant quota to anything other than the default tablespace on Oracle XE.

Next, started the two R clients. The first was the generic R 64-bit version and ran a few of the demo packages. Then I tried the RStudio and compared the graphics pretensions between the two. Caught me off guard when it asked me what tool to use to open the demo file, from RStudio, of course RStudio, it worked.

I did get the error:

Error: cannot change value of locked binding for 'all'

Going back into the database, found I had to grant execute on SYS.DBMS_SYSTEM to rqsys for rqsys.libPath.

Overall the instructions provided by Oracle are good. They worked perfectly where I followed them.

Now on to more.