SQL & PL/SQL :: What Is Cycle And Cache In Sequence
Oct 11, 2011What is cache/no cache & cycle/no cycle in Sequence?what is the use?
View 3 Replies
ADVERTISEMENT
How To Set Sequence Cache
Oct 10, 2012Are there any recommendations or good practices to set sequence CACHE parameter (for example one caching per hour, day etc)?
View 4 Replies View RelatedReal Application Clusters :: Difference Cache Fusion and Cache Coherency
Jun 20, 2013What is the difference between cache fusion and Cache Coherency. Both are same or different functionality.
View 1 Replies View RelatedSQL & PL/SQL :: Call Procedures In Cycle?
Jul 21, 2011I've got some quantity of procedures in package
procedure1
procedure2
...
procedureN
I should call them in cycle instead of typing each of them manually. I know it's a bad idea to do something like this "dynamically".
SQL & PL/SQL :: Execute Procedure In Cycle
Oct 27, 2011My need is to execute procedure in cycle. I can run
EXEC zoo.pkg_z184.rep184_fill( date '2011-09-28' );
EXEC zoo.pkg_z184.rep184_fill( date '2011-09-29' );
But I'd like to do it in cycle when the period is much bigger. Here is my try to do it:
begin
for l_row in (
select date '2011-09-27' as arcdate from dual union all
select date '2011-09-28' as arcdate from dual union all
select date '2011-09-29' as arcdate from dual union all
select date '2011-09-30' as arcdate from dual
)
loop
EXEC zoo.pkg_z184.rep184_fill( l_row.arcdate );
end loop;
end;
It returns error:
ORA-06550: line 9, column 6:
PLS-00103: Encountered the symbol "ZOO" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "ZOO" to continue.
TimesTen In-Memory :: AWT Cache Group With Cache Awt Parallelism
Jul 10, 2012I have some question.
TTversion : TimesTen Release 11.2.2.3.0 (64 bit Linux/x86_64) (tt112230:53376) 2012-05-24T09:20:08Z
We are testing a AWT cache group ( with CacheAwtParallelism=4 ).
Application(1 process) to the DML generates to TimesTen(DSN=TEST).
At this point, Are delivered to the 4 parallel DML?
[TEST]
Driver=/home/TimesTen/tt112230/lib/libtten.so
DataStore=/home/TimesTen/DataStore/TEST/test
PermSize=1024
TempSize=512
PLSQL=1
[code].......
Index In Cache
Apr 21, 2013i have a application which uses 32 tables for retrieval in this 4 tables are important and have a size more than 100 mb can i move the index of these 4 tables cache memory to improve the applications retrieval performance if i done so ,then that will affect any other applications performance
View 1 Replies View RelatedCache Buffers Chains In RAC
Jun 1, 2012I have a query - whch used to be run in 30 minutes and now hangs on for hours and not finishing. The wait i see is
wait event latch: cache buffers chains.
This is a 11.1.0.7 2 Node RAC environment runing on HP-UX Itanium.
Procedure To Activate Cache
Aug 16, 2010i m working on oracle 10g using db_block_buffers. But i m not able to get information from database cache advice. Is there any method or procedure to activate cache advice despite of db_block_buffers use?
View 2 Replies View RelatedData Buffer Cache
Feb 22, 2013how can we check the size of data buffer cache.
View 7 Replies View RelatedBuffer Cache Hit Ratio For A Database
May 20, 2013what will be best buffer cache hit ratio for a database for good performance
note : in general
Database Block And Cache Size
Oct 5, 2010In Oracle 9i
I know that db_block_size defines the standard/default block size for my datafiles.
I know db_cache_size defines the size of the my default database buffer of the size of my standard/default block size.
db_block_size = 8192 (8k)
db_cache_size = 200Mo of 8k block
Is there any needs for the other buffer cache
db_16k_cache_size
db_4k_cache_size
db_32k_cache_size
and so on?
If I have NO datafiles other than of the default block size, would I need to define a size for those other buffer pool? Is there any process that would benifit of these pools?
Query Parsing And Buffer Cache?
Apr 18, 2012I want to know what exact process happens in oracle architecture when an update query is fired.
View 1 Replies View RelatedMetric Row Cache Hit Ratio In V$SYSMETRIC_HISTORY?
Mar 16, 2013understand the metric Row Cache Hit Ratio in V$SYSMETRIC_HISTORY? Is it the dictionary cache hit ratio?
View 1 Replies View RelatedLibrary Cache Locks By Dynamic DDL
Jun 30, 2011At a customers site I see lot of long lasting library cache locks during a complex ETL run. Several Sessions run in parallel and create Database tables with dynamic sql ( CREATE TABLE AS SELECT ....) .
Sometimes these procedures wait for each other with wait event 'Library Cache' . I presume that this is a side effect of the dynamic DDL in the stored procedures. Is that possible even when the Procs create different Tables but reference the same tables in their SELECT clauses ?
I presume that this is plausible but I need some arguments to convince my colleagues.
Smart Flash Cache And Redo With SSD?
Oct 23, 201211.2.0.2.0
OEL.
Design stage of New SAN. I can put a lot of the database, including redo on SSD.
1. Hardware sub tiering enabled. Has anyone ever gauged performance with enabling 11g Smart flash cache using the SSD as the flash device even with the hardware subtiering on? ie, if the hardware subtiering is moving hotblocks to most performant disk, is there much significant benefit having the flash cache on. Im guessing yes as the flash cache is working at the SGA level.
2. To redo or not to redo on SSD. Many notes say not to so as to avoid log file sync waits. Is there a way to keep it on the SSD and avoid the waits? Is there any risk of enhanced degradation of the disks over time with having so much sequential writes from the redo if I leave it on there?
How to Minimise Waits for 'Log File Sync' [ID 857576.1] (discusses keeping redo off ssd but only generally)
[URL]
Performance Tuning :: DB Cache Advice 10g?
May 16, 2013Is db_cache_advice = ON still necessary when sga_target is set?
View 2 Replies View RelatedApplication Express :: Cache In 4.2 - Not Working?
Oct 2, 2013I have an application built in Apex 4.2It's been running fine for several weeksI've not made any changes to the codeSuddenly it developed what seems to be a cache problem.If I enter a search criteria; it displays results. I enter a different search criteria and it brings back the same results as the first criteria.I enter a search criteria in a different field and I still get the results from the first searchI go from Internet Explorer to Mozilla.
DIfferent search criteria, but same issue. Google chrome - same issueThe obvious answer is to clear the cache.I have a process set up that is clear Cache for Items , on submit after computations and validations and then I list all the items individually.This process has been there all along.
I never had this problem before.If I log out and come back in, same issue. What ever I enter as the first search criteria is the results no matter how I search
Script For Monitoring Database Buffer Cache
Mar 26, 2012give a script to find how much my db buffe cache and redo log buffer cache is used and how much is free.
View 1 Replies View RelatedBuffer Cache Hit Ratio Is Low In Oracle 9i After Maintenance
Mar 3, 2011Database : 9.2.0.7
Os : windows 2003 sevrer standdard edition
RAM 4 Gigs
The buffer cache hit ratio in this server is around 83%, where it normaly was around 98% before i did some maintenance activities.
I have done some maintenance activities in January on this database.
Maintenance activties includes below steps
1.In production i have deleted old data in the production tables
2.Reorganized tablespaces,tables
3.Rebuild indexes for those tables.
4. At last collected statistics for those tables.
Now after this activity the buffer cache hit ratio is very low.
Server Administration :: Move Partition To Cache
Feb 28, 2012How to move a partition of a table to db_keep_cache ?
View 8 Replies View RelatedPerformance Tuning :: Increasing Buffer Cache Value
Nov 30, 2012Considering the below factors, I am planning to increase the buffer cache value from 256Mb to 512Mb.
1. Buffer cache hit ratio value is around 35% even in the normal period.
2. free buffer requested value is below during peak & normal hours below.
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
free buffer requested 54,694,995 15,226.9 2,523.7
free buffer requested 23,412,674 6,501.7 2,585.9
3. most of the top 5 physical reads & logical reads queries are well tuned and some of queries are doing FTS on small tables (table count min 1500 max 35000). SO indexing option is not required for these queires. But these queries getting executed frequently.
SQL> show sga
Total System Global Area 2148534928 bytes
Fixed Size 731792 bytes
Variable Size 1879048192 bytes
Database Buffers 268435456 bytes
Redo Buffers 319488 bytes
5.top 5 waitevents during db slow performance & high cpu utilization (>80%) issue.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 1,848,898 153,793 52.00
buffer busy waits 395,280 87,201 29.49
db file scattered read 3,488,648 34,199 11.56
enqueue 4,052 10,897 3.68
CPU time 5,567 1.88
6. Top 5 waitvents during normal activities and CPU utilization is around 40%.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,860 45.32
db file scattered read 1,133,669 985 23.99
imm op 776 605 14.73
sbtinfo2 208 139 3.40
sbtbackup 2 123 3.00
Performance Tuning :: Flushing Buffer Cache
Sep 27, 20101) Is shutting down the DB flush all the data buffers, from the buffer Cache?
2) In any oracle version, do we have any way to flush only the buffer Cache.
Server Administration :: DELETE And Buffer Cache?
Aug 19, 20101)If i issue a DELETE statement to delete a row, will this statement drag any data from the datafile to database buffer? How is the change made by a DELETE statement recorded in buffer cache? How is this change then applied to the data in datafiles after commit?
View 7 Replies View RelatedServer Administration :: How To Know Which Objects Used Keep Buffer Cache
Mar 28, 2011How can i know which objects used keep buffer cache?
View 5 Replies View RelatedRAC & Failsafe :: Global Cache Average CR Get Time?
Mar 4, 2012from last two weeks we are receiving rhe below database alert on our second NODE (RAC ENV).
Global cache Average CR get time " is at .61789
Threshold is set on >.5 (M.Second).
me to figure this out because i really dont know the nature of this alert. what value i can set for the warning threshold.
TimesTen In-Memory :: How To Create DB Cache Groups
Jul 22, 2013Is it possible to use TimesTen to create cache groups that have different table structures from original Oracle tables based on relational conditions?
View 3 Replies View RelatedPerformance Tuning :: Keep Pool In Buffer Cache
Jan 15, 2011I have some confusion about Keep Pool in Buffer Cache.
1. What is the reasoning for placing a table in the KEEP buffer pool because if it is frequently accessed, it will be around when needed (ie if it is constantly being accessed it will not age out) .
2. Would the table be still in the Default Pool if the Keep Pool is not sized and the command is being issued alter TABLE SCOTT.EMP storage (buffer_pool keep) ?
3. If the database is restarted will the table be wiped out of the Keep Pool and again be pinned to the Keep Pool ?
Application Express :: Checkbox Not Clear Cache
Oct 18, 2013i have created form with table page to enter hotel details there is on 6 check box which are not database column .i have create these check box to keep days details when hotel give service .so i have use
LOV FORP1_MON STATIC2:;1 MONDAYP1_TUE STATIC2:;2 TUESDAYP1_WED STATIC2:;3 WEDNESDAYP1_THR STATIC2:;4 THURSDAYP1_FRI STATIC2:;5 FRIDAYP1_SAT STATIC2:;6 SATURDAYP1_SUN STATIC2:;7 SUNDAY
i have one more column operating days where i have use computaation to keep operating days.
:P1_MON||:P1_TUS||:P1_WED||:P1_THR||:P1_FRI||:P1_SAT||:P1_SUN
these 6 check box are used Source used :Only when curren value in session state is nullSource Type : Static Assignment (Value equals source attibute) Problem are when i create new record then these 6 check box are not clear cache and when i update any record value are not clear cache . i need when i create or update recor then all 6 check box should be null after keep recor.
Simulate Latch : Cache Buffer Chains
Apr 25, 2013I want to simulate latch : cache buffer chains wait event due to use of nested loop join for lookup tables
This is what a tried :
-- create parent / child tables
SQL>drop table emp1 purge;
drop table dept1 purge;
create table dept1 (dept_id number primary key,
dept_name char(30));
[Code]....
I traced many queries like the one given below (dept_id between 1 and n where n varied from 10 to 1000) but they always result in hash join
1* select d.dept_name, e.id from sys.dept1 d, sys.emp1 e where d.dept_id = e.dept_id and e.dept_id < 1000
Execution Plan
----------------------------------------------------------
Plan hash value: 619452140
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998K| 41M| 680 (2)| 00:00:09 |
|* 1 | HASH JOIN | | 998K| 41M| 680 (2)| 00:00:09 |
|* 2 | TABLE ACCESS FULL| DEPT1 | 999 | 34965 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP1 | 999K| 8780K| 672 (2)| 00:00:09 |
----------------------------------------------------------------------------
what can I do to get a nested loop join to simulate latch : cache buffer chains?