Home » RDBMS Server » Performance Tuning » Performance (Oracle 9i(9.2.0.1.0))
Performance [message #335983] Thu, 24 July 2008 05:35 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I have one query
select a.borrid, b.mdlid, d.stmtid, f.auditmethod_name||'  '||to_char(d.stmtdt) || ' [ ' || d.stmtperiod || 'M ]' as year, a.user_id, lpad(c.item_name, length(decode(c.item_name, 'DSCR (Mandatory)', 'DSCR', c.item_name))+((c.tr_lvl-1)*5), ' ') as item_name, a.value, a.item_id
from coaratios a, model b, coaitems c, statement d, coatree e, audit_method f
where
a.coaid = b.coaid and
a.coaid = c.coaid and
a.item_id = c.item_id and
a.tr_id = c.tr_id and
a.coaid = d.coaid and
a.stmtid = d.stmtid and
a.borrid = d.borrid and
a.user_id = d.user_id and
a.coaid = e.coaid and
a.tr_id = e.tr_id and
d.audit_method_id = f.id and
trim(lower(e.tr_name)) = 'key financial ratios'


This query is taking 0.797 seconds to execute.But After looking at the plan the table is doing a full table scan.How to overcome this full table scan
below is the explain plan
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=117 Card=52 Bytes=5928)

   1    0   HASH JOIN (Cost=117 Card=52 Bytes=5928)
   2    1     HASH JOIN (Cost=114 Card=70 Bytes=7210)
   3    2       HASH JOIN (Cost=91 Card=2485 Bytes=193830)
   4    3         TABLE ACCESS (FULL) OF 'MODEL' (Cost=2 Card=12 Bytes =48)

   5    3         HASH JOIN (Cost=88 Card=2485 Bytes=183890)
   6    5           TABLE ACCESS (FULL) OF 'COAITEMS' (Cost=11 Card=2011 Bytes=62341)

   7    5           HASH JOIN (Cost=72 Card=14947 Bytes=642721)
   8    7             TABLE ACCESS (FULL) OF 'COATREE' (Cost=2 Card=1 Bytes=22)

   9    7             TABLE ACCESS (FULL) OF 'COARATIOS'(Cost=68 Card =116094 Bytes=2437974)

  10    2       TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=13 Card=15911 Bytes=397775)

  11    1     TABLE ACCESS (FULL) OF 'AUDIT_METHOD' (Cost=2 Card=3 Bytes=33)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        932  consistent gets
       2869  physical reads
          0  redo size
    4383378  bytes sent via SQL*Net to client
      85573  bytes received via SQL*Net from client
       7736  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     116017  rows processed



Any Help

Regards
Re: Performance [message #336009 is a reply to message #335983] Thu, 24 July 2008 07:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Full table scan is not always bad.
Collect proper stats on tables and indexes and let CBO do its job.
>>This query is taking 0.797 seconds
That is not bad. Right?
Re: Performance [message #336013 is a reply to message #336009] Thu, 24 July 2008 07:08 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear mahesh

As you said Collect proper stats on tables and indexes and let CBO do its job.Could you please tell me how to collect the stats and how shall I do let CBO do its JOB.Any brief?

Regards
Re: Performance [message #336015 is a reply to message #336013] Thu, 24 July 2008 07:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Search the docs / google for
dbms_stats.gather_schema_stats / dbms_stats.gather_table_stats and use it with CASACADE=>TRUE.

Re: Performance [message #336018 is a reply to message #336015] Thu, 24 July 2008 07:17 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Mahesh

Now consider query
This query is also taking 2.75 seconds.I wanted the query to execute in 0.15 seconds

select l.borrid, l.mdlid, l.year, l.user_id, l.prmname, l.value, to_char(m.score) as marks, l.roword
from
(
  select a.borrid, b.mdlid, to_number(to_char(d.stmtdt, 'yyyy'), '9999') as year, a.user_id, lpad(c.item_name, length(c.item_name)+((c.tr_lvl-1)*5), ' ') as prmname, to_char(a.value) as value, a.item_id as roword
  from coaratios a, model b, coaitems c, statement d, coatree e, audit_method f
  where
  a.coaid = b.coaid and
  a.coaid = c.coaid and
  a.item_id = c.item_id and
  a.tr_id = c.tr_id and
  a.coaid = d.coaid and
  a.stmtid = d.stmtid and
  a.borrid = d.borrid and
  a.user_id = d.user_id and
  a.coaid = e.coaid and
  a.tr_id = e.tr_id and
  d.audit_method_id = f.id and
  trim(lower(e.tr_name)) = 'key financial ratios'
) l,
(
  select distinct x.borrid, x.mdlid, x.year, x.user_id, x.ratioid, x.score from rat_calc_finratio x
) m
where l.borrid = m.borrid (+) and
l.year = m.year (+) and
l.mdlid = m.mdlid (+) and
l.user_id = m.user_id (+) and
l.roword = m.ratioid (+)
union all
select a.borrid, a.mdlid, a.year, a.user_id, substr(b.prmname, instr(b.prmname, '- ', 0, 1)+2) as prmname, d.attribute as value, TO_CHAR(a.value) as Marks, a.prmid as roword
from subjective_prm_trans a, mdl_parameter_tree b, mdl_parameter_tree c, mdl_grading_details d
where a.mdlid = b.mdlid and
a.prmid = b.prmid and
b.mdlid = c.mdlid and
b.parentid = c.prmid and
a.mdlid = d.mdlid and
a.gddid = d.gddid and
trim(lower(c.prmname)) = 'frs'

Below is the execution plan

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=843 Card=322 Bytes=145624)

   1    0   UNION-ALL
   2    1     HASH JOIN (OUTER) (Cost=472 Card=52 Bytes=112684)
   3    2       VIEW (Cost=116 Card=52 Bytes=108628)
   4    3         HASH JOIN (Cost=116 Card=52 Bytes=5356)
   5    4           HASH JOIN (Cost=113 Card=52 Bytes=5148)
   6    5             HASH JOIN (Cost=110 Card=70 Bytes=6790)
   7    6               HASH JOIN (Cost=88 Card=2485 Bytes=183890)
   8    7                 TABLE ACCESS (FULL) OF 'COAITEMS' (Cost=11 Card=2011 Bytes=62341)

   9    7                 HASH JOIN (Cost=72 Card=14947 Bytes=642721)
  10    9                   TABLE ACCESS (FULL) OF 'COATREE' (Cost=2 Card=1 Bytes=22)

  11    9                   TABLE ACCESS (FULL) OF 'COARATIOS' (Cost=68 Card=116094 Bytes=2437974)

  12    6               TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=13 Card=15911 Bytes=365953)

  13    5             TABLE ACCESS (FULL) OF 'AUDIT_METHOD' (Cost=2 Card=3 Bytes=6)

  14    4           TABLE ACCESS (FULL) OF 'MODEL' (Cost=2 Card=12 Bytes=48)

  15    2       VIEW (Cost=325 Card=55820 Bytes=4353960)
  16   15         SORT (UNIQUE) (Cost=325 Card=55820 Bytes=1116400)
  17   16           TABLE ACCESS (FULL) OF 'RAT_CALC_FINRATIO' (Cost=51 Card=55820 Bytes=1116400)

  18    1     HASH JOIN (Cost=371 Card=270 Bytes=32940)
  19   18       TABLE ACCESS (FULL) OF 'MDL_GRADING_DETAILS' (Cost=7 Card=2985 Bytes=41790)

  20   18       HASH JOIN (Cost=361 Card=3731 Bytes=402948)
  21   20         HASH JOIN (Cost=7 Card=12 Bytes=1020)
  22   21           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=9 Bytes=369)

  23   21           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1099 Bytes=48356)

  24   20         TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=348 Card=378290 Bytes=8700670)





Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
       6477  consistent gets
      11752  physical reads
          0  redo size
    6312723  bytes sent via SQL*Net to client
     105780  bytes received via SQL*Net from client
       9573  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     143577  rows processed

Now How I should increase the performance?

Regards
Re: Performance [message #336213 is a reply to message #335983] Fri, 25 July 2008 04:15 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Any update on the above

Regards
Re: Performance [message #336256 is a reply to message #336213] Fri, 25 July 2008 07:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Predicates such as
trim(lower(e.tr_name)) = 'key financial ratios'
and
trim(lower(c.prmname)) = 'frs'
prevent index scans.

Do a search on Function Based Indexes.

Ross Leishman
Re: Performance [message #336779 is a reply to message #336256] Tue, 29 July 2008 00:02 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Do you wanted me to apply function based index on these columns like
trim(lower(e.tr_name)) = 'key financial ratios'

and

trim(lower(c.prmname)) = 'frs'


Regards

[Updated on: Tue, 29 July 2008 00:03]

Report message to a moderator

Re: Performance [message #337101 is a reply to message #336779] Tue, 29 July 2008 22:09 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's something you could try.

Ross Leishman
Previous Topic: Stale Statisics
Next Topic: Reverse key index
Goto Forum:
  


Current Time: Fri Dec 02 16:48:45 CST 2016

Total time taken to generate the page: 0.17660 seconds