Dynamic sampling – empty stats case

My usecase: Create 2 tables: T_BIG usual heap table with correct all the time stats T_SMALL - "global temporary table" will have empty stats. /* Dynamic sampling issue clean stats */ spool 10c set lines 200 set pages 1000 DROP TABLE T_SMALL; DROP TABLE T_BIG; CREATE GLOBAL TEMPORARY TABLE T_SMALL ON COMMIT PRESERVE ROWS AS …

Dynamic sampling- wrong stats case

My usecase: Create 2 tables: T_BIG will be untouched T_SMALL - "global temporary table" will contain much more records than stats claims. spool 10 set lines 200 set pages 1000 DROP TABLE T_SMALL; DROP TABLE T_BIG; CREATE GLOBAL TEMPORARY TABLE T_SMALL ON COMMIT PRESERVE ROWS AS SELECT rownum id, rpad('a',1000,'b') pad FROM dual connect by …

Severe underestimation of left join cardinality for ANSI join case

Recently I've struggeled with a severe performance issue for one of our friendly projects. That is a vendor's application written mostly using ANSI syntax. After some time spending on it I've realized that one of the major issues is a severe underestimation of join cardinality for complex ANSI left join which often led to suboptimal …

Latches allocation during PDB clone operation.

Following up my previous posts where I considered PDB snapshot cloning and found it is an extremely fast process, here I decided to measure where we spend time during PDB cloning/creation. One of the obvious candidates are various latches. It looks reasonable when you create some complicated object like a new PDB you need to …