FAQ
Hello.

My environment is HP UX 11i. DB Version 10.1.0.4. On this database I have
Oracle replication is in action, replicating certain tables to another
database. I have a very high occurance and time for the event
knpc_anq_AwaitNonemptyQueue.
I could not get much useful info on the event anywhere. Appears to be an
idle event. But, jsut wanted to make sure. If you have any info, pointer
etc. can you please let me know?

The event is reported against SYSTEM.DEF$_DESTINATION_PRIMARY. Total Wait
time is 10,933,039,621.

TIA,

Shiva

Search Discussions

  • Fairlie rego at Feb 20, 2007 at 11:48 pm
    IIRC I have seen this when the delay_seconds in the push call to dbms_defer_sys package had a large value and is not the default of 0.


    -Fairlie

    Shivaswamy Raghunath wrote:
    Hello.

    My environment is HP UX 11i. DB Version 10.1.0.4. On this database I have Oracle replication is in action, replicating certain tables to another database. I have a very high occurance and time for the event knpc_anq_AwaitNonemptyQueue. I could not get much useful info on the event anywhere. Appears to be an idle event. But, jsut wanted to make sure. If you have any info, pointer etc. can you please let me know?

    The event is reported against SYSTEM.DEF$_DESTINATION_PRIMARY. Total Wait time is 10,933,039,621.

    TIA,

    Shiva

    Fairlie Rego
    Senior Oracle Consultant
    http://www.linkedin.com/in/fairlierego


    http://el-caro.blogspot.com/
    M: +61 402 792 405




    Bored stiff? Loosen up...
    Download and play hundreds of games for free on Yahoo! Games.
  • Anurag Varma at Feb 22, 2007 at 4:14 am

    On 2/20/07, Shivaswamy Raghunath wrote:
    Hello.

    My environment is HP UX 11i. DB Version 10.1.0.4. On this database I have
    Oracle replication is in action, replicating certain tables to another
    database. I have a very high occurance and time for the event knpc_anq_AwaitNonemptyQueue.
    I could not get much useful info on the event anywhere. Appears to be an
    idle event. But, jsut wanted to make sure. If you have any info, pointer
    etc. can you please let me know?

    The event is reported against SYSTEM.DEF$_DESTINATION_PRIMARY. Total Wait
    time is 10,933,039,621.

    TIA,
    Shiva
    I had opened an SR about this long time back. I don't remember the exact
    reply but it was something to this effect:
    * Its an IDLE wait event. Can be ignored.
    * Its either what Fairlie mentions it to be or connected with the parallel
    push option. I remember when I changed
    push to serial (once a minute), the wait event had disappeared.
  • Cary Millsap at Feb 23, 2007 at 4:33 am
    Isn't it great the way Oracle tells people that things that consume
    end-user response time can be ignored?



    Cool, let's just call the users up and tell them the good news: they can
    ignore that particular part of what they had previously considered a
    performance problem.





    Cary Millsap

    Hotsos Enterprises, Ltd.

    http://www.hotsos.com

    Nullius in verba



    Hotsos Symposium 2007 / March 4-8 / Dallas

    Visit www.hotsos.com for curriculum and schedule details...

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Anurag Varma
    Sent: Wednesday, February 21, 2007 10:15 PM
    To: shivaswamykr_at_gmail.com
    Cc: Oracle-L Freelists
    Subject: Re: Wait Event





    On 2/20/07, Shivaswamy Raghunath wrote:

    Hello.

    My environment is HP UX 11i. DB Version 10.1.0.4. On this database I
    have Oracle replication is in action, replicating certain tables to
    another database. I have a very high occurance and time for the event
    knpc_anq_AwaitNonemptyQueue. I could not get much useful info on the
    event anywhere. Appears to be an idle event. But, jsut wanted to make
    sure. If you have any info, pointer etc. can you please let me know?

    The event is reported against SYSTEM.DEF$_DESTINATION_PRIMARY. Total
    Wait time is 10,933,039,621.

    TIA,

    Shiva

    I had opened an SR about this long time back. I don't remember the exact
    reply but it was something to this effect:
    * Its an IDLE wait event. Can be ignored.

    Its either what Fairlie mentions it to be or connected with the
    parallel push option. I remember when I changed
    push to serial (once a minute), the wait event had disappeared.
  • Anurag Varma at Feb 23, 2007 at 11:26 pm

    On 2/21/07, Anurag Varma wrote:

    On 2/20/07, Shivaswamy Raghunath wrote:

    Hello.

    My environment is HP UX 11i. DB Version 10.1.0.4. On this database I
    have Oracle replication is in action, replicating certain tables to another
    database. I have a very high occurance and time for the event knpc_anq_AwaitNonemptyQueue.
    I could not get much useful info on the event anywhere. Appears to be an
    idle event. But, jsut wanted to make sure. If you have any info, pointer
    etc. can you please let me know?

    The event is reported against SYSTEM.DEF$_DESTINATION_PRIMARY. Total
    Wait time is 10,933,039,621.

    TIA,
    Shiva
    I had opened an SR about this long time back. I don't remember the exact
    reply but it was something to this effect:
    * Its an IDLE wait event. Can be ignored.
    * Its either what Fairlie mentions it to be or connected with the parallel
    push option. I remember when I changed
    push to serial (once a minute), the wait event had disappeared.


    --
    Anurag Varma
    Just a quick correction... I went back to my old emails and I believe this
    wait event was connected
    to the delay_seconds parameter rather than the parallel push that I
    mentioned. In the v$session_wait, you'd
    see the seconds wait time match the delay seconds wait time.

    HTH

    Anurag
  • David Boyd at Mar 1, 2007 at 1:35 pm
    Hi All,

    We¡¯d like to block a column for some users, but open to other users. We
    don¡¯t care about row level security. If the user has the privilege to see
    the column data, she/he gets to see the data in the column for the entire
    table. Otherwise the column is null. I was wondering if any one uses VPD
    column masking in 10g to block columns. If you do, could you please share
    your code with me?

    Dave

    Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
    Intro*Terms
    https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117
  • Alexander Fatkulin at Mar 1, 2007 at 2:01 pm
    Dave, here is the straight forward example. Hope you'll get the
    feelings of what is it like to use column masking.

    SQL> create table col_masking
    2 (
    3 n number,
    4 data varchar2(100)
    5 );

    Table created.

    SQL> insert into col_masking
    2 select level, to_char(level)
    3 from dual
    4 connect by level <= 10;

    10 rows created.

    SQL> commit;

    Commit complete.

    Now we will show only rows with even N

    SQL> create function sec_function(
    2 p_owner in varchar2,
    3 p_object in varchar2
    4 ) return varchar2 is
    5 begin
    6 return 'mod(n,2)=0';
    7 end;
    8 /

    Function created.

    SQL> begin
    2 dbms_rls.add_policy(

    3 object_name => 'col_masking',
    4 policy_name => 'col_masking',
    5 policy_function => 'sec_function',

    6 sec_relevant_cols => 'data',
    7 sec_relevant_cols_opt => dbms_rls.ALL_ROWS
    8 );
    9 end;
    10 /

    PL/SQL procedure successfully completed.

    SQL> column data format a5
    SQL> select * from col_masking;

    N DATA

    ---------- -----

    1
    2 2
    3
    4 4
    5
    6 6
    7
    8 8
    9
    10 10

    10 rows selected.

    Alternatively, you can use views with the inline predicates.
    On 3/1/07, David Boyd wrote:
    Hi All,

    We¡¯d like to block a column for some users, but open to other users. We
    don¡¯t care about row level security. If the user has the privilege to see
    the column data, she/he gets to see the data in the column for the entire
    table. Otherwise the column is null. I was wondering if any one uses VPD
    column masking in 10g to block columns. If you do, could you please share
    your code with me?

    Dave

    _________________________________________________________________
    Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
    Intro*Terms
    https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117

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

    --
    Alexander Fatkulin,
    Senior Oracle DBA
    --
    http://www.freelists.org/webpage/oracle-l
  • Rajeev Prabhakar at Mar 1, 2007 at 2:09 pm
    David

    Have you already tried "DBMS_RLS.ADD_POLICY" and "EXEMPT ACCESS POLICY" ?

    http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/apdvcntx.htm#1014673

    -Rajeev
    On 3/1/07, David Boyd wrote:

    Hi All,

    We¡¯d like to block a column for some users, but open to other users. We
    don¡¯t care about row level security. If the user has the privilege to
    see
    the column data, she/he gets to see the data in the column for the entire
    table. Otherwise the column is null. I was wondering if any one uses VPD
    column masking in 10g to block columns. If you do, could you please share
    your code with me?

    Dave
    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Mar 1, 2007 at 2:22 pm
    One solution that doesn't involve VPD is to just create a view that doesn't
    include that column. Then grant access to the view to your restricted users
    and revoke their access to the base table. Of course, unless you have
    synonyms in place for this table, this would involve changing the code that
    accesses the table.
    On 3/1/07 7:35 AM, David Boyd wrote:

    Hi All,

    We¡¯d like to block a column for some users, but open to other users. We
    don¡¯t care about row level security. If the user has the privilege to see
    the column data, she/he gets to see the data in the column for the entire
    table. Otherwise the column is null. I was wondering if any one uses VPD
    column masking in 10g to block columns. If you do, could you please share
    your code with me?

    Dave

    _________________________________________________________________
    Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
    Intro*Terms
    https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=m
    ortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117

    --
    http://www.freelists.org/webpage/oracle-l
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
    (850) 478-8496 x2509
    jheinrich_at_pcci.edu
  • David Boyd at Mar 1, 2007 at 2:40 pm
    Jason,

    Thanks for your input. As you said we do have synonyms in place and don't
    want using views.

    Dave
    From: Jason Heinrich
    To:,
    Subject: Re: column masking in 10g
    Date: Thu, 01 Mar 2007 08:22:14 -0600

    One solution that doesn't involve VPD is to just create a view that doesn't
    include that column. Then grant access to the view to your restricted
    users
    and revoke their access to the base table. Of course, unless you have
    synonyms in place for this table, this would involve changing the code that
    accesses the table.
    Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
    Intro*Terms
    https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117
  • Don Seiler at Mar 1, 2007 at 3:26 pm
    Don't you need select privileges on the underlying tables of a view in
    order to select from the view?

    Don.
    On 3/1/07, Jason Heinrich wrote:
    One solution that doesn't involve VPD is to just create a view that doesn't
    include that column. Then grant access to the view to your restricted users
    and revoke their access to the base table. Of course, unless you have
    synonyms in place for this table, this would involve changing the code that
    accesses the table.
    --
    Don Seiler
    http://seilerwerks.blogspot.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Mar 1, 2007 at 3:41 pm
    It depends on who owns the view. If the table owner owns the view, then he
    can just grant select privilege to the view, and select privileges to the
    table are not necessary.
    On 3/1/07 9:26 AM, Don Seiler wrote:

    Don't you need select privileges on the underlying tables of a view in
    order to select from the view?

    Don.
    On 3/1/07, Jason Heinrich wrote:

    One solution that doesn't involve VPD is to just create a view that doesn't
    include that column. Then grant access to the view to your restricted users
    and revoke their access to the base table. Of course, unless you have
    synonyms in place for this table, this would involve changing the code that
    accesses the table.
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
    (850) 478-8496 x2509
    jheinrich_at_pcci.edu
  • Uwe Weber at Mar 1, 2007 at 2:49 pm
    Hi,

    David Boyd schrieb:
    We¡¯d like to block a column for some users, but open to other users.
    We don¡¯t care about row level security. If the user has the
    privilege to see the column data, she/he gets to see the data in the
    column for the entire table. Otherwise the column is null. I was
    wondering if any one uses VPD column masking in 10g to block columns.
    Which reasons do you have to not use a view?

    Regards,
    uwe
  • Dennis Williams at Mar 1, 2007 at 3:11 pm
    David,

    Call me old-fashioned (might as well, everybody else does),
    but have you considered a couple of simple views.
    One view offers the columns for one set of users.
    Another view sets certain columns to null.
    Synonyms as needed.
    Sometimes the simple solution is the best.

    Dennis Williams
    On 3/1/07, David Boyd wrote:

    Hi All,

    We¡¯d like to block a column for some users, but open to other users. We
    don¡¯t care about row level security. If the user has the privilege to
    see
    the column data, she/he gets to see the data in the column for the entire
    table. Otherwise the column is null. I was wondering if any one uses VPD
    column masking in 10g to block columns. If you do, could you please share
    your code with me?

    Dave

    _________________________________________________________________
    Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a
    month.
    Intro*Terms

    https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117

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

    --
    http://www.freelists.org/webpage/oracle-l
  • David Boyd at Mar 1, 2007 at 2:34 pm
    Alexander and Chris,

    Thanks very much for your quick response to share your code with me. What
    I'm looking for is the code that does not involve the row level security.
    Here is the example.

    SQL> create table col_masking
    2 (
    3 n number,
    4 data varchar2(100)
    5 );

    Table created.

    SQL> insert into col_masking
    2 select level, to_char(level)
    3 from dual
    4 connect by level <= 10;

    10 rows created.

    SQL> commit;

    Commit complete.

    SQL> select * from col_masking;

    /* for users that have privilege to see the column data*/

    N DATA

    ---------- -----

    1 1
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    8 8
    9 9
    10 10

    10 rows selected.

    /* for users that don't have privilege to see the column data*/

    N DATA

    ---------- -----

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    10 rows selected.

    Dave

    Play Flexicon: the crossword game that feeds your brain. PLAY now for FREE.
    http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline
  • Alexander Fatkulin at Mar 1, 2007 at 4:02 pm
    That isn't very different from my example. You only need to provide
    correct policy function,

    e.g.

    if (user in ('BOB'))
    then
    return null; --allow user to see content
    else
    return '1=0'; --do not show anything
    end if;
    On 3/2/07, David Boyd wrote:
    Alexander and Chris,

    Thanks very much for your quick response to share your code with me. What
    I'm looking for is the code that does not involve the row level security.
    Here is the example.

    SQL> create table col_masking
    2 (
    3 n number,
    4 data varchar2(100)
    5 );

    Table created.

    SQL> insert into col_masking
    2 select level, to_char(level)
    3 from dual
    4 connect by level <= 10;

    10 rows created.

    SQL> commit;

    Commit complete.

    SQL> select * from col_masking;

    /* for users that have privilege to see the column data*/

    N DATA
    ---------- -----
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    8 8
    9 9
    10 10

    10 rows selected.

    /* for users that don't have privilege to see the column data*/

    N DATA
    ---------- -----
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    10 rows selected.

    Dave

    _________________________________________________________________
    Play Flexicon: the crossword game that feeds your brain. PLAY now for FREE.
    http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline
    --
    Alexander Fatkulin,
    Senior Oracle DBA
    --
    http://www.freelists.org/webpage/oracle-l
  • David Boyd at Mar 1, 2007 at 5:00 pm
    Alexander,

    This works great for me. Thanks so much.

    Did you have experience on the performance between VPD and view?

    Dave
    From: "Alexander Fatkulin"
    To: "David Boyd"
    CC: oracle-l_at_freelists.org
    Subject: Re: column masking in 10g
    Date: Fri, 2 Mar 2007 02:02:29 +1000

    That isn't very different from my example. You only need to provide
    correct policy function,

    e.g.

    if (user in ('BOB'))
    then
    return null; --allow user to see content
    else
    return '1=0'; --do not show anything
    end if;
    Find a local pizza place, movie theater, and more�.then map the best route!
    http://maps.live.com/?icid=hmtag1&FORM=MGAC01
  • Alexander Fatkulin at Mar 2, 2007 at 3:07 am
    Dave, It depends.

    As long as you can get with simple SQL inline predicates the
    performance will virtually be the same

    e.g.

    create view v_t2 as

    select n, case mod(n, 2) when 0 then m else null end m
    from t2;

    will perform virtually the same (from both elapsed time / latches) as
    column masking using the policy function with the "return 'mod(n,2)=0'
    " predicate. Don't forget I'm talking about _static_ policies here,

    BUT

    as long as you start using PL/SQL functions in a view - the
    performance will significantly degrade (mostly due to context switches
    between engines). The subquery caching (don't forget to declare
    function as a deterministic) can help you but anyway - it has it's own
    limitations.

    As always - don't forget to test for your own specific cases.
    On 3/2/07, David Boyd wrote:
    Alexander,

    This works great for me. Thanks so much.

    Did you have experience on the performance between VPD and view?

    Dave
    --
    Alexander Fatkulin,
    Senior Oracle DBA
    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Mar 1, 2007 at 3:54 pm
    Good idea. The advantage to this approach is that all users are presented
    with the same view definition, so you don¹t have to deal with the missing
    column in any programs that access it.
    On 3/1/07 9:20 AM, Bill Ferguson wrote:

    You could also create a view with the CASE statement, similar to:

    CREATE OR REPLACE FORCE VIEW TEST1 ("COL_A", "COL_B", "COL_C") AS
    select col_a, col_b,
    case (user) when 'XYZ' then null
    else col_c end col_c
    from your_table;


    You can handle the "user" part various ways, have a table of restricted users,
    etc. to select from, just manually add their names to the list, etc. Or, you
    may have other things to check for as well.

    If the condition is met, col_c returns a null, if the condition is not met,
    then col_c returns the value for that column. I've tested this on 10gR2, I
    don't know how other versions will work.

    -- Bill Ferguson
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
    (850) 478-8496 x2509
    jheinrich_at_pcci.edu

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 20, '07 at 10:45p
activeMar 2, '07 at 3:07a
posts19
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase