logs archiveIRC Archive / Freenode / #oracle / 2010 / April / 27 / 1
TomJ
How much data storage does a NULL require in a NUMBER column?
RJarett
depends on how number was defined
and your characterset
TomJ
ok, well for example NUMBER(18,3) in UTF8?
RJarett
http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c10datyp.htm#743
Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros.
TomJ
sure.. I know that
but I was asking about NULL
I know how much space anything that is non-null takes, I can get that with VSIZE
RJarett
why would it be different?
TomJ
well it could be 1 byte, or it could be 0 - maybe it just doesnt store a value at all, and takes the absence of any byte i that location as being null? that's what I dont know
RJarett
does it matter?
TomJ
well my application team wanted to store NULL instead of 0 thinking they would save lots of storage space
         

RJarett
your application team are giant fu*king tards
TomJ
I think it's a bad idea, but I want to now specifically if they would save anything
RJarett
and the typeof people who caused y2k
and splitting hairs when i bet their code sucks ass and can save much more elsewhere
TomJ
OK I'll pass that on :) reasons why they're tards? because null isn't indexed and beacuse it breaks queries like "< 1" ?
RJarett
and by doing weird ass savings like that youll end up with much more chained rows and wasted space elsewhere
its pseudomath on space
TomJ
well, to be fair we are talking about billions of rows, but yes I think it's a bad idea
why chained rows?
RJarett
does your app update at all?
TomJ
yes
RJarett
and yes null does have an indicator
a null indicator is 1 byte and a field has length
and billions of rows is still pretty small
your wasted space will be elsewhere im sure
you should be more concerned with your index btree height jumping a level once you fragment your data or start to chain rows
TomJ
sorry, why is that impacted by the null/0 decision?
RJarett
doesnt matter
what lang is your app in?
TomJ
Java using Hibernate
RJarett
of course it is
youll have a world of inefficiencies with hibernate for your developers to be asshatting on null vs 0 ffor space savings
Junior
yello ;)
TomJ
yeah well that's a whole different can of worms
RJarett
dont sweat the small stuff
TomJ
if I had my way all interfaces to the database would be stored procedures and SQL would not come within a mile of a .java file
RJarett
wow
youre a dumbass sqlserver coder arent you?
i dont know who i feel more sorry for, the devs, or the CEO
TomJ
Er no, thanks very much
You don't like SPs either?
RJarett
nope
because they are usually only embraced like you stated by old people with no real experience coding anything else
and tend to make the worst, most inefficient, bloated code
Something that can be done in 5 lines in awk/sed to be a 10 package 1000 line plsql
bloat bloat bloat
         

TomJ
yes, that's why I said interfaces. put the SQL code in the DB and the business code in the app. java calls a SP to set/fetch data.
RJarett
"it's code reuse.. its the future"
TomJ
and if you think awk/sed has a place here then.. well
RJarett
you do nothing to benefit performance by running it in sp vs java
its the same sh*tty ass code
you just make it harder to handle the ref cursor
nope
has anyone migrated from solaris and 10g to rhel 5 and 11g?
RJarett
ive done many solaris 8i and 9i to 10g and 11
TomJ
nope: nope
nor would I dream of doing such a horrible thing
you should be migrating from RHEL 5 to Solaris 10
RJarett
yeah nothing like going to an old slow ass OS thats going to be decommissioned within 3 years
TomJ
ha, your ignorance is palpable
Solaris 10 is the best server OS every created
'old' is particularly laughable. How's your last-generation filesystem working out for you? Oh, and that quaint little /etc/rc.d or /etc/init.d system. So 1990s.
RJarett
you ever use zfs for oracle?
ill stick with ext. k thanks
TomJ
For QA systems yes - snapshots and clones are particularly lovely for that
RJarett
yeah wait till it corrupts or goes slow as hell under load
TomJ
I'm still waiting for that
RJarett
Im betting I have a tad more experience w/ admin work on VLDB and high io DBs than most here and have tried everything possible to tune every bit of performance out of my systems. ZFS tested as one of the worst for oracle
5-6k iops.. eh itll work. 50-60k iops, hope your tape backups are good.
TomJ
I didn't recommend it for production/high performance databases. It still has some work to go there - it's about 4 years old, compared to UFS/ext/whatever which are decades old. For most other purposes, it rocks
RJarett
yeah i dont give a fu*k about those other purposes
this is #oracle where the big boys play
not #mysql
like i said, ill stick w/ ext3
nope
it`s a reason we are removing solaris though
1. the hardware is way too old
TomJ
Yes, as I said I get particular pleasure from snapshots and clones in QA environments, where I can install revert my DB and app stack back to any previous version for comparison testing in seconds
nope
2. extremely limited solaris know-how inhouse
RJarett
i can do that w/ lvm and ext3 too
or most other filesystems
or the san itself
TomJ
You see, I choose the best tool for each particular job. it works out pretty well for me
RJarett
or vmware
or umm
wait about anything for the last decade
TomJ
Also, ZFS is just one of the many ways Solaris rocks. There's SMF, for process/service management which doesn't rely on a bunch of shell scripts, and can actually detect when an app has crashed and needs restarting. Oh, and zones, instant, near-zero overhead virtualisation. Oh, and woring NFS v4 , you know, with ACLs and stuff. then there's RBAC for granular delegated security, and dtrace for deep system monitoring/analysis
But yeah, you're right, Solaris is way old..
RJarett
slow as hell
TomJ
Show me your benchmark data?
RJarett
plus wtf would i want to serve nfs off a little box?
thats what i pay good money for SANs for
nope
RJarett: it`s not all data you want to waste expensive san space for
RJarett
1tb of nand based cache + nfs lovin
TomJ
I don't know. I don't really have any little boxes. And I prefer paying as little as possible to get the job done
RJarett
thats why the san has sata too
nope
looks like we are getting another san just to do backups, definetely only sata drives
TomJ
Anyway, I know there's no point arguing with zealots - reason or facts play little part. So, good luck to you - you use what makes you happy, as I do
RJarett
nope anything w/ dedup?
nope
RJarett: yeah
RJarett
lol youre the only zealot solaris fanbo
boy
nope
probably only using dedup on specific parts of the backup though
RJarett
ive worked with dozens of unix variants over my career. solaris was just one i was happy to get off of
nope
vmdk`s etc
RJarett
yeah im seeing 93:1 dedup on my vms
makes wan replication so nice
nope
hehe
wich backupsystem are you using?
RJarett
data domain
nope
ok
I think the system we are looking at, is dedup light though
RJarett
hmmm
TomJ
Then I don't think you used Solaris 10, or else you didn't take any time to learn it. I agree that Solaris <10 were nothing special - reliable, stable, but not particularly exciting. Solaris 10 is an order of magnitude better than anything else available. Especially Linux - champion of the mediocre, prince of the lowest common denominator.
nope
we use tivoli (tsm) today, and it`s a hassle
RJarett
TomJ: i used 10 alot. i used zfs while it was still beta. i used user space zfs in linux
nope
awesome backupsystem, but it usually needs a dedicated person working only with tsm
RJarett
yeah for the app we use netbackup for everything but vmdk layer
i use vizioncore for that or ghettovcb
but vizioncore is terrible
veeam has gotten really nice
nope
good to know
we`re getting a few options to look at, and it seems actually the new backup exec might be enough
or maybe comvault
RJarett
commvault isnt bad
how much of your env is virtualized?
nope
almost everything
RJarett
look at veamm for everything but putting it to tape
TomJ
RJarett: User space ZFS is slow as hell, and ZFS in beta was somewhat unstable for a while - not too surprising, it's a very complex and completely new system. Try a production copy, and take the time to learn about SMF, Zones, DTrace and RBAC, then look to see what options exist for those features on Linux. Do a side-by-side comparison and I don't see how you can't see that Solaris offers far more.
RJarett
TomJ: stpo talking to me
go fu*k your developers and their smaller than 0 nulls
k thanks
seriously, stfu
nope
when we are "done", I think we will have 2(maybe 3) oracle servers on hw, two windows boxes
the rest should be vm`s
work in progress though
RJarett
thats about how i am. 1 windows fs at the main office, and 1 large oracle db
the rest vm
TomJ
RJarett: sorry, it was very rude of me to intrude into your little shell with 'facts' and 'logic'. I know it upsets people like you.
nope
we`re actually going to put our fileserver in vmware aswell
RJarett
but i did do a test on my largest db switching it from physical w./ 16 cores to virtual w/ 8vcpu against a netapp 3170 w/ 1tb nand cache + 16 physical disks
nope
we have plenty of available io on both the san and the bladecenter
RJarett
the vm/netapp smoked our 16core box w/ a 100+ disk hitachi
nope
nice
RJarett
by a 2x margin w/o any advanced tuning
and that was with 4x 1gb nics vs 4x 4gb fc
so when the new san comes in filled with 10gbe is should b even better
nope
io made the difference?
we use 10gbe iscsi
RJarett
yeah 2ms and faster caching of 30-40% of the data
nope
but our oracle servers are plain old and slow
RJarett
my workload is 70%write/30read
nope
900mhz sparc 3gb ram etc :)
RJarett
hundreds of gb/night
1 quad core opteron should outdo those easy
nope
yeah
RJarett
if they are low io no reason they cant be virtualized
nope
have speced a couple of servers, quadcore xeon, 24gb ram etc
I was hoping to have them as vms, but the people who use them was a bit sceptical
RJarett
im an amd fan
nope: dont tell them ;)
nope
and oracle dont support oracle on vmware all the way
RJarett
just have to be careful of oracles nazi licensing w/ vmware
nope
thats definitely an option :)
yeah, or use alter system set cpu=
RJarett
well if its not raq they usually wont care or ask
nope
and lock it down
ah
RJarett
nope: eh thats still not hard partitioning per the license
nope
attended a talk by dan morgan
RJarett
since you can run multiple instances on the same system on different cores for full performance on each
lol.
nope
and he said that would actually work
RJarett
attend an audit by oracle lawyers
nope
hehehe
RJarett
nope
100% nope
vmware is entirely soft partitioning by oracle terms
nope
then vmware is not an option :)
RJarett
well how many socket licenses do you have?
nope
2 standard 1 cpu, and one enterprise 2 cpu
our vmware environment is 32 cores
RJarett
so you put it n a dual core dual cpu box running vmware and you are fine
or a quad single box
nope
would have to keep that outside of the esx cluster then
RJarett
not really
TomJ
nope: Use Solaris containers/zones on Solaris x64: they're acceptable for Oracle licensing, if you hard allocate CPUs to the container so it can only see the licensed number of CPUs. And, the zone has no CPU overhead and only about 40mb RAM overhead because they share the host kernel.
nope
sounds like vserver on linux
RJarett
TomJ: if your solaris box needs to go down for work, can you drag and drop that zone to another solaris box and keep that zone working?
TomJ
Similar, but much better
RJarett
well no you cant
zones is ghetto virtualization
TomJ
No, not yet. That's the chief disadvantage compared to ESX,yes. But it has plenty of other advantages. Depends on your use case of course. Note that if you use Oracle with vmotion you have to a license for both servers
RJarett
no you dont.
nope
if I have two physical oracle servers, and licenses for those
TomJ
Well, unless server 2 is used for less than 7 days a year I think it is. which is unlikely in a vmotion setup
RJarett
you have 10 calendar days in the event of a disaster or system issue to run on another box
nope
if I migrate them into vmware, and keep them powered off
« prev 1 2 3 next »