Home » RDBMS Server » Performance Tuning » Why my function based did not use? (Oracle 11gr2, any platform)
Why my function based did not use? [message #664141] Wed, 05 July 2017 05:03 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear,

I have a partitioned table, named as TBL_MO_LOG, the information as following:

1. Table description
smsgwadm@SMPPGW> desc smsgw.tbl_mo_log
 Name                                                                             Null?    Type
 -------------------------------------------------------------------------------- -------- ------------------------------------------------------
 ID                                                                               NOT NULL NUMBER
 MSISDN                                                                                    VARCHAR2(20)
 SERVICE_ID                                                                                VARCHAR2(50)
 COMMAND_CODE                                                                              VARCHAR2(50)
 DATE_CREATE                                                                               DATE
 SENT_TIME                                                                                 DATE
 INFO                                                                                      VARCHAR2(2000)
 RETRY_SEND                                                                                NUMBER
 CP_NAME                                                                                   VARCHAR2(200)
 SERVICE_NAME                                                                              VARCHAR2(200)
 PACKAGE_CODE                                                                              VARCHAR2(200)
 PACKAGE_PRICE                                                                             NUMBER
 MO_URL_FW                                                                                 VARCHAR2(500)
 MO_URL_DESTINATION                                                                        VARCHAR2(500)

ID is PK column.

2. Rows
smsgwadm@SMPPGW> select count(*) from smsgw.tbl_mo_log;

3. Local partitioned function based index was created
smsgwadm@SMPPGW> Create index smsgw.indx_molog_senttime on smsgw.tbl_mo_log(to_char(sent_time)) local(
  2  PARTITION MO_LOG_P201703 tablespace SMPPGWINDX,
  3  PARTITION MO_LOG_P201704 tablespace SMPPGWINDX,
  4  PARTITION MO_LOG_P201705 tablespace SMPPGWINDX,
  5  PARTITION MO_LOG_P201706 tablespace SMPPGWINDX,
  6  PARTITION MO_LOG_P201707 tablespace SMPPGWINDX,
  7  PARTITION MO_LOG_P201708 tablespace SMPPGWINDX,
  8  PARTITION MO_LOG_P201709 tablespace SMPPGWINDX,
  9  PARTITION MO_LOG_P201710 tablespace SMPPGWINDX,
 10  PARTITION MO_LOG_P201711 tablespace SMPPGWINDX,
 11  PARTITION MO_LOG_P201712 tablespace SMPPGWINDX)
 12  /

Index created.

4. Gather table, index statistics
smsgwadm@SMPPGW> EXEC DBMS_STATS.gather_index_stats('SMSGW','INDX_MOLOG_SENTTIME');

smsgwadm@SMPPGW> begin
  2     dbms_stats.gather_table_stats (
  3        ownname    => 'SMSGW',
  4        tabname    => 'TBL_MO_LOG',
  5        method_opt => 'for all columns size skewonly for columns (to_char(sent_time))'
  6  );
  7  end;
  8  /

PL/SQL procedure successfully completed.

5. Verify query_* parameter
smsgwadm@SMPPGW> show parameter query

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
query_rewrite_enabled                string                           TRUE
query_rewrite_integrity              string                           enforced

6. Generate explain plan
smsgwadm@SMPPGW> select * from smsgw.tbl_mo_log where sent_time=to_date('03-07-2017 03:03:03','dd-mm-yyyy hh24:mi:ss');

Execution Plan
----------------------------------------------------------
Plan hash value: 2349454200

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |    76 |  6975   (1)| 00:01:24 |       |       |
|   1 |  PARTITION RANGE ALL|            |     1 |    76 |  6975   (1)| 00:01:24 |     1 |    10 |
|*  2 |   TABLE ACCESS FULL | TBL_MO_LOG |     1 |    76 |  6975   (1)| 00:01:24 |     1 |    10 |
--------------------------------------------------------------------------------------------------

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

   2 - filter("SENT_TIME"=TO_DATE(' 2017-07-03 03:03:03', 'syyyy-mm-dd hh24:mi:ss'))

Well, TAF appeared,

7. How many rows in sent_time?
smsgwadm@SMPPGW> select count(sent_time) from smsgw.tbl_mo_log;

COUNT(SENT_TIME)
----------------
         2510291

8. Did any null rows exists in sent_time?
smsgwadm@SMPPGW> select * from smsgw.tbl_mo_log where sent_time is null;

no rows selected

9. Force to use index hint
smsgwadm@SMPPGW> select /*+ INDEX smsgw.indx_molog_senttime*/ id, msisdn, service_id from smsgw.tbl_mo_log where to_char(sent_time,'dd-mm-yyyy hh24:mi:ss')='03-03-2017 03:03:03'
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2349454200

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            | 25092 |   808K|  7012   (1)| 00:01:25 |       |       |
|   1 |  PARTITION RANGE ALL|            | 25092 |   808K|  7012   (1)| 00:01:25 |     1 |    10 |
|*  2 |   TABLE ACCESS FULL | TBL_MO_LOG | 25092 |   808K|  7012   (1)| 00:01:25 |     1 |    10 |
--------------------------------------------------------------------------------------------------

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

   2 - filter(TO_CHAR(INTERNAL_FUNCTION("SENT_TIME"),'dd-mm-yyyy hh24:mi:ss')='03-03-2017
              03:03:03')


What did I forget anything, may you clarify more?

Thank you!

[Updated on: Wed, 05 July 2017 05:05]

Report message to a moderator

Re: Why my function based did not use? [message #664142 is a reply to message #664141] Wed, 05 July 2017 05:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your index needs to be on the column itself, not a function based index on to_char of the column.
Re: Why my function based did not use? [message #664143 is a reply to message #664141] Wed, 05 July 2017 05:11 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
dbms_stats.gather_table_stats (
ownname => 'SMSGW',
tabname => 'TBL_MO_LOG',
method_opt => 'for all columns size 1',
cascade => TRUE,
no_validate =>FALSE);
Re: Why my function based did not use? [message #664144 is a reply to message #664143] Wed, 05 July 2017 05:12 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Why would you want to destroy the histograms by specifying 'for all columns size 1' ?
Re: Why my function based did not use? [message #664146 is a reply to message #664144] Wed, 05 July 2017 05:17 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
As the index on date/time column, so, do we really need histogram? Moreover, I have seen in most of the cases gathering histogram causes problem- so, unless we are absolutely clear about our data, no point of gathering histogram. Without histogram, even skew data works not badly.
Re: Why my function based did not use? [message #664147 is a reply to message #664146] Wed, 05 July 2017 05:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I have never seen histograms cause problems. Completely the reverse: the more information you can give the optimizer, the better it will function. Do you have an example?

Note that your example destroys ALL the histograms, not just one.
Re: Why my function based did not use? [message #664148 is a reply to message #664147] Wed, 05 July 2017 05:26 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
I know, collecting histogram is debatable - but, I would not bother if without histogram works fine and meets SLA of my queries as not collecting histogram will save space and sys time.
Yes, it will remove histogram of all columns of the table
Re: Why my function based did not use? [message #664164 is a reply to message #664142] Wed, 05 July 2017 20:37 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
John Watson wrote on Wed, 05 July 2017 10:05
Your index needs to be on the column itself, not a function based index on to_char of the column.
This is my mistake, I recreated the index and it work well.

smsgwadm@SMPPGW> set linesize 150
smsgwadm@SMPPGW> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2744727136

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    76 |    23   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |                     |     1 |    76 |    23   (0)| 00:00:01 |     1 |    10 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TBL_MO_LOG          |     1 |    76 |    23   (0)| 00:00:01 |     1 |    10 |
|*  3 |    INDEX RANGE SCAN                | INDX_MOLOG_SENTTIME |     1 |       |    21   (0)| 00:00:01 |     1 |    10 |
--------------------------------------------------------------------------------------------------------------------------

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

   3 - access("SENT_TIME"=TO_DATE(' 2017-07-03 03:03:03', 'syyyy-mm-dd hh24:mi:ss'))

Thank you very much.

@biswachk: Thank you, I knew the stats of method option, however, I deny to destroy all of histogram, it is not SYS time story, it is the data working when meet many many complex queries.
Previous Topic: SQL hint between view & table
Next Topic: insert time is drastically reduced
Goto Forum:
  


Current Time: Fri Mar 29 00:44:28 CDT 2024