- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Index usage
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Index usage
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 07-22-2009 10:47 AM
Hello,
I'm using MaxDB 7.6.00.34 on Win2003Server.
I have 2 database instances on 2 computers.
Both has the same database schema, but different data inside.
When I run next statement:
select * from logfile where TSTAMP >= '2009-06-22 00:00:00'
on 1st instance it takes 20sec to get response, but on the 2nd 4-5minutes.
Also explain result is different:
1st instance
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
ADMIN LOGFILE IDX_LOGFILE_TSTAMP RANGE CONDITION FOR INDEX 11
TSTAMP (USED INDEX COLUMN)
RESULT IS NOT COPIED , COSTVALUE IS 34
2nd instance
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
ADMIN LOGFILE TABLE SCAN 4955
RESULT IS NOT COPIED , COSTVALUE IS 4955
so it seams that 2nd instance does not uses index IDX_LOGFILE_TSTAMP, but tis index exists in both DB instances.
I tryed to drop this index and then I created the new one, but it was the same state.
Is some way how to force the statement to use some index ??
Or what I do incorrect ?
Thank you for support. Dusan
- SAP Managed Tags:
- SAP MaxDB
Accepted Solutions (1)
Accepted Solutions (1)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
>
> Hello,
> I'm using MaxDB 7.6.00.34 on Win2003Server.
That's a very old version of MaxDB - in fact it's too old.
Go get the current patch 7.6.06 or 7.7.06 !
> I have 2 database instances on 2 computers.
> Both has the same database schema, but different data inside.
Hmm... I'm not so sure about that.
Let us see the result of the following query for both database:
select count(*) from logfile where TSTAMP >= '2009-06-22 00:00:00'> so it seams that 2nd instance does not uses index IDX_LOGFILE_TSTAMP, but tis index exists in both DB instances.
> I tryed to drop this index and then I created the new one, but it was the same state.
>
> Is some way how to force the statement to use some index ??
Well, you might use an index, but there is very likely a reason for that the optimizer chooses not to use the index.
Please do post the CREATE TABLE statement for both table and index of both databases.
And also post the OPTIMIZE_... parameter settings of both instances.
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Yes, you are right.
2nd (slower) DB instance has bigger LOGFILE table (146570rows).
Does optimizer desides if use (or not use) some index when there is some row count in the table ??
I think that it is faster (and more effective) to use indexes on tables with more rows.
Table and index definition:
CREATE TABLE "ADMIN"."LOGFILE"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TSTAMP" Timestamp DEFAULT TIMESTAMP,
"SERVER_ID" Integer DEFAULT -1,
"CAR_ID" Integer DEFAULT -1,
"EMPLOYEE_ID" Integer DEFAULT -1,
"TABLE_TYPE" Integer DEFAULT -1,
"TABLE_ID" Integer DEFAULT -1,
"MSGTIME" Timestamp DEFAULT TIMESTAMP,
"MSG_ID" Integer DEFAULT -1,
"MSGSTATE" Integer DEFAULT 0,
"COUNTRY" Varchar (30) ASCII DEFAULT ' ',
"POSTCODE" Varchar (30) ASCII DEFAULT ' ',
"TOWN" Varchar (254) ASCII DEFAULT ' ',
"TOWNPART" Varchar (254) ASCII DEFAULT ' ',
"STREET" Varchar (254) ASCII DEFAULT ' ',
"LON" Integer DEFAULT -1,
"LAT" Integer DEFAULT -1,
"GEOCODED" Integer DEFAULT 0,
"ADDRESS_ID" Integer DEFAULT -1,
"USER_ID" Integer DEFAULT -1,
"MSGVALID" Integer DEFAULT 0,
"DESCR_EN" Varchar (255) ASCII DEFAULT ' ',
"DESCR_DE" Varchar (255) ASCII DEFAULT ' ',
"KMPERDAY" Integer NOT NULL DEFAULT -1,
"SPEED" Integer NOT NULL DEFAULT -1,
"INPUT" Integer NOT NULL DEFAULT 0,
"TOTALKM" Integer NOT NULL DEFAULT -1,
"SECONDTICK" Fixed (10,0) NOT NULL DEFAULT 0,
"IGNITIONCNT" Fixed (10,0) NOT NULL DEFAULT -1,
"INPUTCNT" Fixed (10,0) NOT NULL DEFAULT -1,
"ANALOGPERC" Integer NOT NULL DEFAULT -1,
"ANALOGLIT" Integer NOT NULL DEFAULT -1,
"ISPRIVATE" Integer NOT NULL DEFAULT 0,
"IGNITION" Integer NOT NULL DEFAULT -1,
PRIMARY KEY("ID")
)
CREATE INDEX "IDX_LOGFILE_TSTAMP" ON "ADMIN"."LOGFILE"("TSTAMP" ASC)
Table and index definition are the same.
And OPTIMIZE... parameters are :
OPTIMIZE_AGGREGATION YES
OPTIMIZE_FETCH_REVERSE YES
OPTIMIZE_FIRST_ROWS YES
OPTIMIZE_JOIN_HASHTABLE YES
OPTIMIZE_JOIN_HASH_MINIMAL_RATIO 1
OPTIMIZE_JOIN_ONEPHASE YES
OPTIMIZE_JOIN_OPERATOR_SORT YES
OPTIMIZE_JOIN_OUTER YES
OPTIMIZE_JOIN_PARALLEL_MINSIZE 1000000
OPTIMIZE_JOIN_PARALLEL_SERVERS 0
OPTIMIZE_MIN_MAX YES
OPTIMIZE_OPERATOR_JOIN YES
OPTIMIZE_OPERATOR_JOIN_COSTFUNC YES
OPTIMIZE_QUAL_ON_INDEX YES
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
> 2nd (slower) DB instance has bigger LOGFILE table (146570rows).
Is that the count for the query I wrote?
select count(*) from logfile where TSTAMP >= '2009-06-22 00:00:00'Sure about that?
What the result then for
select count(*) from logfile> Does optimizer desides if use (or not use) some index when there is some row count in the table ??
That's what a cost based optimizer is all about.
Look at the query, look at the data and decide based on that what would be the fastest way to get the result.
> I think that it is faster (and more effective) to use indexes on tables with more rows.
And why is that?
Because the index is there to make things faster?
I created the table as you described it and loaded some data (thanks for providing a default clause!):
INSERT INTO LOGFILE (id) (select rowno from tables a, tables b, tables c where rowno <=100000)Now we've 100000 records in this table looking like these:
ID TSTAMP SERVER_ID CAR_ID EMPLOYEE_ID
1 2009-07-22 16:25:08.741000 -1 -1 -1 ...
2 2009-07-22 16:25:08.741000 -1 -1 -1 ...
3 2009-07-22 16:25:08.741000 -1 -1 -1 ...Let's see how many rows would fit your query:
select count(*) from logfile where TSTAMP >= timestamp('2009-06-22 00:00:00')
EXPRESSION1
100000Wow - all of them.
So would it be a good idea to look up all 100000 entries that fit the condition via the index and then take the primary keys we found in the index and look up the full entries in the table?
I don't think so - and neither does the optimizer:
SCHEMANAME TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
LARS LOGFILE TABLE SCAN 2223
RESULT IS NOT COPIED , COSTVALUE IS 2223
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 1--> see respone 2. part
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
But what happens if we look up less data?
INSERT INTO LOGFILE (id, tstamp) values (100001, '2009-07-22 17:00:00' )
INSERT INTO LOGFILE (id, tstamp) values (100002, '2009-07-22 17:00:00' )select * from logfile where TSTAMP >= timestamp('2009-07-22 17:00:00')SCHEMANAME TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
LARS LOGFILE IDX_LOGFILE_TSTAMP RANGE CONDITION FOR INDEX 120
TSTAMP (USED INDEX COLUMN)
RESULT IS NOT COPIED , COSTVALUE IS 19
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 1or if we don't need the table data? Let's say we just want to count the number of fitting values?
select count(*) from logfile where TSTAMP >= timestamp('2009-06-22 00:00:00')SCHEMANAME TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT
LARS LOGFILE IDX_LOGFILE_TSTAMP RANGE CONDITION FOR INDEX 120
ONLY INDEX ACCESSED
TSTAMP (USED INDEX COLUMN)
RESULT IS COPIED , COSTVALUE IS 122
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 1In short: yes the optimizer does choose different access plans depending on your data and on what you want to see.
For more on that you may want to check the documentation for the [optimizer|http://maxdb.sap.com/doc/7_7/44/cebb6b4e8c0446e10000000a114a6b/content.htm].
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Thank you for your description, it was helpfull.
I must read more documents to increase perforance of my database.
My statement is more complex. This was only part of it, which I mistrusted . But you are right, DB optimizer works properly.
There is very big time difference betweet next two statements:
select * from
(
select logfile.* from logfile where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
) as logfile
tihs takes 2-3minutes
select logfile.* from logfile where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
tihs takes 2 secs.
where condition selects 153788 rows from all 153875 rows in the LOGFILE table.
Is this because inner select creates temporary result table and creating of this table take time ?
My complete select statement is:
select *
from
(
select logfile.* from logfile where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
) as logfile
join
(
select Id, Name, RegNr from car
union
select -3 as Id, '' as Name, '' as RegNr from dual
) as car
on logfile.Car_Id = car.Id
left join address
on logfile.Address_Id = address.Id
left join server
on logfile.Server_Id = server.Id
left join fleet_user
on logfile.User_Id = fleet_user.Id
select count (*) from car => 136 rows
select count (*) from address => 17 rows
select count (*) from server => 7 rows
select count (*) from fleet_user => 4 rows
select count (*) from logfile where LOGFILE.TSTAMP >= '2009-06-22 00:00:00' => 153788 rows
select count (*) from logfile => 153875 rows
Column Id is primary key in every table,
columns logfile.Address_Id, logfile.Server_Id, logfile.User_Id are not foreign keys (when they was FK, select time was same).
It seams that problem is in the in the
select * from
(
select logfile.* from logfile where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
)
sub statement (4-5min), because when I change where condition to
select * from
(
select logfile.* from logfile where LOGFILE.TSTAMP >= '2009-07-23 00:00:00'
)
whole select takes only 2-3 seconds.
I think that temporary table creation takes long time.
Do you have any ide how to speed up select execution ??
Dusan
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
> My statement is more complex. This was only part of it, which I mistrusted . But you are right, DB optimizer works properly.
> There is very big time difference betweet next two statements:
> Is this because inner select creates temporary result table and creating of this table take time ?
Likely - but why guess, when you can know?
Post the execution plan for the query and we'll see.
But for subselects we currently have to build up temp. results.
By the way: what's the logic behind the subselect on the logfile table?
This could be just a join.
select *
from logfile join
(
select Id, Name, RegNr from car
union
select -3 as Id, '' as Name, '' as RegNr from dual
) as car
on logfile.Car_Id = car.Id
left join address
on logfile.Address_Id = address.Id
left join server
on logfile.Server_Id = server.Id
left join fleet_user
on logfile.User_Id = fleet_user.Id
WHERE
LOGFILE.TSTAMP >= '2009-06-22 00:00:00'The same goes for that odd thing you do with the CAR table.
Why's that in there?
Another really bad thing is that you don't precisely provide what columns you select.
This is not only bad for performance reasons but makes this query really hard to maintain later on.
How many columns do you get back? What are their names/datatypes?
Do you use all of those columns?
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
It was exactly same select like yours, before. But for some data was qiucker select with subselect.
So, how to make select when it depends on data ?
But it seams that normal JOIN is more effective in more situations.
I'm not selecting all columns, but for simplicity I wrote "select * ..."
Thank you for support, Dusan
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
>
> It was exactly same select like yours, before. But for some data was qiucker select with subselect.
That would be pretty surprising - and a bad optimizer behavior.
Do you have the execution plans for both examples?
> So, how to make select when it depends on data ?
You cannot and should not.
The query should express what data you want to get, not how to get it.
That's the optimizer's job.
> But it seams that normal JOIN is more effective in more situations.
At least we don't need to create intermediate result sets in most cases with joins.
> I'm not selecting all columns, but for simplicity I wrote "select * ..."
Ok, but when we talk about query optimizsation we need to be precise.
So please provide the statement as you use it and the execution plans for it.
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hello,
my secect is :
select logfile.Id, logfile.TStamp, logfile.Server_Id, logfile.Car_Id,
logfile.IsPrivate, logfile.Employee_Id, logfile.Table_Type,
logfile.Table_Id, logfile.MsgTime, logfile.Msg_Id, logfile.MsgState,
logfile.Descr_EN as Descr, logfile.Country, logfile.PostCode,
logfile.Town, logfile.Street, logfile.Lon, logfile.Lat, logfile.User_Id,
logfile.MsgValid, logfile.Ignition, logfile.KmPerDay, logfile.Speed,
logfile.Address_Id, logfile.Input, logfile.AnalogLit, logfile.TotalKm,
decode (logfile.TotalKm, -1, '', logfile.TotalKm) as TotalKmDescr,
decode (logfile.analogPerc, -1, '', logfile.analogPerc) as AnalogPercDescr,
decode (logfile.analogLit, -1, '', logfile.analogLit) as AnalogLitDescr,
decode (logfile.KmPerDay, -1, '', logfile.KmPerDay) as KmPerDayDescr,
decode (logfile.IgnitionCnt, -1, '', chr (IgnitionCnt div 3600) || ':' || lfill (chr ((IgnitionCnt div 60) mod 60), '0', 2)) as IgnitionCntDescr,
decode (logfile.InputCnt, -1, '', chr (InputCnt div 3600) || ':' || lfill (chr ((InputCnt div 60) mod 60), '0', 2)) as InputCntDescr,
case
when logfile.IgnitionCnt = 0 then '' when logfile.IgnitionCnt = -1 then ''
when logfile.InputCnt = -1 then '' else chr (fixed (InputCnt * 100 / IgnitionCnt, 30, 1))
end as InputPercentDescr,
decode (logfile.Speed, -1, '', logfile.Speed) as SpeedDescr,
case car.Id
when -3 then value (server.Descr || ' [' || server.Licence || ']', '')
else rtrim (car.Name || ', ' || car.RegNr, ', ')
end as CarName,
decode (MsgState, 1, 'Try', 2, 'Snt', 3, 'Not', 4, 'Err', 5, 'Err', 6, 'Rcv', 7, 'Alarm', 8, 'Str', 9, 'Err', 10, 'RcvTUp', 11, 'RcvTDw', '') as MsgStateDescr,
case
when 'superroland' <> 'superroland' and isPrivate = 1 then 'Private'
when logfile.Msg_Id < 256 then value (fleet_user.name, '')
else value (decode (address.SerialNo, -1, '', address.SerialNo || ', ' ) || address.Name, logfile.Country || ' ' || logfile.Postcode || ' ' || logfile.Town || rtrim (' - ' || logfile.Townpart, ' - ') || rtrim (', ' || logfile.Street, ', '))
end as PosDescr
from logfile
join
(
select Id, Name, RegNr from car
union
select -3 as Id, '' as Name, '' as RegNr from dual
) as car
on logfile.Car_Id = car.Id
left join address on logfile.Address_Id = address.Id
left join server on logfile.Server_Id = server.Id
left join fleet_user on logfile.User_Id = fleet_user.Id
where logfile.TStamp >= '2009-06-24 00:00:00'
execution plan is :
ADMIN CAR TABLE SCAN 12
ADMIN DUAL TABLE SCAN 1
INTERNAL TEMPORARY RESULT TABLE SCAN 1
ADMIN LOGFILE IDX_LOGFILE_CARID JOIN VIA INDEXED COLUMN 5002
CAR_ID (USED INDEX COLUMN)
ADMIN ADDRESS ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN SERVER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN FLEET_USER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 1561
Dusan
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
structure of tables :
CREATE TABLE "ADMIN"."LOGFILE"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TSTAMP" Timestamp DEFAULT TIMESTAMP,
"SERVER_ID" Integer DEFAULT -1,
"CAR_ID" Integer DEFAULT -1,
"EMPLOYEE_ID" Integer DEFAULT -1,
"TABLE_TYPE" Integer DEFAULT -1,
"TABLE_ID" Integer DEFAULT -1,
"MSGTIME" Timestamp DEFAULT TIMESTAMP,
"MSG_ID" Integer DEFAULT -1,
"MSGSTATE" Integer DEFAULT 0,
"COUNTRY" Varchar (30) ASCII DEFAULT ' ',
"POSTCODE" Varchar (30) ASCII DEFAULT ' ',
"TOWN" Varchar (254) ASCII DEFAULT ' ',
"TOWNPART" Varchar (254) ASCII DEFAULT ' ',
"STREET" Varchar (254) ASCII DEFAULT ' ',
"LON" Integer DEFAULT -1,
"LAT" Integer DEFAULT -1,
"GEOCODED" Integer DEFAULT 0,
"ADDRESS_ID" Integer DEFAULT -1,
"USER_ID" Integer DEFAULT -1,
"MSGVALID" Integer DEFAULT 0,
"DESCR_EN" Varchar (255) ASCII DEFAULT ' ',
"DESCR_DE" Varchar (255) ASCII DEFAULT ' ',
"KMPERDAY" Integer NOT NULL DEFAULT -1,
"SPEED" Integer NOT NULL DEFAULT -1,
"INPUT" Integer NOT NULL DEFAULT 0,
"TOTALKM" Integer NOT NULL DEFAULT -1,
"SECONDTICK" Fixed (10,0) NOT NULL DEFAULT 0,
"IGNITIONCNT" Fixed (10,0) NOT NULL DEFAULT -1,
"INPUTCNT" Fixed (10,0) NOT NULL DEFAULT -1,
"ANALOGPERC" Integer NOT NULL DEFAULT -1,
"ANALOGLIT" Integer NOT NULL DEFAULT -1,
"ISPRIVATE" Integer DEFAULT 0,
"IGNITION" Integer DEFAULT -1,
PRIMARY KEY ("ID")
)
CREATE TABLE "ADMIN"."SERVER"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TSTAMP" Timestamp NOT NULL DEFAULT TIMESTAMP,
"TYPE" Integer NOT NULL DEFAULT -1,
"LICENCE" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"IPADDR" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"MAC" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"COMPUTERNAME" Varchar (255) ASCII NOT NULL DEFAULT ' ',
"USERNAME" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"DESCR" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"STATE" Integer NOT NULL DEFAULT -1,
"WDCHECK" Integer NOT NULL DEFAULT 0,
PRIMARY KEY ("ID")
)
CREATE TABLE "ADMIN"."CAR"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TSTAMP" Timestamp NOT NULL DEFAULT TIMESTAMP,
"PARENT_ID" Integer NOT NULL DEFAULT -1,
"IMAGE_ID" Integer NOT NULL DEFAULT -1,
"NAME" Varchar (254) ASCII NOT NULL DEFAULT ' ',
"REGNR" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"COLOR" Integer NOT NULL DEFAULT 16777215,
"MADE" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"MANUFACTURER" Varchar (100) ASCII NOT NULL DEFAULT ' ',
"TYPE" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"CARNR" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"STATE" Integer NOT NULL DEFAULT 0,
"AVAILABLE" Integer NOT NULL DEFAULT 0,
"MSGTIME" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"COORDVALID" Integer NOT NULL DEFAULT 0,
"LON" Integer NOT NULL DEFAULT -1,
"LAT" Integer NOT NULL DEFAULT -1,
"COUNTRY" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"POSTCODE" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"TOWN" Varchar (254) ASCII NOT NULL DEFAULT ' ',
"TOWNPART" Varchar (254) ASCII NOT NULL DEFAULT ' ',
"STREET" Varchar (254) ASCII NOT NULL DEFAULT ' ',
"ACTADDR_ID" Integer NOT NULL DEFAULT -1,
"ACTLOG_ID" Integer NOT NULL DEFAULT -1,
"EMPLOYEECHNGTIME" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"EMPLOYEE_ID" Integer NOT NULL DEFAULT -1,
"EMPLOYEETYPE" Integer NOT NULL DEFAULT 0,
"SERVER_ID" Integer NOT NULL DEFAULT -1,
"DEVICEADDR" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"DEVICEIDENTITY" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"HWOFFTSTAMP" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"HWONTSTAMP" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"HWOFFMSGTIME" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"HWONMSGTIME" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"LASTMSGTSTAMP" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"LASTMSGTIME" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"LASTMSG_ID" Integer NOT NULL DEFAULT -1,
"ALARMINPUT" Integer NOT NULL DEFAULT 0,
"EDITUSER_ID" Integer NOT NULL DEFAULT -1,
"EDITTSTAMP" Timestamp NOT NULL DEFAULT TIMESTAMP,
"BORDERADDR_ID" Integer NOT NULL DEFAULT -1,
"BORDERRADIUS" Integer NOT NULL DEFAULT 0,
"BORDERSTYLE" Integer NOT NULL DEFAULT 0,
"MODE_ID" Integer NOT NULL DEFAULT 0,
"SMSADDRESS01" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS02" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS03" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS04" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS05" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS06" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS07" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS08" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS09" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSADDRESS10" Varchar (30) ASCII NOT NULL DEFAULT ' ',
"SMSTEXT" Varchar (255) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS01" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS02" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS03" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS04" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS05" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS06" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS07" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS08" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS09" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILADDRESS10" Varchar (50) ASCII NOT NULL DEFAULT ' ',
"MAILSUBJECT" Varchar (255) ASCII NOT NULL DEFAULT ' ',
"MAILTEXT" Varchar (1000) ASCII NOT NULL DEFAULT ' ',
"ALARMSENDTIME" Timestamp NOT NULL DEFAULT TIMESTAMP,
"VALIDMSGTIME" Timestamp NOT NULL DEFAULT '1899-12-30 00:00:00.000000',
"VALIDSECONDTICK" Fixed (10,0) NOT NULL DEFAULT -1,
"DEVICETYPE" Integer NOT NULL DEFAULT 1,
"VALIDCOUNTER" Integer NOT NULL DEFAULT -1,
"ANALOGTOTAL" Integer NOT NULL DEFAULT -1,
"LASTSECONDTICK" Fixed (10,0) NOT NULL DEFAULT -1,
"FLAGS" Integer NOT NULL DEFAULT 0,
PRIMARY KEY ("ID"),
FOREIGN KEY "FK_SERVER_ID" ("SERVER_ID") REFERENCES "ADMIN"."SERVER" ("ID") ON DELETE SET DEFAULT
)
CREATE TABLE "ADMIN"."ADDRESS"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TSTAMP" Timestamp DEFAULT TIMESTAMP,
"NAME" Varchar (254) ASCII DEFAULT ' ',
UNIQUE(NAME),
"PARENT_ID" Integer DEFAULT -1,
"IMAGE_ID" Integer DEFAULT -1,
"LONWGS84" Integer DEFAULT -1,
"LATWGS84" Integer DEFAULT -1,
"COUNTRY" Varchar (30) ASCII DEFAULT ' ',
"POSTCODE" Varchar (30) ASCII DEFAULT ' ',
"TOWN" Varchar (254) ASCII DEFAULT ' ',
"STREET" Varchar (254) ASCII DEFAULT ' ',
"SERIALNO" Integer DEFAULT -1,
"TYPE" Varchar (254) ASCII DEFAULT ' ',
"PHONE" Varchar (30) ASCII DEFAULT ' ',
"FAX" Varchar (30) ASCII DEFAULT ' ',
"MOBILE" Varchar (30) ASCII DEFAULT ' ',
"EMAIL" Varchar (254) ASCII NOT NULL DEFAULT ' ',
"SHOWINMAP" Integer NOT NULL DEFAULT 1,
"RECOGNITIONDIST" Integer NOT NULL DEFAULT 300,
"SHOWTOUCHAPP" Integer NOT NULL DEFAULT 0,
"COLOR" Integer NOT NULL DEFAULT 12632256,
PRIMARY KEY ("ID")
)
CREATE TABLE "ADMIN"."FLEET_USER"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"TSTAMP" Timestamp NOT NULL DEFAULT TIMESTAMP,
"NAME" Varchar (30) ASCII NOT NULL,
"PASSWORD" Varchar (30) ASCII NOT NULL,
"ISCONNECTED" Integer NOT NULL DEFAULT 0,
"CHECKTIME" Timestamp NOT NULL DEFAULT TIMESTAMP,
"CONNECTTIME" Timestamp NOT NULL DEFAULT TIMESTAMP,
"CLIENTID" Varchar (100) ASCII NOT NULL DEFAULT ' ',
"COMPUTERNAME" Varchar (255) ASCII NOT NULL DEFAULT ' ',
"PARENT_ID" Integer NOT NULL DEFAULT -1,
PRIMARY KEY ("ID")
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
HI Dusan,
as this great forum software seems to be unable to correctly format any longer post, your last two replies are not usuable for anything...
Could you provide the correctly formatted information as a text-file to download and just post the link?
Or you split up your posts into several smaller ones.
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Dusan,
fortunately the email I got because of the automatic subscription to threads I reply to I received a correclty formatted version of your posts already.
So spare any reformatting actions for now.
From the execution plan ...
ADMIN CAR TABLE SCAN 12
ADMIN DUAL TABLE SCAN 1
INTERNAL TEMPORARY RESULT TABLE SCAN 1
ADMIN LOGFILE IDX_LOGFILE_CARID JOIN VIA INDEXED COLUMN 5002
CAR_ID (USED INDEX COLUMN)
ADMIN ADDRESS ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN SERVER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN FLEET_USER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 1561... the problem appears to be the join order.
It would have been better to first access LOGFILE via a tablescan and join the temp. result afterwards.
Please post the parameter setup of the db.
Also regather statistics for all tables - it looks a bit suspicious that all other reference tables only consist of 1 single page...
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Here are DB parameters:
ADMIN 1
ALLOW_MULTIPLE_SERVERTASK_UKTS NO
AUTHENTICATION_ALLOW
AUTHENTICATION_DENY
AUTOSAVE 1
AUTO_RECREATE_BAD_INDEXES YES
BACKUPRESULT 1
BACKUP_BLOCK_CNT 64
CACHE_SIZE 10000
CALLSTACKLEVEL 0
CAT_CACHE_SUPPLY 6464
CHECKDATA 1
CHECK_BACKUP NO
CHECK_COMMON 0
CHECK_CONVERTER 0
CHECK_DATACACHE NO
CHECK_DATAINDEX 0
CHECK_DATAPAGELOG 0
CHECK_FBM 0
CHECK_HASHED_RESULTSET 0
CHECK_IOMAN 0
CHECK_KB_REGIONS NO
CHECK_LOCK NO
CHECK_LOCK_SUPPLY NO
CHECK_LOGHISTORY 0
CHECK_LOGPAGE 0
CHECK_LOGTRANS 0
CHECK_LOGVOLUME 0
CHECK_REGIONS NO
CHECK_SRVTASKS 0
CHECK_TABLE_WIDTH NO
CHECK_TASK_SPECIFIC_CATALOGCACHE NO
CHECK_TRANSLIST NO
CHECK_TREE NO
CHECK_TREE_LOCKS NO
CLUSTERED_LOBS NO
CLUSTER_WRITE_THRESHOLD 80
COLUMNCOMPRESSION YES
CONTROLUSERID FLEET
CONVERTER_REGIONS 8
DATABASEFULL 1
DATA_IO_BLOCK_COUNT 64
DATA_VOLUME_GROUPS 1
DATA_VOLUME_MODE_0001 NORMAL
DATA_VOLUME_MODE_0002 NORMAL
DATA_VOLUME_MODE_0003 NORMAL
DATA_VOLUME_NAME_0001 DAT_0001
DATA_VOLUME_NAME_0002 DAT_0002
DATA_VOLUME_NAME_0003 DAT_0003
DATA_VOLUME_SIZE_0001 524288
DATA_VOLUME_SIZE_0002 524288
DATA_VOLUME_SIZE_0003 524288
DATA_VOLUME_TYPE_0001 F
DATA_VOLUME_TYPE_0002 F
DATA_VOLUME_TYPE_0003 F
DATE_TIME_FORMAT INTERNAL
DBFILLINGABOVELIMIT 70L80M85M90H95H96H97H98H99H
DBFILLINGBELOWLIMIT 70L80L85L90L95L
DDLTRIGGER YES
DEADLOCK_DETECTION 4
DEFAULT_CODE ASCII
DIAG_HISTORY_NUM 2
DIAG_HISTORY_PATH c:\dokumente und einstellungen\all users\anwendungsdaten\sdb\data\wrk\GOLDFUNK\DIAGHISTORY
ENABLE_CHECK_INSTANCE YES
ENABLE_SYSTEM_TRIGGERS YES
ERROR 3
EVENT 1
EXPAND_COM_TRACE NO
EXTERNAL_DUMP_REQUEST NO
FBM_VOLUME_BALANCE 10
FBM_VOLUME_COMPRESSION 50
FILEDIR_SPINLOCKPOOL_SIZE 10
FORBID_LOAD_BALANCING NO
FORMATTING_MODE PARALLEL
FORMAT_DATAVOLUME YES
HASHED_RESULTSET YES
HASHED_RESULTSET_CACHESIZE 262144
HEAP_CHECK_LEVEL 0
HIRES_TIMER_TYPE CPU
HS_STORAGE_DLL libhsscopy
HS_SYNC_INTERVAL 50
INDEX_LEAF_CACHING 2
INIT_ALLOCATORSIZE 258048
INSTANCE_TYPE OLTP
JOIN_MAXTAB_LEVEL4 16
JOIN_MAXTAB_LEVEL9 5
JOIN_SEARCH_LEVEL 0
JOIN_TABLEBUFFER 128
KERNELDIAGSIZE 800
KERNELTRACESIZE 1210
KERNELVERSION KERNEL 7.6.00 BUILD 034-123-134-685
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
LOAD_BALANCING_CHK 0
LOAD_BALANCING_DIF 10
LOAD_BALANCING_EQ 5
LOCAL_REDO_LOG_BUFFER_SIZE 0
LOGABOVELIMIT 50L75L90M95M96H97H98H99H
LOGFULL 1
LOGSEGMENTFULL 1
LOG_BACKUP_TO_PIPE NO
LOG_IO_BLOCK_COUNT 4
LOG_IO_QUEUE 50
LOG_MIRRORED NO
LOG_QUEUE_COUNT 1
LOG_SEGMENT_SIZE 21333
LOG_VOLUME_NAME_001 LOG_001
LOG_VOLUME_SIZE_001 64000
LOG_VOLUME_TYPE_001 F
LRU_FOR_SCAN NO
MAXBACKUPDEVS 2
MAXCPU 1
MAXDATAVOLUMES 11
MAXLOCKS 8080
MAXLOGVOLUMES 2
MAXPAGER 11
MAXRGN_REQUEST -1
MAXSERVERTASKS 28
MAXUSERTASKS 100
MAXVOLUMES 14
MAX_HASHTABLE_MEMORY 5120
MAX_LOG_QUEUE_COUNT 0
MAX_MESSAGE_LIST_LENGTH 100
MAX_RETENTION_TIME 480
MAX_SERVERTASK_STACK 500
MAX_SINGLE_HASHTABLE_SIZE 512
MAX_SPECIALTASK_STACK 500
MCOD NO
MEMORY_ALLOCATION_LIMIT 0
MEM_ENHANCE_LIMIT 0
MINI_DUMP NORMAL
MINSERVERTASKS 28
MIN_RETENTION_TIME 60
MONITOR_READ 2147483647
MONITOR_ROWNO 0
MONITOR_SELECTIVITY 0
MONITOR_TIME 2147483647
MP_RGN_LOOP -1
OFFICIAL_NODE
OMS_HEAP_BLOCKSIZE 10000
OMS_HEAP_COUNT 1
OMS_HEAP_LIMIT 0
OMS_HEAP_THRESHOLD 100
OMS_RUN_IN_UDE_SERVER NO
OMS_STREAM_TIMEOUT 30
OMS_VERS_THRESHOLD 2097152
ONLINE 1
OPTIMIZE_AGGREGATION YES
OPTIMIZE_FETCH_REVERSE YES
OPTIMIZE_FIRST_ROWS YES
OPTIMIZE_JOIN_HASHTABLE YES
OPTIMIZE_JOIN_HASH_MINIMAL_RATIO 1
OPTIMIZE_JOIN_ONEPHASE YES
OPTIMIZE_JOIN_OPERATOR_SORT YES
OPTIMIZE_JOIN_OUTER YES
OPTIMIZE_JOIN_PARALLEL_MINSIZE 1000000
OPTIMIZE_JOIN_PARALLEL_SERVERS 0
OPTIMIZE_MIN_MAX YES
OPTIMIZE_OPERATOR_JOIN YES
OPTIMIZE_OPERATOR_JOIN_COSTFUNC YES
OPTIMIZE_QUAL_ON_INDEX YES
OPTIMIZE_QUERYREWRITE STATEMENT
OPTIM_CACHE NO
OPTIM_INV_ONLY YES
OPTIM_JOIN_FETCH 0
OPTIM_MAX_MERGE 500
OUTOFSESSIONS 3
PACKET_SIZE 131072
PREALLOCATE_IOWORKER NO
PROTECT_DATACACHE_MEMORY NO
REQUEST_TIMEOUT 5000
RESERVEDSERVERTASKS 16
RTE_TEST_REGIONS 0
RUNDIRECTORY c:\dokumente und einstellungen\all users\anwendungsdaten\sdb\data\wrk\GOLDFUNK
SEQUENCE_CACHE 1
SESSION_TIMEOUT 900
SET_VOLUME_LOCK YES
SHAREDSQL YES
SHAREDSQL_COMMANDCACHESIZE 262144
SHAREDSQL_EXPECTEDSTATEMENTCOUNT 1500
SHOW_MAX_KB_STACK_USE NO
SHOW_MAX_STACK_USE NO
STANDBY 1
SUBTREE_LOCKS NO
SYMBOL_DEMANGLING NO
SYMBOL_RESOLUTION YES
SYSTEMERROR 3
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
TIME_MEASUREMENT NO
TRACE_AK NO
TRACE_ALLOCATOR 0
TRACE_CATALOG 0
TRACE_CLIENTKERNELCOM 0
TRACE_COMMON 0
TRACE_COMMUNICATION 0
TRACE_CONVERTER 0
TRACE_DATACHAIN 0
TRACE_DATAINDEX 0
TRACE_DATAPAM 0
TRACE_DATATREE 0
TRACE_DBPROC 0
TRACE_DEFAULT NO
TRACE_DELETE NO
TRACE_FBM 0
TRACE_FILEDIR 0
TRACE_FRAMECTRL 0
TRACE_INDEX NO
TRACE_INSERT NO
TRACE_IOMAN 0
TRACE_IPC 0
TRACE_JOIN 0
TRACE_KSQL 0
TRACE_LOCK NO
TRACE_LOGACTION 0
TRACE_LOGHISTORY 0
TRACE_LOGPAGE 0
TRACE_LOGTRANS 0
TRACE_LOGVOLUME 0
TRACE_LONG NO
TRACE_MEMORY 0
TRACE_MESSAGES 0
TRACE_OBJECT NO
TRACE_OBJECTCONTAINER 0
TRACE_OBJECT_ADD NO
TRACE_OBJECT_ALTER NO
TRACE_OBJECT_FREE NO
TRACE_OBJECT_GET NO
TRACE_OMS_CONTAINERDIR 0
TRACE_OMS_CONTEXT 0
TRACE_OMS_ERROR 0
TRACE_OMS_FLUSHCACHE 0
TRACE_OMS_INTERFACE 0
TRACE_OMS_KEY 0
TRACE_OMS_KEYRANGE 0
TRACE_OMS_LOCK 0
TRACE_OMS_MEMORY 0
TRACE_OMS_NEWOBJ 0
TRACE_OMS_SESSION 0
TRACE_OMS_STREAM 0
TRACE_OMS_VAROBJECT 0
TRACE_OMS_VERSION 0
TRACE_OPTIMIZE NO
TRACE_ORDER NO
TRACE_ORDER_STANDARD NO
TRACE_PAGER 0
TRACE_PAGES NO
TRACE_PAGES_BUP 0
TRACE_PAGES_EV 2
TRACE_PAGES_GC 20
TRACE_PAGES_LW 5
TRACE_PAGES_PG 3
TRACE_PAGES_SV 5
TRACE_PAGES_TI 2
TRACE_PAGES_US 10
TRACE_PAGES_UT 5
TRACE_PRIMARY_TREE NO
TRACE_QUERYREWRITE 0
TRACE_RUNTIME 0
TRACE_SELECT NO
TRACE_SHAREDSQL 0
TRACE_SQLMANAGER 0
TRACE_SRVTASKS 0
TRACE_STOP_ERRORCODE 0
TRACE_SYNCHRONISATION 0
TRACE_SYSVIEW 0
TRACE_TABLE 0
TRACE_TIME NO
TRACE_UPDATE NO
TRACE_VOLUME 0
TRANS_HISTORY_SIZE 0
TRANS_THRESHOLD_VALUE 60
UKT_CPU_RELATIONSHIP NONE
UPDATESTAT_PARALLEL_SERVERS 0
UPDATESTAT_SAMPLE_ALGO 1
UPDSTATWANTED 1
USED_MAX_LOG_QUEUE_COUNT 1
USESELECTFETCH YES
USEVARIABLEINPUT NO
USE_COROUTINES YES
USE_FIBERS YES
USE_MEM_ENHANCE NO
USE_OPEN_DIRECT NO
USE_SYSTEM_PAGE_CACHE YES
UTILITY_PROTSIZE 100
VOLUMENO_BIT_COUNT 8
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
XP_CONVERTER_REGIONS 0
XP_DATA_CACHE_RGNS 0
XP_MAXPAGER 0
AKDUMP_ALLOWED YES
BACKUPHISTFILE dbm.knl
BACKUPMED_DEF dbm.mdf
CATCACHE_MINSIZE 262144
_COMMENT
DATACACHE_RGNS 8
DELAYCOMMIT NO
DELAYLOGWRITER 0
DIAGSEM 0
DWIO_AREA_FLUSH 50
DWIO_AREA_SIZE 50
DWLRU_TAIL_FLUSH 25
_EVENTFILE knldiag.evt
_EVENTSIZE 0
FBMLOW_IO_RATE 10
IDXFILELIST_SIZE 2048
IOPROCSFOR_PRIO 0
IOPROCSFOR_READER 0
IOPROCSPER_DEV 1
IOPROCSSWITCH 2
_KERNELDIAGFILE knldiag
_KERNELDUMPFILE knldump
_KERNELTRACEFILE knltrace
LOCKSUPPLY_BLOCK 100
_MAXEVENTS 100
_MAXEVENTTASKS 2
MAXGARBAGECOLL 1
MAXTASKSTACK 1024
_MAXTRANS 808
MAXMESSAGE_FILES 0
MBLOCKDATA_SIZE 32768
MBLOCKQUAL_SIZE 32768
MBLOCKSTACK_SIZE 32768
MBLOCKSTRAT_SIZE 12288
MINREPLYSIZE 4096
MPDISP_LOOPS 1
MPDISP_PRIO DEFAULT
MPRGN_BUSY_WAIT DEFAULT
MPRGN_DIRTY_READ DEFAULT
MPRGN_PRIO DEFAULT
MPRGN_QUEUE YES
OMSREGIONS 0
OMSRGNS 7
PAGESIZE 8192
PRIOBASE_COM 10
PRIOBASE_IOC 80
PRIOBASE_RAV 80
PRIOBASE_REX 40
PRIOBASE_U2U 100
PRIOFACTOR 80
READAHEADBLOBS 32
RESTARTTIME 600
ROWRGNS 8
_RTEDUMPFILE rtedump
SERVERDBFOR_SAP YES
TABRGNS 8
TASKCLUSTER01 tw;al;ut;2000sv,100bup;10ev,10gc;
TASKCLUSTER02 ti,100dw;30000us;
TASKCLUSTER03 compress
TRANSRGNS 8
_UNICODE NO
USEASYNC_IO YES
USEIOPROCS_ONLY NO
UTILITYPROTFILE dbm.utl
WORKDATASIZE 8192
WORKSTACKSIZE 8192
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
And here is content of OPTIMIZERSTATISTICS table (if I understand you correct, you asked me for this)
This is only fragment for tables in the select.
ADMIN ADMIN ADDRESS ? ID 17 ?
ADMIN ADMIN ADDRESS ? NAME 17 ?
ADMIN ADMIN ADDRESS ? PARENT_ID 2 ?
ADMIN ADMIN ADDRESS IDXU_ADDRESS_NAME ? ? 1
ADMIN ADMIN ADDRESS ? TABLE STATISTICS 17 1
ADMIN ADMIN CAR ? ACTADDR_ID 9 ?
ADMIN ADMIN CAR ? ACTLOG_ID 136 ?
ADMIN ADMIN CAR ? ID 136 ?
ADMIN ADMIN CAR ? PARENT_ID 20 ?
ADMIN ADMIN CAR ? TABLE STATISTICS 136 12
ADMIN ADMIN FLEET_USER ? ID 4 ?
ADMIN ADMIN FLEET_USER ? PARENT_ID 2 ?
ADMIN ADMIN FLEET_USER ? TABLE STATISTICS 4 1
ADMIN ADMIN LOGFILE ? ADDRESS_ID 14 ?
ADMIN ADMIN LOGFILE ? CAR_ID 126 ?
ADMIN ADMIN LOGFILE ? EMPLOYEE_ID 4 ?
ADMIN ADMIN LOGFILE ? ID 146941 ?
ADMIN ADMIN LOGFILE ? MSGTIME 123698 ?
ADMIN ADMIN LOGFILE ? MSG_ID 18 ?
ADMIN ADMIN LOGFILE ? SERVER_ID 2 ?
ADMIN ADMIN LOGFILE ? TABLE_ID 146941 ?
ADMIN ADMIN LOGFILE ? TSTAMP 146941 ?
ADMIN ADMIN LOGFILE ? USER_ID 4 ?
ADMIN ADMIN LOGFILE IDX_LOGFILE_MSGID_TSTAMP ? ? 932
ADMIN ADMIN LOGFILE IDX_LOGFILE_TABLE_ID ? ? 520
ADMIN ADMIN LOGFILE IDX_LOGFILE_MSGTIME ? ? 1094
ADMIN ADMIN LOGFILE IDX_LOGFILE_MSGID ? ? 193
ADMIN ADMIN LOGFILE IDX_LOGFILE_CARID ? ? 279
ADMIN ADMIN LOGFILE IDX_LOGFILE ? ? 1730
ADMIN ADMIN LOGFILE IDX_LOGFILE_MSGID_MSGTIME ? ? 993
ADMIN ADMIN LOGFILE IDX_LOGFILE_TSTAMP ? ? 815
ADMIN ADMIN LOGFILE ? TABLE STATISTICS 146941 5002
ADMIN ADMIN SERVER ? ID 7 ?
ADMIN ADMIN SERVER ? TABLE STATISTICS 7 1
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Dusan,
> 7.6.00 BUILD 034-123-134-685
you're kidding me, are you?
Please download and install the current 7.6.06 patch here from SDN and check the execution plan again.
Also make sure to get the "Parameter Check File" for DBAnalyzer (also available as donwload here in SDN) and to check the DB parameters with it.
When the database runs on a reasonable current version (e.g. not 4 years old) and with the recommended parameters, I'll have another look into this issue.
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hello,
I made update to version 7.6.06.03
Here is output of "DB Analyzer" :
===== #0 at 2009-07-28 09:30:56
* I
* I Database Analyzer configuration check version 1.08, December 29, 2008
* I
* I General information:
* I --------------------
* I Instance G (tc-2000plus.TC-2000) is up since 2009-07-28 09:16:31
* I Kernel version: Kernel 7.6.06 Build 003-123-202-135
* I Number of logical CPUs: 2, physical CPUs: 1, processor type: Intel IA32 level f revision 605
* I Physical memory 1023 MB, virtual memory 2473 MB, memory allocated from instance: 440.46 MB
* I Operating system: Windows 2003 Server Service Pack 2 (Build 3790)
* I Configuration of 'MAXCPU': 1, 'MAXUSERTASKS': 100
* I Size of data cache 9633 pages, size of converter cache 259 pages
* I Number of data volumes 2, usable size 1048572 pages, used size 351659 pages (33%)
* I
* I General checks:
* I ---------------
* W2 Recommended value for parameter '_MBLOCK_STRAT_SIZE' is 32768, current value is 32767
* I
* I If instance G is used for Data Warehouse applications, the following recommendations are of interest:
* I ----------------------------------------------------------------------------------------------------------
===== #1 at 2009-07-28 09:31:57
* OK
===== #2 at 2009-07-28 09:32:57
* OK
===== #3 at 2009-07-28 09:33:58
* OK
===== #4 at 2009-07-28 09:34:59
* OK
===== #5 at 2009-07-28 09:35:59
* OK
When I set parameter '_MBLOCK_STRAT_SIZE' to recomended value (32768) next error occured:
-24931 parameter value higher than maximum allowed [[param_put -permanent MBLOCKSTRAT_SIZE "32768"]]
Maximum allowed was 32767, so it should be OK.
Should I send you all DB parameters ?
Dusan
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
And here are my selects (result is 154274rows of all 154284rows).
I will divide it to more parts (formating problem) first is "column list" and second is "from" part and "where" conditions.
select logfile.Id, logfile.TStamp, logfile.Server_Id, logfile.Car_Id,
logfile.IsPrivate, logfile.Employee_Id, logfile.Table_Type, logfile.Table_Id,
logfile.MsgTime, logfile.Msg_Id, logfile.MsgState, logfile.Descr_DE as Descr,
logfile.Country, logfile.PostCode, logfile.Town, logfile.Street, logfile.Lon,
logfile.Lat, logfile.User_Id, logfile.MsgValid, logfile.Ignition, logfile.KmPerDay,
logfile.Speed, logfile.Address_Id, logfile.Input, logfile.AnalogLit, logfile.TotalKm,
decode (logfile.TotalKm, -1, '', logfile.TotalKm) as TotalKmDescr,
decode (logfile.analogPerc, -1, '', logfile.analogPerc) as AnalogPercDescr,
decode (logfile.analogLit, -1, '', logfile.analogLit) as AnalogLitDescr,
decode (logfile.KmPerDay, -1, '', logfile.KmPerDay) as KmPerDayDescr,
decode (logfile.IgnitionCnt, -1, '', chr (IgnitionCnt div 3600) || ':' ||
lfill (chr ((IgnitionCnt div 60) mod 60), '0', 2)) as IgnitionCntDescr,
decode (logfile.InputCnt, -1, '', chr (InputCnt div 3600) || ':' ||
lfill (chr ((InputCnt div 60) mod 60), '0', 2)) as InputCntDescr,
case
when logfile.IgnitionCnt = 0 then ''
when logfile.IgnitionCnt = -1 then '' when logfile.InputCnt = -1 then ''
else chr (fixed (InputCnt * 100 / IgnitionCnt, 30, 1))
end as InputPercentDescr,
decode (logfile.Speed, -1, '', logfile.Speed) as SpeedDescr,
case car.Id
when -3 then value (server.Descr || ' [' || server.Licence || ']', '')
else rtrim (car.Name || ', ' || car.RegNr, ', ')
end as CarName,
decode (MsgState, 1, 'Sen', 2, 'Ges', 3, 'Nic', 4, 'Err', 5, 'Err', 6, 'Emp',
7, 'Alarm', 8, 'Gsp', 9, 'Err', 10, 'EmpTB', 11, 'EmpTE', '') as MsgStateDescr,
case
when 'superroland' <> 'superroland' and isPrivate = 1 then 'Privat'
when logfile.Msg_Id < 256 then value (fleet_user.name, '')
else value (decode (address.SerialNo, -1, '', address.SerialNo || ', ' ) ||
address.Name, logfile.Country || ' ' || logfile.Postcode || ' ' || logfile.Town ||
rtrim (' - ' || logfile.Townpart, ' - ') || rtrim (', ' || logfile.Street, ', '))
end as PosDescr
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
select No1 takes aprox. 21min to return result:
from logfile
join
(
select Id, Name, RegNr from car
union
select -3 as Id, '' as Name, '' as RegNr from dual
) as car on logfile.Car_Id = car.Id
left join address on logfile.Address_Id = address.Id
left join server on logfile.Server_Id = server.Id
left join fleet_user on logfile.User_Id = fleet_user.Id
where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
has next execution plan:
ADMIN CAR TABLE SCAN 12
ADMIN DUAL TABLE SCAN 1
INTERNAL TEMPORARY RESULT TABLE SCAN 1
ADMIN LOGFILE IDX_LOGFILE_CARID JOIN VIA INDEXED COLUMN 5002
CAR_ID (USED INDEX COLUMN)
ADMIN ADDRESS ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN SERVER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN FLEET_USER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 580
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 1
DistinctPushDownTo 3
DistinctPushDownFrom 2
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
select No2 takes aprox. 22min to return result:
from
(
select logfile.* from logfile where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
) as logfile
join
(
select Id, Name, RegNr from car
union
select -3 as Id, '' as Name, '' as RegNr from dual
) as car on logfile.Car_Id = car.Id
left join address on logfile.Address_Id = address.Id
left join server on logfile.Server_Id = server.Id
left join fleet_user on logfile.User_Id = fleet_user.Id
execution plan:
ADMIN LOGFILE TABLE SCAN 5274
ADMIN CAR TABLE SCAN 12
ADMIN DUAL TABLE SCAN 1
INTERNAL TEMPORARY RESULT TABLE SCAN 1
INTERNAL TEMPORARY RESULT JOIN VIA KEY RANGE 1
TABLE TEMPORARY SORTED
CAR_ID (USED COLUMN)
ADMIN ADDRESS ID JOIN VIA KEY COLUMN 1
ADMIN SERVER ID JOIN VIA KEY COLUMN 1
ADMIN FLEET_USER ID JOIN VIA KEY COLUMN 1
RESULT IS COPIED , COSTVALUE IS 3
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 2
DistinctPushDownTo 3
DistinctPushDownFrom 2
PushDownProjection 1
Can you help me please how to make it faster ??
Thank you, Dusan
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Dusan,
22 min. for this query is really long and very likely caused by a too small buffer cache in the first place.
Currently you seem to have 10000 pages cache (ca. 78 MB). That's really not too much, so let's give the database a bit more - how about 25000 pages (ca 195 MB).
Now it should be possible to run the whole query in ram. Should be a lot quicker by now.
Next step: please, just for the sake of this test, remove that awkward subselect for the CAR table and just join it.
What does the execution plan look like then? How about the execution time?
Concerning the false recommendation of the paramter check script: this was a typo in the script that should be handled the way you already did
regards,
Lars
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Yes, you are right. The select is quicker after parameter "CACHE_SIZE" change from 10000 to 25000.
Select No1 (with car subselect) was aprox. 21min before, and now aprox.9min.
from logfile
join
(
select Id, Name, RegNr from car
union
select -3 as Id, '' as Name, '' as RegNr from dual
) as car on logfile.Car_Id = car.Id
left join address on logfile.Address_Id = address.Id
left join server on logfile.Server_Id = server.Id
left join fleet_user on logfile.User_Id = fleet_user.Id
where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
here is execution plan for it:
ADMIN CAR TABLE SCAN 12
ADMIN DUAL TABLE SCAN 1
INTERNAL TEMPORARY RESULT TABLE SCAN 1
ADMIN LOGFILE IDX_LOGFILE_CARID JOIN VIA INDEXED COLUMN 5002
CAR_ID (USED INDEX COLUMN)
ADMIN ADDRESS ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN SERVER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN FLEET_USER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 580
QUERYREWRITE - APPLIED RULES:
DistinctPullUp 1
DistinctPushDownTo 3
DistinctPushDownFrom 2
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
And now when I deleted CAR subselect it takes aprox. 4min:
from logfile
join
car on logfile.Car_Id = car.Id
left join address on logfile.Address_Id = address.Id
left join server on logfile.Server_Id = server.Id
left join fleet_user on logfile.User_Id = fleet_user.Id
where LOGFILE.TSTAMP >= '2009-06-22 00:00:00'
execution plan is :
ADMIN CAR TABLE SCAN 12
ADMIN LOGFILE IDX_LOGFILE_CARID JOIN VIA INDEXED COLUMN 5002
CAR_ID (USED INDEX COLUMN)
ADMIN ADDRESS ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN SERVER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
ADMIN FLEET_USER ID JOIN VIA KEY COLUMN 1
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 72035
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hi Dusan,
Ok, that's already much better.
Now to further improve the query all that I can propose it to make the timestamp of the logfile table its primary key or to put the ID into the where clause instead.
As there is currently no other possible access path available the execution plan cannot be tuned any further.
regards,
Lars
Answers (2)
Answers (2)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Have you tried an explicit update statistcs?
e.g.
update statistics logfileRegards
Alexander
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
Hello Dusan,
Are the table statistics up-to-date?
Regards
Alexander
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
- Usage Procedure Logging SAP Tables in Technology Q&A
- Problem with Select-Options in Technology Q&A
- Checking HANA Cloud Vector Engine performances in Technology Blogs by SAP
- SAP MAXDB : Content Server Administration - Repository Status in Technology Q&A
- HANA DB usage of JSON embedded functions in Technology Q&A
| User | Count |
|---|---|
| 88 | |
| 10 | |
| 10 | |
| 9 | |
| 7 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.