Oracle SQL

    Simple function returning Parallel slave info

    Posted on September 15, 2020 by Sayan Malakshinov Posted in oracle, parallel, query optimizing, SQL, statistics, troubleshooting

    You can add also any information from v$rtsm_sql_plan_monitor if needed

    create or replace function px_session_info return varchar2 parallel_enable as
       vSID int;
       res varchar2(30);
    begin
       vSID:=userenv('sid');
       select 
               to_char(s.server_group,'fm000')
        ||'-'||to_char(s.server_set,'fm0000')
        ||'-'||to_char(s.server#,'fm0000')
        ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
        into res
       from v$px_session s 
       where s.sid=vSID;
       return res;
    exception when no_data_found then
       return 'no_parallel';
    end;
    /
    

    Simple example:

    select--+ parallel
      px_session_info, count(*)
    from sys.obj$
    group by px_session_info
    /
    PX_SESSION_INFO           COUNT(*)
    ------------------------  --------
    001-0002-0001(630,2/2)     38298
    001-0002-0002(743,2/2)     34706
    
    oracle parallel presentations troubleshooting
    « Android Oracle Client 2.0
    Funny friday Oracle SQL quiz: query running N seconds »

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    UK / Aylesbury / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 5 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Funny friday Oracle SQL quiz: query running N seconds
    • Simple function returning Parallel slave info
    • Android Oracle Client 2.0
    • PL/SQL functions and statement level consistency
    • Just short note for myself: OJPPD limitations

    Email Subscription

    Recent Comments

    • Oracle SYSTPH* Type – oraerr.com on A funny fact about collect
    • Is the use of SELECT COUNT(*) before SELECT INTO slower than using Exceptions? – oraerr.com on About the performance of exception handling
    • Oracle SQL | Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions on PRECOMPUTE_SUBQUERY hint
    • Oracle SQL | Deterministic function vs scalar subquery caching. Part 3 on Deterministic function vs scalar subquery caching. Part 2
    • Index Bouncy Scan 2 | Oracle Scratchpad on Amazing optimization of getting distinct values from the index, and TopN for each of them
    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL