FAST '07 Technical Sessions

FAST '07 Technical Sessions

Blogged with Flock

Super Furry Animals at Cats Cradle

We had the pleasure Super Furry animals in Chapel Hill this week, Pete Brooker was over from the UK and so we had a small crowd of neighbors and NetAppers with us.

Blogged with Flock

OS X pmap

Looking for something like Solaris' 'pmap' on OS X? Try 'vmmap' from the terminal window. It outputs the per-segment memory usage, and all the usual stuff like which libraries are mapped, and where. The final section gives a nice summary. The o/p below is from Firefox, which was using over 700Mb of pysical RAM. I am currently experimenting with Flock, which supposedly has better memory management. I think the first issue I might have is that I use the 1Passwd utility pretty heavily, and I do not know if it is supported in the flock browser.

==== Summary for process 1473
ReadOnly portion of Libraries: Total=130.6M resident=44.2M(34%) swapped_out_or_unallocated=86.3M(66%)
Writable regions: Total=1.8G written=1.0G(53%) resident=875.7M(46%) swapped_out=552.6M(29%) unallocated=1.0G(54%)

REGION TYPE             [ VIRTUAL]
===========             [ =======]
ATS (font support)      [   33.9M]
CG backing stores       [   11.9M]
CG raster data          [   2084K]
CG shared images        [   3208K]
Carbon                  [   1464K]
CoreGraphics            [    328K]
IOKit                   [  256.0M]
Java                    [  161.0M]
MALLOC                  [    1.3G]
Mach message            [      8K]
Memory tag=240          [      4K]
STACK GUARD             [   56.3M]
Stack                   [   20.0M]
VM_ALLOCATE ?           [   16.6M]
__DATA                  [   7780K]
__DATA/__OBJC           [     32K]
__IMAGE                 [   1240K]
__IMPORT                [    852K]
__LINKEDIT              [   12.6M]
__OBJC                  [   2296K]
__PAGEZERO              [      4K]
__TEXT                  [  118.0M]
__UNICODE               [    532K]
mapped file             [  103.5M]
shared memory           [   50.1M]
shared pmap             [   4124K]

Use asmcmd to see what's in the ASM database

There are probably other/better ways to see what datafiles are being managed by ASM, but this is one way. The command asmcmd lives in the $ORACLE_HOME/bin irectory. In this example I have created an ASM area for my database files, control files etc. By cd'ing to the DATA directory I can see the files that are created in the group.

[oracle@beefy bin]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> ls DATA
10I/
control_001
control_002
data_1
data_10
data_11
data_12
data_13

See what SQL is executing in Oracle 'right now'.

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null

I have called this 'what.sql'

Useful Oracle scripts

space.sql Display tablespace size and current utilisation.

SQL> @space

										  %
								 %		     MaxPoss	Max
Tablespace Name 	   KBytes	  Used	       Free   Used	Largest       Kbytes   Used
------------------- ------------- ------------ ------------ ------ ------------ ------------ ------
 m SYSTEM		1,048,576      206,976	    841,600   19.7	841,488   0	 .0
*a SYSAUX		1,048,576      200,192	    848,384   19.1	848,384   0	 .0
*a TS_DATA	      503,316,480  384,733,696	118,582,784   76.4    8,257,536   0	 .0
*a TS_DEFAULT		2,097,152	   704	  2,096,448	.0    2,096,448   0	 .0
*m TS_TEMP	      265,420,800  265,420,800		  0  100.0	      0   0	 .0
*m TS_UNDO1		8,388,608      775,232	  7,613,376    9.2    7,482,368   0	 .0
*m TS_UNDO2		8,388,608	 1,344	  8,387,264	.0    8,256,256   0	 .0
		    ------------- ------------ ------------
sum		      789,708,800  651,338,944	138,369,856

7 rows selected.

table_sizes.sql Display #rows, row length #blocks used per schema

SQL> @table_sizes
Enter value for 1: tpch

TABLE_NAME		       TABLESPACE_NAME	      NUM_ROWS AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
------------------------------ -------------------- ---------- ----------- ---------- ------------
CUSTOMER					      45045500	       159     440887		 0
LINEITEM					    1799937800	       128    8800930		 0
NATION			       TS_DEFAULT		    25	       102	    4		 0
ORDERS						     449975200	       112    2797625		 0
PART						      60001700	       132     341698		 0
PARTSUPP					     240135800	       144    2484286		 0
PLAN_TABLE		       TS_DEFAULT		     0		 0	    0		 0
REGION			       TS_DEFAULT		     5		97	    4		 0
SUPPLIER					       3006700	       145	28679		 0

How to restart DB after deleting datafiles.

The database will refuse to start if datafiles are removed from the os/storage without first removing them from within the DB. Dropping the table(s) stored on the datafiles won't cut it.

1. connect / as sysdba
2. startup mount
3. alter databsae drop datafile '/filename' offline drop;

The next part requires the DB to be open so...

4. alter database open;
5. drop tablespace including contents;

You can now recreate your datafiles, rebuild the tablespace and so on.