WHERE CURRENT OF Cursor Loop

WHERE CURRENT OF  clause is used in conjuction with  FOR UPDATE   to update / delete current working row in a cursor for loop.

The  WHERE CURRENT OF  clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the  FOR UPDATE clause to use this feature

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated

Declare
cursor c1 is
  Select    row_number()over(order by process_id)  lineno , process_id, line_item_no
  from wbbs_to_ar where notional_invoice_no='11011875' for update;
Begin
  For cx in c1
  Loop
   update wbbs_to_ar set line_item_no = cx.lineno where current of c1;
  End Loop;
End;

Diagnose Performance Issue Using Dynamic v$Performance Views

Diagnose Performance Issues Using DYNAMIC Performance Views:



v$session : Session Complete Details Type= USER/ BACKGROUND. sid, serial#, event, parameter, etc.. 

v$session_event -- summary of sessions wait-events  prior to Oracle 10g,

v$system_event -- wait experieced by Instace, from the time it started (but only Summary)

View for High Load Session:-  Consuming intensive Resource (CPU, PIO, LIO,No of commits/rollback)
v$sesstat, v$sysstat.   

Get complete details of wait events experienced, for eg. the time (how long), Sql Statment, Event  and corresponding parameter details so as  to drill down to the problem resolution.
v$active_session_history 

RANK & DENSE_RANK

RANK calculates the rank of a value in a group of values. The return type is NUMBER. The ranks may not be consecutive number.

DENSE_RANK computes the rank of a row in an ordered group of rows . The ranks are consecutive integers beginning with 1. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

Q, Give me the set of sales people making the top 3 salaries in each department"? scott@TKYTE816> break on deptno skip 1 scott@TKYTE816> select * 2 from ( select deptno, ename, sal, 3 dense_rank() over ( partition by deptno 4 order by sal desc ) dr 5 from emp ) 6 where dr <= 3 7 order by deptno, sal desc 8 / DEPTNO ENAME SAL DR ---------- ---------- ---------- ---------- 10 KING 5000 1 CLARK 2450 2 MILLER 1300 3 20 SCOTT 3000 1 FORD 3000 1 JONES 2975 2 ADAMS 1100 3 30 BLAKE 2850 1 ALLEN 1600 2 TURNER 1500 3

Materialized View

 Materialized View

Query rewrite feature of Materialized view depends on 2 parameters:

1. QUERY_REWRITE_INTEGRITY = { enforced | trusted | stale_tolerated } , This  determines the degree to which Oracle must enforce query rewriting .

enforced =Oracle enforces and guarantees consistency and integrity.

stale_tolerated Materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.

2. QUERY_REWRITE_ENABLED = {TRUE | False }

Realtime Materialized View can be created using  ENABLE QUERY COMPUTATION keyword, it nake use of mview logs and base table.

Tips for Materialized view

https://danischnider.wordpress.com/2019/02/18/materialized-view-refresh-for-dummies/

https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/refreshing-materialized-views.html#GUID-E4E896C7-8173-4A36-A43F-188158981EB7

How does the METHOD_OPT parameter work?

 

How does the METHOD_OPT parameter work?

The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms but it actually does so much more than that. The METHOD_OPT parameter actually controls the following,

• which columns will or will not have base column statistics gathered on them
• the histogram creation,
• the creation of extended statistics

The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below.

The leading part of the METHOD_OPT syntax controls which columns will have base column statistics (min, max, NDV, number of nulls, etc) gathered on them. The default, FOR ALL COLUMNS, will collects base column statistics for all of the columns (including hidden columns) in the table.  The alternative values limit the collection of base column statistics as follows;

The SIZE part of the METHOD_OPT syntax controls the creation of histograms and can have the following settings;

AUTO, REPEAT, SKEWONLY,  Size Must be in the range [1,254]. 

The second part of the parameter setting needs to specify that a histogram is needed on the CUST_ID column. 

 

https://blogs.oracle.com/optimizer/post/how-does-the-method-opt-parameter-work

Dynamic sampling and its impact on the Optimizer

Dynamic sampling and its impact on the Optimizer

12c 

Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed Dynamic Statistics in Oracle Database 12c. The most common misconception is that DS can be used as a substitute for optimizer statistics, whereas the goal of DS is to  augment optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates.

Parallel Hint

 PARALLEL HINT

Parallel Hint instructs the optimizer to make use the given degree of parallelism in processing  the sql statement parallelly, instead of processing it serially.  

Parallel  execution can improve the performance of the query by dividing the work  among multiple processers or cpus,  Thus reducing the overall cost of Execution Plan. but you have to keep in mind that you MUST have enough CPU power available on the server, or else it can cause performance issues. 

Also Parallel execution can consume a significant amount of system resources, including CPU and memory.Therefore, it is important to monitor the system’s resources when using parallel execution and to adjust the degree of parallelism as necessary.

Refer https://expertoracle.com/2022/12/04/parallel-hint-in-oracle-database-ways-to-use-and-how-to-monitor/