Oakies Blog Aggregator

Why being wrong can be awesome

OK, Now that I’ve started the post with a nice click-bait heading, let’s get down to the business of being wrong. Smile

I did a lot of conference presentations last year, and the great thing about that for me was that I got to meet a lot of new people in the Oracle community in the Developer and DBA space. One of the questions that came up over and over again was about putting one’s knowledge “out there” in the community and how to deal with the repercussions of that.  In particular, “What if you publish something that is proven wrong?”

Here’s the thing about being wrong …. there’s two likely outcomes:

  • Someone tells you that you are wrong, or
  • You never know that you’re wrong and you wallow about in flawed darkness for all eternity.

Which would you prefer ? Smile

This is really all about perception from both the blogger and the reader, and the way they behave.

If you are the reader and you find something that is incorrect, you have choices:

  • Absolutely go ballistic on the author via comments, insult their intelligence, tell them how much smarter than them you are, and that they should never have been born, or
  • Initiate a reasoned discussion about where the errors might be, how the author may have come to that point, discuss boundary conditions and both leave the discussion more knowledgeable as a result.

Rest assured, if you take the former position, no amount of smarts is going outweigh the public reputation you’ve just acquired as being a schmuck.

And similarly, as an author of content, when someone points out an error you can:

  • take it in good faith, and work toward improving your knowledge by investigating further and collaborating with the person who discovered it, or
  • just deny it, and lose your mind at them and denigrate them so that you don’t lose your fine public standing.

If you do the latter….guess what you just lost?  Yup, your fine public standing.

And here’s the thing. Even if the person pointing out the error is indeed lacking the basic skills of civility, and is demonstrating their “schmucky-ness” all over your blog, just put that aside and focus on improving the content.  Other readers will pick up on this, and they’ll value your contribution to the community much more than Joe Schmuck.  They’ll be “red flagged” in the minds of the community as “one of those members that just isn’t worth the time of day”.

And if you’re wondering what the motivation for this post is – just this morning on an AskTOM answer, one of the Product Managers inside Oracle reached out to me and said “Hey Connor – I don’t think that answer is completely correct” and gave some me some additional content about the cause, and how to improve the answer.  The net result of that:

  • I learn some new stuff!
  • The community gets better content from AskTOM!
  • I make a new contact within the Oracle organization!

So don’t ever let being wrong stop you from contributing to the community.  It’s the best way of improving yourself and the community as well.

Linux for the SQL Server DBA- Part II

So we’ve covered a few basics of a Linux host in Part I

  • The File System
  • Users, groups
  • Basic Commands with basic arguments
  • And file and directory permissions

Hopefully, what I share next will build on the first post and enhance your knowledge as you move forward with Linux.

Reading the contents of a directory, gathering information on files and locating files are some of the most common tasks a DBA will perform as they work on a server.  Many of the following commands require you to “pipe” a second command onto the first.  This is done by joining the commands with a “|”, (pipe).

Size Information

We’ll start out by gathering size information about your Linux server and directories.

df -h will offer you information about the host, connected luns in a format that’s easier to read than if you left the “-h” argument off.

df -h

Output Example:  $ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_linuxsource-lv_root
                       14G  3.0G   11G  23% /
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/xvda1            477M  130M  322M  29% /boot
/dev/xvdf              30G  7.0G   21G  25% /u01
/dev/xvdg              20G  4.1G   15G  22% /u02

You can see each of the LUNS, including all information about those.  The far left file system column is the actual lun name vs. the far right that shows the mount name.  /u01 is a common directory for installations and /u02 is a common directory for datafiles.  Don’t be surprised that Linux design comes with less logical or physical luns than Windows.  This is quite common.

You can see the storage allocation, amount used and space available for each.

You can also look at file storage usage at the directory level to capture the immediate directory values, (and it will display every directory in the destination that you are running the command in, so in our example, I’ll add a directory to the argument):

du -h

Output Example: $ du -h /u02/oracle/scripts
508K /u02/oracle/scripts

Listing Information

This is done with the list command, (ls).  There are a number of arguments that are used, but the following I think are the most important ones for a DBA to know when working on Linux.

Last post we discussed the importance of viewing hidden files, (such as those beginning with a “.”)

ls -la

Output Example: $ ls -la
total 2712
drwxr-xr-x. 10 delphix oinstall    4096 Oct 18 19:28 .
drwxr-xr-x.  7 root    root        4096 May 13  2016 ..
lrwxrwxrwx   1 delphix oinstall      24 Jan 13  2017 11g.env -> /u02/app/content/11g.env
-rwxr-xr-x   1 delphix oinstall     648 Jul  6  2015 aseTail
-rwxr-xr-x   1 delphix oinstall     509 Jul  6  2015 aseTest
-rwxr-xr-x   1 delphix oinstall     509 Jul  6  2015 ase_test.sh
-rw-r--r--   1 delphix oinstall     470 Jan 13  2017 .bash_profile
-rw-r--r--   1 delphix oinstall     292 Dec 29  2014 .bashrc

Without the “a” argument at the end, you won’t be able to view the .bash_profile or any unique .profile settings that a user has configured.

In the example list above, note that not only do we see the immediate folder, (‘.’) but the folder above the one we’re in, (‘..’) There’s also an alias for 11g.env that points to the full directory path and then files starting with a ‘.’, (.bash_profile and .bashrc)  These are your configuration files for the user that are loaded whenever the user logs in.  All profile settings and such are contained in these files.

Referring back to our previous post, we can view the permissions for each of the files listed.  The owner, the group and other.

Using the aseTail file, we can see the permissions for each are: -rwxr-xr-x

  • Owner has Read, (r) write, (w) and execute, (x) = 7
  • Group has Read, (r) and execute, (x) = 5
  • Other has Read, (r) and execute, (x) = 5

If we were to assign numerical values to the permissions, aseTail = 755

We could also display the files from the most recent, descending:

ls -ltr

Output Example: $ ls -ltr
total 504
-rwxrwxr-x 1 oracle oinstall    576 Sep 16  2014 startup.sh
-rwxrwxr-x 1 oracle oinstall    442 Jan 14  2015 shutdown.sh
-rwxrwxr-- 1 oracle oinstall 505876 Jun 23  2015 startup_shutdown.log

The files are sorted with the oldest file at the top, (Sept. 16 2014) to the most recent, (Jun 23 2015).

To sort files by size, largest in bytes, at the bottom:

ls -lSr
Output Example: ls -lSr
total 504
-rwxrwxr-x 1 oracle oinstall    442 Jan 14  2015 shutdown.sh
-rwxrwxr-x 1 oracle oinstall    576 Sep 16  2014 startup.sh
-rwxrwxr-- 1 oracle oinstall 505876 Jun 23  2015 startup_shutdown.log

You can also use the “|” to change how the data is displayed and add a second command after the argument or “pipe”:

ls -lSr | less
Output Example: total 504
-rwxrwxr-x 1 oracle oinstall    442 Jan 14  2015 shutdown.sh
-rwxrwxr-x 1 oracle oinstall    576 Sep 16  2014 startup.sh
-rwxrwxr-- 1 oracle oinstall 505876 Jun 23  2015 startup_shutdown.log
(END) 

To exit, hit "q"uit

Searching for Files

OK, we’re ready for commands that are more than two letters long!  The find command can locate files on your Linux host.

#323944; color: #f8f9fa; font-family: monospace, serif; font-size: 0.9em; white-space: pre-wrap;">find 
find **

Output Example: $ find get*
get-pip.py

Search for a file in a directory, (performed on my Mac, so don’t think I’m storing Powerpoint files one of my Linux hosts… :))

find /**

Output Example: find /Users/kellyn/Desktop/*.pptx
/Users/kellyn/Desktop/Containers_DevOps_Kgorman.pptx
/Users/kellyn/Desktop/DBA_devops_dataops.pptx
/Users/kellyn/Desktop/OTW_indexes_mssql_ora.pptx
/Users/kellyn/Desktop/triad_evang.pptx

All files that match my search criteria and reside on the Desktop folder are displayed with this search.

Last but not least, there are times you need to know what installation patch is being used.  In our example, lets say we need to know which of two java installations are being used.  You can use the “which” command to do this:

which 

Output Example:  $ which java
/usr/bin/java

If you want to see how many versions of an application is installed, you can use the -a argument:

#323944; color: #f8f9fa; font-family: monospace, serif; font-size: 0.9em; white-space: pre-wrap;">which -a 

Output Example:  which -a oracle
/u01/app/oracle/product/11.2.0.4/db_1/bin/oracle
/u01/app/oracle/product/12.2/db_home1/bin/oracle

This gives you a good start on navigation, searching and locating.  Next we’ll get into VI/VIM, (good ol’school standards that every DBA should know when editing files!)

 

 

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Linux for the SQL Server DBA- Part II], All Right Reserved. 2017.

The post Linux for the SQL Server DBA- Part II appeared first on DBA Kevlar.

Question Time

It’s that time of year again – the UKOUG Tech conference is approaching and I’ve organised a panel session on the Cost Based Optimizer.

This year I’ve got Christian Antognini, Nigel Bayliss, Maria Colgan and special guest star, all the way from Australia, Richard Foote on the panel, with Neil Chandler and Martin Widlake taking on their inimitable role of MCs.

If you’ve got any questions you’d like to put to the panel, you will have a chance to write them down on the day, but it would be nice to have a few supplied in advance in the comment below.  Tactical, strategic, technical, or just plain curious – this is a panel that can tell you what can be done, what shouldn’t be done, and how to do the things you shouldn’t do but sometimes have to.

If you prefer to email your questions then click this link.

Latest thought – a tweet would be a nicely sized question – if you want to reply to this one.

Best method for tuning sub-optimal execution plans

How do you determine if the Oracle SQL optimizer has created a sub-optimal execution plan? re-run statistics and see what happens? wait for Oracle to find other execution plans? What if neither method is helping? Do you read the execution plan? What do you look at? Differences in actual vs estimated? How successful is that? Look for full table scans?  Do you look at the 10053 trace? How much time and effort does that take?  What do you look at in the 10053 trace. Do you have a systematic methodology  that works in almost all cases?

Well there is a method that is reliable and systematic. It’s laid out in Dan Tow’s book SQL Tuning.

The method is tedious as it requires a lot of manual work to draw join trees, identify constraints and relationships, manual decomposition and execution of every 2 table join in the statement. It can add up to a lot of work but it is systematic and dependable.

Cool thing is it can all be done automatically with a tool called DB Optimizer that now (as I look today) only cost about $400.

If you cost your company $50/hour then in 8 hours of saved work it’s paid for its self. In my experience as a DBA I have serveral SQL a year that take me over a day to optimize manually but that I can get done in a few minutes with DB Optimizer.  Thus with just one hard SQL the tool has paid for itself. The DB Optimizer analysis might run for a couple hours, but afterwords with the data it collects and presents, I can find better tuning path in minutes if it exists.

 

Here is previous blog post that gives some an overview

Here is a video that explains the method. (Same presentation in a different video.)

Slides from the videos.

Here is Dan Tow’s book SQL Tuning that originally laid out the method.

Here is post by Jonathan Lewis demonstrating the method.

Pick up a copy. I think it’s super cool and interested in feedback on the your experiences.

 

sql9

Oracle C functions annotations

Warning! This is a post about Oracle database internals for internals lovers and researchers. For normal, functional administration, this post serves no function. The post shows a little tool I created which consists of a small database I compiled with Oracle database C function names and a script to query it. The reason that keeping such a database makes sense in the first place, is because the Oracle C functions for the Oracle database are setup in an hierarchy based on the function name. This means you can deduct what part of the execution you are in by looking at the function name; for example ‘kslgetl’ means kernel service lock layer, get latch.

To use this, clone git repository at https://gitlab.com/FritsHoogland/ora_functions.git

Use the ‘sf.sh’ (search function) script to query the functions. The script uses sqlite3, use the sqlite rpm package to add this (on linux), or use the packaging method of your platform.

This is how to use sf.sh :

$ ./sf.sh kglGetMutex
kglGetMutex -- kernel generic lock management
---

This shows the sf.sh script could find the first 3 letters (underlined), which probably mean kernel generic lock management. It couldn’t find ‘GetMutex’ however that is so self-explanatory that it doesn’t need annotation.

This is how a full function annotation looks like:

$ ./sf.sh qercoFetch
qercoFetch -- query execute rowsource count fetch
----------

Here the full function is underlined, which means the entire function is found.

If you found explanations for Oracle database code locations, or found an error in the annotation (I made assumptions here and there), please send them to frits.hoogland@gmail.com, or react to this post, so I can add or update it.

Tagged: C, debug, function, internals, oracle, research

nVision Performance Tuning: 1. nVision Performance Options

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

At no point when defining a nVision report does the developer directly specify a SQL statement, all the SQL is generated by nVision from the report definition.  That also means that it is not possible to directly intervene and change the SQL statement, for example, to add an optimizer hint.

However, the way that the SQL is generated can be controlled via the nVision performance options. Setting this appropriately can make a significant difference to nVision performance, but the optimal settings will differ from system to system, from tree to tree and sometimes even report to report.

Specification of Performance Options

nVision performance options are specified for named trees.  Since PeopleTools 8, they are defined in the tree definition.

Tree Performance Options in PIA

They are stored in the database in PeopleTools table PSTREEDEFN.

#666666 .5pt; mso-border-themecolor: text1; mso-border-themetint: 153; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
Field Name
Field Value
Description
#666666 .5pt; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
TREE_ACC_METHOD
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
D
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Use Application Defaults
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
J
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Join to Tree Selector
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
L
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Use Literal Values
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
S
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Sub-SELECT Tree Selector
#666666 .5pt; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
TREE_ACC_SELECTOR
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
D
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Dynamic Selectors
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
S
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Static Selectors
#666666 .5pt; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
TREE_ACC_SEL_OPT
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
B
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Range of Values (BETWEEN)
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
R
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Range of Values (>=…<=)
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
S
#666666 .5pt; mso-border-bottom-themecolor: text1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #666666 .5pt; mso-border-left-themecolor: text1; mso-border-left-themetint: 153; mso-border-right-themecolor: text1; mso-border-right-themetint: 153; mso-border-themecolor: text1; mso-border-themetint: 153; mso-border-top-alt: solid #666666 .5pt; mso-border-top-themecolor: text1; mso-border-top-themetint: 153; padding: 0cm 5.4pt;" valign="top">
Single Values

Performance options can also be specified in individual layouts, and the specific definition in the layout overrides the generic definition in the tree.
Add-Ins- />nVision->Layout Options
You can also see the trees defined in a layout file in the Name Manager in Excel.  The performance options are converted into a string of 5 characters.  Below you can see a tree called OPERATING_UNIT. The easiest way to determine whether performance options are set in a layout is to look in the Name Manager, and the only reliable way to delete a performance option from a layout is to delete them from the Name Manager.

Formulas - /> Name Manager

The string of flags in the NvsTree formula can be decoded with this table.

X
Value
Position
Y
N
S
1
Dynamic Selector
Static Selector
2
Join to tree selector
Supress join; use literal values
Sub-SELECT tree selector
3
Range of values (BETWEEN)
Range of values (>= … <=)
4
Single Value
(overrides position 3)
5
Non-specific node criteria (above 2 billion)

Recommendations

It is rare to need to set different performance options for the same tree in different layouts. Therefore, it is easiest to set performance options at tree level and not at all in layouts unless absolutely necessary.
Tree performance options can even be updated in bulk by SQL script, though it is essential to maintain the versions numbers correctly.  Care should be taken when migrating trees from other environments to ensure that the performance options are still correctly set, as the performance options are part of the tree being migrated
It is now possible to give clear advice to developers and especially business users who create nVision reports:

  • Don't use them.
  • If you have specified them in layouts in the past, then remove them unless you really do need to use a different option in a particular report.

Some customers have written Visual Basic macros to work through all their layouts and remove all tree performance options settings.

In the next post I will start to look at how the performance options control how the SQL generated by nVision.

Linux for the SQL Server DBA- Part I

For the Oracle DBA, Linux is life.  When I was at Oracle, Linux projects were the easy part of my job, unlike the ones on Windows, AIX, HP-UX and at times, even Solaris.  You knew the Linux ones received the most love from development, had the most time towards patching and received attention if there was a bug.

History

Linux was introduced in 1991, thanks to Linus Tovalds and has been in active development, with introduction of different “flavors” of the OS over the years.  As many folks know, Linux was carved out of the GNU open license project, which its original OS, Hurd, just hadn’t become successful over the years since it’s development since 1983 and was a main reason that Linus ended up developing Linux.

Linux development is done on the GNU or Intel C Compiler.  The original goal of the OS was to limit any commercial activity, which seems a bit foreign today as its become so standard for most IT shops- so much so, that Microsoft now is inspired to take it on with SQL Server 2016/2017.  Where Linux has become very commercial in recent years, it’s sibling is OS popularity on the GNU project, Debian, has stayed less in the limelight and is used in many open source projects like one of my favorite, the STEM powerhouse and inexpensive computer, Raspberry Pi, (Raspbian). You’re going to hear other flavors, like SUSE, RedHat, Ubuntu and others, but there are minor differences in each, (even if those religiously loyal to one or an other swears on the details otherwise) at this point, knowing that they exist will help you get past this and move onto the important stuff.

Accessing a Linux System

You use a terminal to log into a Linux system.  Putty is free and a favorite of most DBAs.  If you’re on Mac, then you can just use the Terminal and log straight in.  We’ll get into Graphical UIs later on, but understanding the command line is essential for Linux.

Linux has some basic information that surrounds OS users, logging in and permissions.  You SSH (secure shell) into a Linux host, just as you’ve done from Microsoft Windows.

Also, just as in Windows, you must be able to “reach” the host or ping the host, just as you would with a Windows box:

From putty or your terminal, test this with the DNS for the hostname or directly to the IP Address:

ping 

You log in with the following command:

ssh OSUsername@

If it’s the first time you’ve logged into the host, it will ask you to add the routing address to your list and then you can confirm and you will log in.

The Linux File System

In Linux, everything is configured as a file.  Not just text files, but executables, directories and even device drivers.  Due to this, the way you, as a user view everything is a bit different than the kernel views the system.   Similar to Windows OS, there will be a clear hierarchal layout of all the files that culminate up to root, which is identified by a forward slash, “/”, unlike in Windows, identified by a back slash, (and yes, this will be a common typo you’ll be correcting as you go along… :))

For the kernel, it doesn’t see the hierarchy and in its world, everything is very flat and identified by something called inodes. Each of these inodes have a number of unique identifiers that represent each file, each directory, each permission and so on and so forth.  It allows the kernel to identify, search, locate and perform any process very quickly.

OS Users and Groups

Along with Root, there are users created.  These are the logins and owners of files on the OS and each of these users are assigned to one or more groups that allow them a set of permissions to different file structures.  For the Oracle DBA, it is common to login as their username, (example dsmith) and then switch user, (command su) over to Oracle, (database and oracle installation owner) to perform critical tasks.

Belonging to a group allows you to perform the same tasks and have grants to files in directories.  Each user has, (or should have) a home directory that contains their configuration files and aliases for the user.  You may have someone ask you what is in your profile or bash profile.  These files start with a “.” and aren’t available with a simple list command, which we’ll discuss next.

Beginning Commands

We’ll start with a few simple commands on the system now that you’re logged in

ls = list and is like dir, the directory command on Windows.

The list command is something you’ll use often, even more so when you first start.  I don’t know how often I attempt to use “ls” on Windows and curse, then promptly type in “dir”.

ls -a will give you all the files, including hidden files.

ls -ltr will give you the files ordered from most recent and comes in quite handy for large directories.

pwd = current directory location.  You’ll need to know where you are, as you go down the rabbit hole, there’s rarely Alice to show you… </p />
</p></div></div>

    	  	<div class=

Idle banter

When your car gets a flat tyre, it’s always handy to have a spare.  We do the same with the database Smile


SQL> select
  2    x.ksppinm  name
  3  from
  4    sys.x$ksppi  x,
  5    sys.x$ksppcv2  y
  6  where
  7    x.inst_id = userenv('Instance') and
  8    y.inst_id = userenv('Instance') and
  9    x.indx+1 = y.kspftctxpn and
 10    x.ksppinm like '%spare%parameter%';

NAME
--------------------------------------------------------------------------------
_first_spare_parameter
_second_spare_parameter
_third_spare_parameter
_fourth_spare_parameter
_fifth_spare_parameter
_sixth_spare_parameter
_seventh_spare_parameter
_eighth_spare_parameter
_ninth_spare_parameter
_tenth_spare_parameter
_eleventh_spare_parameter
_twelfth_spare_parameter
_thirteenth_spare_parameter
_fourteenth_spare_parameter
_fifteenth_spare_parameter
_sixteenth_spare_parameter
_seventeenth_spare_parameter
_eighteenth_spare_parameter
_nineteenth_spare_parameter
_twentieth_spare_parameter
_twenty-first_spare_parameter
_twenty-second_spare_parameter
_twenty-third_spare_parameter
_twenty-fourth_spare_parameter
_twenty-fifth_spare_parameter
_twenty-sixth_spare_parameter
_twenty-seventh_spare_parameter
_twenty-eighth_spare_parameter
_twenty-ninth_spare_parameter
_thirtieth_spare_parameter
_thirty-first_spare_parameter
_thirty-second_spare_parameter
_thirty-third_spare_parameter
_thirty-fourth_spare_parameter
_thirty-fifth_spare_parameter
_thirty-sixth_spare_parameter
_thirty-seventh_spare_parameter
_thirty-eighth_spare_parameter
_thirty-ninth_spare_parameter
_fortieth_spare_parameter
_forty-first_spare_parameter
_forty-second_spare_parameter
_forty-third_spare_parameter
_forty-fourth_spare_parameter
_forty-fifth_spare_parameter
_forty-sixth_spare_parameter
_forty-seventh_spare_parameter
_forty-eighth_spare_parameter
_forty-ninth_spare_parameter
_fiftieth_spare_parameter
_fifty-first_spare_parameter
_fifty-second_spare_parameter
_fifty-third_spare_parameter
_fifty-fourth_spare_parameter
_fifty-fifth_spare_parameter
_fifty-sixth_spare_parameter
_fifty-seventh_spare_parameter
_fifty-eighth_spare_parameter
_fifty-ninth_spare_parameter
_sixtieth_spare_parameter
_sixty-first_spare_parameter
_sixty-second_spare_parameter
_sixty-third_spare_parameter
_sixty-fourth_spare_parameter
_sixty-fifth_spare_parameter
_sixty-sixth_spare_parameter
_sixty-seventh_spare_parameter
_sixty-eighth_spare_parameter
_sixty-ninth_spare_parameter
_seventieth_spare_parameter
_seventy-first_spare_parameter
_seventy-second_spare_parameter
_seventy-third_spare_parameter
_seventy-fourth_spare_parameter
_seventy-fifth_spare_parameter
_seventy-sixth_spare_parameter
_seventy-seventh_spare_parameter
_seventy-eighth_spare_parameter
_seventy-ninth_spare_parameter
_eightieth_spare_parameter
_eighty-first_spare_parameter
_eighty-second_spare_parameter
_eighty-third_spare_parameter
_eighty-fourth_spare_parameter
_eighty-fifth_spare_parameter
_eighty-sixth_spare_parameter
_eighty-seventh_spare_parameter
_eighty-eighth_spare_parameter
_eighty-ninth_spare_parameter
_ninetieth_spare_parameter
_ninety-first_spare_parameter
_ninety-second_spare_parameter
_ninety-third_spare_parameter
_ninety-fourth_spare_parameter
_ninety-fifth_spare_parameter
_ninety-sixth_spare_parameter
_ninety-seventh_spare_parameter
_ninety-eighth_spare_parameter
_ninety-ninth_spare_parameter
_one-hundredth_spare_parameter
_one-hundred-and-first_spare_parameter
_one-hundred-and-second_spare_parameter
_one-hundred-and-third_spare_parameter
_one-hundred-and-fourth_spare_parameter
_one-hundred-and-fifth_spare_parameter
_one-hundred-and-sixth_spare_parameter
_one-hundred-and-seventh_spare_parameter
_one-hundred-and-eighth_spare_parameter
_one-hundred-and-ninth_spare_parameter
_one-hundred-and-tenth_spare_parameter
_one-hundred-and-eleventh_spare_parameter
_one-hundred-and-twelfth_spare_parameter
_one-hundred-and-thirteenth_spare_parameter
_one-hundred-and-fourteenth_spare_parameter
_one-hundred-and-fifteenth_spare_parameter
_one-hundred-and-sixteenth_spare_parameter
_one-hundred-and-seventeenth_spare_parameter
_one-hundred-and-eighteenth_spare_parameter
_one-hundred-and-nineteenth_spare_parameter
_one-hundred-and-twentieth_spare_parameter
_one-hundred-and-twenty-first_spare_parameter
_one-hundred-and-twenty-second_spare_parameter
_one-hundred-and-twenty-third_spare_parameter
_one-hundred-and-twenty-fourth_spare_parameter
_one-hundred-and-twenty-fifth_spare_parameter
_one-hundred-and-twenty-sixth_spare_parameter
_one-hundred-and-twenty-seventh_spare_parameter
_one-hundred-and-twenty-eighth_spare_parameter
_one-hundred-and-twenty-ninth_spare_parameter
_one-hundred-and-thirtieth_spare_parameter
_one-hundred-and-thirty-first_spare_parameter
_one-hundred-and-thirty-second_spare_parameter
_one-hundred-and-thirty-third_spare_parameter
_one-hundred-and-thirty-fourth_spare_parameter
_one-hundred-and-thirty-fifth_spare_parameter
_one-hundred-and-thirty-sixth_spare_parameter
_one-hundred-and-thirty-seventh_spare_parameter
_one-hundred-and-thirty-eighth_spare_parameter
_one-hundred-and-thirty-ninth_spare_parameter
_one-hundred-and-fortieth_spare_parameter
_one-hundred-and-forty-first_spare_parameter
_one-hundred-and-forty-second_spare_parameter
_one-hundred-and-forty-third_spare_parameter
_one-hundred-and-forty-fourth_spare_parameter
_one-hundred-and-forty-fifth_spare_parameter
_one-hundred-and-forty-sixth_spare_parameter
_one-hundred-and-forty-seventh_spare_parameter
_one-hundred-and-forty-eighth_spare_parameter
_one-hundred-and-forty-ninth_spare_parameter
_one-hundred-and-fiftieth_spare_parameter
_one-hundred-and-fifty-first_spare_parameter
_one-hundred-and-fifty-second_spare_parameter
_one-hundred-and-fifty-third_spare_parameter
_one-hundred-and-fifty-fourth_spare_parameter
_one-hundred-and-fifty-fifth_spare_parameter
_one-hundred-and-fifty-sixth_spare_parameter
_one-hundred-and-fifty-seventh_spare_parameter
_one-hundred-and-fifty-eighth_spare_parameter
_one-hundred-and-fifty-ninth_spare_parameter
_one-hundred-and-sixtieth_spare_parameter
_one-hundred-and-sixty-first_spare_parameter
_one-hundred-and-sixty-second_spare_parameter
_one-hundred-and-sixty-third_spare_parameter
_one-hundred-and-sixty-fourth_spare_parameter
_one-hundred-and-sixty-fifth_spare_parameter
_one-hundred-and-sixty-sixth_spare_parameter
_one-hundred-and-sixty-seventh_spare_parameter
_one-hundred-and-sixty-eighth_spare_parameter
_one-hundred-and-sixty-ninth_spare_parameter
_one-hundred-and-seventieth_spare_parameter
_one-hundred-and-seventy-first_spare_parameter
_one-hundred-and-seventy-second_spare_parameter
_one-hundred-and-seventy-third_spare_parameter
_one-hundred-and-seventy-fourth_spare_parameter
_one-hundred-and-seventy-fifth_spare_parameter
_one-hundred-and-seventy-sixth_spare_parameter
_one-hundred-and-seventy-seventh_spare_parameter
_one-hundred-and-seventy-eighth_spare_parameter
_one-hundred-and-seventy-ninth_spare_parameter
_one-hundred-and-eightieth_spare_parameter
_one-hundred-and-eighty-first_spare_parameter
_one-hundred-and-eighty-second_spare_parameter
_one-hundred-and-eighty-third_spare_parameter
_one-hundred-and-eighty-fourth_spare_parameter
_one-hundred-and-eighty-fifth_spare_parameter
_one-hundred-and-eighty-sixth_spare_parameter
_one-hundred-and-eighty-seventh_spare_parameter
_one-hundred-and-eighty-eighth_spare_parameter
_one-hundred-and-eighty-ninth_spare_parameter
_one-hundred-and-ninetieth_spare_parameter
_one-hundred-and-ninety-first_spare_parameter
_one-hundred-and-ninety-second_spare_parameter
_one-hundred-and-ninety-third_spare_parameter
_one-hundred-and-ninety-fourth_spare_parameter
_one-hundred-and-ninety-fifth_spare_parameter
_one-hundred-and-ninety-sixth_spare_parameter
_one-hundred-and-ninety-seventh_spare_parameter
_one-hundred-and-ninety-eighth_spare_parameter
_one-hundred-and-ninety-ninth_spare_parameter
_two-hundredth_spare_parameter
_two-hundred-and-first_spare_parameter
_two-hundred-and-second_spare_parameter
_two-hundred-and-third_spare_parameter
_two-hundred-and-fourth_spare_parameter
_two-hundred-and-fifth_spare_parameter
_two-hundred-and-sixth_spare_parameter
_two-hundred-and-seventh_spare_parameter
_two-hundred-and-eighth_spare_parameter
_two-hundred-and-ninth_spare_parameter
_two-hundred-and-tenth_spare_parameter
_two-hundred-and-eleventh_spare_parameter
_two-hundred-and-twelfth_spare_parameter
_two-hundred-and-thirteenth_spare_parameter
_two-hundred-and-fourteenth_spare_parameter
_two-hundred-and-fifteenth_spare_parameter
_two-hundred-and-sixteenth_spare_parameter
_two-hundred-and-seventeenth_spare_parameter
_two-hundred-and-eighteenth_spare_parameter
_two-hundred-and-nineteenth_spare_parameter
_two-hundred-and-twentieth_spare_parameter
_spare_test_parameter

Standard common sense stuff: Please don’t mess with these (or any) underscore parameter without consulting Support.

Buzzword Bingo

Looking for that catchy title for your next presentation ?

I took the first word from the title of 1000 Oracle OpenWorld presentations, and looked for patterns Smile.  I omitted some obvious terms that are either products or definite/indefinite articles:

  • Oracle
  • Peoplesoft 
  • The
  • How 
  • OAUG
  • General
  • MySQL

And here is what we end up with:


SQL> select buzz, count(*)
  2  from buzz
  3  group by buzz
  4  order by 2 desc;

BUZZ                                                                                                   COUNT(*)
---------------------------------------------------------------------------------------------------- ----------
Building                                                                                                     18
Customers                                                                                                    14
Best                                                                                                         13
Digital                                                                                                      11
Migrating                                                                                                    10
Getting                                                                                                      10
Accelerate                                                                                                    8
Modernize                                                                                                     8
Implementing                                                                                                  7
Data                                                                                                          7
Transforming                                                                                                  6
Extend                                                                                                        6
Identifying                                                                                                   6
Managing                                                                                                      6
Achieving                                                                                                     6
Leveraging                                                                                                    5
Making                                                                                                        5
Creating                                                                                                      4

So there you go…All the verbs and adjectives you need for a successful presentation Smile

OpenWorld 2017–grab ALL of the content

Some people use the session catalog to grab just the presentations that they either attended, or could not attend.

Other people want a download of every available presentation so they can peruse the entire catalog offline at a later date.

I am one of the latter people. Smile

So using some node, javascript, awk, sed, grep I managed to data-mine the catalog page to come up with a list of uploaded presentations in the form:


wget --no-check-certificate -O "SessionTitle".extension "https://static.rainfocus.com/full_path_to_presentation.extension"

which of course can then be run as a batch file to grab them all. Woo hoo !!!

You can download the batch file which contains the 932 download commands here

I can’t make any guarantees the list is complete, or that the script will work without error, but it should be a good starting point, and it will be a lot faster than clicking on each presentation !

If it does not work for you, my apologies.  If it does work for you and saves you a lot of time, just don’t forget that I like beer or gin&tonic next time you see me. Smile

Enjoy !