Re: Count(*) slower than *
From: ddf <oratune_at_msn.com>
Date: Mon, 15 Mar 2010 14:22:17 -0700 (PDT)
Message-ID: <b2df8691-c36b-4391-9001-0405f34ac817_at_s25g2000prd.googlegroups.com>
On Mar 15, 4:54 pm, "bob123" <bob..._at_gmail.com> wrote:
> Hi,
>
> I have the following query (9.2.0.6)
>
> with * => <1s
>
> with count(*) => 7:52 min
>
> do you know why ?
>
> Thanks in advance
>
> P98> SELECT count(*)
>
> 2 FROM siebel.s_party t1,
>
> 3 siebel.s_postn_con t2,
>
> 4 siebel.s_party t3,
>
> 5 siebel.s_evt_act t4,
>
> 6 siebel.s_contact t5
>
> 7 WHERE t3.row_id = t5.par_row_id
>
> 8 AND t5.pr_postn_id = t2.postn_id
>
> 9 AND t5.row_id = t2.con_id
>
> 10 AND t2.postn_id = t1.row_id
>
> 11 AND t4.target_per_id = t3.row_id
>
> 12 AND ( (t4.subtype_cd = 'Participant Relem')
>
> 13 AND ( t5.emp_flg = 'N'
>
> 14 AND (t5.status_cd != 'Invalide' OR t4.evt_stat_cd = 'A
> particip '
>
> 15 )
>
> 16 )
>
> 17 )
>
> 18 AND (t4.par_evt_id = '1-5RVX5F')
>
> 19 /
>
> Elapsed: 00:07:52.09
>
> Execution Plan
>
> ----------------------------------------------------------
>
> 0
>
> SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=127)
>
> 1 0
>
> SORT (AGGREGATE)
>
> 2 1
>
> NESTED LOOPS (Cost=5 Card=1 Bytes=127)
>
> 3 2
>
> NESTED LOOPS (Cost=4 Card=13348 Bytes=1414888)
>
> 4 3
>
> NESTED LOOPS (Cost=3 Card=1 Bytes=95)
>
> 5 4
>
> NESTED LOOPS (Cost=2 Card=1 Bytes=52)
>
> 6 5
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' (Cost=1 Card=1
> Bytes=41)
>
> 7 6
>
> INDEX (RANGE SCAN) OF 'S_EVT_ACT_F11' (NON-UNIQUE) (Cost=3
> Card=2)
>
> 8 5
>
> INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>
> 9 4
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=1 Card=1
> Bytes=43)
>
> 10 9
>
> INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
>
> 11 3
>
> INDEX (FULL SCAN) OF 'S_PARTY_P1' (UNIQUE) (Cost=95 Card=263570
> Bytes=2899270)
>
> 12 2
>
> INDEX (RANGE SCAN) OF 'S_POSTN_CON_M3' (NON-UNIQUE)
>
> Statistics
>
> ----------------------------------------------------------
>
> 0 recursive calls
>
> 0 db block gets
>
> 163991324 consistent gets
>
> 452 physical reads
>
> 0 redo size
>
> 199 bytes sent via SQL*Net to client
>
> 278 bytes received via SQL*Net from client
>
> 2 SQL*Net roundtrips to/from client
>
> 0 sorts (memory)
>
> 0 sorts (disk)
>
> 1 rows processed
>
> P98> ed
>
> Wrote file afiedt.buf
>
> 1 SELECT *
>
> 2 FROM siebel.s_party t1,
>
> 3 siebel.s_postn_con t2,
>
> 4 siebel.s_party t3,
>
> 5 siebel.s_evt_act t4,
>
> 6 siebel.s_contact t5
>
> 7 WHERE t3.row_id = t5.par_row_id
>
> 8 AND t5.pr_postn_id = t2.postn_id
>
> 9 AND t5.row_id = t2.con_id
>
> 10 AND t2.postn_id = t1.row_id
>
> 11 AND t4.target_per_id = t3.row_id
>
> 12 AND ( (t4.subtype_cd = 'Participant Relem')
>
> 13 AND ( t5.emp_flg = 'N'
>
> 14 AND (t5.status_cd != 'Invalide' OR t4.evt_stat_cd = 'A
> particip '
>
> 15 )
>
> 16 )
>
> 17 )
>
> 18* AND (t4.par_evt_id = '1-5RVX5F')
>
> P98> /
>
> 310 rows selected.
>
> Elapsed: 00:00:00.03
>
> Execution Plan
>
> ----------------------------------------------------------
>
> 0
>
> SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=1553)
>
> 1 0
>
> NESTED LOOPS (Cost=5 Card=1 Bytes=1553)
>
> 2 1
>
> NESTED LOOPS (Cost=4 Card=1 Bytes=1483)
>
> 3 2
>
> NESTED LOOPS (Cost=3 Card=1 Bytes=1331)
>
> 4 3
>
> NESTED LOOPS (Cost=2 Card=1 Bytes=754)
>
> 5 4
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' (Cost=1 Card=1
> Bytes=684)
>
> 6 5
>
> INDEX (RANGE SCAN) OF 'S_EVT_ACT_F11' (NON-UNIQUE) (Cost=3
> Card=2)
>
> 7 4
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY' (Cost=1 Card=1
> Bytes=70)
>
> 8 7
>
> INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>
> 9 3
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=1 Card=1
> Bytes=577)
>
> 10 9
>
> INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
>
> 11 2
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON' (Cost=1 Card=5
> Bytes=760)
>
> 12 11
>
> INDEX (RANGE SCAN) OF 'S_POSTN_CON_M3' (NON-UNIQUE)
>
> 13 1
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY' (Cost=1 Card=1 Bytes=70)
>
> 14 13
>
> INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>
> Statistics
>
> ----------------------------------------------------------
>
> 0 recursive calls
>
> 0 db block gets
>
> 4368 consistent gets
>
> 0 physical reads
>
> 0 redo size
>
> 188330 bytes sent via SQL*Net to client
>
> 419 bytes received via SQL*Net from client
>
> 22 SQL*Net roundtrips to/from client
>
> 0 sorts (memory)
>
> 0 sorts (disk)
>
> 310 rows processed
Date: Mon, 15 Mar 2010 14:22:17 -0700 (PDT)
Message-ID: <b2df8691-c36b-4391-9001-0405f34ac817_at_s25g2000prd.googlegroups.com>
On Mar 15, 4:54 pm, "bob123" <bob..._at_gmail.com> wrote:
> Hi,
>
> I have the following query (9.2.0.6)
>
> with * => <1s
>
> with count(*) => 7:52 min
>
> do you know why ?
>
> Thanks in advance
>
> P98> SELECT count(*)
>
> 2 FROM siebel.s_party t1,
>
> 3 siebel.s_postn_con t2,
>
> 4 siebel.s_party t3,
>
> 5 siebel.s_evt_act t4,
>
> 6 siebel.s_contact t5
>
> 7 WHERE t3.row_id = t5.par_row_id
>
> 8 AND t5.pr_postn_id = t2.postn_id
>
> 9 AND t5.row_id = t2.con_id
>
> 10 AND t2.postn_id = t1.row_id
>
> 11 AND t4.target_per_id = t3.row_id
>
> 12 AND ( (t4.subtype_cd = 'Participant Relem')
>
> 13 AND ( t5.emp_flg = 'N'
>
> 14 AND (t5.status_cd != 'Invalide' OR t4.evt_stat_cd = 'A
> particip '
>
> 15 )
>
> 16 )
>
> 17 )
>
> 18 AND (t4.par_evt_id = '1-5RVX5F')
>
> 19 /
>
> Elapsed: 00:07:52.09
>
> Execution Plan
>
> ----------------------------------------------------------
>
> 0
>
> SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=127)
>
> 1 0
>
> SORT (AGGREGATE)
>
> 2 1
>
> NESTED LOOPS (Cost=5 Card=1 Bytes=127)
>
> 3 2
>
> NESTED LOOPS (Cost=4 Card=13348 Bytes=1414888)
>
> 4 3
>
> NESTED LOOPS (Cost=3 Card=1 Bytes=95)
>
> 5 4
>
> NESTED LOOPS (Cost=2 Card=1 Bytes=52)
>
> 6 5
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' (Cost=1 Card=1
> Bytes=41)
>
> 7 6
>
> INDEX (RANGE SCAN) OF 'S_EVT_ACT_F11' (NON-UNIQUE) (Cost=3
> Card=2)
>
> 8 5
>
> INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>
> 9 4
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=1 Card=1
> Bytes=43)
>
> 10 9
>
> INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
>
> 11 3
>
> INDEX (FULL SCAN) OF 'S_PARTY_P1' (UNIQUE) (Cost=95 Card=263570
> Bytes=2899270)
>
> 12 2
>
> INDEX (RANGE SCAN) OF 'S_POSTN_CON_M3' (NON-UNIQUE)
>
> Statistics
>
> ----------------------------------------------------------
>
> 0 recursive calls
>
> 0 db block gets
>
> 163991324 consistent gets
>
> 452 physical reads
>
> 0 redo size
>
> 199 bytes sent via SQL*Net to client
>
> 278 bytes received via SQL*Net from client
>
> 2 SQL*Net roundtrips to/from client
>
> 0 sorts (memory)
>
> 0 sorts (disk)
>
> 1 rows processed
>
> P98> ed
>
> Wrote file afiedt.buf
>
> 1 SELECT *
>
> 2 FROM siebel.s_party t1,
>
> 3 siebel.s_postn_con t2,
>
> 4 siebel.s_party t3,
>
> 5 siebel.s_evt_act t4,
>
> 6 siebel.s_contact t5
>
> 7 WHERE t3.row_id = t5.par_row_id
>
> 8 AND t5.pr_postn_id = t2.postn_id
>
> 9 AND t5.row_id = t2.con_id
>
> 10 AND t2.postn_id = t1.row_id
>
> 11 AND t4.target_per_id = t3.row_id
>
> 12 AND ( (t4.subtype_cd = 'Participant Relem')
>
> 13 AND ( t5.emp_flg = 'N'
>
> 14 AND (t5.status_cd != 'Invalide' OR t4.evt_stat_cd = 'A
> particip '
>
> 15 )
>
> 16 )
>
> 17 )
>
> 18* AND (t4.par_evt_id = '1-5RVX5F')
>
> P98> /
>
> 310 rows selected.
>
> Elapsed: 00:00:00.03
>
> Execution Plan
>
> ----------------------------------------------------------
>
> 0
>
> SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=1 Bytes=1553)
>
> 1 0
>
> NESTED LOOPS (Cost=5 Card=1 Bytes=1553)
>
> 2 1
>
> NESTED LOOPS (Cost=4 Card=1 Bytes=1483)
>
> 3 2
>
> NESTED LOOPS (Cost=3 Card=1 Bytes=1331)
>
> 4 3
>
> NESTED LOOPS (Cost=2 Card=1 Bytes=754)
>
> 5 4
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT' (Cost=1 Card=1
> Bytes=684)
>
> 6 5
>
> INDEX (RANGE SCAN) OF 'S_EVT_ACT_F11' (NON-UNIQUE) (Cost=3
> Card=2)
>
> 7 4
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY' (Cost=1 Card=1
> Bytes=70)
>
> 8 7
>
> INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>
> 9 3
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=1 Card=1
> Bytes=577)
>
> 10 9
>
> INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
>
> 11 2
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN_CON' (Cost=1 Card=5
> Bytes=760)
>
> 12 11
>
> INDEX (RANGE SCAN) OF 'S_POSTN_CON_M3' (NON-UNIQUE)
>
> 13 1
>
> TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY' (Cost=1 Card=1 Bytes=70)
>
> 14 13
>
> INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
>
> Statistics
>
> ----------------------------------------------------------
>
> 0 recursive calls
>
> 0 db block gets
>
> 4368 consistent gets
>
> 0 physical reads
>
> 0 redo size
>
> 188330 bytes sent via SQL*Net to client
>
> 419 bytes received via SQL*Net from client
>
> 22 SQL*Net roundtrips to/from client
>
> 0 sorts (memory)
>
> 0 sorts (disk)
>
> 310 rows processed
Select * can return data immediately even if there are more records to fetch; count(*) must process all records meeting the criteria before returning the desired value. Examine both execution plans and you see a SORT(AGGREGATE) for the count(*) query which is missing in the plain vanilla select; also note that the plain select generated 4368 consistent gets and 0 physical reads where the count(*) query generated 163991324 consistent gets and 452 physical reads. Running the count(*) query first may have 'primed' the database for the second query which uses exactly the same critera from exactly the same tables. Have you run the count(*) qiuery a second time, after the select * offering, and if so is the time tor return the result dimished?
David Fitzjarrell Received on Mon Mar 15 2010 - 16:22:17 CDT