logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 20 / 1
Rudemeister
whats the question
"TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them."
done
Kog|Work
well, my question was is there more to TNS than TNSNAme
erm, TNSNAMES.ORA
figuring out if 10G can talk to 11G is easily enough tested
Rudemeister
to have absolute evidence to through off the guy
check oracle's cert.matrix
thats your guide to go
Kog|Work
not familiar with that
Rudemeister
Kog|Work: got mysupport access?
Kog|Work
Rudemeister: the company might, I don't
we *do* pay for support, so I hope we have access
certainly costs enough...
Rudemeister
if one logs on there, they can More.. Certifications
and find out for 100%
gl
(Action) &
Kog|Work
I was actually just looking to make sure I understood tNS
and that there was no other, more insideous piece of it
Rudemeister
(you do)
         

Kog|Work
ok
Rudemeister: wondering if he meant OCI
Rudemeister
..
Kog|Work
well, I'm trying to parse what the hell "TNS Driver" would mean
(Action) shrugs and goes off to defeat ignorance
Rudemeister
++
TheBonsai
m00
Kog|Work
(Action) grills TheBonsai
TheBonsai
huh
Kog|Work
sorry, thought you were delicious, delicious beef
TheBonsai
bonsais aren't made of beef (i heard)
Kog|Work
veal is the bonsai of beef
TheBonsai
hehe
Kog|Work
TheBonsai: that's going to be my new family crest
TheBonsai: a picture of a surprised cow and the statement "veal is the bonsai of beef"
Rudemeister
dus
tragicx
Anybody awake this evening?
When somebody has time I would like to discuss optimization tips on using SQL loader on large data files.
Mainly Parallel Direct loads w/ one or more files, Multithreading options, etc.
What i'm trying to fix is a direct load (truncate) we are having w/ a 260 gig flat file (fixed width) taking ~36 hours to load.
What I do know: Multithreading of off, we are not processing using Parallel...
If anybody has any numbers on what sort of time this could save by switching or any other insight that would be extremely helpful.
krb_hnd
hi all
can somebody help me with indexing
Dormir
ask your question. someone will help if they can
Rudemeister
true, but I cannot:S
yopta
I forget SQL
I have table B with fields (a_id, b_id) and I need to update table A.b_id with values from B.b_id, I remember that it is not complex... but that's all I remember :)
update a join b on a.id=b.a_id set a.b_id=b.b_id; !!!
dsdel
or subselect
yopta
show!
         

Rudemeister
?
idletask
Hello
(Action) is trying to create a database on btrfs
It's quite slow :/
(Action) thinks he should have set filesystemio_options to setall
Rudemeister
hpux?
idletask
No
Gentoo Linux, with attempt to create a database on btrfs :p
Rudemeister
aha
idletask
The machine is no slouch, but the creation is really slow
Rudemeister
what does mysupp say about that
idletask
mysupp?
Rudemeister
oracle notes on that
idletask
I haven't even looked
I'm trying this for fun
Rudemeister
I know
idletask
I've already had to remerge gcc with nptl support, otherwise it doesn't link
Rudemeister
ok
idletask
The install was no problem after that though, it was even quite fast
dstat shows that only 25 MB/s are written on disk on average, and my system can sustain ten times that
I _should_ have setup filesystemio_options
Rudemeister
ok
idletask
Also, I've played quite a bit with dbvault
I can do nice tricks now
Rudemeister
tell me
idletask
Such as allowing users to connect only from certain IP addresses
I can also have sys execute procedures to create users without it having the dv_acctmgr role
Rudemeister
the old protocol.ora thingy
idletask
Heh
No
A command rule on CONNECT
It has required the creation of three tables and three procedures, all owned by a dedicated schema and with the appropriate grants
http://paste.pocoo.org/show/180461/, http://paste.pocoo.org/show/180462/, http://paste.pocoo.org/show/180463/
Rudemeister
ok
idletask
Hmm, things seem to accelerate now
krb_hnd
Is there any impact if I create new function index on table that has million of rows
idletask
Yes, there will be
Insert performance will be slower
krb_hnd
any table locking
I heard that it will lock table
idletask
No idea about that, tbh
I never create functional indexes
krb_hnd
can anyone here help me on this
idletask
The question would rather be: do you need that functional index?
krb_hnd
yes
idletask
Really? Why? How many queries will use it? Can these queries be rewritten _not_ to need it?
krb_hnd
our system captured unixdate
idletask
Yes, so what?
krb_hnd
and stored this date into one the table in our system
idletask
By unixdate, do you mean the number of seconds since epoch?
krb_hnd
when I try to do select statement, exp : select * from table_a where column_that_has unixdate = trunc(sysdate-1)
I need to convert those date to normal date
that is the main reason why I need functional index
idletask
Again, what is this unixdate?
Is this the number of seconds since epoch?
krb_hnd
yes
idletask
Well then no need to create a functional index at all
krb_hnd
but how
idletask
Oracle has functions to deal with that, which can turn Unix dates into Oracle dates _and_ vice versa
So, you should do "where thecolumn between <low value> and <high value>"
And put a simple index on the Unix date column
krb_hnd
let say I want to have sysdate-1
idletask
If you want the _result_ to be in an Oracle date column, just use the appropriate function to convert the column to it in the select part
krb_hnd
how can I get the low and high value
idletask
Well, "where thecolumn >= <expression for sysdate-1 at midnight> and thecolumn <= <expression for sysdate-1 at 23:59:59>"
krb_hnd
cpu cost will be higher
idletask
NEVER use trunc
No, quite the opposite
It will be much faster
Use trunc in a date expression, and it forbids the use of indexes on date columns!
krb_hnd
oo ok
idletask : this query is scheduled on 3 am
dialy basis
at 3 am , daily basis
idletask
Can you pastebin it, if possible?
krb_hnd
what is the url?
idletask
Well, pastebin.ca for instance
Hmm, I cannot use any filesystemio_options on btrfs
:/
krb_hnd
idletask : done
idletask
Give the URL in the titlebar, please
krb_hnd
I gave you only the sample of the query
http://pastebin.ca/1803705
unix_to_date in user defined functon
function
idletask
Is there an index on created_t?
krb_hnd
yes
idletask
So take advantage of it
krb_hnd
but it is a normal index
idletask
Yes, which is perfectly sufficient
krb_hnd
not a functional index
idletask
Your where clause should be "where created_t < <sysdate turned into seconds since epoch>"
This way, you don't have to created a functional index _at all_
krb_hnd
if I convert my sysdate
idletask
AND the function to calculate the seconds since epoch is only computed ONCE
Not once PER ROW
krb_hnd
it will convert on that specific date and time
idletask
Yes, which is why I told you NOT to use trunc
You should operate on intervals
OK, hold on, I'll look up the functions again to convert Oracle dates into Unix epochs and vice versa
http://blogs.sun.com/mock/entry/converting_oracle_dates_to_unix
But there is a more simple way than that
krb_hnd
yes
I already built this function
but got problem with the timestamp
idletask
But you did it the wrong wat
way
What you should do is convert Oracle dates to Unix dates, not the reverse
Since what is stored in the table is Unix dates
krb_hnd
how can I get all transactions start from midnight till 23:59
if I convert oracle dates to unix
and this query run every day at 3 am
idletask
Well, "where created_t >= <thedayat00:00> and created_t < <thenextdayat00:00>"
Oracle has all functions necessary to do that
krb_hnd
idletask : how
idletask
I don't know, but looking up in the doc will yield the solution, I'm pretty sure
krb_hnd
ok
will try your method
idletask
You won't even recognize your query after that
krb_hnd
thanks a million
idletask
It will run much faster
krb_hnd
I thought with indexing
my query will run super fast
idletask
Don't forcefeed indexing to match your queries, forcefeed your queries to match indexes
Here, you have an index on created_t which is ideal for a range scan
Use that
krb_hnd
oo ok
will try
idletask
And remember that more indexes, and _especially_ functional indexes, ruin insert performance
(and I don't even talk about triggers)
krb_hnd
is there any way to overcome that problem
idletask
The "more indexes == slow inserts"? No
krb_hnd
now I need to find a method to convert sysdate especially on the timestamp to 00:00 hour
I think may be INTERVAL can solved my problem
idletask
You will find it, I know these exist
Question: is there a query where I can see the whole values of sys_context('userenv', xxx)?
Rudemeister
/ignore krb_hnd
krb_hnd
thanks idletask
have a nice day
idletask
OK, there definitely is a problem with Oracle and btrfs
Database creation with ext4 as a filesystem is MUCH faster, and filesystemio_options do apply without a problem
(Action) really wonders why
It really shows, iowait time is negligible
And filesystemio_options is not even applicable with ext4!
Hmm
The problem is elsewhere, it seems
Oracle uses too old APIs, maybe
It certainly is since when I select either of these options, there's a syscall by Oracle by which Linux answers -EINVAL
« prev 1 2 next »