Re: Huge plain inserts response time bad with db file sequential read wait event.

Thanks Tanel for the information.

Thanks Stefan. I will try generating SQLD360 for the said query and attach the output.

Regards,
Rakesh RA

On Sat, Dec 22, 2018 at 7:18 AM Tanel Poder <tanel_at_tanelpoder.com> wrote:

> 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_at_soocs.de> wrote:
>
>> Hello Rakesh,
>>
>> > a) Why INSERT is spending more time on db file sequential reads for PK
>> index?
>>
>> 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 OGG
>> queries 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: _at_OracleSK
>>
>> > Rakesh Ra <rakeshra.tr_at_gmail.com> hat am 20. Dezember 2018 um 18:48
>> geschrieben:
>> >
>> > Can someone guide as to,
>> >
>> > a) Why INSERT is spending more time on db file sequential reads for PK
>> index?
>> > b) Why CPU costing is showing as off in the execution plan for OGG
>> queries only?
>> >
>> >
>> > Regards,
>> > RRA
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l