Re: solved our "problem with views" flushing shared_pool and cache every hour, not binding problem

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Thu, 22 Oct 2015 15:55:30 -0400
Message-ID: <CAGYrQyvqH7aqJtvDzENVqhrocx+Qo=TJjen3zTehoSuNjabGVQ_at_mail.gmail.com>

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_at_soocs.de>:

> Hi Juan Carlos,
> just based on your following description:
>
> > But what we recently discovered (thisnot the problem of the view) is
> that 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, 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 not because it is
> small. 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: _at_OracleSK
>
> > Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com> hat am 22. Oktober
> 2015 um 17:38 geschrieben:
> >
> > Hello Jonathan, thank you for commenting my mail, and everyone :), I
> explain you a problem, because you enjoy them :).
> >
> >
> > the problem is our system is small, but it is complex, because there
> are views, and views and views.
> > in example some views are a view joining two table, one table has the
> data of the table, and the other has the historical data of the table we
> > moved to a read only tablespace, for backup (before rman).
> > There are views for translation, this means the table has the data, and
> the view has the data, and a virtual column with the description,
> > translated depending the language.
> > And last view created with joins and for reports, we use some times in
> other reports; and the concept is ok, is better for developement, there is
> > not unnecesary use of them.
> >
> > I try to solve configuring the database properly, in example to solve
> performance problems we had, increase the pga to a minimum to allow
> optimizer
> > to always have enough memory to choose the best join, and activate all
> in statistics_level. And it was ok, plus some indexes.
> > But what we recently discovered (thisnot the problem of the view) is
> that 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, 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 not
> because it is small.
> > 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.
> >
> > I'm a developer-dba, I learn in the way I need,so I'm trying to digg
> and learn a littler more to understand where is the problem going.
> >
> > Thsi is the job so you can smile, a little in 1.2.0.3 windows patch 15
> (standard one)
> > exec DBMS_SCHEDULER.drop_job ( job_name =>
> 'job_Flush_especial_periodico');
> > exec DBMS_SCHEDULER.drop_program ( program_name =>
> 'prg_Flush_especial_periodico');
> > BEGIN
> > DBMS_SCHEDULER.create_program (
> > program_name => 'prg_Flush_especial_periodico',
> > program_type => 'PLSQL_BLOCK',
> > program_action => 'begin execute immediate ''alter system flush
> shared_pool'';execute immediate ''alter system flush shared_pool'';execute
> > immediate ''alter system flush shared_pool'';execute immediate ''alter
> system 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,
> > comments => 'Este es solo para casos especiales, el unico
> hasta ahora es .');
> > DBMS_SCHEDULER.create_job (
> > 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
> hasta ahora es puente.');
> > END;
> > /
> >
> >
> > 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
> >
> >
> *.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'
> > *.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='*'
> >
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 22 2015 - 21:55:30 CEST