Exadata « ORAganism

RAC/Exadata Shell Aliases

Posted in Oracle by Martin Nash on May 27, 2012

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

Tagged with: rac, aliases, Exadata, bash_profile, profile

1 comment

cell smart file creation

Posted in Oracle by Martin Nash on July 29, 2011

One of the offload capabilities you get with Exadata that I’ve been appreciating recently is “cell smart file creation”. In a nutshell, the fact that formatting of blocks is offloaded to the cells rather than being performed by a process on the database server, or “compute node” if you want to use Exadata speak, makes the creation of datafiles faster.

As as quick demonstration look at the elapsed time for the creation of a 100G datafile. First with _cell_fast_file_create at the default value of TRUE and then set to FALSE in order to disable the offloading of the datafile creation:

SQL> create bigfile tablespace martin01 datafile '+DATA01' size 100G;

Tablespace created.

Elapsed: 00:01:40.39
SQL> alter session set "_cell_fast_file_create" = false;

Session altered.

Elapsed: 00:00:00.00
SQL> create bigfile tablespace martin02 datafile '+DATA01' size 100G;

Tablespace created.

Elapsed: 00:04:15.17

101 secs with offload vs. 255 secs without represents a saving of 60%. Examining the wait events associated with the 2 different datafile creations we see “cell smart file creation” for the offloaded file creation and “Data file init write” for the non-offloaded file creation.

Great, creating datafiles is faster on Exadata… But what about redo logs?

OK, I take the point that redo log files aren’t generally created with the same regularity as datafiles, but one of the things I’ve been working on recently has involved a “roll your own” DUPLICATE FROM ACTIVE DATABASE which itself involves creating a reasonable number of redo logs (4G log files, 4 redo groups, 8 threads = waiting). I don’t like waiting, especially when I’ve been asked to make something go as fast as I can, so I thought I’d have a little look and confirm that creation of redo logs is not offloaded…

Tracing the creation of 2 x 4G redo logs revealed the following:

SQL ID: 3r832sm0yj1gv Plan Hash: 0

alter database add logfile thread 1 group 99 ('+DATA01','+RECO') size 4096m
  blocksize 512

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      3.81      37.96          0          0          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.81      37.96          0          0          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 36

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: FS - contention                            2        0.00          0.00
  control file sequential read                   58        0.05          0.13
  KSV master wait                                20        1.71          3.27
  ASM file metadata operation                     6        0.00          0.00
  kfk: async disk IO                              4        0.00          0.00
  Disk file operations I/O                        4        0.00          0.00
  Log file init write                          4381        0.13         31.35
  enq: CF - contention                            1        0.00          0.00
  control file parallel write                    14        0.01          0.03
  log file single write                           2        0.00          0.00
  ges inquiry response                            1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

38 seconds might not be long, but when you consider the number of redo log groups and number of redo threads it soon adds up. And when you have 7 databases to do this for, the opportunity to save 60% of the time in this phase starts to look attractive. Add to this that ALTER DATABASE OPEN RESETLOGS spends the vast majority of its time waiting on “Log file init write” and I can’t help feeling that ”cell smart log creation” would be a nicer event to be waiting on

I realise that it’s probably way down on the list of enhancements, but I for one would appreciate it.

Tagged with: datafile, Exadata, logfile, wait events

Fun with _SERIAL_DIRECT_READ

Posted in Oracle by Martin Nash on June 27, 2011

Serial Direct Reads are a subject that has been getting a reasonable amount of attention since 11g was released. Doug Burns, Dion Cho (and here), Kerry Osborne and Miladin Modrakovic have all written on the subject. Tanel Põder also covered it in his presentation at the UKOUG Exadata Special Event.

Kerry and Tanel both cover the importance of direct reads in the context Smart Scan when running Exadata. I won’t reproduce their material here, you can read it direct from them, but to summarise: seeing “storage” an execution plan simply means that Smart Scan is possible, but does not mean that you’ll get it. You must have direct reads.

There are a number of ways to ensure your statement executes using direct reads and the one I’ll cover here is the (hidden) parameter _serial_direct_read.

In the process of using _serial_direct_read to ensure a serial delete used Smart Scan I ran into something that I find both interesting and slightly concerning. Let me demonstrate.

Create an environment to test in:

SQL> create tablespace sdr_test datafile '+DATA01' size 1G autoextend on maxsize 5G
  2  /

Tablespace created.

Elapsed: 00:00:01.41
SQL> create user sdr_test identified by martin
  2    default tablespace sdr_test
  3    quota unlimited on sdr_test
  4  /

User created.

Elapsed: 00:00:00.05
SQL> grant create session
  2  	 , create table
  3  	 , alter session
  4  	 , alter system
  5  	to sdr_test
  6  /

Grant succeeded.

Elapsed: 00:00:00.01

Connect as the test user and create a test table:

SQL> connect sdr_test/martin
Connected.
SDR_TEST - SESSION 1> create table a as
  2  (
  3  select rownum id
  4  	  , rpad(rownum,200,'X') c1
  5    from dual
  6  connect by rownum <= 10000000
  7  )
  8  /

Table created.

Elapsed: 00:00:25.43

I’m sure it’s obvious, but I’ll state it for clarity: with no index on the table all access will be via full table scan.

Flush the buffer cache, set a trace file identifier, start tracing, run DELETE statement and rollback:

SDR_TEST - SESSION 1> alter system flush buffer_cache
  2  /

System altered.

Elapsed: 00:00:02.65
SDR_TEST - SESSION 1> alter session set tracefile_identifier='sdr_1a'
  2  /

Session altered.

Elapsed: 00:00:00.00
SDR_TEST - SESSION 1> exec dbms_session.session_trace_enable(true,true,'ALL_EXECUTIONS')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SDR_TEST - SESSION 1> var id number
SDR_TEST - SESSION 1> exec :id := 1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SDR_TEST - SESSION 1> delete from a where id = :id
  2  /

1 row deleted.

Elapsed: 00:00:19.93
SDR_TEST - SESSION 1> rollback
  2  /

Rollback complete.

Elapsed: 00:00:00.01