Re: query performance

Jim Poe wrote:
>
> tkprof results
>
> TEST
>
> ****************************************************************************
> ****
>
> SELECT DISTINCT SUBM_QUOTE_SRCH.SUBM_ID, SUBM_QUOTE_SRCH.PDCR_NAME,
> SUBM_QUOTE_SRCH.INSD_NAME, SUBM_QUOTE_SRCH.INSD_PRIMARY_CITY,
> SUBM_QUOTE_SRCH.INSD_PRIMARY_ST, SUBM_QUOTE_SRCH.SUBM_ENTRY_DT,
> SUBM_QUOTE_SRCH.SUBM_SCHED_DT
> FROM
> SUBM_QUOTE_SRCH WHERE ( UPPER( SUBM_QUOTE_SRCH.INSD_NAME_SRCH ) LIKE
> '%' )
> ORDER BY SUBM_QUOTE_SRCH.SUBM_ID
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 1 0.00 0.00 0 3451 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 0.00 0.00 0 48129 5616
> 19
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 3 0.00 0.00 0 51580 5616
> 19
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 19
> ****************************************************************************
> ****
>
> DEVL
>
> ****************************************************************************
> ****
>
> SELECT DISTINCT SUBM_QUOTE_SRCH.SUBM_ID, SUBM_QUOTE_SRCH.PDCR_NAME,
> SUBM_QUOTE_SRCH.INSD_NAME, SUBM_QUOTE_SRCH.INSD_PRIMARY_CITY,
> SUBM_QUOTE_SRCH.INSD_PRIMARY_ST, SUBM_QUOTE_SRCH.SUBM_ENTRY_DT,
> SUBM_QUOTE_SRCH.SUBM_SCHED_DT
> FROM
> SUBM_QUOTE_SRCH WHERE ( UPPER( SUBM_QUOTE_SRCH.INSD_NAME_SRCH ) LIKE
> '%' )
> ORDER BY SUBM_QUOTE_SRCH.SUBM_ID
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> Parse 2 0.00 0.00 0 2042 23
> 0
> Execute 2 0.00 0.00 0 0 0
> 0
> Fetch 2 0.00 0.00 0 3688 6008
> 40
> ------- ------ -------- ---------- ---------- ---------- ---------- ------
> ----
> total 6 0.00 0.00 0 5730 6031
> 40
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 19
> ****************************************************************************
> ****
>
> Thanks for any help.
>
> --
> Jim Poe (jpoe_at_fulcrumit.com)

Just looking at these numbers, I would say this is an extremely inefficient query:

1. blocks read to rows processed is very poor (total query+total current)/total rows; blocks read to executions is also bad.

But more analysis without the explain plan from tkprof will be difficult, at best.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.