Home » RDBMS Server » Server Administration » DBMS_UTILTY
DBMS_UTILTY [message #51366] Fri, 17 May 2002 17:46 Go to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
Is it possible that running this can cause problems for an application that generates it's own sql statements?? Here's what the statement should look like:

SELECT
ST_USERS.USERNAME,ST_USERS.FIRST_NAME,ST_USERS.LAST_NAMEST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME =
'CustomerCareRep_Level1' UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT
ST_USERS.FIRST_NAME,ST_USERS.LAST_NAME,
ST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME ='CustomerCareRep_Level2'

But, here is actually what is being generated and I'm trying to figure out if the DBA ran something that may have cause Oracle to change the query somehow.

SELECT
ST_USERS.FIRST_NAME,ST_USERS.LAST_NAME,ST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME =
'CustomerCareRep_Level1' UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT
ST_USERS.FIRST_NAME,ST_USERS.LAST_NAME,ST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME =
'CustomerCareRep_Level2'

The only thing that is different is it's excluding the st_users.username in the select.

Any thoughts?

Thanks
Tony
Re: DBMS_UTILTY [message #51367 is a reply to message #51366] Fri, 17 May 2002 18:06 Go to previous messageGo to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
This is what he ran:

EXECUTE SYS.DBMS_UTILITY.ANALYZE_SCHEMA(UPPER('SITE2'), 'ESTIMATE');

Thanks
Re: DBMS_UTILTY [message #51384 is a reply to message #51366] Mon, 20 May 2002 08:50 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
Yes it is possible. It depends on what optimization method you use. If you use Cost Based Optimization (CBO) you will want to analyze stats often to keep them up to date so it can CHOOSE the best access method. If you use Rule Based (RBO) you will not want to analyze stats on your tables/indexes. To check your mode do:

svrmgrl> show parameter optimzer_method

If it is CHOOSE you are using CBO. If not remove the statistics and run your query again.

SQL> exec dbms_utility.analyze_schema('SCHEMANAME','REMOVE');

I would suggest you search MetaLink on "optimizer_mode" and read up on this subject.
Re: DBMS_UTILTY [message #51386 is a reply to message #51384] Mon, 20 May 2002 09:55 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
If you use OPTIMIZR_MODE=CHOOSE and delete statistics you are using RULE based. If you analyze statistics you are using COST.
Previous Topic: dbms_sql package
Next Topic: Couldn't find the "Oracle Management Server" from control panel
Goto Forum:
  


Current Time: Wed Dec 25 09:40:45 CST 2024