Regarding the CPU cost and cost in general, things behave a
little differently with *trivial plans* like these:
*1) insert into t values(1)*
-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | T | |
-------------------------------------------------
The #1 above has PLAN_HASH_VALUE = 0 as there's no plan for the CBO to
optimize really. Perhaps some shortcut operation is used for such a trivial
insert.
The CPU_COST & IO_COST = 0 in V$SQL_PLAN. But there's a hardcoded COST=1 in
V$SQL.
*2) insert into t values(s.nextval)*
This plan at least has a PLAN_HASH_VALUE > 0 possibly due to additional
complexity, but still no CPU & IO COST in V$SQL_PLAN.
-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | T | |
| 2 | SEQUENCE | S | |
-------------------------------------------------
*3) insert into t select 1 from dual*
This insert SQL plan has a SELECT component too and that can have any
complexity (and needs to be optimized), so you'll have both a populated
PLAN_HASH_VALUE and CPU & IO_COST populated.
---------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 2 (100)|
| 1 | LOAD TABLE CONVENTIONAL | T | | |
| 2 | FAST DUAL | | 1 | 2 (0)|
---------------------------------------------------------------
--
Tanel Poder
https://blog.tanelpoder.com
On Fri, Dec 21, 2018 at 12:59 AM Stefan Koehler <contact@xxxxxxxx> wrote:
Hello Rakesh,
a) Why INSERT is spending more time on db file sequential reads for PKindex?
We can not make any reliable statement based on the given data (e.g. array
inserts, DML restart, I/O latency histograms, etc.?) but you might be able
to run SQLd360 (https://github.com/sqldb360/sqldb360/archive/v18.3.zip)
for the SQL-ID g4cf65js6kjf0 and send us the output.
b) Why CPU costing is showing as off in the execution plan for OGGqueries only?
The most common reason might be that you have an old PLAN_TABLE in
combination with DBMS_XPLAN usage - this results in the misleading "cpu
costing is off" message.
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Rakesh Ra <rakeshra.tr@xxxxxxxxx> hat am 20. Dezember 2018 um 18:48geschrieben:
index?
Can someone guide as to,
a) Why INSERT is spending more time on db file sequential reads for PK
b) Why CPU costing is showing as off in the execution plan for OGGqueries only?
--
Regards,
RRA
//www.freelists.org/webpage/oracle-l
