RAC/Exadata Shell Aliases
I’ve been working on Oracle RAC systems for a while now and the subject of how best to setup shell environment variables and aliases seems to be something that there is a fair range of opinion on.
- Do you have entries in oratab for both the database and the instance on that server?
- Do you use oraenv or set the variables required for each environment via another method?
- If you use oraenv, do you customise it?
- How do you switch ORACLE_SID between the db_name and instance_name, if you do?
Please feel free to answer these questions via a comment below, however, the main point of this post is to share a few aliases that I’m finding very useful at the moment, and to encourage you to share aliases you use in your RAC environments. Actually, the non-RAC specific, Oracle related, aliases you create would be interesting to me too, but more on that later…
As I mentioned in a much earlier post I feel that in the world of RAC databases there is scope for an environment variable of ORACLE_DB or ORA_DB_NAME… But, I also feel that there’s a lot to be said for not introducing more variables then is necessary. To this end I have taken to creating the following aliases in RAC environments that I’m working in[1].
alias inst='ORACLE_SID=${ORACLE_SID}$($(grep "^+ASM[1-8]" /etc/oratab | cut -d":" -f2)/bin/olsnodes -l -n | cut -f2)'
alias db='ORACLE_SID=${ORACLE_SID%$($(grep "^+ASM[1-8]" /etc/oratab | cut -d":" -f2)/bin/olsnodes -l -n | cut -f2)}'
These aliases allow me to quickly switch ORACLE_SID from being set to the relevant value for the database to it being set to the relevant value for the instance on the given host. Obviously it doesn’t stop you modifying the value of ORACLE_SID to something that is not valid, but at least it is easy to undo a mistake you might have made.
He’s a quick example:
[ORCL@rac-02 ~]$ inst [ORCL2@rac-02 ~]$ inst [ORCL22@rac-02 ~]$ inst [ORCL222@rac-02 ~]$ inst [ORCL2222@rac-02 ~]$ db [ORCL222@rac-02 ~]$ db [ORCL22@rac-02 ~]$ db [ORCL2@rac-02 ~]$ db [ORCL@rac-02 ~]$
And, for a little more alias sharing… In my time working on Exadata environments I’ve found myself using dcli a lot and created the aliases below to save me typing on the basis that I, almost always, either want to use dcli to communicate with all “compute nodes” or all “storage cells” so why not simplify the process and shorten the commands:
alias ddcli='dcli -g ~/dbs_group -l oracle' alias cdcli='dcli -g ~/cell_group -l cellmonitor'
I’d be keen to get a page going for useful aliases so we can all benefit from the ideas of each other… If you’ve got something to share please comment and if the collection builds up we’ll start a page for alias sharing.
__________
[1] – If I’m working in an environment where a shared “oracle” account is in use then I define my aliases in a separate file and source this when I login so that my preferences are not inflicted on others 
GV$ Function
I really thought I’d blogged about this before, but it appears not!
First of all, this isn’t something I’ve worked out, I’m just recycling something that John Beresniewicz (who seems to be better known as JB) shared during one of his presentations at the UKOUG Conference 2011. This wasn’t a big part of the presentation, but it jumped out at me as a gem of knowledge that I was unlikely to get from anywhere else. In fact, as I recall, John described how he only became aware of the functionality as a result of discussing what he was working on, and the problem he had, to a member of the database server development team at Oracle.
That nicely brings me to the usual, and very important, point about Oracle functionality that isn’t documented: It is not supported (unless someone from Oracle tells you otherwise).
So, did you know that as well as the GV$ views in an Oracle database there is a GV$ function?
Keeping it really simple to start with…
Selecting INSTANCE_NAME for all instances via GV$INSTANCE
SQL> select instance_name from gv$instance; INSTANCE_NAME ---------------- ORCL1 ORCL2 SQL>
Selecting INSTANCE_NAME for all instances via V$INSTANCE with the GV$ Function
SQL> select instance_name from TABLE(GV$(CURSOR(select instance_name from v$instance))); INSTANCE_NAME ---------------- ORCL1 ORCL2 SQL>
So, apart from being cool because it’s undocumented, what good is the GV$ function? Well, in the above example it is no use at all apart from allowing you to type more characters and confuse anyone that is looking over your shoulder!
However, what it is doing is executing the statement in CURSOR( ) on each instance in the cluster database and returning the results to the Query Coordinator (QC) session.
I don’t remember the specifics of what John was working on, but as I recall the issue was that, on the basis of performance, he didn’t want to retrieve all the relevant rows from two GV$ views in order to join them locally. I’ll try to demonstrate the point below using a join between [G]V$SQL and [G]V$SESSION
Preparation
The first step was to execute the same statement a number of times in each of my two instances.
Having obtained the SQL_ID for the statement the next step was to query the GV$ views in order to get some output – Note this is an artificially simple statement compared with where you would get a major benefit from this approach.
Querying GV$ Views
I’ve used autotrace as it’s a nice way to get the statement, results and execution plan without having to type/run too many commands…
SQL> select se.inst_id
2 , se.sql_id
3 , sq.executions
4 from gv$sql sq
5 , gv$session se
6 where se.sql_id = sq.sql_id
7 and se.inst_id = sq.inst_id
8 and se.sql_id = 'cw1knhbvzvdbf'
9 /
INST_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 cw1knhbvzvdbf 15
2 cw1knhbvzvdbf 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3376983457
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 1 (100)| 00:00:01 | | | |
|* 1 | HASH JOIN | | 1 | 55 | 1 (100)| 00:00:01 | | | |
| 2 | PX COORDINATOR | | 1 | 34 | 0 (0)| 00:00:01 | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 4 | VIEW | GV$SQL | | | | | Q1,00 | PCWP | |
|* 5 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | PX COORDINATOR | | 1 | 21 | 0 (0)| 00:00:01 | | | |
| 7 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 99 | 0 (0)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
|* 8 | VIEW | GV$SESSION | | | | | Q2,00 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 99 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
| 10 | NESTED LOOPS | | 1 | 86 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 11 | FIXED TABLE FULL | X$KSUSE | 1 | 60 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 12 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 13 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SQL_ID"="SQL_ID" AND "INST_ID"="INST_ID")
4 - filter("SQL_ID"='cw1knhbvzvdbf')
5 - filter("KGLOBT03"='cw1knhbvzvdbf')
8 - filter("SQL_ID"='cw1knhbvzvdbf')
11 - filter("S"."KSUSESQI"='cw1knhbvzvdbf' AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
12 - filter("S"."INDX"="W"."KSLWTSID")
13 - filter("W"."KSLWTEVT"="E"."INDX")
Note
-----
- statement not queuable: gv$ statement
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
The next part was to use the GV$ funtion.
Querying V$ Views with GV$ Function
Running effectively the same statement with the GV$ function.
SQL> select * from TABLE(GV$(CURSOR(select i.instance_number inst_id
2 , se.sql_id
3 , sq.executions
4 from v$sql sq
5 , v$session se
6 , v$instance i
7 where se.sql_id = sq.sql_id
8 and se.sql_id = 'cw1knhbvzvdbf')))
9 /
INST_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 cw1knhbvzvdbf 15
2 cw1knhbvzvdbf 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1284594253
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3400 | 1 (100)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | 100 | 3400 | 1 (100)| 00:00:01 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 4200 | 1 (100)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | VIEW | | | | | | Q1,00 | PCWP | |
| 4 | MERGE JOIN CARTESIAN | | 100 | 4200 | 1 (100)| 00:00:01 | Q1,00 | PCWP | |
|* 5 | HASH JOIN | | 1 | 29 | 1 (100)| 00:00:01 | Q1,00 | PCWP | |
| 6 | VIEW | V_$SQL | 1 | 21 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 7 | VIEW | V$SQL | 1 | 21 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | VIEW | GV$SQL | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 9 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | VIEW | V_$SESSION | 1 | 8 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 11 | VIEW | V$SESSION | 1 | 8 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 12 | VIEW | GV$SESSION | 1 | 21 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | 1 | 99 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 86 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 15 | FIXED TABLE FULL | X$KSUSE | 1 | 60 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 16 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 17 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 18 | BUFFER SORT | | 100 | 1300 | 1 (100)| 00:00:01 | Q1,00 | PCWP | |
| 19 | VIEW | V_$INSTANCE | 100 | 1300 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 20 | VIEW | V$INSTANCE | 100 | 1300 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 21 | VIEW | GV$INSTANCE | 100 | 2600 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 22 | MERGE JOIN CARTESIAN | | 100 | 6000 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 23 | MERGE JOIN CARTESIAN | | 1 | 60 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 24 | FIXED TABLE FULL | X$KSUXSINST | 1 | 26 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 25 | BUFFER SORT | | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 26 | FIXED TABLE FULL | X$KVIT | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 27 | BUFFER SORT | | 100 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 28 | FIXED TABLE FULL | X$QUIESCE | 100 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SE"."SQL_ID"="SQ"."SQL_ID")
9 - filter("KGLOBT03"='cw1knhbvzvdbf' AND "INST_ID"=USERENV('INSTANCE'))
15 - filter("S"."KSUSESQI"='cw1knhbvzvdbf' AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
16 - filter("S"."INDX"="W"."KSLWTSID")
17 - filter("W"."KSLWTEVT"="E"."INDX")
24 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
26 - filter("KVITTAG"='kcbwst')
Note
-----
- statement not queuable: gv$ statement
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
As you can see from the executions plans above:
- The GV$ view statement involves the rows from both GV$SQL and GV$SESSION being sent to the QC (line IDs 3 and 7) with the join between the two views being performed by the QC (line ID 1).
- The GV$ function statement involves all joins happening before any rows are sent to the QC (line ID 2).
I, for one, think that’s pretty cool
Note – In both cases the statements had previously been executed so the execution statistics don’t include the initial parse recursive calls… Just in case you were wondering.
Configuring Oracle GoldenGate Monitor for HTTPS
There is no shortage of information regarding how to install Oracle GoldenGate Monitor both in the Administrator’s Guide and on various websites. If you want a see screenshots of the GUI installation then Michael Verzijl has a step-by-step on his blog. However, when I came to perform my first installation I didn’t find complete instructions on how to set up HTTPS. If you’re a Java person then I imagine that the information provided in the instructions is sufficient, but if you’re not and have found yourself responsible for setting up Oracle GoldenGate Monitor because: 1) It’s called “Oracle…” and you know Oracle; and 2) Other people aren’t interested it doing it, then I hope you’ll find the information below useful.
You are going to need to use keytool and the steps below cover creating a “self-signed” certificate because in my case I simply want SSL/HTTPS rather than any guarantee that the site is what it claims to be.
Creating a Self-Signed Certificate
The suggested command is:
$ keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass <password> -keysize 2048
Sample output:
$ keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass easy_password -keysize 2048
What is your first and last name?
[Unknown]: GoldenGate Monitor
What is the name of your organizational unit?
[Unknown]: GoldenGate Support
What is the name of your organization?
[Unknown]: ORAsavon Limited
What is the name of your City or Locality?
[Unknown]: Leeds
What is the name of your State or Province?
[Unknown]: West Yorkshire
What is the two-letter country code for this unit?
[Unknown]: GB
Is CN=GoldenGate Monitor, OU=GoldenGate Support, O=ORAsavon Limited, L=Leeds, ST=West Yorkshire, C=GB correct?
[no]: yes
Enter key password for <selfsigned>
(RETURN if same as keystore password):
$
At this point you will have a file name keystore.jks in your current directory and you are ready to install Oracle GoldenGate Monitor and configure it to only use SSL/HTTPS.
HTTPS Option During Installation
During the GUI installation you will get to a screen were you can select HTTP and/or HTTPS as well as the corresponding port number (see this image from Michael Verzijl’s step-by-step)
If you’re using the command line install (via -c option) then you’ll see the output below:
Enter ports for use by Oracle GoldenGate Monitor
Select HTTP or HTTPS or both, and enter the ports that Oracle GoldenGate
Monitor will listen on
Configure Monitor HTTP server?
Yes [y, Enter], No [n]
n
Configure Monitor HTTPS(secured) server?
Yes [y], No [n, Enter]
y
HTTPS port:
[5505]
Shutdown port:
[5501]
Please select the valid keystore you want use for Monitor server
To enable SSL, please use the java keystore utility to create a keystore, and then import the SSL certificate to the keystore. The installer copies the keystore to the Tomcat conf directory. Tomcat uses it for SSL authentication.
Keystore file:
[/<path to where you unpacked the installer>/Oracle_GoldenGate_Monitor_solaris_sparc_11_1_1_1_0.sh.29202.dir]
/<path to where you created the keystore>/keystore.jks
After this carry on with the installation, but at the end deselect “Start Oracle GoldenGate Monitor”.
If you’re using the GUI installer then it’s on this screen at this point in Michael Verzijl’s step-by-step.
If you’re using the command line install then the output below applies:
To start Oracle GoldenGate Monitor manually, deselect Start Oracle
GoldenGate Monitor, or accept the default to allow automatic startup.
(Optional)
Start Oracle GoldenGate Monitor?
Yes [y, Enter], No [n]
n
Launch Oracle GoldenGate Monitor Web?
Yes [y], No [n, Enter]
n
View Readme?
Yes [y], No [n, Enter]
n
Finishing installation...
Setting Keystore Password in Tomcat Configuration
Once the installation has completed you need to modify server.xml (/tomcat/conf/server.xml) to add “keystorePass” to the following line:
<Connector SSLEnabled="true" clientAuth="false" keystoreFile="${catalina.base}/conf/monitor.jks" maxThreads="150" port="5505" protocol="HTTP/1.1" scheme="https" secure="true" sslProtocol="TLS"/>
Becomes:
<Connector SSLEnabled="true" clientAuth="false" keystoreFile="${catalina.base}/conf/monitor.jks" keystorePass="<java keystore password>" maxThreads="150" port="5505" protocol="HTTP/1.1" scheme="https" secure="true" sslProtocol="TLS"/>
After this you will/should be able to start Oracle GoldenGate Monitor with it only listening on HTTPS using:
$ <installation directory>/bin/monitor.sh start
RAC Attack
It’s probably a little late in the day to do a post for an event that starts on Wednesday this coming week, but just in case…
Martin Bach and I will be doing a RAC Attack session at the Oracle User Group Norway’s Spring Seminar.
If you’re there and want some assistance setting up a RAC database on your laptop then we’ll be more than happy to help.
LFPW Parameters
During some recent investigations into “log file parallel write” (LFPW) performance I found myself looking at the Oracle 11.2 Database Reference and wondering about P2 of this wait event. In case you’re not familiar with the parameters they are:
Parameter Description ------------ ------------------------------ files Number of files to be written blocks Number of blocks to be written requests Number of I/O requests ------------ ------------------------------
File is obvious, the number of log file members in the current redo log group, but for the other two parameters I wasn’t sure if it is the number of blocks/request per file or in total. I assumed total, but wanted to be sure so did a quick bit of searching. This led me to OraDBPedia where I read,
P2=The number of redo blocks to be written to each log member.
and
P3=Total number of I/O requests needed to satisfy the write load (i.e. P2).
For whatever reason, I didn’t feel 100% happy to accept this so did a quick test in 11.2.0.2 XE as shown below.
Test Details:
- Enable tracing of LGWR
- Run “alter system switch logfile” to cause LGWR to flush the redo buffer (not shown in output below)
- Run small insert statement
- Commit
- Examine trace file
Starting off with the default in XE of 1 member per group:
14:01:19 SQL> insert into t (c1, c2, c3) values (1,'DELETE', sysdate); 1 row created. 14:01:23 SQL> commit; Commit complete. 14:01:27 SQL>
Looking at the 10046 trace reveals:
*** 2011-09-29 14:01:27.315 WAIT #0: nam='log file parallel write' ela= 374 files=1 blocks=2 requests=1 obj#=-1 tim=1481335308118
Adding another member to each of the 2 groups giving 2 members per group:
14:01:27 SQL> insert into t (c1, c2, c3) values (1,'DELETE', sysdate); 1 row created. 14:04:48 SQL> commit; Commit complete. 14:04:53 SQL>
Looking at the 10046 trace reveals:
*** 2011-09-29 14:04:52.477 WAIT #0: nam='rdbms ipc message' ela= 2969787 timeout=300 p2=0 p3=0 obj#=-1 tim=1481538543689 WAIT #0: nam='rdbms ipc message' ela= 30043 timeout=3 p2=0 p3=0 obj#=-1 tim=1481538574024 WAIT #0: nam='rdbms ipc message' ela= 567670 timeout=300 p2=0 p3=0 obj#=-1 tim=1481539141991 WAIT #0: nam='log file parallel write' ela= 550 files=2 blocks=4 requests=2 obj#=-1 tim=1481539142779
Adding another member to each of the 2 groups giving 3 members per group:
14:04:53 SQL> insert into t (c1, c2, c3) values (1,'DELETE', sysdate); 1 row created. 14:07:51 SQL> commit; Commit complete. 14:07:53 SQL>
Looking at the 10046 trace reveals:
*** 2011-09-29 14:07:53.980 WAIT #0: nam='rdbms ipc message' ela= 2560082 timeout=300 p2=0 p3=0 obj#=-1 tim=1481718344261 WAIT #0: nam='log file parallel write' ela= 1658 files=3 blocks=6 requests=3 obj#=-1 tim=1481718346216 WAIT #0: nam='rdbms ipc message' ela= 438069 timeout=44 p2=0 p3=0 obj#=-1 tim=1481718784548
Adding another member to each of the 2 groups giving 4 members per group:
14:07:53 SQL> insert into t (c1, c2, c3) values (1,'DELETE', sysdate); 1 row created. 14:09:48 SQL> commit; Commit complete. 14:09:51 SQL>
Looking at the 10046 trace reveals:
*** 2011-09-29 14:09:51.687 WAIT #0: nam='rdbms ipc message' ela= 1445500 timeout=300 p2=0 p3=0 obj#=-1 tim=1481834980859 WAIT #0: nam='log file parallel write' ela= 1910 files=4 blocks=8 requests=4 obj#=-1 tim=1481834983045
I see the above a conclusive evidence that both blocks (p2) and requests (p3) are the total rather than “per file”.
Maybe things have changed since the OraDBPedia articles was originally written, but I’ve contacted the guys behind the site and expect the page to be updated shortly.
OOW11: The Come Down
I’m on the way back to London. Having had a great time at OpenWorld and fantastic weather the last two days (plus the use of the Pythian Moon Dog) I feel a bit down to be leaving San Francisco…
A few comments about my last post before I get into this one:
- DTrace support in Linux was deliberately missed off the list as it was conveyed in the Oracle Linux 6 session I attended that it was not production ready.
- I’m an Oracle database geek. The other sessions I attended were not bad, but as someone that spends a lot of time trying to learn as much as possible about the Oracle database server, when I attend a presentation on a subject I’m much less familiar, but also very keen on, I tend to get a lot out of it – The point is, don’t get the impression that the other sessions were not good.
This was my 3rd OpenWorld and the best experience for me by far. There are a number of factors contributing to that including an apparent increase in the number of technical sessions (necessary as Unconference was not run this year). I highly recommend attending OpenWorld. It is a different experience from the only other Oracle focused conference I’ve attended (UKOUG Conference), but they are both great from my point of view, which is why I plan to continue attending both… And hopefully getting to some more
Reasons to consider attending OpenWorld:
- The speakers are absolutely top notch – There are too many names to “drop” here, but you really do have the best independent and Oracle employed experts
- The evening events provide an opportunity to meet like-minded people, and “talk tech”
- You are there to hear the latest hardware and software releases as they happen
- It’s in San Francisco!
Anyone that follows me on Twitter may have noticed that I was very impressed by what I saw of Oracle Enterprise Manager 12c “Cloud Control”. While watching the presentation I planned to get it installed on a VM as soon as I got back to the UK. Martin Bach was clearly as keen as I am and he’s posted details of his install here. Boy, that man’s fast. 11.2.0.3 installed about as fast as humanly possible after the release and pretty much the same with this – Nice work Martin
Boarding call!
OOW11: Best Session So Far
I’ve been a little slack getting my words down with respect to OpenWorld 2011 so far. There are many others faster at getting the news out as it breaks, so I won’t even try to cover the big announcements. You can find that elsewhere.
Without a doubt the session that got me most excited so far was “Overview: New Features in Oracle Linux 6″. I’m a DBA, but take a very keen interest in Linux. I’ve been choosing Oracle Enterprise Linux (note that it has actually been renamed as Oracle Linux now) as my OS of choice for pretty much everything I do in my personal lab environment, not just for database servers. This is something that a couple of my Linux geek friends have questioned. They have extolled the joys of Debian and criticised Oracle for being lazy with Linux – “Just taking Red Hat and re-branding it.” I really wished they’d been in this session. I came away with a very strong impression that Oracle really do care about Linux, they do invest in Linux and they have some very smart guys onboard who really know what they are doing. It’s hard for me to cover everything that was discussed during the session here, but here’s an attempt to summarise:
- Oracle Linux is not a desktop distribution – Obvious, but worth stating
- Focus is on the kernel – Hence Unbreakable Enterprise Kernel
- Aim to stay close to the mainline as possible – Aim to release around once a year
- 9 month grace before expecting the new kernel to be used
- Network optimisation such as receive packet steering (RPS) and transmit packet steering (XPS) sound very significant
- Oracle Linux 6.2 is beta now or very very soon – This is wrong (see comment from Lenz below)
- Transcendent Memory
- Btrfs – “Build around snapshots” (Chris Mason)
- Task Control Groups (Cgroups) – Offer fine grained control of resources and particularly useful for NUMA systems
- Linux Containers – Yes, that’s right
- Ksplice – Patch your kernel with zero downtime… Oh, and you can rollback with no downtime too
If you do nothing else after reading this post then checkout Ksplice! Read the official site and this on wikipedia
I should stress that there was the usual disclaimer about the information provided and there is always the possibility that I’ve misrepresented what was said, so do additional research before making big decisions based on the above.
Also mentioned was Open vSwitch which sounds like something I should look into.
While writing this I have been prompted to think about what matter to me with respect to Linux and I’ve concluded that exactly how much money (by paying kernel developers) Oracle have put into the Linux kernel is something that I don’t have the time or inclination to work out. What matters to me is that Linux is developing, Oracle are contributing and Oracle provide me with a completely free version that I can use in my lab for database servers and anything else I want to do.
Setting SDU Size (mainly in 11.2)
I’ve recently had some problems getting a change in SDU size to be picked up and I’m writing this post in the hope of saving others some time.
Changing the SDU size is something to consider if you want to get the most out of your network. The situations where it will offer a notable benefit are covered in MOS ID 99715.1[1].
The 11.2 Net Services Administrator’s Guide and MOS ID 67983.1 (Oracle Net Performance Tuning, which applies to “Oracle Net Services – Version: 8.1.7.4.0 to 11.2.0.1 – Release: 8.1.7 to 11.2″), both list two ways of setting SDU size. One method involves setting SDU in listener.ora (for a statically registered listeners) and the other setting it in sqlnet.ora.
The documentation also states:
If you have configured the listener with a list of targets in the listener.ora file, then the value for SDU in the SID_LIST element overrides the current setting in the sqlnet.ora file when using dedicated server processes.
There is a caveat in the “Purpose” section of MOS ID 67983.1 which seems worth drawing attention to:
Note: This document contains references to obsolete parameters.
If this caveat is intended to alert the reader to what I cover below then I think it would be reasonable to be more explicit. MOS ID 44694.1 also provides examples on setting SDU, but applies to “Oracle Net Services – Version: 8.0.5.0.0 to 11.1.0.6.0″.
It was the subtlties of the applicable versions in the MOS notes listed above and spotting something different in MOS ID 1292915.1 (Scan Listener, Queuesize, SDU, Ports) that led me to get a change in SDU to be effective.
When it comes to establishing the negotiated SDU I have relied on MOS ID 304235.1 and this page from Jonathan Lewis. The latter relates specifically to Oracle 7.3.4.1, but appears to still be relevant.
The system I was originally attempting to enable an increased SDU size on is an 11.2.0.2 Exadata environment, but when things didn’t go as planned I moved to XE 11.2.0.2 in order to experiment. The 11.2.0.2 testing below is from XE and the same approach has been confirmed as effective in the 11.2.0.2 Exadata environment (Enterprise Edition).
I have also carried out tests on 11.2.0.1, 11.1.0.6 and 10.2.0.5 (all Enterprise Edition) and reference to these versions is made where I have observed differences in behaviour.
Testing
1. Validate SDU size used by default (“out of the box”)
1.1. Set “TRACE_LEVEL_LISTENER = ADMIN” in listener.ora (only change to standard configuration)
1.2. Connect via listener[2]
1.3. Searching for “sdu=” in the trace file reveals the following
2011-09-21 09:11:05.759068 : nsconneg:vsn=314, lov=300, opt=0x41, sdu=8192, tdu=65535, ntc=0xc60e 2011-09-21 09:11:05.759125 : nsconneg:vsn=314, gbl=0x1, sdu=8192, tdu=65535
Which, based the methods referenced above, shows that an SDU of 8192 bytes is used.
2. Setting SDU size in sqlnet.ora
2.1. Setting “DEFAULT_SDU_SIZE = 16384″ in sqlnet.ora (both client and server)
2.2. Connect via listener[2]
2.3. Searching for “sdu=” in the trace file reveals the following
2011-09-21 09:22:41.897852 : nsconneg:vsn=314, lov=300, opt=0x41, sdu=8192, tdu=65535, ntc=0xc60e 2011-09-21 09:22:41.897910 : nsconneg:vsn=314, gbl=0x1, sdu=8192, tdu=65535
… Showing that an SDU of 8192 bytes is used.
2.4. “DEFAULT_SDU_SIZE = 16384″ removed from sqlnet.ora
Note: One observation during this test on 11.2.0.1, and earlier, is that when sqlnet.ora is has DEFAULT_SDU_SIZE set the client will attempt to negotiate this SDU size.
3. Setting SDU size in tnsnames.ora
3.1. tnsnames.ora updated to (DESCRIPTION = (SDU = 16384) <–snip–>)
3.2. Connect via listener[2]
3.3. Searching for “sdu=” in the trace file reveals the following
2011-09-21 09:30:21.498865 : nsconneg:vsn=314, lov=300, opt=0x41, sdu=16384, tdu=65535, ntc=0xc60e 2011-09-21 09:30:21.498925 : nsconneg:vsn=314, gbl=0x1, sdu=8192, tdu=65535
… Showing that SDU of 16384 was requested by the client, but the server would only go to 8192.
4. Setting SDU size in listener.ora (static listener entry)
4.1. listener.ora updated to include (SID_DESC = (SDU = 16384) <–snip–>)
4.2. Connect via listener[2]
4.3. Searching for “sdu=” in the trace file reveals the following
2011-09-21 09:54:28.324832 : nsconneg:vsn=314, lov=300, opt=0x41, sdu=16384, tdu=65535, ntc=0xc60e 2011-09-21 09:54:28.324890 : nsconneg:vsn=314, gbl=0x1, sdu=8192, tdu=65535
… Showing that SDU of 16384 was requested by the client, but the server would only go to 8192.
4.4. Searching for SDU= in the trace file reveals a number of lines, including the line below, showing the SDU parameter
2011-09-21 09:54:28.339728 : snlpcss:Spawn Oracle completed oracle (DESCRIPTION=(LOCAL=NO)(SDU=16384)) XE.
I’m taking this as showing that the syntax is not flagged as invalid, but as shown by the previous output, it is not being honored.
4.5. SDU parameter removed from this position in listener.ora
5. Setting SDU size in listener.ora (Listener DESCRIPTION)
5.1. listener.ora updated to include (DESCRIPTION_LIST = (DESCRIPTION = (SDU = 16384) <–snip–>))
5.2. Connect via listener[2]
5.3. Searching for “sdu=” in the trace file reveals the following
2011-09-21 10:07:08.008144 : nsconneg:vsn=314, lov=300, opt=0x41, sdu=16384, tdu=65535, ntc=0xc60e 2011-09-21 10:07:08.008203 : nsconneg:vsn=314, gbl=0x1, sdu=16384, tdu=65535
… Showing that SDU of 16384 was requested by the client, and the server could match it.
5.4. Searching for SDU= reveals a number of lines, including the line below, showing the SDU parameter
2011-09-21 10:06:39.910083 : nsgllsn:Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))(SDU=16384))
I went on to experiment with combining the placement of SDU within listener.ora, but could not find a situation where placement within SID_DESC was honored.
After working through testing above, and finding my experiences did not match the documentation, I ran further tests on 10.2.0.5, which threw up some more interesting discoveries.
Simply enabling tracing and making a connection gave:
nsconneg: vsn=313, lov=300, opt=0x81, sdu=2048, tdu=32767, ntc=0x7308 nsconneg: vsn=313, gbl=0x81, sdu=2048, tdu=32767
This is expected as the default SDU in 10.2 is 2048 bytes.
Setting DEFAULT_SDU_SIZE = 8192 in sqlnet.ora gave:
nsconneg: vsn=313, lov=300, opt=0xc01, sdu=8192, tdu=32767, ntc=0x7f08 nsconneg: vsn=313, gbl=0xc01, sdu=8192, tdu=32767
OK! So now it’s working as documented and I didn’t even need to restart the listener
Setting DEFAULT_SDU_SIZE = 16384 in sqlnet.ora gave:
nsconneg: vsn=313, lov=300, opt=0xc01, sdu=16384, tdu=32767, ntc=0x7f08 nsconneg: vsn=313, gbl=0xc01, sdu=8192, tdu=32767
… and just for good measure setting DEFAULT_SDU_SIZE = 8193 in sqlnet.ora gave:
nsconneg: vsn=313, lov=300, opt=0xc01, sdu=8193, tdu=32767, ntc=0x7f08 nsconneg: vsn=313, gbl=0xc01, sdu=8192, tdu=32767
From this I’m concluding that it is not possible to increase the SDU size above 8K via the sqlnet.ora method alone. Values greater than 8192 are picked up the client, but not by the listener (at least until 11.2.0.2, where they are no longer picked up by the client either).
The next thing I wanted to test was setting the SDU size in listener.ora at 10.2.0.5. Try as I might, I could not get adding SDU to listener.ora using the syntax given in the 10.2 documentation to work, i.e.:
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(SDU=16384)
(SID_NAME=sales)))
However, if used the approach I’d established on 11.2, as below, the SDU size change was picked up by the listener.
LISTENER_listener_name =
(DESCRIPTION_LIST =
(DESCRIPTION =
(SDU = 16384)
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
)
Key Points (it appears that):
- SDU size can’t be increased above 8192 in sqlnet.ora and be honored by the listener
- From 11.2.0.2 changing DEFAULT_SDU_SIZE in sqlnet.ora does not affect the SDU size used by the client or the listener
- The documented location of the SDU parameter within the listener.ora file in all locations apart from MOS ID 1292915.1 (Scan Listener, Queuesize, SDU, Ports) is not effective
I am not ruling out that I’ve missed something in my testing, so if you read this blog post and have different experiences, particularly with respect to the location of SDU within listener.ora, then I would be very happy to hear from you.
________________
- The activity I was attempting to speed up by increasing the SDU did not show an improvement even when the increased SDU was successfully implemented (assuming the method used for establishing the SDU, detail above, is appropriate in 11.2.0.2).
- The connection testing to produce trace output for analysis was done using a SYSDBA connection to a database that was shutdown in order to guarantee that my connection was going via the static listener registration.
Explain Plan and PLAN_TABLE$
Someone told me something a few weeks ago that didn’t make sense to me, and it still doesn’t.
Maybe I’m not seeing something obvious or maybe this is a legacy left over from the days before PLAN_TABLE became a synonym for SYS.PLAN_TABLE$ and SYS.PLAN_TABLE$ existed as a global temporary table? As ever, your comments on what follows are most welcome.
The discussion I was involved in regarded GoldenGate and the impact long running transactions were having. The comment that jumped out at me was that running EXPLAIN PLAN left a transaction open. “We’ll that implies that PLAN_TABLE$ is a global temporary table with on commit delete rows.” was my response. This doesn’t necessarily follow, but it was the first reason I could think of why Oracle would not code EXPLAIN PLAN to automatically commit… But, that raised the question (in my head) of, “Why don’t they just define PLAN_TABLE$ with ON COMMIT PRESERVE ROWS and perform a commit as part of EXPLAIN PLAN”. The discussion quickly moved on, but this seemed something worth spending a few minutes confirming…
First things first, I thought I’d better check the “duration” of PLAN_TABLE$
SYSTEM@orcl> select duration from all_tables where owner = 'SYS' and table_name = 'PLAN_TABLE$'; DURATION -------------------- SYS$SESSION SYSTEM@orcl>
Well, that wasn’t what I was expecting. PLAN_TABLE$ records are persistent for the duration of a session, so why doesn’t EXPLAIN PLAN automatically commit? The best answer I can come up with is that it doesn’t because it just doesn’t. Pathetic, I know.
I can’t imagine that someone would start inspecting execution plans, via EXPLAIN PLAN, midway through a transaction, but hey, they might. However, if we have to learn that DDL, gathering statistics, etc perform implicit commits then why not just add EXPLAIN PLAN to that list?
Anyway, here’s a quick demo to stop you feeling you need to verify this for yourself.
Session 1 – Connect to a new session, verify PLAN_TABLE is empty and run EXPLAIN PLAN
20:59:13 MNASH@orcl> conn mnash
Enter password:
Connected.
20:59:29 MNASH@orcl> select count(*) from plan_table;
COUNT(*)
----------
0
20:59:37 MNASH@orcl> explain plan for select * from t;
Explained.
20:59:50 MNASH@orcl>
Session 2 – Query v$session and v$transaction to verify that there is a transaction open
20:58:57 SYSTEM@orcl> l 1 select username, 2 sid, 3 sql_id, 4 event, 5 state, 6 seconds_in_wait 7 from v$session s 8 , v$transaction t 9* where s.taddr = t.addr 20:59:58 SYSTEM@orcl> / USERNAME SID SQL_ID EVENT STATE SECONDS_IN_WAIT ---------- ----- ------------- ----------------------------- --------- --------------- MNASH 17 6j5vng1ckm1p2 SQL*Net message from client WAITING 11 21:00:01 SYSTEM@orcl>
Session 1 – Use DBMS_XPLAN.DISPLAY to view the plan
20:59:50 MNASH@orcl> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1161K| 107M| 4697 (1)| 00:00:57 | | 1 | TABLE ACCESS FULL| T | 1161K| 107M| 4697 (1)| 00:00:57 | -------------------------------------------------------------------------- 8 rows selected. 21:00:16 MNASH@orcl>
Session 2 – Query v$session and v$transaction to verify that there is a transaction open
21:00:01 SYSTEM@orcl> / USERNAME SID SQL_ID EVENT STATE SECONDS_IN_WAIT ---------- ----- ------------- ----------------------------- --------- --------------- MNASH 17 SQL*Net message from client WAITING 6 21:00:22 SYSTEM@orcl>
Session 1 – Commit
21:00:16 MNASH@orcl> commit; Commit complete. 21:00:33 MNASH@orcl>
Session 2 – Query v$session and v$transaction to verify that the transaction is closed
21:00:22 SYSTEM@orcl> / no rows selected 21:00:40 SYSTEM@orcl>
If the above interested you then have a read of the sections containing “commit” in the EXPLAIN PLAN documentation.
Missing file#
Another in my MEEK (maybe everyone else knows) series…
Without going into too much detail I found myself looking at parameters for “buffer busy waits” after getting a report that a single statement had spent over 5 hours waiting on this event!
The output below shows the parameters for the “buffer busy waits” as they occurred:
SQL> select p1, p2, p3, count(*) from gv$session
2 where event = 'buffer busy waits' group by p1, p2, p3;
P1 P2 P3 COUNT(*)
---------- ---------- ---------- ----------
5002 2 13 42
SQL>
Finding the file and block is easy right? For the file you just take P1 (for file#) and query v$datafile on file#, as covered in the documentation here and to find the segment use both P1 (for file#) and P2 (for block#) with the query found here.
All cool. Well, until you get this:
SQL> select name from v$datafile where file# = 5002; no rows selected SQL>
My next thought was, “OK, what about v$tempfile?”
SQL> select name from v$tempfile where file# = 5002; no rows selected SQL>
Working on this was not a priority to the project, but it had me confused so kept rattling about in my head. I asked a few people, but that didn’t turn up any ideas regarding this apparently missing file.
I kept coming back to it when I had a few spare minutes and the person with the original issue would periodically ask me if I’d worked it out yet, just to make sure I had not forgotten him. He was convinced it was temporary tablespace related and I was happy to believe that he was right, but I needed proof. I needed to understand where the wait event parameters were coming from…
One thing that made me question the temporary tablespace theory was that the trace file I’d generated from one of the parallel slaves during a re-run of the statement, to confirm the problem was not intermittent, did not show any “temp” specific wait events. All I had in the trace for file#=5002 were:
- ‘buffer busy waits’
- ‘db file single write’
- ‘gc buffer busy acquire’
- ‘gc buffer busy release’
- ‘gc current multi block request’
- ‘local write wait’
I shouldn’t have let that sway me. It wasn’t a complete trace.
I’d done a fair bit of digging on MOS, but hadn’t been able to find anything applicable so I turned to Google. I guess I hit upon the right search term as I found this thread on Oracle-L which points out that the file# for tempfiles in wait events and trace files is db_files (parameter) + v$tempfile.file#.
This tied up nicely. db_files was set to 5000 and we had a couple of temporary tablespaces…
I passed this information back and said I’d look for official documentation that confirms this. After a bit of searching in MOS and the documentation I gave up, having realised that I could easily verify the “theory” on my local XE installation. After all, empirical evidence is what counts… The information may be there in MOS or the documentation, I just failed to find it!
For anyone interested in the test, this is what I did:
SQL> col name for a60
SQL> select file#, name from v$tempfile
FILE# NAME
---------- ------------------------------------------------------------
1 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF
SQL> show parameter db_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
SQL> create global temporary table gtt (c1 number);
Table created.
SQL> exec dbms_session.session_trace_enable
PL/SQL procedure successfully completed.
SQL> insert into gtt select rownum from dual connect by rownum <= 10000;
10000 rows created.
SQL> exec dbms_session.session_trace_disable
PL/SQL procedure successfully completed.
SQL> conn / as sysdba
Connected.
SQL> alter system set db_files = 100 scope = spfile;
System altered.
SQL> startup force
.
.
.
SQL> conn mnash
Enter password:
Connected.
SQL> exec dbms_session.session_trace_enable
PL/SQL procedure successfully completed.
SQL> insert into gtt select rownum from dual connect by rownum <= 10000;
10000 rows created.
SQL> exec dbms_session.session_trace_disable
PL/SQL procedure successfully completed.
SQL>
As you’d expect writing rows to a global temporary table will write to the temporary tablespace and inspecting the two trace files shows that file# (actually “fileno” on Windows) changes by the change in the db_files parameter.
From trace file 1
WAIT #255303800: nam='Disk file operations I/O' ela= 21086 FileOperation=2 fileno=201 filetype=2 obj#=-1 tim=283987684761
From trace file 2
WAIT #1086755940: nam='Disk file operations I/O' ela= 3527 FileOperation=2 fileno=101 filetype=2 obj#=-1 tim=285287446239
Martin Nash
Neil Johnson
Pawel Krol
Ben Thompson
Eter Pani
1 comment