Hi,
I am trying to insert a record in a table. Table does not have index, trigger or check constrint.

Following is my explain plan for a singel record insert which take almost 2 secods for me. Can anybdy suggest how to improve this?


Sample of explain plan is pasted below.

Thanks
Ads




Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = Yes
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "BCSUSR"


SQL Statement:

Insert into STG_DIM_LOB(v_rc_code, fic_mis_date, v_rc_desc,
v_lvl18_resp_node, v_lvl18_resp_name, v_lvl20_resp_node,
v_lvl20_resp_name, v_lvl22_resp_node, v_lvl22_resp_name,
v_lvl24_resp_node, v_lvl24_resp_name, v_lvl28_resp_node,
v_lvl28_resp_name, v_lvl35_resp_node, v_lvl35_resp_name,
v_lvl42_resp_node, v_lvl42_resp_name, v_lvl44_resp_node,
v_lvl44_resp_name, v_lvl46_resp_node, v_lvl46_resp_name,
v_lvl48_resp_node, v_lvl48_resp_name, v_lvl54_resp_node,
v_lvl54_resp_name, v_lvl56_resp_node, v_lvl56_resp_name,
v_lvl58_resp_node, v_lvl58_resp_name, v_lvl60_resp_node,
v_lvl60_resp_name, v_lvl84_resp_node, v_lvl84_resp_name,
v_lvl86_resp_node, v_lvl86_resp_name, v_lvl90_resp_node,
v_lvl90_resp_name, n_resp_bod_dim_key, v_geo_code,
v_geo_desc, d_record_start_date, d_record_end_date,
f_latest_record_indicator, v_bod_node_summary_lvl_name,
v_bmo_buss_unit_name, v_bank_group_acronym,
v_bod_node_src_code)Values('2', TODATE('20060331', '
YYYYMMDD'), 'WINDSOR MAIN ONTARIO', null, null, null, null,
null, null, null, null, null, null, 'U7312', 'WINDSOR M/O
CONS', 'U7364', 'WINDSOR AREA', 'U7035', 'WINDSOR DISTRICT',
'U7002', 'ONTARIO DIVISION', 'U7000', 'SUM OF 5 DIVISIONS','
V0076', 'PHISICAL DISTR DIV - CONS', 'U0030', 'PCFS MARKET (
@ TERM MTR)', 'U7900', 'P&C CANADA', 'U0001', 'PERS&COMM
CUSTOMER GROUP(PCCG)', '99990', 'OPERATING COMPANIES', '
K9898', 'TOTAL BANK TEB', '99995', 'TOTAL BANK COST CENTRES'
, 0, 'CA', 'CANADA', null, null, null, 'PCCG Canada', null,'
PCCG', 'I')


Section Code Page = 819

Estimated Cost = 70.808212
Estimated Cardinality = 0.083333

Coordinator Subsection - Main Processing:
Distribute Subsection #2
| Directed to Local Coordinator Node
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4

Subsection #1:
Access Table Queue ID = q1 #Columns = 47
Insert: Table Name = BCSUSR.STG_DIM_LOB ID = 14,140

Subsection #2:
Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
| Conditional Evaluation
| | Condition #1:
| | | #Predicates = 1
| | Condition #2:
| | | #Predicates = 1
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
| | Condition #3:
| | | #Predicates = 1
| | Condition #4:
| | | #Predicates = 1
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
| | Condition #5:
| | | #Predicates = 1
| | Condition #6:
| | | #Predicates = 1
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
| | Condition #7:
| | | #Predicates = 1
| | Condition #8:
| | | #Predicates = 1
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
| | Condition #9:
| | | #Predicates = 1
| | Condition #10:
| | | #Predicates = 1
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
| | Condition #11:
| | | #Predicates = 1
| | Condition #12:
| | | #Predicates = 1
| | | Data Stream 1:
| | | | Correlated
| | | | Piped
| | | | Table Constructor
| | | | | 1-Row(s)
| | | | Residual Predicate(s)
| | | | | #Predicates = 1
| | | | Nested Loop Join
| | | | | Data Stream 2:
| | | | | | Correlated
| | | | | | Not Piped
| | | | | | Table Constructor
| | | | | | | 1-Row(s)
| | | | | | Insert Into Temp Table ID = t1
| | | | | | | #Columns = 0
| | | | | End of Data Stream 2
| | | | | Access Temp Table ID = t1
| | | | | | #Columns = 0
| | | | | | Single Record
| | | | | | Relation Scan
| | | | | | | Prefetch: 1 Pages
| | | | Nested Loop Join
| | | | | Piped Inner
| | | | | Table Constructor
| | | | | | 1-Row(s)
| | | | | Residual Predicate(s)
| | | | | | #Predicates = 1
| | | | | | Conditional Evaluation
| | | | | | | Condition #1:
| | | | | | | | #Predicates = 1
| | | | | | | Condition #2:
| | | | | | | | #Predicates = 1
| | | | | | | | Data Stream 3:
| | | | | | | | | Correlated
| | | | | | | | | Piped
| | | | | | | | | Table Constructor
| | | | | | | | | | 1-Row(s)
| | | | | | | | | Nested Loop Join
| | | | | | | | | | Piped Inner
| | | | | | | | | | Table Constructor
| | | | | | | | | | | 1-Row(s)
| | | | | | | | | | Residual Predicate(s)
| | | | | | | | | | | #Predicates = 1
| | | | | | | | | | Nested Loop Join
| | | | | | | | | | | Piped Inner
| | | | | | | | | | | Table Constructor
| | | | | | | | | | | | 1-Row(s)

Estimate cost of 70.808212 is quite fast, usually .05 seconds or faster elapsed time inside DB2. You can do a snaphot for dynamic SQL to verify the elpased time.

Are you submitting this from a remote client? Is the client already connected to the database? More information is needed on exactly how you submit the insert statement.

hi,
Thanks for replying.
I am pretty much new to DB2. I have a bunch of such (like the one shown in the plan) insert statements to be exeted which are built dynamically and fired on the database as batch. we have a multi partitioned database.
My DBA provided me the logs of these statement fired on the database, and I can see that each statement execution time is more than a second. Hence I tried to execute the statement on my db2 client (Command promt). Again I can see that it takes alomost same amount of time.

Yes, These statements fired from remote client. And client is already connected.

Thanks again
Ads
Ads

I need to see the "log" that you got from your DBA that shows it took 1 second for the insert. I suspect that much of the 1 second you are seeing is in the network, and not in DB2, but I would need to see a snapshot for dyanmic SQL to determine that.

DB2 partitioning (hash partitioning with DPF) is designed for parallel operations, usually with data warehouses that have a lot of table scans needed to retrieve the data.

However, you are only inserting one row at a time, and a single insert cannot be parallelized, so you should not expect good performance for this type of SQL with partitioning compared to a non-partitioned database. For best results with partitioning you should use batch inserts (multiple value clauses for each insert), or db2atld (the DB2 Autoloader which is described in the DB2 Command Reference manual).

Also, there are a lot of other tuning factors that you have not provided that could be affecting performance.

But I will make one suggestion: Change the value TODATE('20060331', '
YYYYMMDD') to '2006-03-31' (that is all you need if the column is defined as a DB2 date).

It seems a lot more is going on there than meets the eye. If you look at the plan fragment included in the original message you'll notice that after the actual insert there's some stuff happening on the coordinator node that includes NL joins and application of predicates... Might me something related to the referential integrity constraints, or a bunch of MQT's are being refreshed, or something else...