Home » RDBMS Server » Performance Tuning » Query tuning help (11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
| Query tuning help [message #563227] |
Fri, 10 August 2012 04:29  |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Hello All,
The below query takes more than 30 minutes to return data.
All the objects used are views. There is no direct reference to any table.
The views with _mnth_ have data for 7 distinct months.
The base table for all the views have a composite PK on the columns AR_ID (or ACCT_AR_ID),MSRMNT_PRD_ID
I need the order by, as the query is part of informatica code, and the order by helps in the further processing.
SELECT ac.ar_id AS acct_ar_id, m.msrmnt_prd_dt AS msrmnt_prd_dt
--removed the rest of column list to reduce size of code.
FROM edxf.ar_rsrv_mnth_v ac,
edxf.crdt_acct_mnth_v c,
edxf.crdt_acct_v ca,
(SELECT msrmnt_prd_id, msrmnt_prd_dt
FROM edxf.msrmnt_prd_v
WHERE msrmnt_prd_id =
(SELECT MAX (msrmnt_prd_id)
FROM edxf.ar_rsrv_mnth_v
WHERE msrmnt_prd_mnth =
TO_CHAR (ADD_MONTHS (TO_DATE ('20120731','yyyymmdd'),-1),'YYYYMM'))) m
WHERE ac.src_syst_code = 'ABC'
AND ac.msrmnt_prd_id = m.msrmnt_prd_id
AND ac.ar_id = c.acct_ar_id
AND ac.msrmnt_prd_mnth = c.msrmnt_prd_mnth
AND ac.ar_id = ca.acct_ar_id(+)
AND (ca.end_dt IS NULL OR ca.end_dt > m.msrmnt_prd_dt)
ORDER BY ac.ar_id
Explain Plan :
PLAN_TABLE_OUTPUT
Plan hash value: 124769705
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 2800 | 11436 (1)| 00:02:18 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ20009 | 25 | 2800 | 11436 (1)| 00:02:18 | | | Q2,09 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 25 | 2800 | 11436 (1)| 00:02:18 | | | Q2,09 | PCWP | |
| 4 | PX RECEIVE | | | | | | | | Q2,09 | PCWP | |
| 5 | PX SEND RANGE | :TQ20008 | | | | | | | Q2,08 | P->P | RANGE |
| 6 | BUFFER SORT | | 25 | 2800 | | | | | Q2,08 | PCWP | |
| 7 | NESTED LOOPS | | | | | | | | Q2,08 | PCWP | |
| 8 | NESTED LOOPS | | 25 | 2800 | 11402 (1)| 00:02:17 | | | Q2,08 | PCWP | |
|* 9 | FILTER | | | | | | | | Q2,08 | PCWC | |
| 10 | NESTED LOOPS OUTER | | 25 | 2600 | 11402 (1)| 00:02:17 | | | Q2,08 | PCWP | |
|* 11 | HASH JOIN | | 30 | 2850 | 11402 (1)| 00:02:17 | | | Q2,08 | PCWP | |
| 12 | PX RECEIVE | | 3274 | 239K| 11398 (1)| 00:02:17 | | | Q2,08 | PCWP | |
| 13 | PX SEND HASH | :TQ20006 | 3274 | 239K| 11398 (1)| 00:02:17 | | | Q2,06 | P->P | HASH |
|* 14 | HASH JOIN BUFFERED | | 3274 | 239K| 11398 (1)| 00:02:17 | | | Q2,06 | PCWP | |
| 15 | JOIN FILTER CREATE | :BF0000 | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 16 | PX RECEIVE | | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 17 | PX SEND HASH | :TQ20003 | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,03 | P->P | HASH |
| 18 | PX BLOCK ITERATOR | | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,03 | PCWC | |
| 19 | TABLE ACCESS STORAGE FULL | MSRMNT_PRD | 7671 | 84381 | 2 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 20 | PX RECEIVE | | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,06 | PCWP | |
| 21 | PX SEND HASH | :TQ20004 | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,04 | P->P | HASH |
| 22 | JOIN FILTER USE | :BF0000 | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,04 | PCWP | |
|* 23 | HASH JOIN | | 39291 | 2455K| 11396 (1)| 00:02:17 | | | Q2,04 | PCWP | |
| 24 | PART JOIN FILTER CREATE | :BF0001 | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,04 | PCWP | |
| 25 | PX RECEIVE | | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,04 | PCWP | |
| 26 | PX SEND BROADCAST | :TQ20001 | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,01 | P->P | BROADCAST |
| 27 | PX BLOCK ITERATOR | | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,01 | PCWC | |
|* 28 | TABLE ACCESS STORAGE FULL | SRC_SYST_CLNDR_DAY | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,01 | PCWP | |
|* 29 | HASH JOIN | | 1071K| 56M| 11393 (1)| 00:02:17 | | | Q2,04 | PCWP | |
| 30 | PX RECEIVE | | 153K| 5528K| 29 (4)| 00:00:01 | | | Q2,04 | PCWP | |
| 31 | PX SEND BROADCAST | :TQ20002 | 153K| 5528K| 29 (4)| 00:00:01 | | | Q2,02 | P->P | BROADCAST |
|* 32 | HASH JOIN | | 153K| 5528K| 29 (4)| 00:00:01 | | | Q2,02 | PCWP | |
| 33 | PX RECEIVE | | 1 | 24 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 34 | PX SEND BROADCAST | :TQ20000 | 1 | 24 | 2 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 35 | NESTED LOOPS | | 1 | 24 | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 36 | PX BLOCK ITERATOR | | | | | | | | Q2,00 | PCWC | |
|* 37 | TABLE ACCESS STORAGE FULL | MSRMNT_PRD | 1 | 18 | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 38 | SORT AGGREGATE | | 1 | 60 | | | | | Q2,00 | PCWP | |
| 39 | PX COORDINATOR | | | | | | | | | | |
| 40 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 60 | | | | | Q1,03 | P->S | QC (RAND) |
| 41 | SORT AGGREGATE | | 1 | 60 | | | | | Q1,03 | PCWP | |
| 42 | NESTED LOOPS | | 26850 | 1573K| 32 (7)| 00:00:01 | | | Q1,03 | PCWP | |
|* 43 | HASH JOIN | | 26850 | 1494K| 32 (7)| 00:00:01 | | | Q1,03 | PCWP | |
|* 44 | HASH JOIN | | 26850 | 1206K| 30 (7)| 00:00:01 | | | Q1,03 | PCWP | |
| 45 | PX RECEIVE | | 743 | 28977 | 3 (34)| 00:00:01 | | | Q1,03 | PCWP | |
| 46 | PX SEND BROADCAST | :TQ10001 | 743 | 28977 | 3 (34)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
|* 47 | VIEW | | 743 | 28977 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
|* 48 | ANK WINDOW BUFFER PUSHED R | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 49 | SORT GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 50 | PX RECEIVE | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 51 | PX SEND HASH | :TQ10000 | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 52 | HASH GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q1,00 | PCWP | |
| 53 | NESTED LOOPS | | 2648 | 58256 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 54 | R PX BLOCK ITERATO | | | | | | | | Q1,00 | PCWC | |
|* 55 | ORAGE FULL TABLE ACCESS ST | SRC_SYST_CLNDR_DAY | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 56 | N INDEX UNIQUE SCA | UQ_SRC_SYST_SCHEMA_LOAD_EV | 1 | 13 | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 57 | PX BLOCK ITERATOR | | 2906K| 19M| 26 (0)| 00:00:01 | | | Q1,03 | PCWC | |
| 58 | L TABLE ACCESS STORAGE FUL | AR_RSRV_SUMRY | 2906K| 19M| 26 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 59 | PX RECEIVE | | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 60 | PX SEND BROADCAST | :TQ10002 | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |
| 61 | PX BLOCK ITERATOR | | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | |
|* 62 | LL TABLE ACCESS STORAGE FU | MSRMNT_PRD | 30 | 330 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 63 | INDEX UNIQUE SCAN | UQ_SRC_SYST | 1 | 3 | 0 (0)| 00:00:01 | | | Q1,03 | PCWP | |
|* 64 | INDEX UNIQUE SCAN | UQ_CL | 1 | 6 | 0 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 65 | PX BLOCK ITERATOR | | 2906K| 36M| 26 (0)| 00:00:01 | | | Q2,02 | PCWC | |
| 66 | TABLE ACCESS STORAGE FULL | AR_RSRV_SUMRY | 2906K| 36M| 26 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 67 | PX BLOCK ITERATOR | | 72M| 1241M| 11363 (1)| 00:02:17 | 1 | LAST | Q2,04 | PCWC | |
| 68 | TABLE ACCESS STORAGE FULL | CRDT_ACCT_PRD | 72M| 1241M| 11363 (1)| 00:02:17 | 1 | 150 | Q2,04 | PCWP | |
| 69 | PX RECEIVE | | 743 | 14860 | 3 (34)| 00:00:01 | | | Q2,08 | PCWP | |
| 70 | PX SEND HASH | :TQ20007 | 743 | 14860 | 3 (34)| 00:00:01 | | | Q2,07 | P->P | HASH |
|* 71 | VIEW | | 743 | 14860 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | |
|* 72 | WINDOW NOSORT | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | |
| 73 | SORT GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | |
| 74 | PX RECEIVE | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,07 | PCWP | |
| 75 | PX SEND HASH | :TQ20005 | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,05 | P->P | HASH |
| 76 | HASH GROUP BY | | 743 | 16346 | 3 (34)| 00:00:01 | | | Q2,05 | PCWP | |
| 77 | NESTED LOOPS | | 2648 | 58256 | 2 (0)| 00:00:01 | | | Q2,05 | PCWP | |
| 78 | PX BLOCK ITERATOR | | | | | | | | Q2,05 | PCWC | |
|* 79 | TABLE ACCESS STORAGE FULL | SRC_SYST_CLNDR_DAY | 2949 | 26541 | 2 (0)| 00:00:01 | | | Q2,05 | PCWP | |
|* 80 | INDEX UNIQUE SCAN | UQ_SRC_SYST_SCHEMA_LOAD_EV | 1 | 13 | 0 (0)| 00:00:01 | | | Q2,05 | PCWP | |
| 81 | TABLE ACCESS BY INDEX ROWID | CRDT_ACCT | 1 | 9 | 0 (0)| 00:00:01 | | | Q2,08 | PCWP | |
|* 82 | INDEX UNIQUE SCAN | UQ_CRDT_ACCT | 1 | | 0 (0)| 00:00:01 | | | Q2,08 | PCWP | |
|* 83 | INDEX UNIQUE SCAN | UQ_SRC_SYST | 1 | | 0 (0)| 00:00:01 | | | Q2,08 | PCWP | |
|* 84 | TABLE ACCESS BY INDEX ROWID | SRC_SYST | 1 | 8 | 1 (0)| 00:00:01 | | | Q2,08 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("END_DT" IS NULL OR "END_DT">"M"."MSRMNT_PRD_DT")
11 - access("A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID")
14 - access("C"."MSRMNT_PRD_MNTH"=TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0')) AND "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID")
23 - access("C"."SRC_SYST_ID"="A"."SRC_SYST_ID" AND "C"."MSRMNT_PRD_ID"="A"."MSRMNT_PRD_ID")
28 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
29 - access("A"."AR_ID"="C"."ACCT_AR_ID")
32 - access("A"."MSRMNT_PRD_ID"="M"."MSRMNT_PRD_ID")
37 - storage("M"."MSRMNT_PRD_ID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("A"."MSRMNT_PRD_ID")),0)) FROM (SELECT "A"."MSRMNT_PRD_ID" "MSRMNT_PRD_ID","A"."SRC_SYST_ID"
"SRC_SYST_ID",RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC ) "RANK" FROM "BDW"."SRC_SYST_SCHEMA_LOAD_EV"
"B","BDW"."SRC_SYST_CLNDR_DAY" "A" WHERE "A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y' AND "B"."SCHEMA_NAME"='EDXF' AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND
"A"."SRC_SYST_ID"="B"."SRC_SYST_ID" GROUP BY "A"."SRC_SYST_ID",INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID")) "from$_subquery$_013","BDW"."MSRMNT_PRD" "C","BDW"."SRC_SYST"
"B","BDW"."AR_RSRV_SUMRY" "A" WHERE "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND
"A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID" AND TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206 AND "RANK"<=7))
filter("M"."MSRMNT_PRD_ID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("A"."MSRMNT_PRD_ID")),0)) FROM (SELECT "A"."MSRMNT_PRD_ID" "MSRMNT_PRD_ID","A"."SRC_SYST_ID"
"SRC_SYST_ID",RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC ) "RANK" FROM "BDW"."SRC_SYST_SCHEMA_LOAD_EV"
"B","BDW"."SRC_SYST_CLNDR_DAY" "A" WHERE "A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y' AND "B"."SCHEMA_NAME"='EDXF' AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND
"A"."SRC_SYST_ID"="B"."SRC_SYST_ID" GROUP BY "A"."SRC_SYST_ID",INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID")) "from$_subquery$_013","BDW"."MSRMNT_PRD" "C","BDW"."SRC_SYST"
"B","BDW"."AR_RSRV_SUMRY" "A" WHERE "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND
"A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID" AND TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206 AND "RANK"<=7))
43 - access("A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID")
44 - access("A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID")
47 - filter("RANK"<=7)
48 - filter(RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC )<=7)
55 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
56 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "B"."SCHEMA_NAME"='EDXF')
62 - storage(TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206)
filter(TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206)
63 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID")
64 - access("CL"."CL_ID"="M"."MSRMNT_PRD_TYPE_ID")
71 - filter("RANK"<=7)
72 - filter(RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC )<=7)
79 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
80 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "B"."SCHEMA_NAME"='EDXF')
82 - access("A"."AR_ID"="ACCT_AR_ID"(+))
83 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID")
84 - filter("B"."SRC_SYST_CODE"='ABC')
Note
-----
- dynamic sampling used for this statement (level=7)
EDIT :
Sorry for the explain plan, i cant seem to get it pasted in line. Attached the plan as a text file.
Also, the count of data in the views is as below.
View Total count Count for 1 msrmnt_prd_id
---------------------------------------------------------
ar_rsrv_mnth_v 1841892 281945
crdt_acct_mnth_v 66494145 7087369
crdt_acct_v 12258728 NA
[Updated on: Fri, 10 August 2012 04:39] Report message to a moderator |
|
|
|
| Re: Query tuning help [message #563229 is a reply to message #563227] |
Fri, 10 August 2012 05:02  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Is this running against Exadata? The presence of the STORAGE word in the plan suggests that it is.
In which case, you aren't getting much benefit from it at the moment: few scan offloads, fewer
predicate offloads, no Bloom filters at all. I suspect that the partitioning is not perfect, there
are no partitionwise joins. Lastly, views are being materialized, presumably because they
are non-mergeable.
So, if it is Exadata, you probably need to go through the process of tuning for Exadata, because
it isn't working well. Not an easy job. Apart from that, perhaps the partitioning strategy needs
assessing, and the design of the views. That isn't easy either! Those are the areas I would look
at first.
Hope this helps.
|
|
|
|
| Re: Query tuning help [message #563234 is a reply to message #563229] |
Fri, 10 August 2012 05:23 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Thanks for the quick reply John.
They were moving stuff to Exadata. So maybe this database too is in Exadata.
Its actually a production DB and we only have read access.
I will forward the points in your reply to the DBA (frankly I did not understand much from that - but i will read about)
If there is anything that can be done with the query, it would be great as I have 5 more such queries. Not expecting much of an improvement, but anything in the range of 15 minutes is also good for us now.
The query returns 281769 rows.
|
|
|
|
|
|
|
|
| Re: Query tuning help [message #563279 is a reply to message #563259] |
Fri, 10 August 2012 14:17 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
KNW,
I would run the following two sqls that take snapshots of what is being read logically and physically during the execution of your sql. Then without any doubt or complicated analysis you have the tables that are being read
in a loop that will benefit from better indexing. Then you can create indexes to reduce the physical reads and
you can create indexes to reduce the logical reads.
ENDOCP1P > @io605
INSTANCE IO_PER_MINUTE STATISTIC_NAME OBJECT_NAME
--------- ------------- ----------------------- -------------------------------
NDOCP2 1776 physical reads NFLPROD.D_1F000D5D8000010A.
NDOCP2 1776 physical read requests NFLPROD.D_1F000D5D8000010A.
NDOCP2 5040 physical read requests NFLPROD.DM_RELATION_S.
NDOCP2 117900 physical read requests NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2 119148 physical reads NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2 639924 physical reads direct NFLPROD.DM_RELATION_S.
NDOCP2 639936 physical reads NFLPROD.DM_RELATION_S.
-------------
sum 1525500
ENDOCP1P > @logical605
GIG_PER_MINUTE INSTANC STATISTIC_NAME TYPE OBJECT_NAME
-------------- ------- -------------- ----- ----------------------------
.001 NDOCP1 logical reads TABLE SYS.SEG$
.001 NDOCP1 logical reads INDEX SYS.I_OBJ4
.001 NDOCP1 logical reads INDEX SYS.I_COL3
.001 NDOCP1 logical reads TABLE SYS.JOB$
.001 NDOCP1 logical reads INDEX SYS.I_SYN2
.001 NDOCP1 logical reads INDEX SYS.I_TS#
.001 NDOCP1 logical reads INDEX SYS.I_OBJ5
.003 NDOCP1 logical reads INDEX SYS.I_COL1
.003 NDOCP1 logical reads INDEX SYS.I_COL2
.003 NDOCP1 logical reads TABLE SYS.FILE$
.004 NDOCP1 logical reads INDEX SYS.I_OBJ#
.025 NDOCP2 logical reads TABLE SYS.IND$
.034 NDOCP4 logical reads TABLE SYS.IND$
.042 NDOCP4 logical reads INDEX SYS.I_IND1
.045 NDOCP3 logical reads TABLE SYS.IND$
.048 NDOCP3 logical reads INDEX SYS.I_IND1
.048 NDOCP1 logical reads INDEX SYS.I_IND1
.053 NDOCP2 logical reads INDEX SYS.I_IND1
.104 NDOCP3 logical reads TABLE SYS.OBJ$
.104 NDOCP4 logical reads TABLE SYS.OBJ$
.107 NDOCP2 logical reads TABLE SYS.OBJ$
.116 NDOCP3 logical reads TABLE SYS.USER$
.123 NDOCP4 logical reads TABLE SYS.USER$
.135 NDOCP2 logical reads TABLE SYS.USER$
.170 NDOCP1 logical reads TABLE SYS.OBJ$
.217 NDOCP1 logical reads TABLE SYS.USER$
.258 NDOCP1 logical reads TABLE SYS.TS$
.267 NDOCP1 logical reads INDEX SYS.I_OBJ1
.422 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_R
.595 NDOCP2 logical reads TABLE SYS.TS$
.602 NDOCP2 logical reads INDEX SYS.I_OBJ1
.608 NDOCP3 logical reads TABLE SYS.TS$
.624 NDOCP4 logical reads TABLE SYS.TS$
.640 NDOCP3 logical reads INDEX SYS.I_OBJ1
.642 NDOCP4 logical reads INDEX SYS.I_OBJ1
.776 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_S
.844 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D8000010A
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000109
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000146
2.451 NDOCP2 logical reads INDEX NFLPROD.DM_SYSOBJECT_R_COMP2
----------
sum 13.421
IO605.sql looks like the following:
set termout off
set lines 160
break on report
col instance for a9
compute sum of io_per_minute break on report
drop table gv$segment_statistics1;
drop table gv$segment_statistics2;
column statistic_name format a23
create table gv$segment_statistics1 as select * from gv$segment_statistics
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
execute dbms_lock.sleep(5);
create table gv$segment_statistics2 as select * from gv$segment_statistics
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
set wrap off
set termout on
select i.instance_name instance,(b.value-a.value)*12 IO_PER_MINUTE,
a.statistic_name,
a.owner||'.'||a.object_name||'.'||a.subobject_name object_name
from gv$segment_statistics2 b,gv$segment_statistics1 a,gv$instance i
where a.statistic_name=b.statistic_name
and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and a.inst_id=b.inst_id and a.object_type=b.object_type
and a.owner||'.'||a.object_name||'.'||a.subobject_name=
b.owner||'.'||b.object_name||'.'||b.subobject_name
and upper(a.statistic_name) like '%PHYSICAL%'
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
set termout on
logical605.sql looks like the following:
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
column statistic_name format a14
column object_name format a50
column object_type format a11
column gig_per_minute format 9999.999
column type format a5
column instance format a7
SET WRAP OFF
SET TRUNC OFF
SET LINES 200
break on report
compute sum of gig_per_minute break on report
compute sum of buf_per_min break on report
compute sum of buf_per_sec break on report
create table gv$segment_statistic1 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
execute dbms_lock.sleep(5);
create table gv$segment_statistic2 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
set wrap off
set pages 50
set termout on
select b.value last, a.value first,(b.value-a.value)*12 buf_per_min,
(b.value-a.value)*12/60 buf_per_sec,
(b.value-a.value)*12*8192/1024/1024/1024*1 GIG_PER_MINUTE,
i.instance_name instance,a.statistic_name,a.object_type type,
a.owner||'.'||a.object_name||' '||a.subobject_name object_name
from gv$segment_statistic2 b,gv$segment_statistic1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=b.inst_id
and b.inst_id=i.inst_id and a.inst_id=i.inst_id
and a.object_type=b.object_type
and a.owner||'.'||a.object_name||' '||a.subobject_name
=b.owner||'.'||b.object_name||' '||b.subobject_name
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
set termout on
|
|
|
|
| Re: Query tuning help [message #563344 is a reply to message #563279] |
Mon, 13 August 2012 01:47 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Hi Alan,
I wont be able to use the script you posted as it involves drop/create tables. Its a production DB and we got only read access.
I have repeatedly made requests to be able to query the base tables directly, but I am just a humble developer who does not understand the system or need for views (or so they say). There is not much being done in the views anyway ! But I am stuck with them for now.
Thanks.
|
|
|
|
| Re: Query tuning help [message #563435 is a reply to message #563344] |
Mon, 13 August 2012 18:12 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
You can look at the physical and logical reads the last hour by the following with selects only but you will need "select any dictionary" privilege.
ECSCDAD3 > grant select any dictionary to alan;
Grant succeeded.
ECSCDAD3 > connect alan/alan@cscdad3;
Connected.
ALAN > @logical
DATE LOGICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ----------------------- --------------------
2012-08-14 Tuesday 448 SCHEDULER$_EVENT_LOG
2012-08-14 Tuesday 464 SYS_C0011404
2012-08-14 Tuesday 576 OBJ$
2012-08-14 Tuesday 864 TAG
2012-08-14 Tuesday 1584 JOB$
2012-08-14 Tuesday 1632 WRH$_SEG_STAT_OBJ_PK
2012-08-14 Tuesday 1760 AUDIO_VIDEO
2012-08-14 Tuesday 2048 I_SYSAUTH1
2012-08-14 Tuesday 22160 CONTENT
ALAN > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.LOGICAL_READS_DELTA) LOGICAL_READS_LAST_HOUR,
3 a.object_name
4 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
5 where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
6 and a.object_id=b.OBJ#
7 and b.LOGICAL_READS_DELTA>0
8 and c.instance_number=(select instance_number from v$instance)
9 and c.snap_id=b.snap_id
10 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
11* order by 2
ALAN > @physical
DATE PHYSICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ------------------------ --------------------
2012-08-14 Tuesday 1 WRH$_COMP_IOSTAT_PK
ALAN > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.PHYSICAL_READS_DELTA) PHYSICAL_READS_LAST_HOUR,
3 a.object_name
4 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
5 where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
6 and a.object_id=b.OBJ#
7 and b.PHYSICAL_READS_DELTA>0
8 and c.instance_number=(select instance_number from v$instance)
9 and c.snap_id=b.snap_id
10 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
11* order by 2
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 15:28:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
