Solved

Oracle - index or not indexed after review db stats

Posted on 2009-05-07
16
1,035 Views
Last Modified: 2013-12-18
Dear experts,

I am troubleshooting a performance issue, and found out that it's due to one SQL statement that "sort by one column". Column name = CREATED and it is not indexed.

I've gathered the db stats to consider whether it should be index or not index. (DBA_IND_STATISTICS, DBA_INDEXES, DBA_TAB_HISTOGRAMS, DBA_TAB_COL_STATISTICS, DBA_TAB_STATISTICS, DBA_TABLES).

My question which stat above shall i refer to for index or not index decision, and how to interpret?

This below is from DBA_TAB_COL_STATISTICS
COLUMN_NAME = CREATED
NUM_DISTINCT = 6882134
LOW_VALUE = 77B40101010101
HIGH_VALUE = 786D041B043B20
DENSITY = 1.79E-07
NUM_NULLS = 0
NUM_BUCKETS = 254
LAST_ANALYZED = 4/27/2009 7:15
SAMPLE_SIZE = 4475391
GLOBAL_STATS = YES
USER_STATS = NO
AVG_COL_LEN = 8
HISTOGRAM = HEIGHT BALANCED

Thanks!!
0
Comment
Question by:isuhendro
  • 6
  • 5
  • 2
  • +3
16 Comments
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 400 total points
ID: 24334281
Also sort that cannot fit into RAM will have to be done in the TEMP tablespace, very slow with lots of disk I/O.

So increase SORT_AREA_SIZE  in SPFILE.

SELECT name,value
FROM v$parameter
WHERE name LIKE '%sort_area%';

NAME                        VALUE
--------------------------- -------
sort_area_size              512000
sort_area_retained_size     128000



Investigate the sort in Memory and on the Disk (Temporary tablespace)

SELECT  name, value
  FROM  v$sysstat
WHERE  name IN ('sorts (memory)',
        'sorts (disk)',
        'sorts (rows)')
ORDER BY statistic#;

Average Number of sorted Rows pro Sort Operation

SELECT TO_CHAR(ROUND(tot.value/(mem.value+dsk.value)))
  FROM v$sysstat tot, v$sysstat mem, v$sysstat dsk
WHERE tot.name = 'sorts (rows)'
   AND mem.name = 'sorts (memory)'
   AND dsk.name = 'sorts (disk)';

0
 
LVL 7

Expert Comment

by:Piloute
ID: 24334689
Hi,

For Oracle,  indexing = sorting.

In other words if you really wonder whether to index a column that you will use for sorting or not, just go for it. It will avoid you sorting operations.

Better, if you only need a few columns in your query, just create your index with those columns including all of them (and of course starting with the one you want to use for sorting). => Your select will only read the index.

Cheers,
P
0
 
LVL 7

Expert Comment

by:BobMc
ID: 24335227
Its likely that there are several ways to tune your system, the index option being one of them.

Can we see the query?
Also what version of Oracle are you using?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 400 total points
ID: 24335353
====>  For Oracle,  indexing = sorting.  <=======
This is not true.
There are simple and complex arguments.

The most simple argument is that CBO (The Optimizer) in many cases will avoid
the usage of the index (the select rows are more then 2-4 % of the rows in the table, the table is too small, index is with big depth due "lazy, logical" deletes, etc.)
The complex argument is e.g. Transparent Data Encrypted columns that are indexed.
Oracle up to 11g is not able and will not use thise kind of indexes (improved in Oracle 11g).

But sorting (ORDER BY clause) is unavoidable - there is no way to avoid it.
0
 

Author Comment

by:isuhendro
ID: 24336366
Hi Schwertner, thanks for very good suggestion. Herewith the data, however, it may reflect actual data as now is not peak time of usage

sort_area_size= 65536
sort_area_retained_size = 0
sorts (memory) = 689771128
sorts (disk) = 2819
sorts (rows) = 40423794298

Let me know if you have any comment? Too low size of sort area? The query that i caused troubleshooting will return around 8K of rows. It took more than 3 minutes.

Hi BobMC,
Below is the actual SQL, Oracle 10g

Thanks!!

SELECT /*+ ALL_ROWS */
      T23.CONFLICT_ID,
      T23.LAST_UPD,
      T23.CREATED,
      T23.LAST_UPD_BY,
      T23.CREATED_BY,
      T23.MODIFICATION_NUM,
      T23.ROW_ID,
      T19.SRV_REGN_ID,
      T10.X_UNIQUE_ID,
      T3.X_UNIQUE_ID,
      T23.X_ACTIVITY_RESULT,
      T23.X_TYPE_CATEGORY,
      T23.X_PRTNR_FLG,
      T7.DCP_ID,
      T23.APPT_DURATION_MIN,
      T23.APPT_REPT_FLG,
      T23.APPT_REPT_END_DT,
      T23.TODO_ACTL_END_DT,
      T23.TODO_ACTL_START_DT,
      T23.CAL_DISP_FLG,
      T23.ALARM_FLAG,
      T23.NAME,
      T22.NAME,
      T23.TARGET_OU_ID,
      T22.X_UNIQUE_ID,
      T7.ROW_ID,
      T1.ATTRIB_07,
      T21.NAME,
      T23.X_ONE_ON_ONE_EMAIL_FLAG,
      T23.OPTY_ID,
      T10.LOC,
      T23.SRA_SR_ID,
      T10.NAME,
      T23.TODO_PLAN_START_DT,
      T23.SRA_DEFECT_ID,
      T23.SRC_ID,
      T23.COST_CURCY_CD,
      T23.TARGET_PER_ID,
      T23.APPT_REPT_APPT_ID,
      T23.APPT_REPT_REPL_CD,
      T23.TODO_PLAN_END_DT,
      T23.PCT_COMPLETE,
      T23.DONE_FLG,
      T23.TEMPLATE_FLG,
      T23.PR_SYMPTOM_CD,
      T23.X_ORIGINATING_SYSTEM,
      T23.TARGET_OU_ADDR_ID,
      T23.TODO_AFTER_DT,
      T23.ACTIVITY_UID,
      T23.PROJ_ID,
      T13.NAME,
      T23.PR_ATT_ID,
      T23.X_GEC_TODO_CD,
      T8.SRC_NUM,
      T23.X_CRM_SRC_QT_NUM,
      T23.X_CRM_SRC_TYPE,
      T23.X_GEC_EVT_STAT_CD,
      T23.X_USER_GROUP,
      T23.PREV_ACT_ID,
      T23.APPT_START_DT,
      T23.APPT_ALARM_TM_MIN,
      T23.BILLABLE_FLG,
      T12.MKTG_OFFR_ID,
      T23.X_WAVE_ID,
      T23.AGREEMENT_ID,
      T23.OWNER_LOGIN,
      T23.OWNER_PER_ID,
      T4.X_EPRIME_FLG,
      T5.OWN_INST_ID,
      T5.INTEGRATION_ID,
      T13.PROJ_NUM,
      T10.BASE_CURCY_CD,
      T13.BL_CURCY_CD,
      T22.CURCY_CD,
      T14.ACTL_START_DT,
      T14.ACTL_END_DT,
      T14.PLAN_START_DT,
      T14.PLAN_END_DT,
      T13.BL_CLASS_CD,
      T23.PR_ORDER_ID,
      T23.CAL_TYPE_CD,
      T16.FORMAT_TYPE_CD,
      T3.FST_NAME,
      T3.LAST_NAME,
      T20.PIM_APPT_FLG,
      T20.UNSPRTD_REPT_FLG,
      T2.ACTIVE_FLG,
      T2.SRM_REQUEST_ID,
      T2.STAGE_ID,
      T20.SEBL_APPT_FLG,
      T23.X_REASON,
      T23.X_ACTIVITY_OUTCOME,
      T23.APPT_REPT_TYPE,
      T23.EVT_STAT_CD,
      T23.TODO_CD,
      T6.CONSUMER_FLG,
      T6.X_CRM_HPP_ID,
      T15.ROW_ID,
      T6.WORK_PH_NUM,
      T18.ROW_ID,
      T17.LOGIN,
      T9.ROW_STATUS,
      T2.ROW_ID,
      T2.PAR_ROW_ID,
      T2.MODIFICATION_NUM,
      T2.CREATED_BY,
      T2.LAST_UPD_BY,
      T2.CREATED,
      T2.LAST_UPD,
      T2.CONFLICT_ID,
      T2.PAR_ROW_ID,
      T1.ROW_ID,
      T1.PAR_ROW_ID,
      T1.MODIFICATION_NUM,
      T1.CREATED_BY,
      T1.LAST_UPD_BY,
      T1.CREATED,
      T1.LAST_UPD,
      T1.CONFLICT_ID,
      T1.PAR_ROW_ID,
      T16.ROW_ID,
      T16.PAR_ROW_ID,
      T16.MODIFICATION_NUM,
      T16.CREATED_BY,
      T16.LAST_UPD_BY,
      T16.CREATED,
      T16.LAST_UPD,
      T16.CONFLICT_ID,
      T16.PAR_ROW_ID,
      T5.ROW_ID,
      T5.PAR_ROW_ID,
      T5.MODIFICATION_NUM,
      T5.CREATED_BY,
      T5.LAST_UPD_BY,
      T5.CREATED,
      T5.LAST_UPD,
      T5.CONFLICT_ID,
      T5.PAR_ROW_ID,
      T20.ROW_ID,
      T20.PAR_ROW_ID,
      T20.MODIFICATION_NUM,
      T20.CREATED_BY,
      T20.LAST_UPD_BY,
      T20.CREATED,
      T20.LAST_UPD,
      T20.CONFLICT_ID,
      T20.PAR_ROW_ID,
      T15.ROW_ID,
      T9.ROW_ID,
      T18.ROW_ID,
      T11.ROW_ID,
      T11.MODIFICATION_NUM,
      T11.CREATED_BY,
      T11.LAST_UPD_BY,
      T11.CREATED,
      T11.LAST_UPD,
      T11.CONFLICT_ID,
      T11.CON_ID,
      T11.ACTIVITY_ID,
      T11.ACTIVITY_ID,
      T11.CON_ID
   FROM 
       S_EVT_ACT_X T1,
       S_EVT_MKTG T2,
       S_CONTACT T3,
       S_ORG_EXT_X T4,
       S_EVT_ACT_SS T5,
       S_CONTACT T6,
       S_SRC_DCP T7,
       S_SRC T8,
       S_ACT_EMP T9,
       S_ORG_EXT T10,
       S_ACT_CONTACT T11,
       S_DMND_CRTN_PRG T12,
       S_PROJ T13,
       S_PROJITEM T14,
       S_PARTY T15,
       S_EVT_MAIL T16,
       S_USER T17,
       S_PARTY T18,
       S_EMP_PER T19,
       S_EVT_CAL T20,
       S_DMND_CRTN_PRG T21,
       S_OPTY T22,
       S_EVT_ACT T23
   WHERE 
      T23.TARGET_OU_ID = T4.ROW_ID (+) AND
      T23.SRC_ID = T8.ROW_ID (+) AND
      T23.TARGET_OU_ID = T10.PAR_ROW_ID (+) AND
      T23.OPTY_ID = T22.ROW_ID (+) AND
      T23.TARGET_PER_ID = T3.PAR_ROW_ID (+) AND
      T23.PROJ_ID = T13.ROW_ID (+) AND
      T23.PROJ_ITEM_ID = T14.ROW_ID (+) AND
      T23.OWNER_PER_ID = T19.PAR_ROW_ID (+) AND
      T23.SRC_ID = T7.SRC_ID (+) AND
      T7.DCP_ID = T21.ROW_ID (+) AND
      T1.ATTRIB_07 = T12.ROW_ID (+) AND
      T23.ROW_ID = T2.PAR_ROW_ID (+) AND
      T23.ROW_ID = T1.PAR_ROW_ID (+) AND
      T23.ROW_ID = T16.PAR_ROW_ID (+) AND
      T23.ROW_ID = T5.PAR_ROW_ID (+) AND
      T23.ROW_ID = T20.PAR_ROW_ID (+) AND
      T23.TARGET_PER_ID = T15.ROW_ID (+) AND
      T23.TARGET_PER_ID = T6.PAR_ROW_ID (+) AND
      T23.OWNER_PER_ID = T9.EMP_ID (+) AND T23.ROW_ID = T9.ACTIVITY_ID (+) AND
      T9.EMP_ID = T18.ROW_ID (+) AND
      T9.EMP_ID = T17.PAR_ROW_ID (+) AND T11.ACTIVITY_ID = T23.ROW_ID AND
      ((T23.APPT_REPT_REPL_CD IS NULL) AND
      (T23.TEMPLATE_FLG != 'Y' AND T23.TEMPLATE_FLG != 'P' OR T23.TEMPLATE_FLG IS NULL) AND
      (T23.PRIV_FLG != :1 OR T23.PRIV_FLG IS NULL OR T23.OWNER_PER_ID = :2)) AND
      (T11.CON_ID = :3)
   ORDER BY
      T23.CREATED DESC

Open in new window

0
 

Author Comment

by:isuhendro
ID: 24336394
Average Number of sorted Rows pro Sort Operation = 59
0
 
LVL 47

Accepted Solution

by:
schwertner earned 400 total points
ID: 24336637
Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option.
Oracle recommends that you enable automatic sizing of SQL working areas by setting pga_aggregate_target instead.
SORT_AREA_SIZE is retained for backward compatibility.

Example (for very large RAMs!!! Not realistic!!! Only Example!):
alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2000000000;

Discussion:
http://www.dba-oracle.com/tips_pga_aggregate_target.htm

0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
ID: 24338857
You have 23 tables in the "from" clause!  (That's a lot!)  And you have all outer joins!  (Those are slower than standard, inner joins.)  And you have some "!=" conditions and some "or" conditions in the "where" clauses.  (These are very difficult for Oracle to optimize.)

And you wonder if you should index the column used for the "order by"?  Good question!  With all of the complexity that your query includes, an index on this columm may or may not help the query at all.  Indexes on the join columns (at least those with "=" conditions) will likely help much more than an index on the "created" column.

It looks to me like either the database was not well-designed for the business (or for the application) or this query is an attempt to answer a question the database was not designed to answer efficiently.
0
 

Author Comment

by:isuhendro
ID: 24343573
Hi markgeer, your comment is indeed make sense. But option to modify the query is difficult option. The sql is generate from siebel oracle product, after defining high level business logic, that most of them are vanilla. There are some existing indexes already in place.
0
 
LVL 1

Assisted Solution

by:Tomac_Yao
Tomac_Yao earned 50 total points
ID: 24346475
increase pga_aggregate_target maybe help you.
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 400 total points
ID: 24349374
Also try to assign (if Siebel will allow this) separate temporary tablespace for sorting.
0
 

Author Comment

by:isuhendro
ID: 24360713
dear experts,
thanks for your suggestions!
regarding pga size, herewith the current setting:
* aggregate PGA target parameter = 12.5G
* aggregate PGA auto target = 10 G

i am wondering, by increasing the value above
* i need to consider available memory hardware size, which may not be visible
* the query that i highlighted was executed by many users in concurrent, thus would not it cause memory starvation by adding aggregate PGA target?

sorry, i am not familiar with these kind of tuning.. schwertner, what do you mean by "separate temporary tablespace for sorting"?
0
 

Author Closing Comment

by:isuhendro
ID: 31579309
dear experts, thanks for all your suggestion.. it's a great help. however, frankly i still can't solve it.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24363572
There is a common temporary tablespace that is shared with the other
users of the instance. There are some tricks that begins with creating and assigning
to your sesion a separate temporary tablespace. An enhanced variant used by Genius Markgeer is
to place this tablespace in the RAM (RAM disk) - if you have enough RAM, of course.
 
0
 

Author Comment

by:isuhendro
ID: 24365195
Thanks again.
Fyi, i generate the explain plan, and the highest cost for this query is for SORT operation. I am expecting indexing "created" should help this case...
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 24365351
These PGA values are much larger than any I have ever seen:
* aggregate PGA target parameter = 12.5G
* aggregate PGA auto target = 10 G

How much RAM does this server have?  What is the SGA_MAX_SIZE?  Is this a 64-bit server?  (It must be with PGA values that high!)  Does this server have just one Oracle instance on it?
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question