ORACLE : DATABASE قاعدة بيانات Base de données

ORACLE PIVOT FUNCTION & ORA-00918

2022-07-14T01:19:00.000-07:00

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

2022-07-03T02:32:00.001-07:00

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

2022-04-23T15:48:00.006-07:00

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

2022-02-22T01:30:00.003-08:00

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

2020-12-05T02:08:00.001-08:00

19 features you will missif you leave Oracle Database

Franck Pachot

Missing rows under dba_hist_sqlstat : is it a BUG?

2020-11-01T02:46:00.041-08:00

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.

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
*/

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

TEST CASE

with LiveSQL Test Case:

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

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

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

              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

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)

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…

Automate Oracle Using DevOps Tools

2020-08-30T03:07:00.001-07:00

Real-Time SQL Monitoring : settings

2020-07-01T00:26:00.002-07:00

The Real-Time SQL Monitoring feature of Oracle Database enables monitoring the performance of SQL statements while they are executing. By default, SQL monitoring is automatically started when a SQL statement either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution.

select ksppinm, ksppstvl, ksppdesc from sys.x$ksppi a, sys.x$ksppsv b where a.indx=b.indx and lower(ksppinm) like lower('%sqlmon%') order by ksppinm;

KSPPINM KSPPSTVL KSPPDESC

------------------------- --------- --------------------------------------------------------------------------------

_sqlmon_binds_xml_format default format of column binds_xml in [G]V$SQL_MONITOR

_sqlmon_max_plan 640 Maximum number of plans entry that can be monitored. Defaults to 20 per CPU

_sqlmon_max_planlines 300 Number of plan lines beyond which a plan cannot be monitored

_sqlmon_recycle_time 60 Minimum time (in s) to wait before a plan entry can be recycled

_sqlmon_threshold 5 CPU/IO time threshold before a statement is monitored. 0 is disabled

PL/SQL & SQL Coding Guidelines Version 3.6

2019-12-16T02:03:00.002-08:00

PL/SQL & SQL Coding Guidelines Version 3.6

ORA-13511: Error Code and Solution Suggestion

2018-11-29T04:37:00.001-08:00

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    interval  => 5);          -- Minutes. Current value retained if NULL.
END;
-------- -------------------------------- --------------- ---------------
ORA-13511: invalid INTERVAL 5, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 198
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 246
ORA-06512: at line 2
-------- -------------------------------- --------------- ---------------
ORA-13511: invalid INTERVAL string, must be in the range (string, string)
Cause: The user has specified a INTERVAL setting that is not in the supported range of (MIN, MAX).
Action: Choose a valid INTERVAL setting and retry the operation.
 
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
  job_name        => 'DBMS_WORKLOAD_REPOSITORY_CHB',
  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END;',
  start_date      => SYSDATE,
  repeat_interval => 'FREQ=MINUTELY;INTERVAL=3',
  
  end_date        => SYSDATE + 1 / 24,
  enabled         => TRUE,
  comments        => 'DBMS_WORKLOAD_REPOSITORY_CHB');
END;

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later

2016-10-31T19:00:00.002-07:00

This feature is controlled by _partition_large_extents for tables and _index_partition_large_extents for indexes

set linesize 210
col "Parameter" format a30
col "Session Value" format a20
col "Instance Value" format a20
col "Desc" format a70
select a.ksppinm  "Parameter", c.ksppstvl "Instance Value", ksppdesc "Desc"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm like ('%_partition_large_extents');
 
Parameter                      Instance Value   Desc
------------------------------ ---------------- -------------------------------------------------------
_partition_large_extents       TRUE             Enables large extent allocation for partitioned tables 
_index_partition_large_extents TRUE              Enables large extent allocation forpartitioned indices

The details of the behavior change are available in the below metalink.

Doc ID 1295484.1 — Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later.

ORA-22992: cannot use LOB locators selected from remote tables

2015-11-29T04:41:00.000-08:00

ORA-22992: cannot use LOB locators selected from remote tables

--- Remote DB
create table CHB_TEST 
(
 DATA CLOB  

);

--- LOCAL DB
select   DATA    from CHB_TEST@<ORACLE_DB_LINK_REMOTE_DB>

ORA-22992: cannot use LOB locators selected from remote tables

 select dbms_lob.substr@<ORACLE_DB_LINK_REMOTE_DB>( DATA , 4000) from CHB_TEST@<ORACLE_DB_LINK_REMOTE_DB

The Oracle Optimizer : Explain the Explain Plan

2015-10-21T09:58:00.001-07:00

Explain the Explain Plan

SQL Plan Directives

2015-10-21T05:25:00.001-07:00

Franck Pachot :
SQL Plan Directives

PLW-07204: conversion away from column type may result in sub-optimal query plan

2015-08-17T08:59:00.001-07:00

Oracle Database 12c: Interactive Quick Reference

2015-06-14T08:18:00.002-07:00

Oracle Database 12c: Interactive Quick Reference

Oracle Database 11g: Interactive Quick Reference

2015-05-06T08:07:00.000-07:00

11g interactive quick reference

How to get Oracle system change number (SCN) ?

2015-02-01T00:32:00.002-08:00

The SYSTEM CHANGE NUMBER = A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.

USER_A@MYDB 01022015 09:24:45> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

2544927

USER_A@MYDB 01022015 09:24:46> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

GET_SYSTEM_CHANGE_NUMBER

------------------------

2544927

The User_A should have the privileges execute on DBMS_FLASHBACK and SELECT on V_$DATABASE

Getting the difference between Dates

2014-11-19T09:53:00.001-08:00

To get the difference between two dates;
The below function return the interval time between two dates:

create or replace function

get_elapsed (pstart in date , pend in date )

return interval day to second
as
begin
return (cast(pend as timestamp)- cast(pstart as timestamp)) DAY TO SECOND;
end ;

SELECT GET_ELAPSED(

to_date('19/11/2014 12:10:20', 'DD/MM/YYYY HH24:MI:SS'), to_date('19/11/2014 13:10:20', 'DD/MM/YYYY HH24:MI:SS')

) ELAPSED

FROM DUAL;

ELAPSED:

+000000000 01:00:00

We can extract hour :

SELECT EXTRACT(hour from GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'))) HOUR
FROM DUAL;

HOUR:

We can extract hours,minutes and seconds

SELECT
   GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS') ) ELAPSED,

   EXTRACT(HOUR FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) HOUR,
             EXTRACT(MINUTE FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) MINUTE,
           EXTRACT(SECOND FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) SECOND
FROM DUAL;

ELAPSED HOUR MINUTE SECOND

+000000000 00:49:40 0 49 40

You can refer to Database SQL Language Reference to get more details about Interval type and extract function;

A Database Limits

2014-10-28T08:03:00.000-07:00

Coskan Gundogar = Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

2014-10-19T01:11:00.001-07:00

Plan stability through Upgrade to 11G-Why is my plan changed?-Auto Adjusted Dynamic Sampling

SQL Patch: Another way to change the plan without changing the code

2014-10-19T01:06:00.003-07:00

SQL Patch: Another way to change the plan without changing the code

Indexes: Structure, Splits and Free Space Management Internals from Christian Antognini (OOW14)

2014-10-12T00:12:00.004-07:00

Indexes: Structure, Splits and Free Space Management Internals from Christian Antognini

Adaptive Query Optimization from Christian Antognini (OOW14)

2014-10-12T00:06:00.005-07:00

Adaptive Query Optimization from Christian Antognini

Everything That Is Really Useful in Oracle Database 12c for Application Developers (OOW 2014, CON 2394

2014-10-11T23:58:00.001-07:00

Everything That Is Really Useful in Oracle Database 12c for Application Developers (OOW 2014, CON 2394) from Lucas Jellema