logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 11 / 1
misty
ping hali
usn
ping?
misty
yes :)
it's probably the middle of the night in the UK anyway
usn
it's 22:12 there
misty
right, hopefully he is sleeping
I'm in Australia, we're all upside down
usn
hehe
checkpoint
Good night, when I use the command "alter system switch log file" I see the Oracle alert the following msg => Private strand flush not complete / Thread 1 can not allocate new log, sequence 26 ... ?
Anybody know the solution to this problem?
RJarett
are you writing both log members to the same array?
if you have alot of redo group members, and they all hit the same disk, and you dont have many CPU but log parallelism is high
youll hit the error
but its more a warning
read note 372557.1 on metalink
cause : The message means that we haven't completed writing all the redo information to the log when we are trying to switch. It is similar in nature to a "checkpoint not complete" except that is only involves the redo being written to the log. The log switch can not occur until all of the redo has been written.
A "strand" is new terminology for 10g and it deals with latches for redo .
Strands are a mechanism to allow multiple allocation latches for processes to write redo more efficiently in the redo buffer and is related to the log_parallelism parameter present in 9i.
The concept of a strand is to ensure that the redo generation rate for an instance is optimal and that when there is some kind of redo contention then the number of strands is dynamically adjusted to compensate.
The initial allocation for the number of strands depends on the number of CPU's and is started with 2 strands with one strand for active redo generation.
You can disregard these messages as normal messages.
When you switch logs all private strands have to be flushed to the current log before the switch is allowed to proceed.
checkpoint
RJarett, I even had more than 1 member in the group ... put I removed to try to resolve the problem
RJarett, So I can ignore these messages?
         

RJarett
i asked, do you have more than 1 member, and they are both on the same mountpoint
checkpoint
RJarett, I had more than 1 member in different partitions I removed it to try to solve the problem and even then the error kept the error in the alert
RJarett
how many cpu?
you should always have 2+ members per group
but you can ignore this one
checkpoint
RJarett, yes I had two members per group and error was the same ... the strange thing is that I have other databases with the same version and do not have this message
RJarett
its more of a system load issue
checkpoint
RJarett, it is a new database ... not have much load
zambaboo
hi all
im learning rman, running into a situation where it is asking for an archivelog that wont be there till tmrw. what am i doing wrong? 10g SE
RJarett
recover until time. recover until cancel recover until scn
how does it know what the last is if your recovery is being done from a controlfile after the point of the last archive log?
zambaboo
this is during backup, not recovery, sorry i should have mentioned that
RJarett
what are you talking about
zambaboo
i issues a backup command, backup database plus archivelog delete input;
i get this error: ORA-19625: error identifying file /opt/oracle/flash_recovery_area/ORCL/archivelog/2009_12_11/o1_mf_1_21859_5l64lpxg_.arc
RJarett
why is your date off so much?
zambaboo
oh shucks. ok i see what happened
yeah that makes sense
RJarett
you need to crosscheck
zambaboo
yessir that i do
RJarett
crosscheck archivelog all;
crosscheck copy;
crosscheck backup;
zambaboo
looks better now, thank you
didnt notice it was looking for dec 11.. since today is the 10th, thought is looking for tmrw :)
sileni
hey guys
i'm using oracle9i and when i did insert i did something like values(blahblah,null);
and then it inserted that row
but when i go to query like select * from orders where shipped = null;
it doesn't show.. does anyone know why?
RJarett
null doesnt equal null
sileni
RJarett, how do i insert the null that i want in my situation
         

RJarett
you already inserted it
sileni
oh
how do i query
RJarett
you just need to think about selecting it different
sileni
hmm
RJarett
shipped is null
sileni
yea
thank you
idle_task
Hello
I know that it's possible to inspect the contents of a command in a command rule in dbvault, using the instr function
But what are the parameters?*
Reign1
hi, wtf is wrong, im on 11r2 http://pastebin.com/m7ad96ff2
anyone
?
whats wrong with DEFAULT COMPRESS FOR OLTP on 11r2?
idle_task
No idea
I have another problem :p
Reign1
as i get compress type not specified
nope
do Oracle support 10g/11g in vmware yet?
idle_task
Theoretically, no
But we do run 10g in vmware and they don't seem to really care
Reign1
does anyone used compression on tablespaces on 11r2?
MaL0
hi
does oracle support an upgrade of jdbc/oci/thin drivers on oracle application server ?
sidh
Greetings Gentlemen
on a new customer running 11g , i found this query http://pastie.org/818756 is strangely taking hours. I remember we had a slowdown problem too with this query on 10g that was solved with an "alter session _btree_bitmap_plans=false" before running that query, but iirc on 11G btree_bitmap_plans is set to false by default (i set it to false, and nothing change though). So would you have an idea why this query that looks "simple" have such a slowdown impact ?
linkd
Greetings Gentleman
idle_task
Great
Scripts generated by dbca don't activate dbvault :(
sidh
do you think using a pl/sql procedure to do the same as the above query could do the trick ?
idle_task
I'm not surprised, you do a full select statement for each row you update
This is no "strange" at all
Are stats up to date?
sidh
idle_task: what do you mean by stats up to date ?
sorry i'm learning with the dba handbooks, if you could explain i can check in the book how to answer you
idle_task
I mean the table stats
I don't remember offhand the query to use to inspect them, though
It's probably in dba_stats or something
sidh
dbms_stats.gather_schema_stats
?
idle_task: as it is a migration database and i'm alone on it , i shutdown the db and startup it , to see if stats have been updated
Reign1
sooo, does anyone did compressed tablespace on 11r2? as i get error that i dont specify compression type while: DEFAULT COMPRESS FOR OLTP
usn_work
good afternoon
idle_task
Does anyone apart from me here use dbvault at all? :p
usn_work
at least, they don't talk about it ;)
maybe it's what "vault" means ;)
idle_task
Yeah :/
Well, anyway
I have found a solution to my problem
Reign1
when it was on, i used it as much as: make -f ins_rdbms.mk dv_off ioracle
idle_task
Now I need to learn more about string manipulation functions :p
Reign1
usn_work: maybe ull know: i get error that i dont specify compression type while: DEFAULT COMPRESS FOR OLTP, its on 11r2, while creating tablespace
idle_task
Reign1: not very smart if I'm brutally honest
But you have to understand what dbvault is all about, and the doc doesn't explain it really well
Wolfcastle
hello
I can't seem to find a 64bit version of oracle express 10g
is it not available?
sidh
ok i'm still searching for my slowdown query problem and as i'm migrating a sqlserver 2000 DB to oracle 11G, i ran the equivalent SQLserver 2000 query on the original db, the results a totally amazing : http://pastie.org/819936
Wolfcastle
for gnu/linux that is
sidh
of course these are the same table on both DB
usn_work
sidh, can you provide the query plan (execution plan) for the update on oracle?
idle_task
sidh: this is not the same query _at all_
Maybe the result is the same, but the query itself is _very_ different
teite
can one do a loop inside a sql statement?
like get all tables from a single tablespace and sum the number of rows from these tables?
usn_work
teite, combine group by and sum
teite
hmm need a hint :p
sidh
usn_work: i'm googling how to show you that query plan
usn_work
this was the hint. Won't provide a query
idle_task
teite: you'll need to query the dictionary for that, no need for a loop
usn_work
sidh, SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&&SQL_ID',0,'COST,IOSTATS,LAST'));
teite
idle_task: yeah all_tables
usn_work
use the sql_id from v$sql to feed the variable
idle_task
teite: such as (untested) select sum(num_rows) from dba_tables where tablespace_name = 'thetablespacenamehere';
teite
idle_task: oh num_rows
lol
i tried to do it via count(*)
idle_task
Or select table, num_rows from dba_tables where tablespace_name = 'thetablespacename';
This is more like what you want, really
teite
yeah thats easy
didnt thought about the num_rows field in the all/dba_tables view
thx guys
idle_task
np
Hmm, strange that in dba_tables, there are tables with a num_rows set to null :/
sidh
usn_work: what is SQL_ID
teite
idle_task: yeah num_rows is completely empty in my oracle 9er db ;)
so back to plan 1
maybe with plsql
maybe it analyze table fills the data in the dictionary?
AcridSaint
Hey folks - I'm running into a problem with one of my databases. Using utl_file.open to create files, the database does not appear to be getting the umask setting that I changed.
I set umask in my login script, shut the database and reopened it. When a job runs it does not get the new umask, but if I run the procedure from sqlplus on the db it gets the correct umask.
Anyone have any suggestions on where to look?
Never mind, I think I need to restart the listener as well.
usn_work
teite, idle_task - num_rows isn't correct, it depends on statistics
idle_task
usn_work: ah :/
usn_work
sidh, did you look into v$sql view and its docs?
idle_task
(Action) HATES bugs in dbca script creation
sidh
usn_work: that's ok i've just found how to retrive sql_id, that's amazing how i can learn a lot in trying to answer one of your question
usn_work
For sidh and all information seekers: http://www.oracle.com/pls/db102/homepage is one of your very best friends ion the world of oracle. It's available for all modern DB versions
sidh
usn_work: sorry for the delay i had vpn connexion problem
so the result you ask is : www.pastie.org/819994
usn_work
rofl ;)
please "set serveroutput off;" first, sorry for the confusion
the plan you provided is from plan creation itself.
sidh
usn_work: setting serveroutput off gives the same result
usn_work
The query with this sql_id isn't the update you showed us initially
What tool are you using to show us the plan?
sidh
first it was the sqldeveloper, the i retry from the sqlplus
usn_work
Preferably, you should use SQL*PLUS in the nude or SQL Developer from Oracle. Toad, PL/SQLDeveloper etc. are wrapping their statements in a way that prevents us to access the current cursor in a sane way
Ok. But the query with this sql_id isn't the update you showed us initially
sidh
?
i use the same query as here www.pastie.org/819936
LFC_fan
Will setting up Oracle Developer Suite would be simple enough in Oracle Linux Enterprise Desktop?
Just couldn't figure it out for Debian/Ubuntu
usn_work
sidh, SQL_ID 2fjdnx0ma0h5c is "select sql_id, address, executions, buffer_gets, sql_text from
v$sqlarea where sql_text like '%UPDATE RFICHES a SET F_1=(SELECT
MIN(VALEUR) FROM FICHES b WHERE (ITEM = 1) and
(a.F_DATEITEM=b.DATEITEM) and (a.F_NIDENT=b.NIDENT))%' order by
buffer_gets desc"
please add the commands issued to the pastebin entries, it helps a lot to understand what you did
especially the way how you did get the SQL_ID seems to have an issue
sidh
www.pastie.org/820028 <= the query I used to get the sql_id
usn_work
what was the result? Hey, tey to unerstand my situation - I can't see what you do, and guesswork is sh*t here
Always paste query - result, command - result together
idle_task
OK, good, now I know something else
SYS hasn't had its create user privilege revoked, create user is protected by a command rule
sidh
sorry usn_work for not giving you the information you ask , i set the result on the same url
usn_work
hehe, you found your own query
frop the first "%" in your where condition
drop
2fjdnx0ma0h5c ist the select on v$sqlarea looking for the update, not the update itself
sidh
on the same url i add the output without the ending order bybuffer_gets desc
usn_work
that's not the point
drop the first "%" in your where condition
and use the sql_id you get
sidh
i have no result if i drop the % in '%UPDATE ...
usn_work
true
But you NEED a statement that starts with the word UPDATE, not with SELECT, because an UPDATE is slow, not a SELECT, isn't it?
So try the following:
Start the update that's slow, and let it run
then start the query on v$sql again, without the "%"
(in another session)
idle_task
Hmm
« prev 1 2 next »