SQL & PL/SQL :: Difference Between Deterministic Functions And Function Result Cache In 11g

Oct 30, 2012

difference between Difference between Deterministic functions and Function result cache in 11g?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

Real Application Clusters :: Difference Cache Fusion and Cache Coherency

Jun 20, 2013

What is the difference between cache fusion and Cache Coherency. Both are same or different functionality. 

View 1 Replies View Related

PL/SQL :: Oracle 11g / R2 Query Result Cache - Incremental Update

Oct 15, 2012

In Oracle 11g/R2, I created replica of HR.Employees table & executed the following statement (+Although using SUM() function is non-logical in this case, but just testifying the result+)

STEP - 1

SELECT      /+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)*
FROM           HR.Employees_copy
WHERE      department_id = 20
GROUP BY      employee_id, first_name, last_name;

EMPLOYEE_ID      FIRST_NAME LAST_NAME     SUM(SALARY)
-------------------------------------------------------------------------------------------------------
202           Pat           Fay          6000
201           Michael           Hartstein     13000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT      | | 2 | 130 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE      | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY      | | 2 | 130 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL     | EMPLOYEES_COPY | 2 | 130 | 3 (0)| 00:00:01 |
  
  Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
*690* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

STEP - 2

INSERT INTO HR.employees_copy
VALUES(200, 'Dummy', 'User','Dummy.User@email.com',NULL, sysdate, 'MANAGER',5000, NULL,NULL,20);

STEP - 3

SELECT      /*+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)
FROM           HR.Employees_copy
WHERE      department_id = 20
GROUP BY      employee_id, first_name, last_name;

EMPLOYEE_ID      FIRST_NAME LAST_NAME SUM(SALARY)
--------------------------------------------------------------------------------------------------
202      Pat      Fay      6000
201      Michael      Hartstein      13000
200      Dummy User      5000

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 3 | 195 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 3 | 195 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES_COPY | 3 | 195 | 3 (0)| 00:00:01 |
    
 Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
*714* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

In the execution plan of STEP-3, against ID-1 the operation RESULT CACHE is shown which shows the result has been retrieved directly from Result cache. Does this mean that Oracle Server has Incrementally Retrieved the resultset?

Because, before the execution of STEP-2, the cache contained only 2 records. Then 1 record was inserted but after STEP-3, a total of 3 records was returned from cache. Does this mean that newly inserted row is retrieved from database and merged to the cached result of STEP-1?

If Oracle server has incrementally retrieved and merged newly inserted record, what mechanism is being used by the Oracle to do so?

View 2 Replies View Related

Performance Tuning :: Demonstrate Working Of OCI Client Result Cache?

Jan 2, 2013

I'm trying to demonstrate the working of the OCI client result cache. I've set some parameters,orcl> sho parameter result_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 1000000
result_cache_max_result integer 5
result_cache_max_size big integer 1984K
result_cache_mode string FORCE
result_cache_remote_expiration integer 0

If I understand the docs correctly, that should be all that is needed.I've complied and run the cdemoqc and cdemoqc2 OCI demos, but I never get anything in the V$CLIENT_RESULT_CACHE_STATS or CLIENT_RESULT_CACHE_STATS$ views. This is probably because the sessions exit after running the queries.

So I've also tried repeating arbitrary queries through cdemo2, which gives a persistent session, but there is still nothing in those views and furthermore the v$result_cache_objects.scan_count for my queries keeps increasing. So I don't think think the client side cache is working.

View 4 Replies View Related

Analytic Functions - Pretend Result

Feb 24, 2012

I have a question regarding analytic functions. I've been working with some functions, but I can't achieve the one which gives me the pretend result. I know to resolve this without using a function, with a internal select, but I think the analytical function is faster and proper.

I've got the following data:

Brand Qt
A 150
B 200
C 50
D 100

I wanna be following output;

Brand Overall %
A 30
B 40
C 10
D 20

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

Difference Between Cache Buffer Chain Latch And Buffer Busy Wait

Jul 30, 2012

Does cache buffer chain latch and buffer busy wait event are related to one any another.

Latch definition from Google says : Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA).

what does it mean my protect. Does this mean protects from aging as per LRU algorithm and getting removed from SGA
or
protect from other processes ,say from example from simultaneously DML operations.
or
both

Does buffer busy wait event occurs , because of the cache buffer chain latch ?

View 3 Replies View Related

PL/SQL :: Writing User Defined Function The Same Way As The Oracle Functions

Sep 26, 2012

In one of the interviews , I have attended the guy asked me to write a user defined function which will take the column name and list all the values .

For example

Table Name:Employees
Column Name: Employee_Name

Employee_name
Scott
Ivgun
Jack
Shane

The query should be in this fromat

SELECT col_agg(Employee_name) emp_name from Employees;

The output should be

Emp_names
scott,ivgun,jack,shane

View 6 Replies View Related

SQL & PL/SQL :: Use DECODE Function For Getting Result

Aug 28, 2012

I have a table like Create temptbl (Client_status CHAR(1), Rollbak NUMERIC(3), count_org NUMERIC(3), count_member NUMERIC(3))

With Values
Cliet_Status Rollbak count_org count_member
------ ------- --------- ------------
Z 3 7 5
P 39 5 8
R 49 1 6
S 5 6 4

I need to use DECODE function for getting the result like

IF client_status NOT IN('Z', 'P')
THEN rollbak in(7, 39,49,10)and count_org is not null and count_member is not null.
ELSE NULL

View 3 Replies View Related

SQL & PL/SQL :: Assignment In Result In Decode Function

Dec 13, 2011

I have 3 tables.

Table 1 have 3 columns
ID, CUS_NAME, LOC
insert into table1 values (001,ABC,North);
insert into table1 values (002,DEF,South);
insert into table1 values (003,GHI,West);

Table 2 have 3 columns
ID, CUS_NAME, LOC
insert into table2 values (001,ABC,North);
insert into table1 values (002,DEF,East);
insert into table1 values (003,JKL,South);

Table 3 is Result_Tab table having 8 columns
ID, TAB1_CUS_NAME, TAB2_CUS_NAME, Cus_Name_Res, TAB1_CUS_LOC, TAB2_CUS_LOC, Cus_LOC_Res, Comment.

I have written two cursors which fetches data from both the tables and compares each data between each other and inserts the value into the result table.

the code is as follow:

Insert into Result_Tab values
(T1.ID, T1.Cus_Name, T2.Cus_Name, decode(T1.Cus_Name,T2.Cus_Name,'Y','N'),T1.LOC, T2.LOC, decode(T1.LOC,T2.LOC,'Y','N'),Null);

Now I want the resul as follows:
ID T1.N T2.N N_Res T1.L T2.L L_Res Comment
001 ABC ABC Y North North Y Null
002 DEF DEF Y South East N Loc
003 GHI JKL N West South N Name, Loc

Is there a way wherein i could capture the column names in decode function when it doesn't match, so that I can insert the same in the comment column.

View 4 Replies View Related

Function To Take In Expression And Fetch The Result

Jun 5, 2009

I have an arithmatic expression which is dynamic say

expression = [Col5]/[Col1]

I will be using this in building a dynamic SQL.so i have to make sure that the divisible by zero is taken care. In the expression Col1,Col5 are values coming from a SQL.

For the above expression, my conversion will be

ROUND( ((CCol4 / DECODE (CCol1, 0, NULL, CCol1) )), 0).

I have to build a function which takes in the expression and fetches me the result.My expression can be any combination of arithmaticexpression involving columns.

Ex:
[Col4 + Col2]/[Col3-Col1]*[Col5].

View 1 Replies View Related

TimesTen In-Memory :: AWT Cache Group With Cache Awt Parallelism

Jul 10, 2012

I 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].......

View 7 Replies View Related

SQL & PL/SQL :: How To Refer Result Of Inner Query (analytic Function) In Outer One

Feb 23, 2012

How can I refer the result of inner query - max(t.col3) in the outer query. I tried the 'group by' but it is not performing well

select
t1.col5, t1.col6
from t1,
(select t.col1
t.col2
[code].......

View 8 Replies View Related

SQL & PL/SQL :: Difference Between Procedure And Function

May 22, 2010

I know difference between procedure and function.if we want a return value from procedure, we can have OUT parameter. Similarly with function, in addition to returning a value from function, it can also send an OUT parameter value as a return value. That means, in one or the other way we are able to get a return value from both program units. Normally, I would fill a OUT variable with error message when an exception occurs. I use this varaible,after procedure call, to detect if an exception occurred or not. Similar task can be performed by a function, it returns a true/false and a value thru OUT variables.

both program units return values in the form of OUT parameters. Where exactly should we use a function, where exactly should we use a procedure?

View 4 Replies View Related

PL/SQL :: Difference Between Procedure And Function

Aug 25, 2012

difference between Procedure and Function.We Know that Procedure is Basically Used to perform Actions and Functions are Basically Used for Calculations but what would be the exact difference since Actions/Calculations can be done in both Procedure and Functions.Which factors decide whether we need to write a Procedure and in which cases we need Function.

View 4 Replies View Related

Difference Between Stored Procedure And Function?

Jan 12, 2010

I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.

The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..

I want to know what is the difference betn stored procedure and function?

From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).

View 2 Replies View Related

SQL & PL/SQL :: Difference In Num_rows And COUNT Function?

Dec 7, 2012

SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402

This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.

SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998

But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.

View 2 Replies View Related

SQL & PL/SQL :: Difference Between Concat Function And (||) Operator?

May 6, 2013

Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.

Select 'H '||' S' From Dual;
--Output H S

Select Concat('H ',' S') A From Dual;
--Output H S

Select Length('H '||' S') A From Dual;
--Output 6

Select Length(Concat('H ',' S')) A From Dual;
--Output 6

View 5 Replies View Related

PL/SQL :: Difference Between Translate And Replace Function

Jan 6, 2013

Explain with the best example for difference between translate and replace function.

View 2 Replies View Related

PL/SQL :: Difference In Num_rows And Count Function

Dec 7, 2012

SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402

This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.

SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'

and owner='APP'; --Number of records 237213998But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.

View 3 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

Sep 22, 2011

create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;

that i have worte..

View 3 Replies View Related

Reports & Discoverer :: Difference Between Formula Column And Function In Program Unit?

May 24, 2013

know when to use a formula column and when to use a function in program unit in oracle reports.

View 1 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

Index In Cache

Apr 21, 2013

i 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 Related

How To Set Sequence Cache

Oct 10, 2012

Are there any recommendations or good practices to set sequence CACHE parameter (for example one caching per hour, day etc)?

View 4 Replies View Related

Cache Buffers Chains In RAC

Jun 1, 2012

I 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.

View 2 Replies View Related

Procedure To Activate Cache

Aug 16, 2010

i 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 Related

SQL & PL/SQL :: What Is Cycle And Cache In Sequence

Oct 11, 2011

What is cache/no cache & cycle/no cycle in Sequence?what is the use?

View 3 Replies View Related

Data Buffer Cache

Feb 22, 2013

how can we check the size of data buffer cache.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved