Frederick Tang Weblog

If you are using Windows and like me, want to use something more exciting than the standard Cygwin xterm or Putty to ssh to a Unix server, then these two tools might fancy you. I have been using these two tools for a while now and both are quite good.

Their features are quite similar and you can read more about them on their websites, but I will list what I found here:

Terminator: http://software.jessies.org/terminator/

• around 20MB to run with two tabs open, feels more lightweight.
• can run a Shell on the local machine.
• Tab interface, feels cleaner
• Have “Find” and excellent scrollback.
• Does not force line break, and uses the scrollbars.

Poderosa: http://en.poderosa.org/

• around 22MB to run with two tabs open.
• Tab interface
• the window space can be split into half easily, so you can have terminals organized side by side to each other at the same time, useful if you are running some commands, and watch some log file scroll output in another window at the same time.
• cannot run a Shell on the local machine. Purely to establish a session on a remote Unix machine using profiles (stores Hostname, IP, Username, Password, Encoding… etc.)

I use Terminator for simple stuff, but uses Poderosa for more complex database work. Both are free… but Poderosa has not been updated since 2006

Ever since becoming an Oracle DBA late last year, it has been a constant quest to understand what truly is a DBA. It is not that I do not know what I am doing or what’s required for my job, but to truly appreciate the many facets might take some time for a newcomer:

• what a DBA does, the skillsets and traits, roles and responsibilities
• where they sit in the organization,
• how to work with different departments
• career path, progression
• the technology itself – 9i, 10g, 11g….
• tools and processes
• many more…

Mike Ault wrote an article on How to be hired as DBA, and briefly, he summarizes DBA knowledge into categories:

• Installation
• Configuration Management
• Security
• Monitoring and Tuning
• Backup and Recovery
• Troubleshooting
• Vendor Interface

He also listed the desired traits of a DBA, which I won’t repeat here. I like his expression of “database baby-sitter” and “full-charged DBA”. The article was written in 1997 (as dated in the Microsoft doc).

Craig Mullins also wrote an article on What is a DBA? He summaries the types of DBAs into:

• System DBA
• Database Architect
• Database Analyst
• Data Modeler
• Application DBA
• Task-oriented DBA
• Performance Analyst
• Data Warehouse Administrator

The article looks to be written in 2002. I believe these types take different specialization into the different database knowledge such as those mentioned by Mike. Craig explains what each types of DBA focuses on in his article nevertheless.

John Bostick has written a recent article titled DBAs fishing for an elevated role. John suggests the many tasks a DBA performs regularly are operational roles. It doesn’t seems as though John draws distinction between the different types of DBA such as those put forward by Craig.

Roles such as Database Architect, Data Modeler or Data Warehouse Administrator are beyond operations. DA and DM are roles that work closely with the development team, offering in-depth database knowledge and best practices.

However, I like what John proposes at the ending paragraphs, to paraphrase -

“… DBAs have a chance to take on a more strategic role within the organization…”, 

“… determine which of this data is most important to the business and how to extract it in a way that is actionable…”

“… turning the data into information that helps move the sales needle…”

“Pooling and parsing the data to show patterns and trends that help the organization make critical decisions…”

Although John did not give a name for this role, it sounds somewhat closely related to what (Craig describes as) a Data Warehouse Administrator (DWA) does. The small difference lies in that a DWA “monitors and supports” the data warehouse environment – database design, ETL, data quality, data formats, interface with BI tools… etc. Whilst John’s idea is for a DBA to turn the data into information to add value the business.

An experienced DBA understands the database technologies available,  understand data models/schemas/metadata, understands and can improve database performance, perform query tuning… etc. It would be a lot more interesting to be a DBA that combine these technical knowledge/skills with soft/business skills, and explore the realm of Business Intelligence, Data Warehouse, Data Mining… etc.

I like where that’s heading and it’s definitely an interesting career path to follow…

[edit] Also found an article written by Doug Burns on “What use is a Development DBA?”. Similar if not the same as the Application DBA Craig wrote about.

For some reason, some people call their computer *rigs*… so I am going to tell you what’s in my new rig.

I put together my new computer a couple of months ago, but I thought I wouldn’t blog about it until I get some benchmarks… turns out I am more lazy than I thought and have delayed until now… so I thought I would write this without benchmarks.

CPU – Intel E6420

I don’t know why spent more money to get the 6420, could have spent less to get a 6320 and get a same deal – overclock to 3GHz. Both are 4MB cache.

Motherboard – Gigabyte GA-965P-DS4

DS4 and DS3P is very similar but DS4 cost more. MSY ran out of stock and I wasn’t willing to wait…. Wasn’t prepared to step up to P35 boards as at the time, they only started to come out, I liked the more matured boards that has bug fixes fixed in revisions.

DS4 only has 1x IDE port, I was trying to reuse my IDE Optical drives and 2x IDE drives from my old computer. A normal IDE cable has 2 sockets for 2 IDE devices, but in reality, the cable wasn’t long enough for me to connect both a IDE HDD and a IDE optical drive.

Buying an extra PCI-IDE card cost money, might as well buy one more SATAII HDD and give the IDE drive to someone else.

Disk Drive – Seagate SATAII 320GB

Not much to say here – Seagate is the brand of choice for me.

Memory – OCZ 2GB-800 DDR2 Kit

Come on… you know you want it! *evil*. I am not running Vista yet.

Graphics Card – Gigabyte 320MB 8800 GTS

This one took me the longest time to decide. This card is the first card that’s coming out that supports DX10, and the price to pay is steep ($400-$500). One model down is the 8600, but if you take a look at various reviews e.g. tomshardware, you will find the 8600 is performing worse than the 7900. So the question comes down to 1). how much can you pay 2). what does DX10 entry level support mean for you. 3). your bragging rights…

After using this new computer for a while, the graphics card is the noisiest component. I have chosen other components based on low noice, except this one I have no control over.

When warranty is over, I might get a Thermalright HR03 Plus… Time will tell how noisy it will eventually get when summer comes. I might also give RivaTuner a go…

Case – Cooler Master Centurion 534 RC-534-KKN2

Good cheap case without power supply. Very good construction, fans at the front and back, plus one on the side panel for the graphics card… but I chose to not use the side fan.

Power Supply -  Seasonic S12-430

This one I am proud of… it was a toss up between Seasonic and Zalman ZM460-APS. Nobody was stocking the Zalman when I was trying to gather the parts, so I went for the Seasonic. The one I got had sleeved cables and it is quiet [silentpcreview]

Case Fan – Noctua NF-S12-1200

I wanted to use my own case fan – quiet, silent and moves good amount of air to keep my case inside cool. So I used silentpcreview.com recommendation. I used the extra adapter to lower the fan speed and used the rubber thumb screws instead of metal screws for noise dampening. Look online to see how it is installed, I found it hard to install the rubber screws, but it is good once it’s in.

Mouse – Razer DeathAdder + eXactMat + Armadillo

I bought a Wireless Logitech MX110 and the charger died after less than 6 months. I didn’t have warranty on it since I bought it from HK. I hate mouse that needs recharging and/or batteries. Period. What’s more annoying than during a game, your mouse is dead!?

Off I go shopping for a wired gaming mouse. Razer came highly recommended by several internet reviews, so I gave it a go… well… at the same time, I also bought the eXactMat and Armadillo.

The mouse itself is nice, but it consumes some CPU when I was testing it on my Centrino laptop (open Task Manager and move your mouse, you will see some CPU spikes). Maybe that’s normal… but the jump is not noticeable with my other M$ Wireless IntelliMouse. The mouse itself is great, read reviews if you want to know how well it performs. I noticed some dust do get inside the mouse after awhile and is hard to clean – the gap between the when you rest your palm and the base of the mouse.

The mat itself is good and wide… but a bit too wide for my desk, so I ended up placing it sideways. I am not a hardcore gamer, so the dual surfaces doesn’t mean much to me, but I got it because I was sick of foam mats.

The armadillo is interesting, it acts as a rest for the mouse cable. The mouse cable is actually quite long, and the weight can sometimes drag and inhibit movements of the mouse. The armadillo is there to stop the dragging. It’s good in general but some times I find the cable were pushing against the armadillo when I move it up and down.

Extra cooling

I might get a Scythe Kama Bay [ocmodshop] at some stage… the basic idea is to keep air intake from the front of the case, and out the back exhaust fan (which I have a Noctua), keeping the air around graphics card and CPU cool. Will see how it goes in summer… I don’t want either the CPU heatsink fan or the graphics card fan to spin up.

I am quite excited today to discover what Oracle10g ADDM can show me. Thanks to various blogs and articles that introduce this feature, I ran some simple tests in a 10.2.0.2 database. The test database was used to support a reporting application, very low volume of transactions. The hardware is SunFire V240 Solaris 10, 2x UltraSPARC iiii CPUS, 2GB memory, all datafiles are on one single disk locally.

I decided to alter the SQL from the article a little bit to include the rationale behind the recommendations. Here’s what some of the results I was able to get.

select a.execution_end, b.type, b.impact, d.rank, d.type,
      ’Message : ‘||b.message MESSAGE,
      ‘Rationale : ‘||r.message RATIONALE,
      ‘Action Message : ‘||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,
    dba_advisor_actions c, dba_advisor_recommendations d,
    dba_advisor_rationale r
where a.owner=b.owner and a.task_id=b.task_id
  and b.task_id=d.task_id and b.finding_id=d.finding_id
  and a.task_id=c.task_id and d.rec_id=c.rec_Id
  and r.task_id(+)=d.task_id and r.rec_id(+)=d.rec_id
  and a.task_name like ‘ADDM%’ and a.status=’COMPLETED’
order by b.impact, d.rank;

Here are some results:

Good contribution towards diagnosing database problems or making informed tuning decisions.

I was *googling* the web for some Oracle DBA blogs, and found this a good DBA blog by Hermant with regards to gaps in sequence numbers. I guess I learn something new everyday – as a Web application developer previously, I always thought sequence numbers cannot be lost unless I do a rollback (which would throw the values away).

One of my Master of Commerce was Information Systems Auditing [wiki], and one of the techniques introduced was missing sequence numbers detection – which would detect gaps in what’s seemingly a set of numbers in sequence, e.g. cheque numbers, invoice numbers, payroll numbers, purchase order numbers… etc. If it is not possible to maintain a set of gap-free sequence numbers, then what does this mean for auditing. I decided to dig a little bit deeper in understanding sequence number generation in Oracle.

Suppose I want to create a simple sequence for testing -

Statement #1 (as normal user) -
SQL> create sequence seq_test start with 1 increment by 1;
Sequence created.

Sequence values are in fact cached in the SGA when nextval is first called. The number of values to cache depends on the sequence creation statement, and is 20 by default.

Statement #2 (as sysdba) -

SQL> select MIN_VALUE, MAX_VALUE, INCREMENT_BY, CACHE_SIZE, LAST_NUMBER
  2  from dba_sequences where sequence_name=’SEQ_TEST’;

 MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20           1

The table behind the dba_sequences view is seq$, where last_number is the “sequence high water mark”. Ways to lose sequence numbers was talked about by Tom Kyte and Jonathon Lewis:

1. Throw the values away. Either by “select seq_test.nextval from dual” a few times or “insert into <table> (<columns>) values (seq_test.nextval, …); rollback;”.

2. Flush the shared pool – alter system flush shared_pool. Which would throw away the cached values.

3. Shutdown abort, in which case Oracle doesn’t have a chance to write back the high water mark of the sequence back to the seq$ table.

4. Cache aged out of Shared Pool by the Least Recently Used policy.

Sequence caching

To test out how sequence caching works, I ran some simple tests in my Oracle9iR2 test database. Ok… so you saw what happened when I first created the sequence in Statement #1 and #2 above. Suppose I get a nextval now:

Statement #3 (as normal user) -

SQL> select seq_test.nextval from dual;

   NEXTVAL
———-
         1

Now if I run Statement #2 immediately:

 MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20          21

This shows the high water mark in the seq$ table is updated, and 20 values of the sequence should be now cached in the shared pool (part of SGA).

Running Statement #3 a couple more times have no effect on the seq$ table, because all it is doing is getting more values from the shared pool cache until nextval reaches 21:

   NEXTVAL
———-
        21

 MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20          41

Flushing shared pool

Let’s try flushing the shared pool and see what happens.

SQL> alter system flush shared_pool;

System altered.

If I now run Statement #1 and #2:

   NEXTVAL
———-
        41

 MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20          61

So I have just lost 20 values, and the high water mark is incremented to 61.

Shutdown

Let’s try a few shutdown options – abort and immediate.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

…… (memory statistics cut out here)
Database mounted.
Database opened.

Running Statement #1 and #2 will now show:

   NEXTVAL
———-
        61

 MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20          81

How about shutdown immediate?

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

…… (memory statistics cut out here)
Database mounted.
Database opened.

We now run Statement #2, then Statement #1 then Statement #2 again:

 MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20          62

   NEXTVAL
———-
        62

MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
———- ———- ———— ———- ———–
         1 1.0000E+27            1         20          82

It shows Oracle updates the last number of the sequence used just before the database is closed. Therefore, next time the database starts, the sequence number is not skipped but continues immediately from where it was left off.

Summary

So what does this mean? It is a *feature* of Oracle to cache sequence numbers and Jonathan provided a detailed timing analysis of the benefits [source]. This means it is not safe to assume that sequence numbers in a database are always gap-free.

But putting this all into perspective, when will any of those scenarios (that would cause a sequence to skip) occur in a production real-time database (e.g. OLTP) ? How likely will someone go in, write a insert statement and decides – no, that’s not right – and rollback? When will a DBA go in and flush the shared_pool or do a shutdown abort?

Given the chances are low, doesn’t mean they won’t happen, and it is not safe enough to use one single test – detect missing sequence numbers – to conclude there’s fraud. But I like this as a good explanation to auditors as to why there are missing sequence numbers

Fed up with the Blogspot HTML editor and their themes.

See if this is any better.

Earlier this week, I was asked whether a production database (Oracle9iR1) has a database connection limit, and whether we have reached it. I found Oracle does not limit connections, but rather the number of sessions, which is effectively “maximum number of concurrent users in the system” [source].

So how do I find out what is the maximum number of concurrent users (or sessions) for this database? A search on MetaLink and Google shows that I can use the Sessions High Water Mark to determine what is the maximum number of concurrent active sessions since instance startup. This information is recorded in v$license [source]. Another way to get to this information is to grep “License high water mark” from the alert.log.

So… as usual, I ran a few queries against my test database to see what’s going on:

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
———— —————- —————- —————— ———-
           0                0                2                  3          0

So why is sessions_current=2 when I am the only one logged onto the database? What/who is the other sessions?

SQL> select  substr(sid,1,3)||’(‘||serial#||’)'  sid_ser#,
  2          substr(username,1,6)    username, 
  3          substr(terminal,1,13)   terminal,
  4          substr(program,1,30)    program,
  5          substr(type,1,10)       type, 
  6          substr(to_char(logon_time, ‘HH24:MI:SS’),1,8) logon_time
  7  from    v$session
  8  /

SID_SER#   USERNAME   TERMINAL   PROGRAM                        TYPE       LOGON_TIME
———- ———- ———- —————————— ———- ———-
1(1)                  UNKNOWN    oracle@ora9i1 (PMON)         BACKGROUND 13:46:59
2(1)                  UNKNOWN    oracle@ora9i1 (DBW0)         BACKGROUND 13:46:59
3(1)                  UNKNOWN    oracle@ora9i1 (LGWR)         BACKGROUND 13:47:00
4(1)                  UNKNOWN    oracle@ora9i1 (CKPT)         BACKGROUND 13:47:00
5(1)                  UNKNOWN    oracle@ora9i1 (SMON)         BACKGROUND 13:47:00
6(1)                  UNKNOWN    oracle@ora9i1 (RECO)         BACKGROUND 13:47:00
7(1)                  UNKNOWN    oracle@ora9i1 (CJQ0)         USER       13:47:00
8(1)                  UNKNOWN    oracle@ora9i1 (ARC0)         BACKGROUND 13:47:00
9(1)                  UNKNOWN    oracle@ora9i1 (ARC1)         BACKGROUND 13:47:00
14(6370)   SYSTEM     pts/3      sqlplus@ora9i1 (TNS V1-V3)   USER       15:40:36

10 rows selected.

So why is CJQ0 (Job Queue Process) counted as a user session? It seems Oracle knew about this, MetaLink Bug No. [455222][496136]… etc.

Another view I found was v$resource_limit:

SQL> select * from v$resource_limit where resource_name in (‘processes’, ’sessions’, ‘transactions’);

RESOURCE_NAME   CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION             LIMIT_VALUE
————— ——————- ————— —————————— ——————————
processes                        11              13        500                            500
sessions                         13              16        555                            555
transactions                      0               5        610                      UNLIMITED          

It doesn’t make sense to me why this view is reporting 13 current sessions when v$license is reporting 2. Maybe it is because my test database is a 9.2.0.1 – MetaLink Bug No. [3896119] – CURRENT_UTILIZATION of V$RESOURCE_LIMIT maybe be too high. The issue is claimed to be fixed in 9.2.0.7 and 10.2.0.1.

Anyone know how to find out a similar high water mark for cursors?

The v$license view in Oracle10gR2 is a lot more interesting:

SQL> desc v$license
 Name                          Null?    Type
 —————————-  ——– ——————-
 SESSIONS_MAX                           NUMBER
 SESSIONS_WARNING                       NUMBER
 SESSIONS_CURRENT                       NUMBER
 SESSIONS_HIGHWATER                     NUMBER
 USERS_MAX                              NUMBER
 CPU_COUNT_CURRENT                      NUMBER
 CPU_CORE_COUNT_CURRENT                 NUMBER
 CPU_SOCKET_COUNT_CURRENT               NUMBER
 CPU_COUNT_HIGHWATER                    NUMBER
 CPU_CORE_COUNT_HIGHWATER               NUMBER
 CPU_SOCKET_COUNT_HIGHWATER             NUMBER

Back to the production database I was working on…

So I have learnt that there are different partitioning methods, e.g. hash, range, list, composite… etc. I wanted to find out how each tables and indexes were partitioned. For example, if it’s using hash, then which column(s) of the table is used as input to the Oracle hash function? if it’s using range, which column of the table is used to read the range value.

Part of the answer lies in the dba_part_tables and dba_part_indexes tables. (I have ran these queries on my practice database…)

SQL> select table_name, partitioning_type, subpartitioning_type, partition_count, partitioning_key_count
2 from dba_part_tables where owner=’FTANG’;

TABLE_NAME PARTI SUBPART PARTITION_COUNT PARTITIONING_KEY_COUNT
———- —– ——- ————— ———————-
SALES RANGE NONE 2 1

SQL> select index_name, table_name, partitioning_type, subpartitioning_type, partition_count, partitioning_key_count
2 from dba_part_indexes where owner=’FTANG’;

To view which column(s) of the table is used for the partitioning for the table or index:

SQL> select name, object_type, column_name
2 from dba_part_key_columns where owner=’FTANG’;

NAME OBJEC COLUMN_NAME
—————————— —– —————
SALES TABLE SALES_DATE

If the partitioning is range, it might be necessary to view how each partition is setup:

SQL> SELECT partition_name, tablespace_name, high_value
2 from dba_tab_partitions
3 where table_name=’SALES’;

In the production database I was looking at, there’s a AUDIT_TRAIL table using range partitioning over a primary key column – TRAIL_ID. The table partition had no MAXVALUE defined. It might be a matter of time before the ORA-14400 error is encountered.

So there was a production database I looked at this week, it was using partitioning on quite a few tables and indexes.

I haven’t seen how partitioning works before, and decided to try a very small demo on my practice database:

SQL> create table sales    
  2  (sales_date date)
  3  partition by range(sales_date)
  4  (partition sales_jan2007 values less than (to_date(‘02/01/2007′, ‘dd/mm/yyyy’)));

Table created.

SQL> insert into sales (sales_date) values (to_date(‘01/01/2007′, ‘dd/mm/yyyy’));

1 row created.

SQL> insert into sales (sales_date) values (to_date(‘12/12/2008′, ‘dd/mm/yyyy’));
insert into sales (sales_date) values (to_date(‘12/12/2008′, ‘dd/mm/yyyy’))
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

As SYSDBA:

SQL> SELECT partition_name, tablespace_name, high_value
  2  from all_tab_partitions
  3  where table_name=’SALES’;

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
—————————— —————————— ——————————————————————————–
SALES_JAN2007                  SYSTEM                         TO_DATE(‘ 2007-01-02 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA

So… I maybe missing a partition option with the keyword MAXVALUE.

SQL> drop table sales;

Table dropped.

SQL> create table sales  
  2  (sales_date date)
  3  partition by range(sales_date)
  4  (partition sales_jan2007 values less than (to_date(‘02/01/2007′, ‘dd/mm/yyyy’)),
  5   partition sales_future values less than (MAXVALUE));

Table created.

SQL> insert into sales (sales_date) values (to_date(‘01/01/2007′, ‘dd/mm/yyyy’));

1 row created.

SQL> insert into sales (sales_date) values (to_date(‘12/12/2008′, ‘dd/mm/yyyy’));

1 row created.

As SYSDBA:

SQL> SELECT partition_name, tablespace_name, high_value
  2  from all_tab_partitions
  3  where table_name=’SALES’;

PARTITION_NAME                 TABLESPACE_NAME                HIGH_VALUE
—————————— —————————— ——————————————————————————–
SALES_JAN2007                  SYSTEM                         TO_DATE(‘ 2007-01-02 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
SALES_FUTURE                   SYSTEM                         MAXVALUE

It works this time around…