Home » SQL & PL/SQL » SQL & PL/SQL » not gettting output for showplanfrpspreadsheetcode11g (Oracle 12c, Solaris SPARC)
not gettting output for showplanfrpspreadsheetcode11g [message #663917] |
Mon, 26 June 2017 01:10 |
|
Gurubalan
Messages: 2 Registered: January 2017
|
Junior Member |
|
|
Hi All,
I've bought the book "Oracle SQL performance Tuning and Optimization" by Kevin. I am not getting the output as expected below while running showplanfrpspreadsheecode11g.
Plan Filtered Actual
ID TABLE_NAME NUM_ROWS ROWCOUNT Cardinality Cardinality FRP
Here is what I did:
-------------------------------
sys@GDB> explain plan for select * from dept where deptno = 40;
Explained.
Elapsed: 00:00:00.01
sys@GDB> @showplanfrpspreadsheetcode11g
SQLTEXT
------------------------------------------------------------------------------------------------------------------------------------
with
frp_data as (
select ' 1' id,'SYS' table_owner,'DEPT' table_name,'DEPT' table_alias,100000 num_rows,count(*) rowcount,1000
cardinality,count(case when "DEPTNO"=40 then 1 end) filtered_cardinality from SYS.DEPT DEPT union all
select null,null,null,null,null,null,null,null from dual
)
select frp_data.*,round(frp_data.filtered_cardinality/case when frp_data.rowcount = 0 then cast(null as number) else frp_data.rowcou
nt end*100,1) actual_frp,decode(frp_data.filtered_cardinality,null,cast(null as number),round(frp_data.cardinality/case when frp_dat
a.num_rows = 0 then cast(null as number) else frp_data.num_rows end*100,1)) plan_frp
from frp_data
where id is not null
order by frp_data.id
/
10 rows selected.
Elapsed: 00:00:00.01
sys@GDB>
------
Am I missing something?
Thanks,
Guru.
|
|
|
|
Re: not gettting output for showplanfrpspreadsheetcode11g [message #663927 is a reply to message #663918] |
Mon, 26 June 2017 06:19 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Hi. My thanks to Barbara for letting me know there was a open question.
So that gen... script is a code generator. When it works correctly, it is supposed to produce code, which it certainly appears to have done for you. You run that code and you should get a FRP (Filtered Rows Percentage) spreadsheet. Here is the example from the book chapter #1.
Plan Filtered Actual
ID TABLE_NAME NUM_ROWS ROWCOUNT Cardinality Cardinality FRP
----- ------------- ---------- ---------- ----------- ----------- ------
8 EMP_DIM 6243035 6243035 240117 215414 3.5
9 EMP_LOC_DIM 329699 329699 296337 329699 100.0
15 EMPLR_LOC_DIM 8874 8874 8874 8872 100.0
19 EMP_DIM 6243035 6243035 240117 236469 3.8
21 EMP_LOC_DIM 329699 329699 251761 212993 64.6
5 rows selected.
Notice the different columns please. The ID line from the plan where a table will be feeding rows into query execution (in some form, consider a full index scan that never touches the table, is still giving you data from that table), the table name, then the metrics. These are taken from different places (DBA_TABLES, EMP table, PLAN_TABLE, EMP table filtering query). So you need to do two things:
1. examine if you are interested, in the details inside the generated code. Try to see how the different pieces generate the data you will see.
2. run the FRP query and see what it gives you.
Looking at the first part of your generated FRP query:
select ' 1' id,'SYS' table_owner,'DEPT' table_name,'DEPT' table_alias,100000 num_rows,count(*) rowcount,1000
cardinality,count(case when "DEPTNO"=40 then 1 end) filtered_cardinality from SYS.DEPT DEPT union all
We see that:
. table DEPT (which apparently is owned by SYS (naughty naughty))
. has 100,000 rows (so says dba_tables.num_rows)
. the FRP generated code plans to count the actual rows on the table by using COUNT(*), so you can compare that to num_rows to gauge accuracy of stats.
. your query plan line#1 thinks the filtering against DEPT (DEPTNO=40) will result in 1000 rows being returned
. the FRP generated code plans to count the number of rows returned after filtering by applying your filter criteria as seen noted in the plan table
Consider these queries.
select owner,table_name,num_rows NUM_ROWS from dba_tables where owner = 'SYS' and table_name = 'DEPT';
select 'SYS','DEPT',count(*) ROWCOUNT from sys.dept;
select id,object_owner,object_name,cardinality PLAN_CARDINALITY from plan_table order by line;
select 'SYS','DEPT',COUNT(*) FILTERED_CARDINALITY from sys.dept where deptno = 40;
These four queries are the basis of the FRP Spreadsheet. The generating code (the script) does queries #1/#3, and the generated code (what you got) will do queries #2/#4 and put the results together to give you the spreadsheet, calculating the % column at that time.
If you think there might be a bug somewhere (which is possible) try this first. Clearing out the plan table so that you only have one plan it when you generate code "might" help. I don't have to do it but early versions of the generator had an issue.
delete from plan_table;
explain plan for select * from dept where deptno = 40;
@showplanfrpspreadsheetcode11g -- or whatever is the current code you have.
All this is explained on chapter#1 as I recall. Good luck. Hope this gives you the answer you needed. Kevin
|
|
|
Re: not gettting output for showplanfrpspreadsheetcode11g [message #663956 is a reply to message #663927] |
Wed, 28 June 2017 00:05 |
|
Gurubalan
Messages: 2 Registered: January 2017
|
Junior Member |
|
|
Thank you Kevin for your quick response. I appreciate it.
I agree that you have given a detailed information on how we can populate the spreadsheet by running individual sql query (which I'm following now) without running showplanfrpspreadsheetcode11g. The code generator makes things easier as it provides populated data in the form of spreadsheet. As you've mentioned, I'll run the part of script and see how it works.
thanks for your time again.
Regards,
Guru.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:39:09 CDT 2024
|