Solved

Oracle - index or not indexed after review db stats

Posted on 2009-05-07
16
1,019 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 400 total points
Comment Utility
====>  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
Comment Utility
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
Comment Utility
Average Number of sorted Rows pro Sort Operation = 59
0
 
LVL 47

Accepted Solution

by:
schwertner earned 400 total points
Comment Utility
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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:isuhendro
Comment Utility
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
Comment Utility
increase pga_aggregate_target maybe help you.
0
 
LVL 47

Assisted Solution

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

Author Comment

by:isuhendro
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now