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='*'
