logs archiveIRC Archive / Freenode / #oracle / 2010 / April / 6 / 1
Dormir
looking at my setup, though, I didn't have to set that
didn't have to set anything up except my putty session
Spec
same with the oracle-running-on-leenux
Dormir
must be something special with Windows
Spec
that can't be right either though
where NetworkA = network-with-oracle, I can do NetworkA.Linux1 ssh -> NetworkA.Linux2 -> NetworkA.windows:1521
and that works
Dormir
not sure then. windows does do odd things at time
don't have a windows box to try it out on
f0urtyfive
Hey #oracle, is 70ms from a TNS connect packet to a the response frfom the server acceptable connection time?
I have an oracle server that sends an ACK for a packet with a millisecond or two, but doesnt send a respond to the connect packet for another 70 ms or so
which seems extremely slow to me
scurnoid
hi all
usn
hi one
f0urtyfive, tnsping is no connect
f0urtyfive
usn: I'm looking at wireshark dumps.
         

usn
it may be a TCP connection, but no TNS connection
f0urtyfive
usn: connection request comes from client, ACK comes from server 0.0002 seconds later, then tns RESPONSE comes 0.074916 seconds later
it is a TNS connection request
74.9 ms seems very slow from a TNS Connection request to a TNS response
usn
did you check the listener load?
f0urtyfive
as in cpu load?
usn
sometimes slow listener logging causes slow connection times
CPU, IO
f0urtyfive
its a dev box
so load is negligible
0.00 load avg
usn
try to switch off logging and recheck
just guessing
f0urtyfive
:/ I dont know how I'm no DBA!
usn
what's your ICMP round trip time?
f0urtyfive
64 bytes from 10.10.1.217: icmp_seq=2 ttl=128 time=0.657 ms
usn
how to configure listener logging is on google
f0urtyfive
sam amount of slowness with log_status set to off
usn
f0urtyfive, what's your DB client/server versions?
f0urtyfive
11.1.0.6.0
server
client I havent a clue
usn
just type in "sqlplus"
the banner will tell you
f0urtyfive
the client thats connect is subsonic trhough
same as the server
for sqlplus
usn
mh
I haven't a Db here at home so I can't check
if you are here tomorrow, I can give you some reference times
f0urtyfive
that'd be handy.
tnsping certainly takes a lot less time
but I imagine it doesnt do the same thing
it wouldnt be much of a problem, except the stupid app doesnt use persistent connections, so it connects 6 times in a simple page load
so 6*75ms starts to add up
usn
yeah
What's client and server OS?
f0urtyfive
btw
the oracle server is rhel 5
client is win2k3 r2
         

usn
mh
just a try
f0urtyfive
btw, is there anything on the oracle side that would prevent the app from opening persistent connections?
usn
switch off OS authentication in client's sqlnet.ora
usually you have to replace "NTS" with "NONE"
just worth a try
what's a "persistent connection" in your terms?
usually one can open one session and keep it
f0urtyfive
usn: the webserver has pantloads of time_wait connections
it looks like it opens multiple connections to the same oracle server for each page load
usn
mh
f0urtyfive
but It looks like most of there code is trying to use subsonic's persistent connection stuff
usn
for a web server, there's a good alternative
or better, two
one is to use a connection pool
the other one is, to choose an entirely different connection method
quick explanation: Using the usual sessions, you have to wait for the DB server to open a "dedicated server" for each session
this is one OS thread to start up, and if you are connecting multiple times, for one short query and then disconnect, it may create severe overhead
in this single situation, when OS thread startup time is massive, you can go to "shared servers"
f0urtyfive
changing from nts to none had no impact :(
usn
there will be a dispatcher process, and several shared server threads in reserve on the server, and your connection can use one of them straight ahead
(without having to wait for its start)
f0urtyfive
hmmm
would the first one be client side, second be oracle side?
usn
yes
but on oracle server side it's easier
its just tweaking three parameters
f0urtyfive
that'd be handy...
any chance you could tell me how to check what those settings are currently?
usn
but on the downside, for expensive statements, it's slower this "shared" way
I'll give you a link
f0urtyfive
that'd be handy.
usn
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/manproc003.htm
ok, I've got to go to bed now, but in about 10 hours I'll be in the office, just peek in, maybe I'm here then. If not, the other guys will help you
f0urtyfive
hah, ok thanks.
usn
np
snagnever
Hey! Please, i'm newbie to oracle and i've tried some google searches with no success.
i'm trying to create a user and allow him to create tables, alter table, inser, select, update on tables on his schema
so, i've done a CREATE USER labbd IDENTIFIED BY labbd;
then created a role called exercicio
thehttp://pastebin.org/138493n
http://pastebin.org/138493 **
but i'm not able to create tables
dsdel
11gr2 for win out \o/
TheBonsai
who runs oracle on windows at free will? ;-)
dsdel
95% our customers
usn_work
good morning
mator
ello
bungley
my oracle is killing itself at 2am every day
anyone got any pointers?
mator
oom-killer?
bungley, what is your OS ?
bungley
solaris
i think it's rman failing.
but i've not been able to artificially induce it
usn_work
do you have an alert log entry for the time?
bungley
hmmm
i dropped this a week or two ago and thought i could pick it up again without doing my research....
(Action) sobs
usn_work
without logs and traces available, it's useless to do anything
BAAG - battle against any guess
bungley
sure.
actually, can someone tell me how i disable rman from running? this db is just a dev one.
dsdel
rman doesn't run 'alone' without got scheduled ones :P
FlaPer87
hey guys, I'm trying to connect to Oracle using python bindings but I get a TNS error saying that Oracle could'n't resolve the identifier... Am I missing something?
Windows with Oracle XE
=)
bungley
how do i ungot the scheduled ones?
usn_work
FlaPer87, can you connect with sqlplus?
bungley, check cron and the dbms_scheduler
FlaPer87
usn_work: I can connectusing the web manager
usn_work
FlaPer87, can you connect with sqlplus?
FlaPer87
usn_work: I'm downloading it
usn_work
usinf the same tns config as your phyton does?
downloading??
it's part of the client
you should already have it
FlaPer87
usn_work: yeap, sorry about that, I can connect with sqlplus
usn_work
check if your python environment has a properly set oracle home directory, or at least TNS_ADMIN set to the tnsnames.ora
FlaPer87
usn_work: nothing, I set TNS_ADMIN as global env pointing to the file tnsnames.ora and I got the same error
bungley
usn_work: ta
usn_work
FlaPer87, make it point to the directory the tnsnames.ora is in
bungley, ta?
FlaPer87
usn_work: I did that too
bungley
usn_work: for the pointers...
usn_work
what's "ta"?
cmug
like thanks
usn_work
okay, then: no problem
bungley
usn_work: "thanks"
usn_work
FlaPer87, somehow your python does not know the TNS entry
try to make sure to have tested with sqlplus in the same same same environment
e.g. make it start from your www-run user, whatever the python runs in
FlaPer87
usn_work: ok
sytse
hmmm, sh*t, there is a problem at one of our clients with lots and lots of sessions waiting for 'library cache' latches, but we can't find any single culprit.. no blocking sessions (for more than a second or so), it appears to me as if it's the total SQL parsing load that's just a little bit too high
we could try upping some random parameters, like session_cached_cursors, but I'm afraid that's not going to be enough.. has anyone else experienced this kind of apparently systemic library cache contention problem?
oh and yes, I know the best solution would be 'hire a real DBA' ;-)
usn_work
hehe
just a desperate test: Does flushing the shared pool help?
not as solution, just to narrow possibilities
another approach, sytse - do a statspack or preferably AWR report to find out what's going on
sytse
hmm, I guess I could try, though my gut says that with 600 sessions waiting, that could cause an immediate extra wait of several minutes?
usn_work
the script is in ORACLE_HOME/rdbms/admin - named awrrpt.sql
sytse
I'm looking at the results of an RDA run right now
usn_work
it should give you details on waits and latches
mh
I don't know it, I don't use it
sytse
is it safe to run ALTER SYSTEM FLUSH SHARED POOL on a live system?
usn_work
yes, it's safe. But it will cause re-parsing
-> CPU-Load
sytse
CPU-load is no problem at all
it's got 8 hyperthreading cores that are not at all running at full capacity
usn_work
do you see a library chache hit ratio from your RDA somethingy?
and, what's your Db version?
sytse
sytse
version is ROUND ((SUM (gets) - SUM (misses)) * 100 / SUM (gets), 2)
FROM v$latch
um
version is 10.2.0.3.0
library cache latch hit ratio is 99,81 (from v$latch, dunno if that's the correct statistic)
SQL area get hitrate: 4,86
(percent)
usn_work
v$latch is aggegated from last restart I think
RJarett
sytse: run collectl on your box for a day or 2. watch your iowait.
usn_work
so its an all-the-time average
RJarett
i may have missed it. is this linux or windows?
sytse
linux, amd64
RJarett
sometimes you just have to eat some performance issues in a few places. I process a ton of data every night, and my latch waits are through the roof.
usn_work
but SQL area get hitrate of 5% - it means you are running dynamic SQL
it's parsing all the time
Maybe you don'T NEED A "real DBA", but a "real SQL developer" ;)
RJarett
developers are sh*tty db tuners
Trengo
i always worry about performance
RJarett
worry maybe, actually do something about it besides say crap like "we need to buy more hardware" eh i dunno
usn_work
yeah, but introducing bind variables is a sh*tty job for a DBA
RJarett
What has been done to profile all the queries on this system?
What is the io scheduler of the disk subsystem ?
usn_work
let me guess: Anticipatory :)
RJarett
CFQ is my guess
stall stall stall stall
sytse
according to the awrrpt stats I ran over the past few hours, the SQL AREA library cache has 93.91% misses for get requests, but that's only 72985 requests
usn_work
but I miss the relation to a library cache problem, RJarett?
RJarett
call it a hunch
sytse
(this is a report between the snapshots of 09:00 and 15:00 today.. it's 15:36 in my timezone now)
usn_work: 'real SQL developers' we need, too, that's definitely true ;-)
« prev 1 2 next »