Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2013
    Posts
    9

    Unanswered: need help in sequence monitoring

    I am trying to make a check if sequence exceed your column precision..PLEASE I NEED YOUR ALL HELP IN CALCULATION PART WHERE IT IS CALCULATING PERCENTAGE..... OR IF YOU HAVE BETTER QUERY THAN THIS PLEASE LET ME KNOW.... OUR SEQUENCE HAS NAMING CONVENTION LIKE THIS "SEQ_<TABLE_NAME>" WHICH MAKES IT MUCH EASIER TO MONITOR RELATED TABLE WITH RELATED COLUMN....SEQUENCES ARE USING ON THE FIRST COLUMN WHICH ARE NUMBERS AND USING FOR ID COLUMN....I REALLY NEED IT ASAP......PLEASE LET ME KNOW IF I M DOING THIS CALCULATION IN A RITE WAY....IF NOT THEN PLEASE LET ME KNOW THAT HOW SHOULD I CALCULATE..I M SORRY IF I TYPED A LOT ...I M GETTING FRUSTRATED....THANKS IN ADVANCE....
    Select ds.sequence_name,
    dtc.table_name,
    dtc.column_name,
    ds.last_number,
    dtc.data_precision,
    round(ds.last_number/power(10,dtc.data_precision),2)*100||'%' percent_used
    from all_sequences ds,
    all_tab_columns dtc
    where ds.sequence_name like 'SEQ_'||trim(upper(dtc.table_name))
    order by percent_used desc, sequence_name;

    Result was:
    SEQUENCE_NAME TABLE_NAME
    ------------------------------ ------------------------------
    COLUMN_NAME LAST_NUMBER DATA_PRECISION PERCENT_USED
    ------------------------------ ----------- -------------- --------------------
    SEQ_INSTALLMENT GT_BILLINST_CALC_OUTPUT
    INSTALLMENT 4973770 ########## 4974%

    SEQ_CHARGE INSURANCE_CHARGES_SAVINGS_VT
    CHARGE 4901537 ########## 490% ------->SHOULD GIVE ME IN DECIMAL OR SOME AMOUNT OF PERCENTAGE WHICH WILL MAKE SENSE


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

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Shouldn't your percentage be gotten like this example:
    Code:
    select 457, 72, ( 72/457 ), round( ( 72/457 ),2 ) *100 as percentage_rounded, trunc( ( 72/457 ),2 ) *100 as percentage_truncated
     from  dual

  3. #3
    Join Date
    May 2013
    Posts
    9
    ?? I am asking that if I am doing anything wrong with calculation... because I am getting weird percent total

  4. #4
    Join Date
    May 2013
    Posts
    9
    I did it...instead of using data_precision I used data_length ..... I fixed this problem but can some one tell me how I can set up an alert with mailx utility....for example if I get percentage more 50% then send email alert.... Please let me know How can I do it??? Do I have to put loop ...

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do you know what a Cartesian Product is?
    It is what the posted SQL does since there is NO join between the two tables.

    Why are you doing calculations against strings & other non-numeric columns?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2013
    Posts
    9
    data_length and last_number are numeric column.....I am not data_precision column anymore since lots of tables have null values..... You are right about cartesian product...I am still in a process of joining two tables..... Can you tell me about sending alert I just posted earlier
    ???

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect that you are a minority of ONE that has ever considered such a monitor is needed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  SELECT data_length,
      2         Count(data_type)
      3  FROM   user_tab_columns
      4  WHERE  data_type = 'VARCHAR2'
      5* GROUP  BY data_length
    SQL> /
    
    DATA_LENGTH COUNT(DATA_TYPE)
    ----------- ----------------
             30                4
             25                7
             20                3
             35                2
             40                3
             10                4
             12                1
    
    7 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2013
    Posts
    9
    Lolzz..... Believe me I said the same thing to my manager..... I already have a select statement which is working fine but I am wondering how can I put alert....I mean if percentage is more then 60% then send an email alert....

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Enterprise Manager can do what you desire.

    In my opinion, the posted SQL does not satisfy the posted requirements.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by anacedent View Post
    do you know what a Cartesian Product is?
    It is what the posted SQL does since there is NO join between the two tables.

    Why are you doing calculations against strings & other non-numeric columns?
    Adding to the above quote, perhaps your query would need to look like this:
    Code:
      SELECT ds.sequence_name
           , dtc.table_name
           , dtc.column_name
           , ds.last_number
           , dtc.data_length
           , ROUND ( ds.last_number / POWER ( 10, LEAST(8,dtc.data_length)), 2) * 100 || '%' percent_used
        FROM all_sequences ds, all_tab_columns dtc
       WHERE ds.sequence_owner = 'MY_SCHEMA'  --<= Schema(s) to examine
         AND ds.sequence_name LIKE  'SEQ_' || dtc.table_name 
         AND dtc.owner = ds.sequence_owner
         AND dtc.column_name like '%ID' --<= Column(s) candidate for sequence
         AND dtc.data_type like 'NUMBER'
    ORDER BY percent_used DESC, sequence_name;
    /
    Now, you can use a customized version of your above modified query to create an event in EM "User-Defined metrics" to monitor the percentages resulting from that query.
    Good luck!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  12. #12
    Join Date
    May 2013
    Posts
    9
    Thanks LKbrwn_DBA............ Let me test it and I will let you know the results.

  13. #13
    Join Date
    May 2013
    Posts
    9
    It worked perfect ..... Is it possible instead of data_length I can grab max value from each column and compute that value with last_number of sequence ???
    e.g (ROUND ( ds.last_number / max (dtc.column_name)) * 100 || '%' percent_used) <--something like this.... Max function didn't work .... Please let me know
    Thanks

  14. #14
    Join Date
    May 2013
    Posts
    9
    Not MAX value I mean last value from each column

  15. #15
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by arsoo007 View Post
    Not MAX value I mean last value from each column
    Last value from each column will be more or less between sequence last value and sequence last value plus sequence "cache size".
    Means you would always get ~ 99%
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •