logs archiveIRC Archive / Freenode / #oracle / 2010 / April / 13 / 1
galeras
Weird: "update A set B = C where rowid = 'XX'; shows an execution plan using INDEX FULL SCAN, Any idea?
rizzo
are you expecting FTS?
I'm not a fan of using rowid in predicates
for no valid reason that I know of
davo
hi all. i'm trying to install Oracle 10g release 2 on linux, which is failing at Oracle Net Configuration Assistant. Is there someone that can help me resolve the details of the errors?
details of the installation errors: http://pastebin.com/VSFy0qKp
lostabeer
is oracle express come with management studio like tools like the one's from microsoft sql server?
Junior
yello ;)
Dormir
sup Junior
Junior
just woked up Dormir
you?
Dormir
relaxing before bed
Junior
:))
         

TheBonsai
the miracles of different timezones...
(of having*)
admin0
hi . i need to paste a explain plan result and get some insights .. what field do i nee d to paste ? select * from PLAN_TABLE ?
Hi, this is an output for oracle EXPLAIN_PLAN : http://shashi.org/table_export.htm .. can you please suggest if it looks OK or need optimizations ?
KeiKurono
morning
usn_work
good morning
admin0
hey guys .. can help me in a query ?
usn_work
nope, because we don't know it
:P
admin0
true :D
usn_work
Please don't ask to ask
admin0
select count(*), sum(vamt), sum(call_dur)
from summary where yyyymmdd between '15-Mar-10' and '31-Mar-10' and vendid=2201 and vprefix like '60%' ;
that is the query
explain plan is at http://shashi.org/table_export.htm
and it takes more than 20 mins to execute and give results :(
yymmdd, vendid and vprefix are all indexed
i see 97% -99% wa time in top when i give this query .. the server is on raid10 on 4 scsi disks, 15k rpm.. something strange is this query used to take less than a min in the old 10gR2 system .. we did an exp of the table here to 11gR2 and suddenly start to face this .
usn_work
admin0
when i gvei this query, on enterprize manager, the graph on Active Sessions Waiting: User I/O is maxed on db file sequential read, and also on db file parallel read
ok
checking
usn_work
admin0, please don't forget the hint in the query
we need the runtime information
admin0
following the url u gave, can i use the second example and try to use the curor cache parameters
usn_work
yes
admin0
i know the sql id.. how do i get the child number ?
usn_work
EM should provide the SQL_ID, and if you are on 10g, the child_number is 0
admin0
i am on 11g
usn_work
ok, one moment
admin0
i assumed 0 in 11g also
i got a result
let me try to paste
usn_work
stop
define SQL_ID='cak7wpt15yn8m'
select child_number, first_load_time, last_load_time, executions, buffer_gets, round(buffer_gets/executions,1) as bg_per_exec, is_bind_sensitive, is_bind_aware, is_shareable
from v$sql
where sql_id = '&&SQL_ID'
--and IS_SHAREABLE='Y'
order by child_number
;
you need all "IS_SHAREABLE=Y"
they are your active, adapted cursors
         

admin0
http://shashi.org/table_export.txt -- this is the result based on the website you shown
i am generating based on what you gave me
usn_work
a shame that parameter 'statistics_level' is not set to 'ALL', that would be real valuable information
admin0
based on your second query, the output is:
"CHILD_NUMBER""FIRST_LOAD_TIME""LAST_LOAD_TIME""EXECUTIONS""BUFFER_GETS""BG_PER_EXEC""IS_BIND_SENSITIVE""IS_BIND_AWARE""IS_SHAREABLE"
0"2010-04-13/00:48:33""2010-04-13/02:09:35"11356113561"N""N""Y"
1"2010-04-13/00:48:33""2010-04-13/01:16:48"3940640313546.7"N""N""N"
usn_work
so wea rea tlaking about child number 0
admin0
i think.
http://shashi.org/table_export.txt -- how to analyze this and find out whats wrong ?
usn_work
you don't us ebinds here - can't you process the query plan with the first option in the URL?
admin0
let me try
usn_work
with /*GATHER_PLAN_STATISTICS */
admin0
the summary table has 55000288 rows, if that is of any importance
usn_work
We can see how good the row count estimate is
it will take your 20minutes to complete, but it's worth it
admin0
how do I give the gather_plan_statistics ?
usn_work
please have a look into the code example on the URL
admin0
select count(*), sum(vamt), sum(call_dur)
from summary where yyyymmdd between :a and :b and vendid in (':x') and vprefix like ':y%' ; -- does this look OK ?
User has no SELECT privilege on V$SESSION :D
let me add priv
usn_work
nope, you forgot the hint
select /*+ HINT */ ...
The example looks like: SELECT /*+ gather_plan_statistics */ *
from EMP
where NAME = :b
and SALARY < :a
;
is it THAT difficult? ;)
admin0
i figured that out:)
i am giving privs to the user for statistics, plan-statistics
this is my statement now: select /*+ gather_plan_statistics */ * count(*), sum(vamt), sum(call_dur) from summary where yyyymmdd between :a and :b and vendid in (':x') and vprefix like ':y%' ;
usn_work
Does the initial problem statement use binds?
admin0
it does
hmm.. getting error on the syntax
usn_work
ok, well then
admin0
select + gather_plan_statistics, count(*), does not work .. nor does select /*+ gather_plan_statistics */ * count(*) or select /*+ gather_plan_statistics */ *, count(*)
i am not sure exactly how to put that gather_plan_statistics in the query
usn_work
select /*+ gather_plan_statistics */ count(*), sum(vamt)
skip the "all fileds" asterisk :)
admin0
SQL Error: ORA-00904: "GATHER_PLAN_STATISTICS": invalid identifier .
select /*+ gather_plan_statistics */ count(*), sum(vamt), sum(call_dur)
from summary where yyyymmdd between :a and :b and vendid in (':x') and vprefix like ':y%' group by + gather_plan_statistics ;
that was my operation
sorry
let me try again
i got stuck on User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL , however I have already did grant select on v_$sql_plan_statistics_all to user
hmm.. i am not sure if I have that V$SQL_PLAN_STATISTICS_ALL .. error i get tis ORA-02030: can only select from fixed tables/views
usn_work
grant SELECT_CATALOG_ROLE to &&1;
GRANT "CONNECT" TO &&1;
GRANT "RESOURCE" TO &&1 ;
should do
you'll have to relogin
admin0
i relogin.. no errros. here is the query and its output: http://shashi.org/ora.txt
usn_work
uh, what SQL tool did you use?
can you try with sqlplus?
admin0
sql developer
usn_work
Version?
"This script was designed to run in Oracle SQL Developer very well, but it seems that version 2.1 has trouble with the LAST cursor, due to some changed output."
admin0
Version 2.1.1.64
usn_work
can you try with sqlplus?
admin0
ok
updated results at: http://shashi.org/ora.txt
i think the binds are not being passed correctly
can i try direct query once?
usn_work
mh, you can do whatever you want ;) But it would be nice to have the lines not broken in the plan
admin0
i am sorry, but i don't think i understand what you mean by lines not broken in the plan mean
i am now running without bind variables, but exact values
now its taking a long time .i guess its worth the wait
i get it what you mean my lines not broekn in the plan :)
let me paste the result, by using direct query
Hi
here is the query and result: http://shashi.org/ora2.txt
deebo
stored procedures can be run in a transaction? (to reverse all changes they make if needed)
admin0
i think yes
hali
they automatically are
the procedure themselves that is
admin0
hali, usn_work was helping me, but i guess he went out.. can u help on a issue i have .. very slow query .. he asked me to run a report and its available at http://shashi.org/ora2.txt
hali
index on vendid?
admin0
yes, index on all
hali
yes
admin0
vendid, yymmdd, vendprefix
hali
the range scan is quite expensive
how large is the table?
admin0
55000288 rows
hali
hm, not that bad
admin0
8cpu, 8gb ram, raid 10 disk array of 4 300k rpm disks
hali
8-cores?
admin0
issue is it used to work in less than 1 min on our 10gR2 system (similar hardware) .. but i moved data to fresh 11gR2 release using exp
yeah
hali
and statistics are up to date?
recalculated on 11g
admin0
that i am not sure doesn't it restore at the time of imp ?
hali
it does, or at least tries
but may not be accurate between versions
admin0
so need to recalculate all indexes ?
hali
and tables
just run dbms_stats.gather_schema_stats on it
admin0
coudl it be due to this that even small operation and i see it use 97% etc of cpu wait on top
hali
i would guess it's the sort operation
if you see IO load the tablescan
admin0
i am not sure exactly hwo to run the dbms_stats.gather_schema_stats
EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE); -- user and the stats I want to run r
hali
exec dbms_stats.gather_schema_stats('USER_NAME', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto');
yes
admin0
is there a easier way to fix the indexes, or shall I drop and re-create all ?
hali
no
dropping and recreating the indexes is a waste of time
oracle hardly ever needs that
admin0
so just a rebuild is sufficient
hali
no
why?
dont rebuild / drop /recreate anything
just run the stats procedure
admin0
its been running since 5-6 mis now
hali
yep
may take a while
admin0
though i ran "exec dbms_stats.gather_schema_stats('dbusernanme', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto'); " - i am not sure exactly what this will do .. this will report me the stattistics of what is broken etc, whcih i have to fix, or just fix all automaticlaly ?
sorry .. http://ss64.com/orap/DBMS_STATS.html -- it will collect the stats
now i know
balek
hi i have a problem when i start dbca on the node1 i can't create rac oracle i can see this message log erro on the node1 : Host.checkOPS:2073] cluster existence:false on the node2 : [Host.checkOPS:2073] cluster existence:true if you have any idea thanks
vmnew
Oracle 10G, could it be that on every file created by oracle, it throws something in my /tmp directory? I got tons of file there looking like dac31af9149e547785fe94515a07bd1b and their content is my regular html builds from Oracle
maybe cached objects or something?
balek
i search what is checkOPS
hali
df
admin0: did it make any difference?
admin0
Hi .. it finished running like 30 seconds ago
Elapsed: 01:12:49.74
now i am checking to find out
i think its still the same as before ..
vmnew
hali: hey! :)
balek
nobody know what is checkOPS ?
admin0
thanks all .. going out
KeiKurono
what the fu*k.
dsdel
benc: you are not really irc'ing from an administrative account - aren't you? :D
usn_work
(Action) is now know as SYS :P
hali
7nick tiger
Davedan
dsdel: what do you mean by administrative account?
usn_work
* benc (~Administr@kaplun001 ....
dsdel
7nick scott
balek
nobody know what is checkOPS ?
please
« prev 1 2 next »