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
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
Whenstats collection, assetbyassetbyDBMS_MVIEW_STATS.SET_SYSTEM_DEFAULT isTYPICAL, "select count(*)"queries are used tocollect cardinalities oftables andMVs.
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.
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
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)
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.
