Thank you Stefan.
The problem was like these.
They are working ok a time, and then they call and say is slow, (that
custoemr had a virtual server)
1) we see when restarting the database it solved
2) we see after restarting several times the database , restarting doesn't
solve, so we restart the server, and it get fixed again.
3) the same problem to other customers with the same product.
One custoemr had a virtual sever, and that could be the reason, but the
other customers didn't so, that wasn't the reason.apparently was the
operating system (windows server 2008).
I was curious if some one had a similar problem.
The true is I don't have any idea what it was, and I didn't have time to
check in that time, and I'm not a real dba like you, I don't have the
expertise because most time I'm developing I'm dba when something bad
happens in the database :).
2015-10-22 12:53 GMT-04:00 Stefan Koehler <contact@xxxxxxxx>:
Hi Juan Carlos,
just based on your following description:
But what we recently discovered (thisnot the problem of the view) isthat the system required to restart periodically to avoid problems. It is
not
bad binding, I have no idea what it is, but trying, only for try, Iflushed shared_pool and cache and it worked ok, so we created a job every
our;
maybe in other systems is stupid, but in our system not because it issmall. We did only for a customer, but it was usefult to others too, and
they
are happy because a query of 30s started to take 2 minutes or more.
This sounds like some kind of (adaptive) features like cardinality
feedback (or statistics feedback how it is called nowadays) or temporary
table
issues. Have you already checked both? Do you really need to flush the
buffer cache as well?
However i (personally) would consider this procedure as a short-term
work-around only.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx> hat am 22. Oktober2015 um 17:38 geschrieben:
explain you a problem, because you enjoy them :).
Hello Jonathan, thank you for commenting my mail, and everyone :), I
are views, and views and views.
the problem is our system is small, but it is complex, because there
in example some views are a view joining two table, one table has thedata of the table, and the other has the historical data of the table we
moved to a read only tablespace, for backup (before rman).the view has the data, and a virtual column with the description,
There are views for translation, this means the table has the data, and
translated depending the language.other reports; and the concept is ok, is better for developement, there is
And last view created with joins and for reports, we use some times in
not unnecesary use of them.performance problems we had, increase the pga to a minimum to allow
I try to solve configuring the database properly, in example to solve
optimizer
to always have enough memory to choose the best join, and activate allin statistics_level. And it was ok, plus some indexes.
But what we recently discovered (thisnot the problem of the view) isthat the system required to restart periodically to avoid problems.
it is not bad binding, I have no idea what it is, but trying, only fortry, I flushed shared_pool and cache and it worked ok, so we created a job
every our; maybe in other systems is stupid, but in our system notbecause it is small.
We did only for a customer, but it was usefult to others too, and theyare happy because a query of 30s started to take 2 minutes or more.
and learn a littler more to understand where is the problem going.
I'm a developer-dba, I learn in the way I need,so I'm trying to digg
(standard one)
Thsi is the job so you can smile, a little in 1.2.0.3 windows patch 15
exec DBMS_SCHEDULER.drop_job ( job_name =>'job_Flush_especial_periodico');
exec DBMS_SCHEDULER.drop_program ( program_name =>'prg_Flush_especial_periodico');
BEGINshared_pool'';execute immediate ''alter system flush shared_pool'';execute
DBMS_SCHEDULER.create_program (
program_name => 'prg_Flush_especial_periodico',
program_type => 'PLSQL_BLOCK',
program_action => 'begin execute immediate ''alter system flush
immediate ''alter system flush shared_pool'';execute immediate ''altersystem flush buffer_cache'';execute immediate ''alter system flush
buffer_cache'';execute immediate ''alter system flush buffer_cache'';daz.db_err_graba_autonomous2(''1'',''JOB FLUSH'');end;',
enabled => TRUE,hasta ahora es .');
comments => 'Este es solo para casos especiales, el unico
DBMS_SCHEDULER.create_job (hasta ahora es puente.');
job_name => 'job_Flush_especial_periodico',
program_name => 'prg_Flush_especial_periodico',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY;INTERVAL=1;',
end_date => NULL,
enabled => TRUE,
comments => 'Este es solo para casos especiales, el unico
END;*.CONTROL_FILES='E:\ORAAPAN\CONTROLFILES\CTL_APAN01.CTL','E:\ORAAPAN\CONTROLFILES\CTL_APAN02.CTL','D:\ORAAPAN\CONTROLFILES\CTL_APAN01.CTL','D:\ORAAPAN\CONTROLFILES\CTL_APAN02.CTL'
/
Here is the init.ora if you areinterested.
*.AUDIT_FILE_DEST='D:\ORAAPAN\audit'
*.audit_sys_operations=TRUE
*.audit_trail='NONE'
*.compatible='11.2.0.3'
*.control_file_record_keep_time=180
*.DB_BLOCK_SIZE=8192
*.DB_CREATE_FILE_DEST='D:\ORAAPAN\datafiles'
*.DB_DOMAIN=''
*.db_keep_cache_size=40m
*.DB_NAME='APAN'
*.DB_RECOVERY_FILE_DEST_SIZE=1000000000000
*.db_recovery_file_dest='D:\APANFLASHBACK'
*.DB_RECYCLE_CACHE_SIZE=40m
*.db_securefile='PERMITTED'
*.DIAGNOSTIC_DEST='E:\ORAAPAN\DIAGNOSTIC'
*.DISPATCHERS='(PROTOCOL=TCP) (SERVICE=APANXDB)'
*.JOB_QUEUE_PROCESSES=10
*.license_max_users=500
*.log_archive_dest_1='LOCATION=D:\APANflashback\ARCH'
*.log_archive_dest_2='LOCATION=E:\APANARCHLOG'
*.max_dump_file_size='UNLIMITED'
*.memory_max_target=2306867200
*.memory_target=2306867200
*.OPEN_CURSORS=2000
*.open_links=10
*.optimizer_dynamic_sampling=4
*.pga_aggregate_target=1048576000
*.plsql_code_type='NATIVE'
*.plsql_optimize_level=3
*.PLSQL_WARNINGS='DISABLE:ALL'
*.processes=120
*.QUERY_REWRITE_ENBLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='TRUSTED'
*.READ_ONLY_OPEN_DELAYED=FALSE
*.recyclebin='OFF'
*.REMOTE_DEPENDENCIES_MODE='SIGNATURE'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.session_cached_cursors=4000
*.SESSION_MAX_OPEN_FILES=100
*.sql92_security=TRUE
*.statistics_level='ALL'
*.timed_statistics=TRUE
*.undo_retention=900
*.UNDO_TABLESPACE='TBL_UNDO'
*.UTL_FILE_DIR='*'
