Home » RDBMS Server » Performance Tuning » 11g- query Performance issue (11g,Release 11.1.0.6.0,windowsXP)
| 11g- query Performance issue [message #449261] |
Sun, 28 March 2010 23:34  |
vs0037476
Messages: 4
Registered: March 2010
|
Junior Member |
|
|
How to optimize a select query on a tabe that is range partition on date index.
Insertion is occuring at a very high rate in this table.
Latest 600 records is to be retrived.
The explain plan shows that fetch is taking lot of time in disc IO,s
1- plan is also poor due to full table index.
2- Optimizer is not picking the better plan may be high insertion making the index selection redundant.
The issue getting surfaced when in a day partition total number of alerts reaches more than 10 lac.
Please suggest how to attack on such issues.
|
|
|
|
|
|
| Re: 11g- query Performance issue [message #449292 is a reply to message #449262] |
Mon, 29 March 2010 01:57  |
vs0037476
Messages: 4
Registered: March 2010
|
Junior Member |
|
|
CREATE TABLE AlmEvtTbl
(
AlmEvtId NUMBER(20),
AlmType NUMBER(10) CONSTRAINT AlmEvtTbl_AlmType_NN NOT NULL CONSTRAINT chk_AlmEvtTbl_AlmType CHECK(AlmType BETWEEN 1 AND 5),
ComponentId NUMBER(10),
CompType NUMBER(10),
CompNum NUMBER(10),
CompParentNum NUMBER(10),
CompDBId NUMBER(10),
TimeStamp DATE CONSTRAINT almTimeStamp_NN NOT NULL,
Severity NUMBER(10) CONSTRAINT almSvrty_NN NOT NULL CONSTRAINT chk_almSvrty CHECK(Severity BETWEEN 0 AND 7),
Category NUMBER(10) CONSTRAINT almCategory_NN NOT NULL CONSTRAINT chk_almCategory CHECK(Category BETWEEN 1 AND 16),
State NUMBER(10) CONSTRAINT almState_NN NOT NULL CONSTRAINT chk_almState CHECK(State BETWEEN 1 AND 3),
CauseCode NUMBER(10) CONSTRAINT almCauseCode_NN NOT NULL CONSTRAINT chk_almCauseCode_NZ CHECK(CauseCode > 0),
UnitType NUMBER(10) CONSTRAINT almUnitType_NN NOT NULL CONSTRAINT chk_almUnitType_NZ CHECK(UnitType BETWEEN 0 AND 14),
UnitId NUMBER(10),
UnitName VARCHAR2(2048),
UnitDbId NUMBER(10),
ServerName VARCHAR2(256),
AckTimeStamp DATE,
DelTimeStamp DATE,
StrParam VARCHAR2(2048),
CustomerId NUMBER(10),
ParentCustId NUMBER(10),
ProfileId NUMBER(10),
ParentProfileId NUMBER(10),
ScriptName VARCHAR2(256),
SRCIPADDRFAMILY NUMBER(1),
SRCIPADDRESS11 NUMBER(10),
SRCIPADDRESS12 NUMBER(10),
SRCIPADDRESS13 NUMBER(10),
SRCIPADDRESS14 NUMBER(10),
DESTIPADDRFAMILY NUMBER(1),
DESTIPADDRESS11 NUMBER(10),
DESTIPADDRESS12 NUMBER(10),
DESTIPADDRESS13 NUMBER(10),
DESTIPADDRESS14 NUMBER(10),
SrcPort NUMBER(10),
DestPort NUMBER(10),
SessionId NUMBER(10),
IfId NUMBER(10),
SrcPhyIf NUMBER(10),
DestPhyIf NUMBER(10),
SessionDir NUMBER(10),
AppDir NUMBER(10),
PolicyVersion NUMBER(20),
RuleId NUMBER(10),
CustomerName VARCHAR2(256),
IdsUrlId1 NUMBER(10),
IdsUrlId2 NUMBER(10),
IdsUrlId3 NUMBER(10),
AttkDir NUMBER(10),
SrcZone NUMBER(10),
DestZone NUMBER(10),
L4Protocol NUMBER(10),
UserId NUMBER(10),
ExtraParam1 NUMBER(10),
ExtraParam2 NUMBER(10),
ExtraParam3 NUMBER(10),
ExtraParam4 NUMBER(10),
ExtraParam5 NUMBER(10),
ExtraStrParam VARCHAR2(2048),
ExtraStrParam2 VARCHAR2(256),
ExtraStrParam3 VARCHAR2(256),
ExtraStrParam4 varchar2(2048),
ExtraStrParam5 varchar2(2048),
ExtraStrParam6 varchar2(2048),
ExtraStrParam7 varchar2(2048),
ExtraStrParam8 varchar2(2048),
ExtraDataParam RAW(256),
subCategory NUMBER(10) CONSTRAINT chk_almSubCat CHECK (SubCategory BETWEEN 1 AND 22),
RiskCategory NUMBER(10) CONSTRAINT chk_almRiskCat CHECK (RiskCategory BETWEEN 1 AND 5),
AssetValue NUMBER(10) CONSTRAINT chk_almAssetVal CHECK (AssetValue BETWEEN 1 AND 3),
IPSAction NUMBER(10) CONSTRAINT chk_almIpsAction CHECK (IPSAction BETWEEN 1 AND 8),
UserName VARCHAR2(256),
IpAddrFamily1 NUMBER(1),
IPAddrValue11 NUMBER(10),
IPAddrValue12 NUMBER(10),
IPAddrValue13 NUMBER(10),
IPAddrValue14 NUMBER(10),
IpAddrFamily2 NUMBER(1) ,
IPAddrValue21 NUMBER(10),
IPAddrValue22 NUMBER(10),
IPAddrValue23 NUMBER(10),
IPAddrValue24 NUMBER(10),
futureInt1 NUMBER (10) default 0,
futureInt2 NUMBER (10) default 0,
futureInt3 NUMBER (10) default 0,
futureInt4 NUMBER (10) default 0,
futureStr1 VARCHAR2(256) default ' ',
futureStr2 VARCHAR2(256) default ' ' ,
CONSTRAINT AlmEvtTbl_PK PRIMARY KEY (AlmEvtId, Timestamp) USING INDEX LOCAL
)
PARTITION BY RANGE (TimeStamp) (
PARTITION ALMEVTTBLP20050101 VALUES LESS THAN
(TO_DATE('02-01-2005','DD-MM-YYYY'))
);
1- We have created two index on the table.
The issue getting surfaced when in a day partition total number of alerts reaches more than 10 lac.
2- We are using below two index
CREATE INDEX AlmEvtTbl_Index on AlmEvtTbl
(
TimeStamp DESC,
Category,
AlmEvtId DESC
)LOCAL;
CREATE INDEX AlmEvtTbl_index2 on AlmEvtTbl
(
CATEGORY,
CUSTOMERID,
PARENTCUSTID,
SEVERITY,
STATE,
ALMEVTID,
COMPONENTID,
UNITID,
COMPTYPE,
TIMESTAMP DESC
)LOCAL;
3- There is no management startegy for partitions except a new partition created and every night indexed is rebuild.
4- There was no stats collection explicitly. I did using dbconsole for every hour today. it might improve the results.
|
|
|
|
| Re: 11g- query Performance issue [message #449294 is a reply to message #449292] |
Mon, 29 March 2010 02:00 |
vs0037476
Messages: 4
Registered: March 2010
|
Junior Member |
|
|
1- The SQL and a short description of its purpose
The SQL::
select almevttbl.AlmEvtId, almevttbl.AlmType, almevtt
bl.ComponentId, almevttbl.TimeStamp, almevttbl.Severity, almevttbl.State, almevt
tbl.Category, almevttbl.CauseCode, almevttbl.UnitType, almevttbl.UnitId, almevtt
bl.UnitName, almevttbl.ServerName, almevttbl.StrParam, almevttbl.ExtraStrParam,
almevttbl.ExtraStrParam2, almevttbl.ExtraStrParam3, almevttbl.ParentCustId, alme
vttbl.ExtraParam1, almevttbl.ExtraParam2, almevttbl.ExtraParam3,almevttbl.ExtraP
aram4,almevttbl.ExtraParam5, almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,
almevttbl.SrcIPAddress12,almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, alme
vttbl.DESTIPADDRFAMILY,almevttbl.DestIPAddress11,almevttbl.DestIPAddress12,almev
ttbl.DestIPAddress13,almevttbl.DestIPAddress14, almevttbl.DestPort, almevttbl.S
rcPort, almevttbl.SessionDir, almevttbl.CustomerId, almevttbl.ProfileId, almevtt
bl.ParentProfileId, almevttbl.CustomerName, almevttbl.AttkDir, almevttbl.SubCate
gory, almevttbl.RiskCategory, almevttbl.AssetValue, almevttbl.IPSAction, almevtt
bl.l4Protocol,almevttbl.ExtraStrParam4 ,almevttbl.ExtraStrParam5,almevttbl.usern
ame,almevttbl.ExtraStrParam6,IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrVal
ue13,IPAddrValue14,IpAddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAdd
rValue24
FROM
AlmEvtTbl PARTITION(ALMEVTTBLP20100323)
WHERE AlmEvtId
IN
( SELECT * FROM ( SELECT /*+ FIRST_ROWS(1000) INDEX (AlmEvtTbl AlmEvtTbl_Index) */AlmEvtId FROM AlmEvtTbl PARTITION(ALMEVTTBLP20100323)
where ((AlmEvtTbl.Customerid = 0 or AlmEvtTbl.ParentCustId = 0)) ORDER BY AlmEvtTbl.TIMESTAMP DESC
)
WHERE ROWNUM < 602)
Order by timestamp desc
1- The Above Sql fetches the latest 600 records from the almevttbl.
2- This uses the index and as well hint.
3- Most of the time it works well. but for current partition
when insertion high then query takes too much time around 20 minutes to 40 minutes.
4- The Above table is range partitioned on the date.
2- The version of my database with 4-digits (Release 11.1.0.6.0)
3- Optimizer related parameters
SQL> show parameter opt;
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
filesystemio_options string
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
optimizer_use_sql_plan_baselines boolean TRUE
plsql_optimize_level integer 2
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 20
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- -----------------
cursor_sharing string EXACT
|
|
|
|
| Re: 11g- query Performance issue [message #449297 is a reply to message #449262] |
Mon, 29 March 2010 02:14 |
vs0037476
Messages: 4
Registered: March 2010
|
Junior Member |
|
|
AutoTrace Output
*********First Varinat
*****We Are using ALL_ROWS INDEX (AlmEvtTbl AlmEvtTbl_Index2)*****************
SQL> select almevttbl.AlmEvtId, almevttbl.AlmType, almevttbl.ComponentId, almevt
tbl.TimeStamp, almevttbl.Severity, almevttbl.State, almevttbl.Category, almevttb
l.CauseCode, almevttbl.UnitType, almevttbl.UnitId, almevttbl.UnitName, almevttbl
.ServerName, almevttbl.StrParam, almevttbl.ExtraStrParam, almevttbl.ExtraStrPara
m2, almevttbl.ExtraStrParam3, almevttbl.ParentCustId, almevttbl.ExtraParam1, alm
evttbl.ExtraParam2, almevttbl.ExtraParam3,almevttbl.ExtraParam4,almevttbl.ExtraP
aram5, almevttbl.SRCIPADDRFAMILY,almevttbl.SrcIPAddress11,almevttbl.SrcIPAddress
12,almevttbl.SrcIPAddress13,almevttbl.SrcIPAddress14, almevttbl.DESTIPADDRFAMILY
,almevttbl.DestIPAddress11,almevttbl.DestIPAddress12,almevttbl.DestIPAddress13,a
lmevttbl.DestIPAddress14, almevttbl.DestPort, almevttbl.SrcPort, almevttbl.Sess
ionDir, almevttbl.CustomerId, almevttbl.ProfileId, almevttbl.ParentProfileId, al
mevttbl.CustomerName, almevttbl.AttkDir, almevttbl.SubCategory, almevttbl.RiskCa
tegory, almevttbl.AssetValue, almevttbl.IPSAction, almevttbl.l4Protocol,almevttb
l.ExtraStrParam4 ,almevttbl.ExtraStrParam5,almevttbl.username,almevttbl.ExtraStr
Param6,IpAddrFamily1,IPAddrValue11,IPAddrValue12,IPAddrValue13,IPAddrValue14,IpA
ddrFamily2,IPAddrValue21,IPAddrValue22,IPAddrValue23,IPAddrValue24 FROM
AlmEvtTbl PARTITION(ALMEVTTBLP20100325) WHERE AlmEvtId IN ( SELECT * FROM ( SEL
ECT /*+ ALL_ROWS INDEX (AlmEvtTbl AlmEvtTbl_Index2) */AlmEvtId FROM AlmEvtTbl PA
RTITION(ALMEVTTBLP20100325) where ((AlmEvtTbl.Customerid = 0 or AlmEvtTbl.
ParentCustId = 0)) ORDER BY AlmEvtTbl.TIMESTAMP DESC) WHERE ROWNUM < 602) or
der by timestamp desc;
601 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 747477227
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 12673 | | 3771 (1)| 00:00:46 | | |
| 1 | SORT ORDER BY | | 23 | 12673 | | 3771 (1)| 00:00:46 | | |
| 2 | NESTED LOOPS | | | | | | | | |
| 3 | NESTED LOOPS | | 23 | 12673 | | 3770 (1)| 00:00:46 | | |
| 4 | SORT UNIQUE | | 601 | 3606 | | 2866 (1)| 00:00:35 | | |
| 5 | VIEW | VW_NSO_1 | 601 | 3606 | | 2866 (1)| 00:00:35 | | |
| 6 | HASH UNIQUE | | 601 | 7813 | | | | | |
|* 7 | COUNT STOPKEY | | | | | | | | |
| 8 | VIEW | | 169K| 2155K| | 2866 (1)| 00:00:35 | | |
|* 9 | SORT ORDER BY STOPKEY | | 169K| 3150K|10M | 2866 (1)| 00:00:35 | | |
| 10 | PARTITION RANGE SINGLE | | 169K| 3150K| | 1863 (1)| 00:00:23 | 25 | 25 |
|* 11 | INDEX FULL SCAN | ALMEVTTBL_INDEX2 | 169K| 3150K| | 1863 (1)| 00:00:23 | 25 | 25 |
| 12 | PARTITION RANGE SINGLE | | 1 | | | 2 (0)| 00:00:01 | 25 | 25 |
|* 13 | INDEX RANGE SCAN | ALMEVTTBL_PK | 1 | | | 2 (0)| 00:00:01 | 25 | 25 |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID| ALMEVTTBL | 1 | 545 | | 3 (0)| 00:00:01 | 25 | 25 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(ROWNUM<602)
9 - filter(ROWNUM<602)
11 - filter("ALMEVTTBL"."PARENTCUSTID"=0 OR "ALMEVTTBL"."CUSTOMERID"=0)
13 - access("ALMEVTID"="ALMEVTID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22643 consistent gets
19533 physical reads
37060 redo size
25538 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
42 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
601 rows processed
*********Explain Plain output**********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 84 14.98 367.72 38363 50540 0 1202
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 88 14.98 367.72 38363 50540 0 1202
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 82
Rows Row Source Operation
------- ---------------------------------------------------
601 SORT ORDER BY (cr=27897 pr=18830 pw=18830 time=46 us cost=3771 size=12673 card=23)
601 NESTED LOOPS (cr=27897 pr=18830 pw=18830 time=328 us)
601 NESTED LOOPS (cr=27811 pr=18805 pw=18805 time=220 us cost=3770 size=12673 card=23)
601 SORT UNIQUE (cr=27654 pr=18766 pw=18766 time=19 us cost=2866 size=3606 card=601)
601 VIEW VW_NSO_1 (cr=27654 pr=18766 pw=18766 time=21 us cost=2866 size=3606 card=601)
601 HASH UNIQUE (cr=27654 pr=18766 pw=18766 time=8 us)
601 COUNT STOPKEY (cr=27654 pr=18766 pw=18766 time=40 us)
601 VIEW (cr=27654 pr=18766 pw=18766 time=25 us cost=2866 size=2207283 card=169791)
601 SORT ORDER BY STOPKEY (cr=27654 pr=18766 pw=18766 time=14 us cost=2866 size=3226029 card=169791)
1800887 PARTITION RANGE SINGLE PARTITION: 25 25 (cr=27654 pr=18766 pw=18766 time=1372361 us cost=1863 size=3226029 card=169791)
1800887 INDEX FULL SCAN ALMEVTTBL_INDEX2 PARTITION: 25 25 (cr=27654 pr=18766 pw=18766 time=1338919 us cost=1863 size=3226029 card=169791)(object id 72585)
601 PARTITION RANGE SINGLE PARTITION: 25 25 (cr=157 pr=39 pw=39 time=0 us cost=2 size=0 card=1)
601 INDEX RANGE SCAN ALMEVTTBL_PK PARTITION: 25 25 (cr=157 pr=39 pw=39 time=0 us cost=2 size=0 card=1)(object id 71739)
601 TABLE ACCESS BY LOCAL INDEX ROWID ALMEVTTBL PARTITION: 25 25 (cr=86 pr=25 pw=25 time=0 us cost=3 size=545 card=1)
********************************************************************************
CM: removed some blank lines
[Updated on: Mon, 29 March 2010 03:39] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Nov 22 07:56:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
