ORACLE PIVOT FUNCTION & ORA-00918

First we create a view v_emp

create or replace view scott.v_emp as
select a.empno,a.sal,b.dname from scott.emp a, scott.dept b
where a.deptno=b.deptno;

Case One:

We get this error because we are pivoting multiple columns without giving alias for each column, we should give alias at least for one column.

Case Two:

In the second case, we still get the same error even if we have added an alias of at least of one of pivoted column.

The length of the name of the last department was 28 , Oracle reconstruct the name for column name as below : 'name_of_department'_NAME_OF_PIVOTING_COLUMN' ==> This gives  

'S013456789013456789013456789'_SAL; it length is 34 . This will be truncated and get 'S013456789013456789013456789'_ with length 30 characters.

The max size of column is 30 characters.

Now try with department_name =S013456789013456789013456788

Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c

Massive Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c (Doc ID 2611336.1)

Last updated on MAY 01, 2020

BUG 27268249 - ENHANCE INDEX NDK STATISTICS GATHERINGW

 it's recommended by change the fix control to disable 27268249 either at the session level or the system level: 
alter session set "_fix_control"='27268249:0';
or
alter system set "_fix_control"='27268249:0';
+++delete existing statistics:
sql> exec DBMS_STATS.DELETE_TABLE_STATS ('<owner-name>','<table-name>', partname => '<part-name>');
++ regather state++++
begin
 DBMS_STATS.gather_table_stats(ownname => '<owner-name>'
 ,tabname => '<table-name>'
 ,partname => NULL
 ,DEGREE => 1
 ,CASCADE => TRUE
 ,no_invalidate =>FALSE
 );
 end;
 /
On Jul 1, 2022 oracle 
reference :  
Ref Doc ID 2611336.1
Patch 33427856: GATHERING STATISTICS FOR INDEXES ON LARGE TABLES MAY BE TIME-CONSUMING

Materialized View

Materialized View Refresh is very slow in Oracle Database 12.2

Bug 26338953 — MVIEW refresh slowed down because of select count(*) queries (Doc ID 26338953.8), 

This fix improves refresh performance when statistics-collection level, as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT, is TYPICAL. With this fix, Cardinality Collection is turned on only when STATISTICS_LEVEL = ADVANCED.   Rediscovery Notes  When stats collection, as set by as set by DBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT is TYPICAL, "select count(*)" queries are used to collect cardinalities of tables and MVs.  This causes refresh slowdown.

Same behavior since upgrading from 12.2 to 19c.

Regarding this MOS "12.2: Drop Materialized View Is Very Slow or Hangs (Doc ID 2401976.1)"
I had to disable the statistic collection and truncate the statistic tables. Now it works.

Move and improve your application on Oracle Autonomous Database and Oracle Cloud Infrastructure

About this workshop

Workshop Outline

• Generate SSH Keys
• Generate Auth Token
• Create Virtual Cloud Network
• Create Computes
• Create Autonomous Database
• Copy SSH Key and Wallet
• Install the Application and Migrate the Database
• Run the Application Workload
• Manage Storage Volumes and Images

19 features you will miss if you leave Oracle Database

 19 features you will missif you leave Oracle Database

Franck Pachot

Missing rows under dba_hist_sqlstat : is it a BUG?

         During monitoring of PROD long running reporting… I identified a query taking more than 17 000 sec. 

The next day when I generated AWR covering its execution period, I found that this query  had taken 2600 sec. I have checked dba_hist_sqlstat , there is only one row / one snap. The delta elapsed time was 2600 sec but the total elapsed time was exact / AWR report is based on delta columns.

Under dba_hist_active_sess_history, I get the exact number of snap_id, in fact
, there are many missing rows under dba_hist_sqlstat .

        I have identified the root cause. During these snapshots , we don’t have a final execution plan ( it was a complex query with more than 14 tables), it  was executed with an adaptive execution plan.

I identified that this behavior happened with dozens of queries.  I cannot share with you the real issue, I succeed in reproducing it.

1 2 3 4 5 6 7 8 9

Tested under 12.1.0.2.0 and 19.8 optimizer_adaptive_features =false optimizer_adaptive_plans =true Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE    12.1.0.2.0      Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production */

1 2 3 4 5 6

--------------------------------------------------------------------------- BEGIN   DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval  => 10);          -- Minutes. Current value retained if NULL. END; ---------------------------------------------------------------------------

TEST CASE

with LiveSQL Test Case:

1 2 3 4 5 6 7 8

---------------------------------------------------------------------------   select a.ELAPSED_TIME,a.executions from gv$sqlstats a where sql_id='6daxqtyukunhf';   ELAPSED_TIME   EXECUTIONS 1964510271      1 ---------------------------------------------------------------------------

1 2 3 4 5 6 7 8

select distinct snap_id from dba_hist_active_sess_history where sql_id='6daxqtyukunhf'; SNAP_ID 52546 52545 52544 52543 52542

1 2 3

select a.snap_id,round(a.ELAPSED_TIME_TOTAL/1e6) ELAPSED_TIME_TOTAL, round(a.ELAPSED_TIME_DELTA /1e6) ELAPSED_TIME_DELTA from dba_hist_sqlstat a where sql_id=’6daxqtyukunhf’

SNAP_ID	ELAPSED _TIME_TOTAL	ELAPSED_ TIME_DELTA
52543	1138	602
52544	1739	600
52545	1965	226
		1428

AWR

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Snap Id      Snap Time      Sessions Curs/Sess             --------- ------------------- -------- --------- Begin Snap:     52542 01-Nov-20 14:40:27       204       2.0   End Snap:     52546 01-Nov-20 15:10:29       199       2.0    Elapsed:               30.03 (mins)    DB Time:               47.84 (mins)     SQL ordered by Elapsed Time                               Snaps: 52542-52546 -> Captured SQL account for   64.3% of Total DB Time (s):           2,870 -> Captured PL/SQL account for    0.3% of Total DB Time (s):           2,870           Elapsed                  Elapsed Time                                           Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    ---------------- -------------- ------------- ------ ------ ------ -------------          1,428.4              0           N/A   49.8   99.9     .0 6daxqtyukunhf Module: PL/SQL Developer SELECT /*+optimizer_adaptive_plans_True1 */ count(CHB_t1.pad), count(CHB_t2.pad) FROM CHB_t1, CHB_t2 WHERE CHB_t1.id = CHB_t2.id AND CHB_t1.n = 666 AND burn _cpu(CHB_t1.id/CHB_t1.id) = 1

I have worked on many cases where « wall clock time » >>  real execution time
of the query. ( file generation taking time par example) .

In that case , sum(DBA_HIST_SQLSTAT.ELAPSED_TIME_DELTA)
=MAX(DBA_HIST_SQLSTAT.ELAPSED_TIME_TOTAL)  and MAX(DBA_HIST_SQLSTAT.ELAPSED_TIME_TOTAL) << « wall clock time »
I assumed that this query was executed one time.

In my case , the query is executed via PL/SQL function.

Another finding, the insertion into wrh$_sqlstat (DBA_HIST_SQLSTAT) is
based on V$SQLAREA_PLAN_HASH . When the query is under execution , there is
no row into V$SQLAREA_PLAN_HASH

1 2 3 4 5 6 7

insert into wrh$_sqlstat SELECT ...      FROM X$KEWRSQLIDTAB sie, X$KGLCURSOR_CHILD_SQLIDPH sql    WHERE (1 = 1)      AND sie.sqlid_kewrsie = sql.kglobt03      AND nlssort(sie.sqlid_kewrsie, 'nls_sort = binary') =          nlssort(sql.kglobt03, 'nls_sort = binary')      AND sie.pdbid_kewrsie = decode(sql.con_id, NULL, 1, 0, 1, sql.con_id)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

Plan hash value: 1821829401    -------------------------------------------------------------------------------------------------- |   Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------- |     0 | SELECT STATEMENT               |               |       |       |    50 (100)|          | |     1 |  SORT AGGREGATE                |               |     1 |   214 |            |          | |  *  2 |   HASH JOIN                    |               |     1 |   214 |    50   (0)| 00:00:01 | |-    3 |    NESTED LOOPS                |               |     1 |   214 |    50   (0)| 00:00:01 | |-    4 |     NESTED LOOPS               |               |     1 |   214 |    50   (0)| 00:00:01 | |-    5 |      STATISTICS COLLECTOR      |               |       |       |            |          | |  *  6 |       TABLE ACCESS FULL        | CHB_T1        |     1 |   109 |    49   (0)| 00:00:01 | |- *  7 |      INDEX UNIQUE SCAN         | SYS_C00604946 |     1 |       |     0   (0)|          | |-    8 |     TABLE ACCESS BY INDEX ROWID| CHB_T2        |     1 |   105 |     1   (0)| 00:00:01 | |     9 |    TABLE ACCESS FULL           | CHB_T2        |     1 |   105 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id): ---------------------------------------------------       2 - access("CHB_T1"."ID"="CHB_T2"."ID")    6 - filter(("CHB_T1"."N"=666 AND "BURN_CPU"("CHB_T1"."ID"/"CHB_T1"."ID")=1))    7 - access("CHB_T1"."ID"="CHB_T2"."ID")    Note -----    - this is an adaptive plan (rows marked '-' are inactive)

This should be a bug. Disable optimizer_adaptive_plan fix this issue…

Let’s wait for oracle analyze…