logs archiveIRC Archive / Freenode / #oracle / 2010 / June / 8 / 1
Ramen
is there a way I can select a,b,count(c) from table group by a?
I want the group by but the field in b to still show up
without having to do subqueries
nm
was a dumb question
niko-nojo
drdimitri - downloaded both files. How do I install them ?
drdimitri - ignore that
ygip
any suggestion to how I can "fold" out rows in my table. I have a column with an inetegr value e.g. 15, I want to create 15 new rows in the same table with the value set to 1, i want to do this for each row in my table
deebo
using sql developer can i somehow dump a cursor in some structured format?
currently calling a stored proc and using 'print out_cursor;'
sytse
dbms_lob.createtemporary(cache => true) + dbms_lob.open/dbms_lob.close.. benefits? Not necessary? Very dramatic for performance if you don't, and then perform many operations on the LOB? Which is it?
dsdel
hmm got oracle 11g r2 x64 installed on an oracle unbreakable linux (rhel 5.5 tikanga) on top of an vmware esx server
the i/o performance is really poor - anyone had such problem already?
usn_work
dsdel, yes
this is a general problem of VMware, one of the reasons why it isn't supported
A workaround is to map a lun directly into the VM, bypassing the emulation layer of VMware
dsdel
running windows sap system with oracle 10g on the same box - great performance
hmm that will be hard to implement on the disk but thanks
btw. oracle certifies only operating system - they never certified hardware or anything else. so I don't get the point of oracle telling they don't support vmware
usn_work
did you ORION benchmarks on that volume?
         

dsdel
checked with hdparm
there are 4 boxes running in parallel: debian/debian/windows 2003/oul
only oul has that performance problem
usn_work
ORION has the same IO stack and method Oracle products are using. It's alwqays good to check it this way to avoid surprises
dsdel
but rhel native on another box which is running same esx version has no performance problems...
usn_work
(avoiding the installation efford)
dsdel
learned again something new - thanks :)
usn_work
are you using raw devices, ASM or file system on the slow box?
dsdel
no nothing - fs ext3
usn_work
if FS, which one? Is it created with the same parameters everywhere?
dsdel
er sorry - fle system naturally
yes, ext3 on top of lvm on top of 3 disks (vmdk)
usn_work
uh-oh
nasty stacking of troublemakers ;)
dsdel
btw. using lsi logic parallel
yes but it's going to get HA through vmware
usn_work
is the ext3 created the SAME way everywhere?
dsdel
and i don't want a SPOF at disk side
usn_work: naturally
usn_work
same parameters?
mh
dsdel
everything exaclty the same
RJarett
mornin
usn_work
hio
eh, lowio@dsdel ;)
dsdel
:P
if I have fun today evening I'll install a win2k8 box and check again
usn_work
Is the device itself slower than on the other boxes, when checked with dd?
dsdel
if the performance is better I'll throw oul at oracle
         

usn_work
(reading of course for now)
dsdel
usn_work: yes, it seems that the problem is somewhere at OUL
usn_work
can't you skip the LVM?
dsdel
the complete machine behafes like that, equal what you are doing (oracle rdbms/hdparm/massive copy/and so on)
usn_work
what about the devices if there is no LVM in service?
dsdel
I can't think of that LVM causes such performance problems
usn_work
did you check?
dsdel
nah, unable to - the esx box is filled up from storage side
but the RHEL is also using lvm
usn_work
mh? dd usually reads without harming anybody ;)
ORION as well
Do you have the performance impact on reading, writing or both?
dsdel
ah now I got you - you mean to measure the performance hehe
wil check that
usn_work: both
usn_work
yeah, measure AND compare
dsdel
thanks - will check that. but I don't think that it will enhance somewhere
will also try a kernel from rhel native - if the performance problems are gone then
RJarett
describe your setup
usn_work
I can't enhance anything, but detect where the culprit sits
RJarett, you dont want to hear it :)
RJarett
if its sh*t, then why bother with any of this routine?
cant polish a turd
dsdel
vmware vsphere / virtual machine with lsilogic parallel / 3 vmdk's / oralce unbreakable linux (rhel 5.5) / lvm / ext3 / oracle 11g r2
RJarett
how many disks
dsdel
3 vmdk's :)
RJarett
answer the fu*king question
a vmdk isnt a disk
usn_work
=:-0
RJarett
it is a file
dsdel
ah raid 1+0 on top of 4 disks
RJarett
and what other VMs are sharing the same disks?
so you have 2 disks usable for IO. what type of disks?
usn_work
are all three vmdb's on the same raid 1+0?
dsdel
the debian ones (mail & webserver - low frequented - 70% idle)
sas / 15000 rpm / wd
usn_work
are all three vmdk's on the same raid 1+0?
RJarett
ok so you have 150 iops/disk max with proper seq io. lucky w/ 300 iops
dsdel
usn_work: only oracle box is completly on the 1+0, from other two there are only the data partitions mapped to
RJarett
im thinking you are pushing 250 iops max, with your mail server using about 20 of that
so. figure you have 230 iops usable
dsdel
the mail server is only outgoing routing of internal device mails/emca status mails and so on - no real users
RJarett
if you are multiplexing your archive logs, and redo logs, and undo, and dbwr normal stuff
230 isnt much
dsdel
ah shutdown immediate ran yesterday 45 minutes...
RJarett
And Im betting alot of this is random io type activity if it hasnt been well tuned on the db
well that waits for connections to die and close
thats not a good indicator of anything
dsdel
immediate not
usn_work
dsdel, you told me that you are on a device that is mirrored by LVM over three vmdk's. Now my question: Are the three vmdks on the same RAID?
dsdel
usn_work: yes
usn_work
=:-O
dsdel
and I haven't told ya they are mirrored
usn_work
so you have 1/3 of the IOPS available
dsdel
I'm just using lvm to resize partitions dynamically
RJarett
what do you mean mirrored over lvm over 3 vmdk?
usn_work
so how do you distribute the DB's files over the LVMs?
RJarett
if you are just concat w/ LVM its not changing anything
usn_work
<dsdel> yes but it's going to get HA through vmware
<dsdel> and i don't want a SPOF at disk side
dsdel
usn_work: datafiles partition which uses 2 vmdk's
usn_work: HA through VMWARE :P
vmware mirroring between 2 vmware vsphere
RJarett
youre speed is going to be the same as running this guy off a usb external
dsdel
and 'it's going to' - not yet implemented
RJarett
there are no other limiting factors besides the basic, utterly low spindle count
usn_work
... and the fact that it's a vmdk
RJarett
no
usn_work
y?
RJarett
in this case it probably helps it
usn_work
how?
RJarett
better block reads and more seq io vs if it was just native w/ ext3/lvm
dsdel
hm so RJarett you say running this setup with 15k sas,4disks,raid 1+0, vmfs with vmdk on it, linux on lvm (no mirror) is like external usb?
RJarett
you dont have 4 usable disks of io
you have 2
plus, does your raid card have bbwc (battery backed write cache)
dsdel
naturally
512mb raidcache IIRC
write through activated
RJarett
ok
dsdel
raid stripeset 512k
RJarett
yeah vmware is going to handle the larger block size better than linux native
dsdel
ye, that was the reason for choosing 512k
RJarett
now you just have to figure if oracle is working well with it
plus, did you align your partition in your vmdk with the block boundary?
dsdel
yes
RJarett
i absolutely cant stand the concept because its more a buzzword than a real useful thing in 99% of the cases, but this is a case where itll mean alot
you did?
thats rare for linux vm users
how did you go about it
dsdel
but it was the first machine I've done that
and before I never used it and had no performance problems
RJarett
well its not a black and white performance problem thing
dsdel
ah need to get back to my documentation - the setup is already 6 months gone
but I needed around 2/3 days to get this done...
RJarett
it just helps reduce SOME multiblock reads
have you analyzed your workload to see how many IOPS you NEED?
this is a case where your only solution may be popping in some SSD or a fusionio
depending on the budget
is it an HP?
dsdel
Timing buffered disk reads: 2 MB in 6.88 seconds = 297.53 kB/sec
...
no supermicro system
RJarett
what raid card?
dsdel
sorry for the late response, had phone
adaptec - have to get the correct type / model nr if you need to
but hardware/real raid no softwareraidfoo
software/hostraid...
RJarett
you sure the bbwc is functional?
and do you have vmware tools installed and up to date?
dsdel
yes and yes
Timing buffered disk reads: 4 MB in 4.62 seconds = 886.92 kB/sec - the mail server on the disk on the same raid
will get further stats and then move it to another esx with different hardware and re-check
RJarett
date;time dd if=/dev/zero of=/tmp/test1.tst bs=8k count=524288;date
thats going to write a 4gb file which will overflow your bbwc and youll see real performance on writes
hdparm cant be trusted
dsdel
hehe yes, 2mb is inside the cache - didn't thought onto that thx
usn_work
to view it in realtime, do "iostat -x 1", maybe only for the device you are writing to
dddh
dsdel: show your dd timing
dsdel
oul: 4294967296 bytes (4.3 GB) copied, 444.194 s, 9.7 MB/s
mail: 4294967296 bytes (4.3 GB) copied, 41.4496 seconds, 104 MB/s
dddh
;(
RJarett
heh oracle sucks
dsdel
so enough for today, have to get other work done as of checking some strange performance problems currently
RJarett
whats your io scheduler?
dsdel
luckily there are only secondary database instances
dddh
RJarett: do you recommend deadline?
RJarett
yeah
for all VMs
its the most fair and play nice
dsdel
hmm needed to check
thanks RJarett, usn_work for your time and efforts
usn_work
you're welcome
strange 1:10 factor ...
dsdel
yes, everything is very strange there
hyphenex
Does oracle 10g express have schemas?
usn_work
yes
hyphenex
in the sense that MySQL Does databases? Create a schema, give multiple users access to that db?
usn_work
In Oracle, one user has one schema
RJarett
mysql doesnt have scemas
schemas
usn_work
you can grant access privileges to other users if you want
hyphenex
ok, cool :)
usn_work
but it's nasty, you will have to grant object by object
hyphenex
so when I created a user through the GUi, they pretty much have their own schema and can't touch any other users stuff?
usn_work
what GUI are you talking about?
hyphenex
the apex one
usn_work
uh, no clue
do SELECT username from dba_users to see the user list
RJarett
users only have access to what you give them outside of their own
and public
« prev 1 2 next »