Home » RDBMS Server » Performance Tuning » Tuning options while using RBO (Oracle 10.2.0.3.0 AIX)
Tuning options while using RBO [message #580721] Wed, 27 March 2013 05:35 Go to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello

I am working on an assignement where client is using Oracle 10g but stuck to using RBO

Now the application team, from the GUI available to them build dynamic queries and some of them run very slow

Neither the code can not be changed to tune the queries nor do we get the exact step in the plan which is an issue (being RBO)

Fewer times we have executed SQL Tuning advisor on the selected queries and implemented the SQL profiles suggested which resolved issue for some of the queries

For some long running queries the Tuning advisor is not producing any recommendations

Another hurdle is that all the application users are using same application user id so we can not write a logon trigger to use CBO for some particular queries to see what is happening in the background!

Is there any other mechanism apart from this "SQL Tuning Advisor + SQL Profile" option in situation described above?

Thanks and Regards
sysdba007

Report message to a moderator

Re: Tuning options while using RBO [message #580727 is a reply to message #580721] Wed, 27 March 2013 09:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL Tuning Advisor & SQL Profile are useless if you use RBO, they assume you use CBO.
If you use RBO then you are stuck to RBO rules.

Regards
Michel

Report message to a moderator

Re: Tuning options while using RBO [message #580732 is a reply to message #580721] Wed, 27 March 2013 10:08 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
The best way is to tune your application for using CBO. If it isn't possible and your new queries run fast with CBO, you can use for ex. the hint all_rows.

Report message to a moderator

Re: Tuning options while using RBO [message #580745 is a reply to message #580732] Wed, 27 March 2013 12:53 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Thank you for the replies

@Michel isn't it the fact that if I run sql tuning advisor over a sql and create sql profile over it CBO is used for that particular statement which serves the purpose to some extent

Thanks and Regards
sysdba007

[Updated on: Wed, 27 March 2013 12:54]

Report message to a moderator

Re: Tuning options while using RBO [message #580749 is a reply to message #580745] Wed, 27 March 2013 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It depends on what you want in the end.
Use the RBO or not? If not what does mean "stuck to using RBO"?

Regards
Michel

Report message to a moderator

Re: Tuning options while using RBO [message #580797 is a reply to message #580749] Thu, 28 March 2013 05:23 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel

Michel said:Quote:

It depends on what you want in the end.

Query not taking 'much' execution time!

Michel said:Quote:

Use the RBO or not? If not what does mean "stuck to using RBO"?

As we know CBO mechanism is recommended by Oracle whereas as with some ideas our client chose to deploy their application with RBO
(I am sure you will agree, this can happen that our views and clients views may not match and sometimes we are not in position to pursuade the client)

sysdba007 said :Quote:

if I run sql tuning advisor over a sql and create sql profile over it CBO is used for that particular statement which serves the purpose to some extent


on that do you still insist following?; note that we have got resolved some performance issues using above technique
Michel said:[Quote:

SQL Tuning Advisor & SQL Profile are useless if you use RBO



I intend to use the dbms_advanced_rewrite.declare_rewrite_equivalence where I can force a particular SQL statement to use CBO using OPT_PARAM but as of now I am stuck on 2 points
1) writing a pl/sql block which will retrieve sql_text into a variable which I can use as source_stmt parameter in the dbms_advanced_rewrite.declare_rewrite_equivalence
2) CBO as a hint to include in OPT_PARAM; I know of CHOOSE, RULE but as of now not sure of CBO hint

Of courese I will need another hint of dynamic sampling over it as we don't gather stats being working under RBO

Could you please suggest on this option

Thanks and Regards
sysdba007

Report message to a moderator

Re: Tuning options while using RBO [message #580799 is a reply to message #580797] Thu, 28 March 2013 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I still don't understand what are your prerequisite and requirements.
Are you or not stuck with RBO? This means does RBO be mandatory be used or not?
If yes, use the rules for RBO optimization.
If not, then then gather statistics and let Oracle optimizer does its job.

Regards
Michel

[Updated on: Thu, 28 March 2013 05:27]

Report message to a moderator

Re: Tuning options while using RBO [message #580802 is a reply to message #580799] Thu, 28 March 2013 05:33 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel

RBO is mandatory for the system

However for tuning certain slow running / longrunning statements we have liberty to place some mechanism

Does this answer your question?

Not all the time we get 100% things in aboslute black and absolute white -- only RBO or only CBO

Even while using CBO i have seen queries with RULE hint has improved performance which might be incorrect in 'theory'!

Thanks and Regards
sysdba007

Report message to a moderator

Re: Tuning options while using RBO [message #580818 is a reply to message #580802] Thu, 28 March 2013 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
If yes, use the rules for RBO optimization.

So you can't use profile nor anything else that comes after the RBO.

Regards
Michel

Report message to a moderator

Re: Tuning options while using RBO [message #581210 is a reply to message #580818] Wed, 03 April 2013 07:23 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
In particular cases you can create stored outlines for the slow queries to use the indexes/access ways you want to force upon them for an execution plan that was proven to be fast (you can *check* this with Hints).

[Updated on: Wed, 03 April 2013 07:24]

Report message to a moderator

Re: Tuning options while using RBO [message #581881 is a reply to message #580721] Thu, 11 April 2013 10:08 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello

As mentioned above we have database with RBO as optimizer and want to force {rather pursuade Smile } queries to use CBO

I was trying to build a test case as following

The results show that creating sql profile manually shows some execution statistics details as in CBO but still did not used CBO
Am I missing something?
[1]
create table x as select * from dba_objects;
Table created

[2]
select /* pszope_txt */ object_name from x where object_id>12001 and object_id<12007;
no rows selected

[3]
select sql_text,sql_id from v$sql where sql_text like '%pszope_txt%';
select sql_id from v$sql where sql_text like '%pszope_txt%'
2a2y94xzw8y84

select /* pszope_txt */ object_name from x where object_id>12001 and object_id<12007
0vk9qn0z3gkwr



[4]
declare
sqltxt varchar2(4000);
begin
select sql_text into sqltxt from v$sql where sql_id='0vk9qn0z3gkwr';
dbms_sqltune.import_sql_profile(sql_text =>sqltxt, profile =>sqlprof_attr('OPT_PARAM(@"SEL$1" X@SEL$1 "OPTIMIZER_MODE,FIRST_ROWS_10")'),name =>'0vk9qn0z3gkwr', force_match => true);
end;
/


[5] Following are the results which show that creating sql profile manually shows some execution statistics details as in CBO but still did not used CBO
(in fact same PHV as well)

Am I missing anything in this? -> 
 sqlprof_attr('OPT_PARAM(@"SEL$1" X@SEL$1 "OPTIMIZER_MODE,FIRST_ROWS_10")')


Honestly speaking even if I am able to force a query to use CBO at least once I shall get the bottleneck or time/resource consuming step to work upon

(1) Execution plan details Before SQL Profile was created
(2) Execution plan details After SQL Profile was created

(1)
********************************************************************************************
Before SQL Profile was created
********************************************************************************************
SQL> select * from table(dbms_xplan.display_cursor('0vk9qn0z3gkwr',null,'ADVANCED'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  0vk9qn0z3gkwr, child number 0
-------------------------------------
select /* pszope_txt */ object_name from x where object_id>12001 and
object_id<12007

Plan hash value: 1157146653

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| X    |
----------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / X@SEL$1

Outline Data
-------------


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      OPT_PARAM('optimizer_index_caching' 100)
      RBO_OUTLINE
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "X"@"SEL$1")
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OBJECT_ID"<12007 AND "OBJECT_ID">12001))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------                                                                                                 -----------------------
Note
-----
   - rule based optimizer used (consider using cbo)


48 rows selected.


(2)
********************************************************************************************
After SQL Profile was created
********************************************************************************************
SQL> select * from table(dbms_xplan.display_cursor('0vk9qn0z3gkwr',null,'ADVANCED'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  0vk9qn0z3gkwr, child number 0
-------------------------------------
select /* pszope_txt */ object_name from x where object_id>12001 and
object_id<12007

Plan hash value: 1157146653

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| X    |
----------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / X@SEL$1

Outline Data
-------------


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      OPT_PARAM('optimizer_index_caching' 100)
      RBO_OUTLINE
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "X"@"SEL$1")
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OBJECT_ID"<12007 AND "OBJECT_ID">12001))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Note
-----
   - rule based optimizer used (consider using cbo)

SQL_ID  0vk9qn0z3gkwr, child number 1
-------------------------------------
select /* pszope_txt */ object_name from x where object_id>12001 and
object_id<12007

Plan hash value: 1157146653


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   968 (100)|          |
|*  1 |  TABLE ACCESS FULL| X    |    11 |   869 |   968   (0)| 00:42:40 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / X@SEL$1

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('optimizer_index_cost_adj' 1)
      OPT_PARAM('optimizer_index_caching' 100)
      OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
      FULL(@"SEL$1" "X"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OBJECT_ID">12001 AND "OBJECT_ID"<12007))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]

Note
-----
   - dynamic sampling used for this statement
   - SQL profile "0vk9qn0z3gkwr" used for this statement


96 rows selected.

SQL>

Thanks and Regards
sysdba007

Report message to a moderator

Re: Tuning options while using RBO [message #581898 is a reply to message #581881] Thu, 11 April 2013 12:33 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Try the following attribute

sqlprof_attr(q'[OPT_PARAM('OPTIMIZER_MODE', 'FIRST_ROWS_10')]')

[Updated on: Thu, 11 April 2013 12:34]

Report message to a moderator

Previous Topic: How to find out session consuming high resource in the past?
Next Topic: Avg time taken by an execution plan
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 18 03:35:21 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.