Database, Objects

Decoding ROWID

“Holy location address, DBAtman!”

Recently I needed to decode a rowid so I could find some information about it, I found this bit of code that I thought useful:

v_rid VARCHAR2(20) ;
v_type NUMBER;
v_obj NUMBER;
v_rfno NUMBER;
v_bno NUMBER;
v_rno NUMBER;
dbms_output.put_line('Row_ID = "'||v_rid||'"');
dbms_rowid.rowid_info(CHARTOROWID(v_rid), v_type, v_obj, v_rfno, v_bno, v_rno);
IF v_type = 0 THEN
dbms_output.put_line('RowID Type -> Restricted');
ELSE dbms_output.put_line('RowID Type -> Extended');
dbms_output.put_line('Object ID = "'||v_obj||'"');
dbms_output.put_line('Relative File Number = "'||v_rfno||'"');
dbms_output.put_line('Block Number = "'||v_bno||'"');
dbms_output.put_line('Row Number = "'||v_rno||'"');

Note that I have hard-coded the rowid but it is relatively easy to either edit this or indeed to incorporate this into a procedure.

Here’s the sample output

RowID Type -> Extended
Object ID = "18"
Relative File Number = "1"
Block Number = "241"
Row Number = "27"

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

Database, Statistics

Oracle TPS: Evaluating Transaction per Second

“Holy ‘My Boss wants Oracle TPS metrics’, DBAtman!”
Don’t worry young Robin, we can get it for you…

Sometimes this information has some ‘myth atmosphere’… Maybe because of that Oracle doesn’t have this information that clear and it’s not the most useful metric.
But for comparison to another systems and also to performance/’throughput’ with different infrastructure/database configuration, it can be useful.

It can be seen by AWR on “Report Summary” section, on “Load Profile”, “Transactions” item:


But if you want to calculate it through SQL query?
And if you want to have a historic from this metric?

I found a reference for this calculation here, using v$sysstat.
It’s the only reference I found, and it on 10g documentation… It refers this metric as:

Number of Transactions = (DeltaCommits+DeltaRollbacks)/Time

It also refers as DeltaCommits and DeltaRollbacks, respectively, “user commits” and user “rollbacks”.

Where it goes a possible SQL to do that:

WITH hist_snaps
AS (SELECT instance_number,
round(begin_interval_time,'MI') datetime,
(  begin_interval_time + 0 - LAG (begin_interval_time + 0)
OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time
FROM dba_hist_snapshot), hist_stats
AS (SELECT dbid,
VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)
FROM dba_hist_sysstat
WHERE stat_name IN ('user commits', 'user rollbacks'))
SELECT datetime,
ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"
FROM hist_snaps sn, hist_stats st
WHERE     st.instance_number = sn.instance_number
AND st.snap_id = sn.snap_id
AND diff_time IS NOT NULL
GROUP BY datetime
ORDER BY 1 desc;

I like to use PL/SQL Developer to see this kind of data. And it regards us to make very good charts very quickly. I try it in a small database here, just for example:


Jedi Master Jonathan Lewis wrote a good post about Transactions and this kind of AWR metric here.

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!

Database, Instance

Getting Oracle Parameters: Hidden and Unhidden

“Holly Secret Stuff, DBAtman!”
What other secrets do you have there?

Connected as sys with sysdba:

select x.ksppinm name,ksppdesc description,
y.kspftctxvl value,
y.kspftctxdf isdefault,
decode(bitand(y.kspftctxvf, 7), 1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, decode(bitand(y.kspftctxvf, 2), 2, 'TRUE', 'FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv2 y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx + 1 = y.kspftctxpn

order by name;Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!