Wednesday 6 May 2020
Tuesday 5 May 2020
PT - Statistics Gathering Optimizer Table Example
PT - Statistics Gathering Optimizer Table Example
- Checking current analyzed status of table
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
879 11 31-JUL-19
- Table created from existing table
SQL> CREATE TABLE SOE.EMP2 NOLOGGING AS SELECT * FROM SOE.EMP;
Table created.
- Checking Analyzed status
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP2';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
879 10 05-MAY-20
- New table created EMP3
SQL> CREATE TABLE SOE.EMP3 ( EMP_NO NUMBER(4), ENAME VARCHAR2(20), HIRE_DATE DATE, DEPT_NO NUMBER(2), JOB_CODE CHAR(4), SALARY NUMBER(8,2), MGR_ID NUMBER(4), TERMINATED CHAR(1), NOTES VARCHAR2(1000));
Table created.
- Checking Analysed status
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
- Checking the Trace status
SQL> SET LINESIZE 180
SQL> SET AUTOT TRACE EXP
SQL> SELECT * FROM SOE.EMP3 WHERE EMP_NO=641;
Execution Plan
----------------------------------------------------------
Plan hash value: 2425169977
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 584 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP3 | 1 | 584 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP_NO"=641)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
- Checking the status
SQL> SET AUTOT OFF
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
SQL>
- Insert the values from existing table emp
SQL> INSERT INTO SOE.EMP3 SELECT * FROM SOE.EMP;
879 rows created.
SQL> COMMIT;
Commit complete.
- Check the analysed status of table emp3
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
SQL>
- Create unique index on EMP3 table
SQL> CREATE UNIQUE INDEX SOE.EMP2_EMPNO_UQ ON SOE.EMP3(EMP_NO) NOLOGGING TABLESPACE SOETBS;
Index created.
- Checking index compute status
SQL> SELECT BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY", AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY", AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY" FROM DBA_IND_STATISTICS WHERE OWNER = 'SOE' AND INDEX_NAME = 'EMP2_EMPNO_UQ';
no rows selected
SQL>
SQL>
- Checking the analysed status
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
SQL>
- Explain plan of query
SQL> SET LINESIZE 180
SQL> SET AUTOT TRACE EXP
SQL> SELECT * FROM SOE.EMP3 WHERE EMP_NO=641;
Execution Plan
----------------------------------------------------------
Plan hash value: 2136555587
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 584 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP3 | 1 | 584 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP2_EMPNO_UQ | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP_NO"=641)
SQL>
- Gather stats of Table EMP3
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SOE','EMP3');
PL/SQL procedure successfully completed.
- Checking analysed status of table emp3 after stats gathering
SQL> set autot off
SQL> SELECT NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='SOE' AND TABLE_NAME='EMP3';
NUM_ROWS BLOCKS LAST_ANAL
---------- ---------- ---------
879 13 05-MAY-20
SQL>
SQL> DROP TABLE SOE.EMP2;
Table dropped.
SQL> DROP TABLE SOE.EMP3;
Table dropped.
SQL>
Wednesday 29 April 2020
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x10] [PC:0xCE518CE, qerixGetKey()+782] [flags: 0x0, count: 1]
Scenario Preview:
ERRORS:
Error at application level and in sqlplus prompt:
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3388
Session ID: 324 Serial number: 55071
Error in Alert.log
Fri Apr 24 09:41:23 2020
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x10] [PC:0xCE518CE, qerixGetKey()+782] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/TESTENV/TESTENV/trace/TESTENV_ora_2543.trc (incident=615):
ORA-07445: exception encountered: core dump [qerixGetKey()+782] [SIGSEGV] [ADDR:0x10] [PC:0xCE518CE] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/TESTENV/TESTENV/incident/incdir_615/TESTENV_ora_2543_i615.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Apr 24 09:41:25 2020
Errors in file /u01/app/oracle/diag/rdbms/TESTENV/TESTENV/incident/incdir_615/TESTENV_ora_2543_i615.trc:
ORA-10173: Dynamic Sampling time-out error
ORA-07445: exception encountered: core dump [qerixGetKey()+782] [SIGSEGV] [ADDR:0x10] [PC:0xCE518CE] [Address not mapped to object] []
Fri Apr 24 09:41:25 2020
Errors in file /u01/app/oracle/diag/rdbms/TESTENV/TESTENV/incident/incdir_615/TESTENV_ora_2543_i615.trc:
ORA-10173: Dynamic Sampling time-out error
ORA-07445: exception encountered: core dump [qerixGetKey()+782] [SIGSEGV] [ADDR:0x10] [PC:0xCE518CE] [Address not mapped to object] []
Solution:
1) The recommended temporary workaround is to set one of the following
OPTIMIZER_ADAPTIVE_FEATURES=false
alter session set "_projection_pushdown"=false;
alter session set optimizer_adaptive_features=false;
2) The permanent solution is for Teamcenter customers who have downloaded
and installed Oracle 12.1.0.2 to download and install the following
patch bundle, which includes the correction for Oracle Bug 18430870.
This patch is available on the GTAC download server for each
supported Teamcenter platform in the Oracle / Product Updates / Oracle12
folder.
p22809813_121020_MSWIN-x86-64.zip
p22899531_121020_Linux-x86-64.zip
p22899531_121020_Solaris86-64.zip
Practice Solution Scenario:
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
xxxTESTE TESTENV TESTENV READ WRITE PRIMARY NO 0
SQL> SELECT /*+ DYNAMIC_SAMPLING(t_02 4)*/ ;
SELECT /*+ DYNAMIC_SAMPLING(t_02 4)*/) ) ) ) )
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3388
Session ID: 324 Serial number: 55071
SQL>
SQL> ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET "_projection_pushdown"=false SCOPE=BOTH;
System altered.
SQL> @d
NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
xxxPATCH TESTENV TESTENV READ WRITE PRIMARY NO 0
SQL> SELECT /*+ DYNAMIC_SAMPLING(t_02 4)*xxxxx -------------------------------;
COLUMN_ENTRY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
xxx
xxxxx
SQL> sho parameter OPTIMIZER_ADAPTIVE_FEATURES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features boolean FALSE
SQL> sho parameter _projection_pushdown
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_projection_pushdown boolean FALSE
SQL>
Subscribe to:
Posts (Atom)