> Do you tell us that explain says: Indexes will be used, but
> the indexes are not used?
That's the matter. Explain says that indexes will be used, but the
activity-monitor just mentions tons of table scans.
The current version is 7.5.0.19 on 32bit Linux.
This phenomenon happens with every statement, just an example here :
select customer.name, path, sum(quantity), sum(rebate)/100 from ecoupon,
salesreport, customer
where ecoupon.salesreport_uuid = salesreport.uuid and ecoupon.customer_uuid
= customer.uuid
and salesreport.reportdate = date
group by customer.name, path
Explain :
SALESREPORT SALESREPORT_REPORTDATE EQUAL CONDITION FOR INDEX
269
ECOUPON ECOUPON_SALESREPORT_UUID JOIN VIA INDEXED COLUMN
283996
CUSTOMER UUID JOIN VIA KEY COLUMN
26
TABLE HASHED
NO TEMPORARY RESULTS
CREATED
TEMPORARY RESULT TABLE SCAN
500
RESULT IS COPIED ,
COSTVALUE IS 6681
The Table 'salesreport' has indexes on 'reportdate', 'uuid' is the primary
key.
'ecoupon' has indexes on 'salesreport_uuid' (FK for 'salesreport'), same on
'customer_uuid', which is FK for 'customer'.
'customer' has the primary key 'uuid'
Optimizer Statistics are up to date.
Flo
> -----Urspr�ngliche Nachricht-----
> Von: Zabach, Elke [mailto:[EMAIL PROTECTED]
> Gesendet: Mittwoch, 15. Dezember 2004 15:45
> An: Florian Schmitz
> Betreff: AW: SDBUPD Error. cannot stat x_server althoug x_server was
> started sucessfully
>
>
> Hi,
>
> Please use a new 'Betreff'/'topic'-line. Noone of the
> optimization-guys will
> read topics named like yours.
>
> Do you tell us that explain says: Indexes will be used, but
> the indexes are not used?
>
> Please tell us which version (you see, I did not follow this
> topic, just saw the content of the mail by chance) you use,
> the statement, where there were parameters used in, the
> indexes known for the table(s), the explain-output.
>
> Do not send directly to me as I am not the optimization guy.
>
> Elke
> SAP Labs Berlin
>
> > -----Urspr�ngliche Nachricht-----
> > Von: Florian Schmitz [mailto:[EMAIL PROTECTED]
> > Gesendet: Mittwoch, 15. Dezember 2004 15:12
> > An: [EMAIL PROTECTED]
> > Betreff: AW: SDBUPD Error. cannot stat x_server althoug x_server was
> > started sucessfully
> >
> > Another strange fact :
> >
> > I've experimented with some statements now, every 'explain' produces
> > feasible results. (indexes are used, cost values are
> astonishing low), but
> > the execution takes up to 40x as long as on 7.3.
> > The activity-information-panel in the dmgui shows 73
> tablescans and 0
> > indexscans.
> >
> > Now i'm completly confused.
> >
> > Flo
> >
> > > -----Urspr�ngliche Nachricht-----
> > > Von: Florian Schmitz [mailto:[EMAIL PROTECTED]
> > > Gesendet: Mittwoch, 15. Dezember 2004 12:14
> > > An: [EMAIL PROTECTED]
> > > Betreff: AW: SDBUPD Error. cannot stat x_server althoug
> x_server was
> > > started sucessfully
> > >
> > >
> > > Hmm, i'm a bit disappointed now.
> > > We suffer big performance-loses after the migration.
> > > what confuses me :
> > > we got two data-volumes. on 7.3 both showed almost an equal
> > > distribution of
> > > i/o-load
> > > now, volume 1 is doing 99% of io's. (300k reads to 15(no k))
> > >
> > > Caches are well used (98 to 100%)
> > >
> > > I attached the current parameter. Perhaps anyone can have a
> > > look at the new
> > > 7.5 params.
> > >
> > > :-/
> > >
> > > Regards,
> > >
> > > Flo
> > > >>
> > > BACKUP_BLOCK_CNT 8
> > > _DELAY_LOGWRITER 0
> > > LOG_IO_QUEUE 50
> > > _TRANS_RGNS 8
> > > MP_RGN_LOOP 100
> > > MAXRGN_REQUEST 3000
> > > _DELAY_COMMIT NO
> > > _SVP_1_CONV_FLUSH NO
> > > _DW_IO_AREA_SIZE 50
> > > _DW_IO_AREA_FLUSH 50
> > > _EVENT_ALIVE_CYCLE 0
> > > FORMAT_DATAVOLUME YES
> > > SYMBOL_DEMANGLING NO
> > > EXPAND_COM_TRACE NO
> > > MEMORY_ALLOCATION_LIMIT 0
> > > HASHED_RESULTSET NO
> > > HASHED_RESULTSET_CACHESIZE 262144
> > > FORBID_LOAD_BALANCING NO
> > > OPTIMIZE_JOIN_PARALLEL_SERVERS 0
> > > OPTIMIZE_JOIN_OUTER YES
> > > UTILITY_PROTSIZE 100
> > > KERNELVERSION KERNEL 7.5.0 BUILD 019-121-082-363
> > > MAXSERVERTASKS 30
> > > MAXLOGVOLUMES 2
> > > XP_CONVERTER_REGIONS 0
> > > _DW_LRU_TAIL_FLUSH 25
> > > MAXVOLUMES 23
> > > DEFAULT_CODE ASCII
> > > REQUEST_TIMEOUT 5000
> > > PACKET_SIZE 36864
> > > _WORKSTACK_SIZE 8192
> > > KERNELTRACESIZE 705
> > > _UTILITY_PROTFILE dbm.utl
> > > CONVERTER_REGIONS 8
> > > FORMATTING_MODE PARALLEL
> > > MIN_RETENTION_TIME 60
> > > MAX_RETENTION_TIME 480
> > > _LOCK_SUPPLY_BLOCK 100
> > > _PAGE_SIZE 8192
> > > _MP_DISP_LOOPS 2
> > > _MP_DISP_PRIO YES
> > > _MAXTASK_STACK 400
> > > _EVENTSIZE 0
> > > _DIAG_SEM 0
> > > SUPPRESS_CORE YES
> > > CACHE_SIZE 220399
> > > SHAREDSQL NO
> > > SHAREDSQL_EXPECTEDSTATEMENTCOUNT 1500
> > > SHAREDSQL_COMMANDCACHESIZE 32768
> > > USE_SYSTEM_PAGE_CACHE YES
> > > MAX_HASHTABLE_MEMORY 5120
> > > _SERVERDB_FOR_SAP YES
> > > _RESTART_TIME 600
> > > MAXUSERTASKS 35
> > > LRU_FOR_SCAN YES
> > > XP_MP_RGN_LOOP 0
> > > _MAXGARBAGE_COLL 0
> > > _SERVER_DESC_CACHE 68
> > > _SERVER_CMD_CACHE 31
> > > _SHMKERNEL 1811786
> > > LOG_MIRRORED NO
> > > OMS_VERS_THRESHOLD 2097152
> > > OMS_STREAM_TIMEOUT 30
> > > MAX_SERVERTASK_STACK 100
> > > HS_STORAGE_DLL libhsscopy
> > > USE_COROUTINES YES
> > > LOCAL_REDO_LOG_BUFFER_SIZE 0
> > > _MINREPLY_SIZE 4096
> > > TRACE_PAGES_TI 2
> > > TRACE_PAGES_GC 0
> > > TRACE_PAGES_US 10
> > > TRACE_PAGES_UT 5
> > > TRACE_PAGES_SV 5
> > > TRACE_PAGES_EV 2
> > > TRACE_PAGES_BUP 0
> > > _BACKUP_HISTFILE dbm.knl
> > > _BACKUP_MED_DEF dbm.mdf
> > > TRACE_PAGES_LW 5
> > > TRACE_PAGES_PG 3
> > > JOIN_OPERATOR_IMPLEMENTATION YES
> > > _USE_IOPROCS_ONLY NO
> > > _FBM_LOW_IO_RATE 10
> > > DATA_VOLUME_NAME_0001 DAT_0001
> > > DATA_VOLUME_SIZE_0001 1572864
> > > DATA_VOLUME_TYPE_0001 F
> > > DATA_VOLUME_NAME_0002 DAT_0002
> > > DATA_VOLUME_SIZE_0002 1572864
> > > DATA_VOLUME_TYPE_0002 F
> > > DATA_VOLUME_GROUPS 1
> > > USE_UCONTEXT YES
> > > SESSION_TIMEOUT 900
> > > _MP_RGN_QUEUE YES
> > > _MP_RGN_DIRTY_READ YES
> > > _MP_RGN_BUSY_WAIT YES
> > > _MP_RGN_PRIO YES
> > > VOLUMENO_BIT_COUNT 8
> > > MAX_SINGLE_HASHTABLE_SIZE 512
> > > OPTIM_MAX_MERGE 500
> > > _AK_DUMP_ALLOWED YES
> > > _RTEDUMPFILE rtedump
> > > __PARAM_CHANGED___ 0
> > > __PARAM_VERIFIED__ 2004-12-09 18:55:13
> > > ALLOW_MULTIPLE_SERVERTASK_UKTS NO
> > > CONTROLUSERID DBM
> > > _TASKCLUSTER_01 tw;al;ut;2000*sv,100*bup;10*ev,10*gc;
> > > _TASKCLUSTER_02 ti,100*dw;9*us;
> > > _TASKCLUSTER_03 equalize
> > > _IDXFILE_LIST_SIZE 2048
> > > _KERNELDIAGFILE knldiag
> > > _KERNELTRACEFILE knltrace
> > > _KERNELDUMPFILE knldump
> > > FBM_VOLUME_COMPRESSION 50
> > > FBM_VOLUME_BALANCE 10
> > > LOG_VOLUME_NAME_001 /media/dblog/DBA/LOG_001
> > > LOG_VOLUME_SIZE_001 65536
> > > LOG_VOLUME_TYPE_001 F
> > > INIT_ALLOCATORSIZE 233472
> > > SET_VOLUME_LOCK YES
> > > MAXBACKUPDEVS 1
> > > MAXLOCKS 50000
> > > _MBLOCK_DATA_SIZE 32768
> > > _MBLOCK_QUAL_SIZE 16384
> > > _MBLOCK_STACK_SIZE 32768
> > > _MBLOCK_STRAT_SIZE 8192
> > > OPTIM_JOIN_FETCH 0
> > > DATE_TIME_FORMAT INTERNAL
> > > _MULT_IO_BLOCK_CNT 8
> > > DIAG_HISTORY_NUM 2
> > > DIAG_HISTORY_PATH /media/data/DBA/DIAGHIST
> > > OPTIM_INV_ONLY YES
> > > _READAHEAD_BLOBS 32
> > > HEAP_CHECK_LEVEL 0
> > > LOG_SEGMENT_SIZE 21845
> > > _TAB_RGNS 8
> > > _OMS_RGNS 25
> > > _ROW_RGNS 8
> > > _PRIO_FACTOR 80
> > > _EVENTFILE knldiag.evt
> > > _OMS_REGIONS 0
> > > JOIN_SEARCH_LEVEL 4
> > > RUNDIRECTORY /media/data/DBA
> > > _MAXEVENTTASKS 0
> > > _MAXEVENTS 100
> > > _MAX_MESSAGE_FILES 0
> > > MAXPAGER 64
> > > _MIN_SERVER_DESC 29
> > > MAX_SPECIALTASK_STACK 100
> > > USE_OPEN_DIRECT NO
> > > AUTO_RECREATE_BAD_INDEXES YES
> > > _UNICODE YES
> > > KERNELDIAGSIZE 800
> > > _SHAREDDYNDATA 220975
> > > _SHAREDDYNPOOL 37771
> > > INSTANCE_TYPE OLTP
> > > LOG_BACKUP_TO_PIPE NO
> > > DEADLOCK_DETECTION 800
> > > _IOPROCS_PER_DEV 2
> > > _IOPROCS_FOR_PRIO 0
> > > _IOPROCS_SWITCH 2
> > > CAT_CACHE_SUPPLY 5000
> > > _DATA_CACHE_RGNS 64
> > > SEQUENCE_CACHE 100
> > > OPTIM_CACHE NO
> > > HS_SYNC_INTERVAL 50
> > > _PRIO_BASE_U2U 100
> > > _PRIO_BASE_IOC 80
> > > _PRIO_BASE_RAV 80
> > > _PRIO_BASE_REX 40
> > > _PRIO_BASE_COM 10
> > > OMS_HEAP_LIMIT 0
> > > _MAXTRANS 288
> > > _WORKDATA_SIZE 4096
> > > _CAT_CACHE_MINSIZE 262144
> > > _DYN_TASK_STACK NO
> > > JOIN_MAXTAB_LEVEL4 30
> > > JOIN_MAXTAB_LEVEL9 10
> > > MAXDATAVOLUMES 20
> > > OMS_HEAP_COUNT 1
> > > OMS_HEAP_BLOCKSIZE 10000
> > > OMS_HEAP_THRESHOLD 100
> > > XP_DATA_CACHE_RGNS 0
> > > SHOW_MAX_STACK_USE NO
> > > LOAD_BALANCING_CHK 0
> > > LOAD_BALANCING_DIF 10
> > > LOAD_BALANCING_EQ 5
> > > JOIN_TABLEBUFFER 128
> > > USE_STACK_ON_STACK YES
> > > MAXCPU 4
> > > OPMSG1 /dev/console
> > > OPMSG2 /dev/null
> > > MCOD YES
> > > <<
> > >
> > >
> > > --
> > > MaxDB Discussion Mailing List
> > > For list archives: http://lists.mysql.com/maxdb
> > > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> > --
> > MaxDB Discussion Mailing List
> > For list archives: http://lists.mysql.com/maxdb
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
