Rule base at system level
July 27, 2009 - 12pm Central time zone
Reviewer: ilan from Israel
I am also DBA of a system very heavilly invested in rule base. We are currently in 9.2.0.8
Can I upgrade to 10 and stay in rule base?
Can I put optimizer_mode = rule in init.ora ?
as second best maybe put on logon trigger with alter session ?
Followup July 27, 2009 - 7pm Central time zone:
... Can I upgrade to 10 and stay in rule base? ...
not in a supported fashion, no.
can you put optimizer mode = rule, yes, will you be supported? no. should you upgrade to 10g? no.
July 28, 2009 - 2am Central time zone
Reviewer: ilan from Israel
Thank you. That puts to rest that debate.
Need urgent help
July 29, 2009 - 1pm Central time zone
Reviewer: Sandhya from UK
Hi Tom ,
I have 9.2.0.8 database. For one module performance problem , i have added one new index
(CLIENT_S9), which has changed explain plans for existing statements of old modules . Now these old
modules are performing worst and only one new module is performing good. With and without new index
explain plans for old statements is as below .
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1656 | 16 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLIENT | 3 | 192 | 2 |
| 2 | NESTED LOOPS | | 23 | 1656 | 16 |
| 3 | INDEX RANGE SCAN | ES_COMMAND_TTW_S0 | 7 | 56 | 2 |
| 4 | INDEX RANGE SCAN | CLIENT_S9 | 16493 | | 1 |
----------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Note: cpu costing is off, PLAN_TABLE' is old version
12 rows selected.
SQL> explain plan for SELECT /*+ NO_INDEX(c CLIENT_S9) */ c.cln_no, c.cln_surname, c.cln_forename,
c.cln_sex, c.cln_birth_date, c.cln_nino, c.cln_inctv_fg,
2 c.cln_dup_ind, nvl(c.cln_cms_no,:"SYS_B_0"), c.ecm_id, c.cln_signing_day, c.cln_claim_cycle
FROM client c, es_command_ttw t WHERE cln_surname = :"SYS_B_1" AND c.cln_dup_ind = :"SYS_B_2"
AND c.ecm_id = t.ttw_ecm_id AND t.ecm_id = :"SYS_B_3";
Explained.
SQL> @?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1656 | 38 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLIENT | 3 | 192 | 6 |
| 2 | NESTED LOOPS | | 23 | 1656 | 38 |
| 3 | INDEX RANGE SCAN | ES_COMMAND_TTW_S0 | 7 | 56 | 2 |
| 4 | INDEX RANGE SCAN | CLIENT_S3 | 3 | | 2 |
----------------------------------------------------------------------------------
SQL> select dbms_metadata.get_ddl('INDEX','CLIENT_S3') from dual;
DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------
CREATE INDEX "MASTER"."CLIENT_S3" ON "MASTER"."CLIENT" ("ECM_ID", "CLN_SURNAME
", "CLN_FORENAME", "CLN_DUP_IND")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "LMS_CLIENT_IND"
SQL> select dbms_metadata.get_ddl('INDEX','CLIENT_S9') from dual;
DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------
CREATE INDEX "MASTER"."CLIENT_S9" ON "MASTER"."CLIENT" ("ECM_ID", "CLN_FND_IND
")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 2 BUFFER_POOL DEFAULT)
TABLESPACE "LMS_CLIENT_IND"
My question is, which factors do we need to consider while tuning statements? Why cost of
statement is less, still its performing worst ? I think cost gets calculated based on number of
disk reads and cpu? then why my newly created index caused such performance downgrade.
Regards
Sandhya G
Followup August 3, 2009 - 4pm Central time zone:
who or what is "UR" - an ancient city? http://en.wikipedia.org/wiki/Ur
cursor sharing, you have bigger fish to fry first - why wouldn't you fix your bug (no binds when you should bind). You have a massive security hole there, aren't you concerned about that?????
I would first check my statistics, looks like they might be super current on this new index and not so on the table and old index.
|