Home » RDBMS Server » Server Administration » utlrp - many invokes different results :/
utlrp - many invokes different results :/ [message #629232] Thu, 04 December 2014 15:20 Go to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
Hi All,

could you explain me one strange thing which I am experiencing with this script?
I have database with TS importing which is reconnected every day by bash scripts.
At the end of the script I am invoking utlrp.sql script to compile all invalid objects after TS importing.
Since some time utlrp is not able recompile objects from the schema resides in early imported Tablespace. The problem is that when I am trying compile them manually (alter procedure xx compile) everyting works fine.

Here is part of my bash code:

  @?/rdbms/admin/utlrp.sql
        @?/rdbms/admin/utlrp.sql
        @?/rdbms/admin/utlrp.sql


so I am invoking 3 times utlrp.sql in it and here is output:



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2014-12-04 21:48:44


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2014-12-04 21:49:21


PL/SQL procedure successfully completed.


OBJECTS WITH ERRORS
-------------------
                 76


ERRORS DURING RECOMPILATION
---------------------------
                         26


PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2014-12-04 21:49:29


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2014-12-04 21:49:44


PL/SQL procedure successfully completed.


OBJECTS WITH ERRORS
-------------------
                 85


ERRORS DURING RECOMPILATION
---------------------------
                         26


PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2014-12-04 21:49:45


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2014-12-04 21:50:00


PL/SQL procedure successfully completed.


OBJECTS WITH ERRORS
-------------------
                 86


ERRORS DURING RECOMPILATION
---------------------------
                         26


... so why I am receiving different results after each execution and (which wonders the most) after last execution I still have invalidated objects.
After this script completed I entered to databse and executed utlrp.sql manually and all invalidated object have been validated.

Could you help me understand this?

thanks in advance.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629234 is a reply to message #629232] Thu, 04 December 2014 15:46 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
.. of course I can try EXEC DBMS_UTILITY.compile_schema(schema => 'SCHEMA'); but this is not the point. I want understand why:

1. when I am invoking utlrp.sql 3 times in row by bash script (from cron) I am receiving diffenent invalidated objects amount afeter each execution completes? ( i would bet that if I would invoke 5 more times this script it would give me different results after each execution, and at the end I would stay with some objects not validated).
2. why its not able compile them?
3. when I am invoking it manually - it compiles (validates) all procedures. why?

thanks in advance.

[Updated on: Thu, 04 December 2014 15:48]

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629246 is a reply to message #629234] Fri, 05 December 2014 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

1. It depends what you do in the script
2. It depends what you do in the script
3. Because all what has to be done to compile is now done.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629248 is a reply to message #629246] Fri, 05 December 2014 01:04 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
Michel,

What do you mean "It depends what you do in the script"?
I just invoke utlrp.sql and I am expecting that it will "do the work", but it dont.

If you mean my bash script I can only say that those 3 lines where I am invoking utlrp.sql are last one, and nothing more is invoked afterwards (its last three lines).

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629251 is a reply to message #629248] Fri, 05 December 2014 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
If you mean my bash script I can only say that those 3 lines where I am invoking utlrp.sql are last one, and nothing more is invoked afterwards (its last three lines).


You know this we don't, we have not the script.

In addition, we have not your Oracle version.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629252 is a reply to message #629251] Fri, 05 December 2014 01:27 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
Michel,

Oracle RDBMS 10.2.0.5 (Solaris)
Script is here (nothing special) - last script in the chain of scripts which I am invoking.

#!/bin/ksh


sqlplus -s /nolog <<-EOF
        CONN / AS SYSDBA
        DROP INDEX newschema.itest3;
        DROP INDEX newschema.idx_historical;
        BEGIN
        FOR r in (SELECT mview_name FROM  dba_mviews WHERE mview_name LIKE 'MV_newschemaviews%' AND owner='newschema')
        LOOP
                EXECUTE IMMEDIATE 'drop snapshot newschema.'||r.mview_name;
        END LOOP;
        END;
        /

        TRUNCATE TABLE systbs.t_logs;
        @?/rdbms/admin/utlrp.sql
        @?/rdbms/admin/utlrp.sql
        @?/rdbms/admin/utlrp.sql
EOF

echo "Post refresh finished"

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629256 is a reply to message #629252] Fri, 05 December 2014 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

utlrp.sql uses UTL_RECOMP which uses DBMS_RANDOM, so the result (when not 0) may vary at each executions.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629258 is a reply to message #629256] Fri, 05 December 2014 01:53 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
thanks Michel - this is kind of hint for me.

but does this matter in my case?

do you mean this parameter?

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.


should I provide 1 instead of 0 (default) parameter to the script when its executed?

[Updated on: Fri, 05 December 2014 01:54]

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629259 is a reply to message #629258] Fri, 05 December 2014 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You can try it but I wonder if it will be not the same thing and I advise you to try the following:
- copy utlprp.sql in a new script, say utlsrp.sql
- in it remove "threads pls_integer := &&1;" and replace "recomp_parallel(threads)" by "RECOMP_SERIAL"
but this is not guaranteed.
Tell us he result.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629260 is a reply to message #629259] Fri, 05 December 2014 02:29 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
I am thinking about one more modification.
Because I think that maybe 'execute immediate...' delays some thing (becuse its working in background). Maybe something is not finally completed on database level (and its processed in the background) and as the result, even if I am invoking utlrp.sql, some procedures get invalidated because 'execute immediate...' works in background.
I am thinking of splitting this on two.
first part - execute immediate
than wait 120 s
and second part - invoking utlrp.sql

Michel, do you thing this make sense for you and its explainable this way?

  

sqlplus -s /nolog <<-EOF
        CONN / AS SYSDBA
        DROP INDEX newschema.itest3;
        DROP INDEX newschema.idx_historical;
        @?/rdbms/admin/utlrp.sql
        BEGIN
        FOR r in (SELECT mview_name FROM  dba_mviews WHERE mview_name LIKE 'MV_newschemaviews%' AND owner='newschema')
        LOOP
                EXECUTE IMMEDIATE 'drop snapshot newschema.'||r.mview_name;
        END LOOP;
        END;
        /

        TRUNCATE TABLE systbs.t_logs;
      
EOF

sleep 120

sqlplus -s /nolog <<-EOF
  @?/rdbms/admin/utlrp.sql
  @?/rdbms/admin/utlrp.sql
EOF

[Updated on: Fri, 05 December 2014 02:33]

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629261 is a reply to message #629260] Fri, 05 December 2014 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

There is a prerequisite to use UTL_RECOMP package (and so utlrp.sql script), see Operational Notes aboutt he package:

Quote:
There should be no other DDL on the database while running entries in this package. Not following this recommendation may lead to deadlocks.


Execute immediate does not work in background, it works in the (Oracle) session that execute it.
So in the posted script you don't need to wait as when sqlplus is exited then execute immediate statements are completed.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629264 is a reply to message #629261] Fri, 05 December 2014 02:59 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
so I have still no idea why such situation happens in my db.
I have modified scripts and put sleeps sections otherwise. 120 secounds of delay.

The only reasonable explanation of my issue is that database is doing something in the background, not everyting is yes compiled, and invoking utlrp is to early. I know that this sound not logical, but I can't find other cause.

....
sleep 120

sqlplus -s /nolog <<-EOF
        CONN / AS SYSDBA
        @?/rdbms/admin/utlrp.sql
EOF

sleep 120

sqlplus -s /nolog <<-EOF
        CONN / AS SYSDBA
        EXEC DBMS_UTILITY.compile_schema(schema => 'NEWSCHEMA');
EOF

[Updated on: Fri, 05 December 2014 02:59]

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629266 is a reply to message #629264] Fri, 05 December 2014 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
so I have still no idea why such situation happens in my db

Quote:
utlrp.sql uses UTL_RECOMP which uses DBMS_RANDOM, so the result (when not 0) may vary at each executions.


Try the alternative I posted.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629267 is a reply to message #629266] Fri, 05 December 2014 03:06 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
I will try after my last new approach to this problem.
Thanks Michel.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629602 is a reply to message #629267] Fri, 12 December 2014 02:04 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
The problem was not on DB side, but on application side.

From ApplicationServer there were invoked scripts after every time when TTS has been connected - and I didn't know that.
Application scripts contain DDLs, and some other commands and (because they was bad written) this caused objects invalidation in other places.

Problem solved.

Thanks Michel.

Report message to a moderator

Re: utlrp - many invokes different results :/ [message #629603 is a reply to message #629602] Fri, 12 December 2014 02:07 Go to previous message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Previous Topic: Lost ASH report
Next Topic: error in view while moving schema from db1 to db2
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 02 17:32:49 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.