1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Parallel query server error

Discussion in 'SQL PL/SQL' started by jhonnyrip, Mar 2, 2017.

  1. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    for non rac servers, the query works and

    for rac servers
    SELECT host_name,count(*)
    FROM (SELECT b.host_name, c.VALUE
    FROM gv$session a,
    gv$instance b,
    (SELECT TO_NUMBER (
    ROUND (
    TO_CHAR (
    (CURRENT_UTILIZATION / LIMIT_VALUE) * 100),
    0))
    VALUE
    FROM gv$resource_limit
    WHERE resource_name LIKE '%proces%') c
    WHERE a.inst_id = b.inst_id AND c.VALUE >= 80)
    GROUP BY host_name
    /

    SELECT host_name,count(*)
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server PZ99, instance
    zimgrid45.vzbi.com:dev1d (2)
    ORA-01722: invalid number
     
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Hi,

    There may be a non-numeric value in LIMIT_VALUE.
    Run the query to see the values

    SELECT CURRENT_UTILIZATION, LIMIT_VALUE
    FROM gv $ resource_limit
    WHERE resource_name LIKE '% process%'
     
  3. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    no rows selected
     
  4. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
  5. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    select CURRENT_UTILIZATION/LIMIT_VALUE*100 from gv$resource_limit where resource_name like '%proces%';

    CURRENT_UTILIZATION/LIMIT_VALUE*100
    -----------------------------------
    22.64
    3.08

    select CURRENT_UTILIZATION/LIMIT_VALUE*100 from gv$resource_limit where resource_name like '%proces%' and CURRENT_UTILIZATION/LIMIT_VALUE*100>'80';
    select CURRENT_UTILIZATION/LIMIT_VALUE*100 from gv$resource_limit where resource_name like '%proces%' and CURRENT_UTILIZATION/LIMIT_VALUE*100>'80'
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server PZ99, instance
    zimgrid45.vzbi.com:dev1d (2)
    ORA-01722: invalid number
     
  6. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    NATLAS@Nccrs1q > select CURRENT_UTILIZATION/LIMIT_VALUE*100 from gv$resource_limit where resource_name like '%proces%' and CURRENT_UTILIZATION/LIMIT_VALUE*100>80;
    select CURRENT_UTILIZATION/LIMIT_VALUE*100 from gv$resource_limit where resource_name like '%proces%' and CURRENT_UTILIZATION/LIMIT_VALUE*100>80
    *
    ERROR at line
     
  7. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    what need to do if this need to work
    select CURRENT_UTILIZATION/LIMIT_VALUE*100 from gv$resource_limit where resource_name like '%proces%' and CURRENT_UTILIZATION/LIMIT_VALUE*100>80
     
  8. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    Try to execute with the where this way:

    select CURRENT_UTILIZATION/LIMIT_VALUE*100
    from gv$resource_limit
    where CURRENT_UTILIZATION/LIMIT_VALUE*100>80
    and resource_name like '%proces%'

    If I am not mistaken the oracle read from the bottom up
     
  9. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    select CURRENT_UTILIZATION/LIMIT_VALUE*100
    2 from gv$resource_limit
    3 where CURRENT_UTILIZATION/LIMIT_VALUE*100>80
    and resource_name like '%proces%' 4 ;
    select CURRENT_UTILIZATION/LIMIT_VALUE*100
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server PZ99, instance
    zimgrid45.vzbi.com:dev1d (2)
    ORA-01722: invalid number
     
  10. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    29
    Likes Received:
    4
    Trophy Points:
    90
    Location:
    São Paulo - Brasil
    In my test, error only if LIMIT_VALUE is not numeric.
     
  11. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    I am using v$ table
    select * from v$resource_limit where resource_name like '%proces%' ;

    RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
    ------------------------------ ------------------- --------------- ---------- ----------
    processes 226 257 2500 2500
    select ROUND(TO_CHAR((CURRENT_UTILIZATION/LIMIT_VALUE)*100),0) from v$resource_limit where resource_name like '%proces%' and ROUND(to_number((CURRENT_UTILIZATION/LIMIT_VALUE)*100),0) >80;
    select ROUND(TO_CHAR((CURRENT_UTILIZATION/LIMIT_VALUE)*100),0) from v$resource_limit where resource_name like '%proces%' and ROUND(to_number((CURRENT_UTILIZATION/LIMIT_VALUE)*100),0) >80
    *
    ERROR at line 1:
    ORA-01722: invalid number
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You supply no information on the version of Oracle being used nor the RAC configuration in place. Depending on the release of Oracle there could be RAC-specific bugs affecting the query.

    Post the version and configuration, please.
     
  13. jhonnyrip

    jhonnyrip Active Member

    Messages:
    39
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    lansing
    if anybody interested , the following query is worked


    SELECT host_name,VALUE,count(*)
    FROM (SELECT b.host_name, c.VALUE
    FROM gv$session a,
    gv$instance b,
    (SELECT inst_id,TO_NUMBER (
    ROUND (
    TO_CHAR (
    (CURRENT_UTILIZATION / LIMIT_VALUE) * 100),
    0))
    VALUE
    FROM gv$resource_limit
    WHERE resource_name LIKE '%proces%') c
    WHERE c.VALUE >= 80 and a.inst_id = b.inst_id and b.inst_id = c.inst_id )
    GROUP BY host_name,VALUE

    /
     
    Sadik likes this.