Thursday, December 17, 2009

upgrade 11g R1 to 11g R2 without DBUA

1. update the timezone file for ease, refere

2. then follow the same step as in 10g to 11g

3. Few modifications

instead of utlu111i.sql use utlu112i.sql,
utlu111s.sql use utlu112s.sql



Find out about the invalid views with
SELECT count(*) FROM dba_invalid_objects;
SELECT distinct object_name FROM dba_invalid_objects;

Wednesday, December 2, 2009

Result Cache for Ref Cursor returning PL/SQL codes

According to Oracle documentation if a PL/SQL SP is returning a ref cursor then result cache is not supported. But there's a way to circumvent with somewhat beneficial results.

Below is a package written for the HR sample schema

create or replace package emps_pkg as

type detail is ref cursor;

function getempdetails (options in INTEGER, departname in VARCHAR2, jobtitle in VARCHAR2) return detail;

end;
/


create or replace package body emps_pkg as

function getempdetails(options in INTEGER,departname in VARCHAR2, jobtitle in VARCHAR2) return detail is

employees detail;
main_query VARCHAR2(2000) := 'select /*+ result_cache */ e.* from employees e,departments d,jobs j
where e.department_id = d.department_id and j.job_id = e.job_id ';
begin
if options = 1 then
main_query := main_query ||'and d.department_name=:1';
open employees for main_query using departname;

return employees;

else
main_query := main_query ||'and j.job_title=:1';

open employees for main_query using jobtitle;

return employees;
end IF ;
end;
end;
/


Package body contains some dynamic sql (there's no real logic behind this package only to demonstrate the use of result cache) which is constructed with the result_cache hint.

execute the package code and observe the plan


select emps_pkg.getempdetails(2,'Purchasing','Purchasing Manager') from dual;
select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

Plan hash value: 980169617

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | RESULT CACHE | a68hth9dfygb5ddg0hr8hnty9b | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------


Recompile the package body without the result cache hint and observe the plan and it would be

Plan hash value: 980169617

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 6 | 570 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | JOBS | 1 | 27 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 6 | | 0 (0)| |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 408 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


The benefit comes from the fact the sql query inside the SP could use the result cache. But the overhead of executing the PL/SQL code is still there which I believe won't be present in "proper" result cached SPs.

Any changes to depending tables will make the cache invalid just like the expected behavior.

Oracle Parallel Query Differences (not enough processes)

10g

If users initiate too many concurrent operations, Oracle might not have enough query server processes. In this case, Oracle executes the operations sequentially or displays an error if PARALLEL_MIN_PERCENT is set to a value other than the default value of 0 (zero).
This condition can be verified through the GV$SYSSTAT view by comparing the statistics for parallel operations not downgraded and parallel operations downgraded to serial. For example:
SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';


11g R1

Oracle Database can process a parallel operation with fewer than the requested number of processes.
If all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started, the parallel execution coordinator switches to serial processing.

11g R2

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Once the necessary resources become available, the SQL statement will be dequeued and allowed to execute. The queue is a simple First In - First Out queue based on the time a statement was issued.
Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVERS_TARGET. By default, this parameter is set to 4 X CPU_COUNT X PARALLEL_THREADS_PER_CPU X ACTIVE_INSTANCES. This is not the maximum number of parallel server processes allowed on the system, but the number available to run parallel statements before statement queuing will be used. It is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes. Note all serial (non-parallel) statements will execute immediately even if statement queuing has been activated.
There are two wait events to help identity if a statement has been queued. A statement waiting on the event PX QUEUING: statement queue is the first statement in the statement queue. Once the necessary resource become available for this statement, it will be dequeued and will be executed. All other statements in the queue will be waiting on PX QUEUING: statement queue. Only when a statement gets to the head of the queue will the wait event switch to PX QUEUING: statement queue.