Commit to reduce temp tablespace usage

Question

I have a large table insert as part of a job for reporting. For ease of development, I did a single insert with a single select, rather than splitting this up into multiple commits.

    insert /*+ parallel(AUTO) */ into sc_session_activity_stage1(fiscal_year
                                        ,fiscal_quarter_id
                                        ,date_stamp
                                        ,time_stamp
                                        ,session_key
                                        ,activity_call_type_key
                                        ,user_key
                                        ,device_key
                                        ,url_key
                                        ,ref_url_key
                                        ,event_key
                                        ,page_type_key
                                        ,link_url_key
                                        ,component_key
                                        ,content_trace_key
                                        ,key) (
        select  /*+ parallel(AUTO) */ 
                schfql.fiscal_year fiscal_year
                ,schfql.fiscal_quarter_id fiscal_quarter_id
                ,pkg_sc_portfolio_load.sc_datestamp_from_epoch(swa.time_stamp)
                ,swa.time_stamp time_stamp
                ,schuse.session_key session_key
                ,schact.activity_call_type_key  activity_call_type_key
                ,schu.user_key user_key
                ,schde.device_key device_key
                ,schurl_url.url_key url_key
                ,schurl_ref.url_key ref_url_key
                ,schev.event_key event_key
                ,schapt.page_type_key page_type_key
                ,schurl_link_url.url_key link_url_key
                ,schwac.component_id component_id
                ,schti_content_unique_id.trace_id_key content_unique_id_trace_id_key
                ,schti_unique_id.trace_id_key unique_id_trace_id_key 
    from web_activity swa
        inner join sc_fiscal_quarter_list schfql
        on pkg_sc_portfolio_load.sc_datestamp_from_epoch(swa.time_stamp) between schfql.start_date and schfql.end_date
        inner join sc_user_sessions schuse
    on schuse.session_id = swa.session_id
        inner join sc_activity_call_types schact
        on schact.activity_call_type_name =  swa.calltype
        inner join sc_users schu
        on schu.user_email = sc_normalize_email(swa.userid)
        inner join sc_devices schde
        on swa.device=schde.device and 
           swa.ip=schde.source_ip and
           swa.operation_system = schde.operating_system and
           swa.browser = schde.browser
        left join sc_urls schurl_url 
        on schurl_url.full_url = trim(swa.url)
        inner join sc_events schev
        on schev.event=trim(swa.event)
        inner join sc_activity_page_types schapt
        on schapt.page_type_name=swa.pagetype
        left join sc_urls schurl_link_url 
        on schurl_link_url.full_url = trim(swa.linkurl)
        left join sc_urls schurl_ref
        on schurl_ref.full_url = trim(swa.ref)
        inner join sc_web_activity_components schwac
        on schwac.component_name=trim(swa.component)
        left join sc_trace_ids schti_content_unique_id
        on schti_content_unique_id.alfresco_trace_id = swa.CONTENT_UNIQUE_ID
        left join sc_trace_ids schti_unique_id
    on schti_unique_id.alfresco_trace_id=swa.UNIQUE_ID
    );
    commit;

On production, this triggers alarms for TEMP tablespace. If I were to split the above into multiple commits, would that reduce the TEMP usage at any one point in time? This may be obvious to some, but I'm not sure how Oracle works. I'm not seeing any ORA type errors, rather some threshold is being triggered and someone from the DBA team sends an email.

Thank you from the Woodsman.

you can ask your dba to add more space to the temp tablespace. or you do a commit after x rows — hotfix, Jan 21 '20 at 20:01
I'm not sure hat you mean by splitting it into multiple commits. Do you mean adding some `where` clause such that only some of the rows are inserted at a time and committing after each one? It might reduce temp usage, or it might not. Perhaps the joins are taking all the resources, and filtering the rows processed might reduce that, depending on the execution plan. Or perhaps there is something wrong with the plan, e.g. https://hourim.wordpress.com/2015/02/24/parallel-query-broadcast-distribution-and-temp-space/ — William Robertson, Jan 21 '20 at 20:52
Probably you should start by [optimizing the query which drives the insert](https://stackoverflow.com/a/34975420/146325). Are you sure the parallel query is making things better rather worse? — APC, Jan 22 '20 at 08:30

score 0 · Answer 1 · answered Jan 21 '20 at 22:03

TEMP tablespace blowouts are common and can be addressed by increasing the TEMP tablesapce AND/OR tuning the SQL to use less TEMP. For tuning: I usually start with the SQL Tuning Advisor recommendations (requires diagnostics and tuning pack). BTW: TEMP usage goes up with parallel queries and also is mostly specific to the SELECT part. You can also reduce the TEMP tablespace usage by doing more in memory (i.e. increasing the PGA).

Commit to reduce temp tablespace usage

1 Answers1